[20140512]关于降序索引.txt
https://jonathanlewis.wordpress.com/2014/05/07/quiz-night-23/
提到建立降序索引，会出现建立唯一索引出现促错误的问题，自己做一个测试了解一些细节：
1.测试环境：
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t (id number,name varchar2(10));
insert into t values (1,'aaaaaa');
insert into t values (2,'bbbbbb');
insert into t values (null,null);
insert into t values (null,null);
commit ;
2.可以发现建立唯一索引，但是如果使用降序索引就不行。
SCOTT@test> create unique index i_t_id on t(id);
Index created.
SCOTT@test> create unique index i_t_id on t(id desc);
create unique index i_t_id on t(id desc)
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
--可以发现建立降序索引失败。
3.参见链接：https://jonathanlewis.wordpress.com/2014/05/07/quiz-night-23/
Answer
Well it didn't take long for an answer and several bits of related infomration to show up – as Martin pointed out, all
I have to do is insert NULL into the table twice.
To create an entry in a descending index, Oracle takes the 1's-complement of each column and appends an 0xFF byte to
each column – except in the case of a null column where the null is replaced with a 0x00. (And, as Sayan points out,
funny things happen if you have a varchar2() column which has already reached the 4,000 byte limit)
The point of the 1's-complement is that if you walk through the stored values in ascending order you're walking through
the original values in descending – provided you have the 0xFF on the end of each non-null entry.
4.看看降序索引是如何保存的.
SCOTT@test> create index i_t_id on t(id desc);
Index created.
SCOTT@test> create index i_t_name on t(name desc);
Index created.
SCOTT@test> select segment_name,header_file,header_block from dba_segments where owner=user and segment_name in ('I_T_ID','I_T_NAME');
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------- ----------- ------------
I_T_NAME 4 1426
I_T_ID 4 634
--记录很少，仅仅只有一个索引根节点。
SCOTT@test> alter system dump datafile 4 block 635;
System altered.
SCOTT@test> alter system dump datafile 4 block 1427;
System altered.
--看看里面保存的信息：
Block header dump: 0x0100027b
Object id on Block? Y
seg/obj: 0x45d9b csc: 0x00.c2f0184a itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000278 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.c2f0184a
Leaf block dump
===============
header address 182927221348=0x2a974fe264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 7984=0x1f30
kdxcoavs 7940
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8021] flag: ------, lock: 0, len=11
col 0; len 1; (1): 00
col 1; len 6; (6): 01 00 02 73 00 02
row#1[8010] flag: ------, lock: 0, len=11
col 0; len 1; (1): 00
col 1; len 6; (6): 01 00 02 73 00 03
row#2[7997] flag: ------, lock: 0, len=13
col 0; len 3; (3): 3e fc ff
col 1; len 6; (6): 01 00 02 73 00 01
row#3[7984] flag: ------, lock: 0, len=13
col 0; len 3; (3): 3e fd ff
col 1; len 6; (6): 01 00 02 73 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 635 maxblk 635
Block header dump: 0x01000593
Object id on Block? Y
seg/obj: 0x45d9c csc: 0x00.c2f0189c itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000590 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.c2f0189c
Leaf block dump
===============
header address 182927221348=0x2a974fe264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 7976=0x1f28
kdxcoavs 7932
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8021] flag: ------, lock: 0, len=11
col 0; len 1; (1): 00
col 1; len 6; (6): 01 00 02 73 00 02
row#1[8010] flag: ------, lock: 0, len=11
col 0; len 1; (1): 00
col 1; len 6; (6): 01 00 02 73 00 03
row#2[7993] flag: ------, lock: 0, len=17
col 0; len 7; (7): 9d 9d 9d 9d 9d 9d ff
col 1; len 6; (6): 01 00 02 73 00 01
row#3[7976] flag: ------, lock: 0, len=17
col 0; len 7; (7): 9e 9e 9e 9e 9e 9e ff
col 1; len 6; (6): 01 00 02 73 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 1427 maxblk 1427
---可以发现NULL无论number还是varchar2，都是00，在降序索引里面。
SCOTT@test> select dump('aaaaaa',16),dump('bbbbbb',16) from dual ;
DUMP('AAAAAA',16) DUMP('BBBBBB',16)
------------------------------- -------------------------------
Typ=96 Len=6: 61,61,61,61,61,61 Typ=96 Len=6: 62,62,62,62,62,62
SCOTT@test> @16to10 61
16 to 10 DEC
------------
97
SCOTT@test> @16to10 9e
16 to 10 DEC
------------
158
SCOTT@test> select 97+158 from dual ;
97+158
----------
255
--可以发现相当于使用0xff与name异或，然后在后面加0xff.看看数字：
row#3[7984] flag: ------, lock: 0, len=13
col 0; len 3; (3): 3e fd ff
col 1; len 6; (6): 01 00 02 73 00 00
SCOTT@test> select dump(1,16),dump(1,10),dump(2,16),dump(2,10) from dual ;
DUMP(1,16) DUMP(1,10) DUMP(2,16) DUMP(2,10)
----------------- ------------------ ----------------- ------------------
Typ=2 Len=2: c1,2 Typ=2 Len=2: 193,2 Typ=2 Len=2: c1,3 Typ=2 Len=2: 193,3
SCOTT@test> @16to10 3e
16 to 10 DEC
------------
62
SCOTT@test> @16to10 fd
16 to 10 DEC
------------
253
-- 62+193=255
-- 2+253=255
-- 可以发现数字性也是一样，使用0xff与id异或，然后在后面加0xff.
5.虽然降序索引是这样建立，但是如果这样查询依旧不会使用。
SCOTT@test> execute dbms_stats.gather_table_stats(user,'t',cascade=>true);
PL/SQL procedure successfully completed.
SCOTT@test> select /*+ index(t,i_t_id) */ count(*) from t ;
COUNT(*)
----------
4
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-----------------------------------------------------------
SQL_ID 92v6cqgrkpkw2, child number 0
-------------------------------------
select /*+ index(t,i_t_id) */ count(*) from t
Plan hash value: 2966233522
---------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS FULL| T | 4 | 3 (0)|
---------------------------------------------------------
--建立函数索引看看。
SCOTT@test> create index if_t_id on t(id,0);
Index created.
SCOTT@test> select /*+ index(t,if_t_id) */ count(*) from t ;
COUNT(*)
----------
4
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2tawnyjhj1ufb, child number 0
-------------------------------------
select /*+ index(t,if_t_id) */ count(*) from t
Plan hash value: 428201273
----------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX FULL SCAN| IF_T_ID | 4 | 1 (0)|
----------------------------------------------------------