今天看到一個索引，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;
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
