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

落日映苍穹つ 2022-08-06 13:12 154阅读 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