Oraclecreateindexon(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，如何转换请看 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
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。