[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)|
----------------------------------------------------------