oracle 中-%3e的作用,Oracle create index on (colname,零)的意义

Oracle create index on (colname,0)的意义

今天看到一个索引，create index index_name tableanme(colname,0),很奇怪，以前没见过。经过测试发现就是一个相对于联合索引的东西，没有任何意义，就是建错了。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

drop table test;

create table test as select * from dba_objects;

SQL> select count(1) from test t where t.object_id =20;

COUNT(1)

----------

1

SQL> select object_id from dba_objects s where s.object_name='IND_T_OBJECT_ID';

OBJECT_ID

----------

107156

SQL> alter session set events  'immediate trace name treedump level 107156';

在Oracle的trace目录下找到trace文件

----- begin tree dump

branch: 0x20fddc3 34594243 (0: nrow: 104, level: 1)

leaf: 0x20fddc4 34594244 (-1: nrow: 814 rrow: 814)

leaf: 0x20fddc5 34594245 (0: nrow: 808 rrow: 808)

leaf: 0x20fddc6 34594246 (1: nrow: 808 rrow: 808)

leaf: 0x20fddc7 34594247 (2: nrow: 808 rrow: 808)

leaf: 0x248381c 38287388 (3: nrow: 808 rrow: 808)

leaf: 0x248381d 38287389 (4: nrow: 808 rrow: 808)

leaf: 0x248381e 38287390 (5: nrow: 808 rrow: 808)

leaf: 0x248381f 38287391 (6: nrow: 808 rrow: 808)

..................................................

..................................................

leaf: 0x2483832 38287410 (42: nrow: 766 rrow: 766)

leaf: 0x2483833 38287411 (43: nrow: 766 rrow: 766)

leaf: 0x20fdddd 34594269 (45: nrow: 766 rrow: 766)

..................................................

..................................................

SQL> select dbms_utility.data_block_address_file(34594269) "file",

dbms_utility.data_block_address_block(34594269) "block"

from dual;

file      block

---------- ----------

8    1039837

SQL> alter system dump datafile 8 block 1039837;

在Oracle的trace目录下找到trace文件，c1 15就是20，如何转换请看http://blog.csdn.net/stevendbaguo/article/details/8010105 。 col0是object_id,col1是20，col2是rowid。

row#0[16207] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3e 62

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 4a

row#1[16190] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3e 63

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 4b

row#2[16173] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3e 64

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 4c

row#3[16157] flag: ------, lock: 0, len=16

col 0; len 3; (3):  c3 04 3f

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 4d

row#4[16140] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 02

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 4e

row#5[16123] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 03

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 4f

row#6[16106] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 04

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 50

row#7[16089] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 05

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 51

row#8[16072] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 06

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 52

row#9[16055] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 07

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 53

row#10[16038] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 08

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 54

row#11[16021] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 09

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 55

row#12[16004] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 0a

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 56

row#13[15987] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 0b

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 57

row#14[15970] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 0c

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 58

row#15[15953] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 0d

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 59

row#16[15936] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 0e

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 5a

row#17[15919] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 0f

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 5b

row#18[15902] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 10

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 5c

row#19[15885] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 11

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 5d

row#20[15868] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 12

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 5e

row#21[15851] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 13

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 5f

row#22[15834] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 14

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 60

row#23[15817] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 15

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 61

row#24[15800] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 16

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 62

row#25[15783] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 17

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 63

row#26[15766] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 18

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 64

row#27[15749] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 19

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 65

row#28[15732] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 1a

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 66

row#29[15715] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 1b

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 67

row#30[15698] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 1c

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 68

row#31[15681] flag: ------, lock: 0, len=17

col 0; len 4; (4):  c3 04 3f 1d

col 1; len 2; (2):  c1 15

col 2; len 6; (6):  01 58 2e c1 00 69