Oracle create index on (colname,0)的意義，oraclecolname
今天看到一個索引，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
