[20181124]關於降序索引問題4.txt

[20181124]關於降序索引問題4.txt

--//連續寫3篇關於降序索引相關問題,連結:
http://blog.itpub.net/267265/viewspace-2221425/
http://blog.itpub.net/267265/viewspace-2221527/
http://blog.itpub.net/267265/viewspace-2221529/

--//我自己還有一個小疑問,沒有答案,在解答前,自己在測試如果插入字串lpad('a',3999,'a')||chr(0),降序索引如何儲存鍵值的.

1.環境:
SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.測試:
create table t (id number,name varchar2(4000));
insert into t values (1,lpad('a',3999,'a')||chr(0));
insert into t values (2,lpad('a',3999,'a')||chr(1));
commit ;

SCOTT@test01p> create  index if_t_name on t(name desc);
create  index if_t_name on t(name desc)
                             *
ERROR at line 1:
ORA-01706: user function result value was too large

D:\tools\rlwrap>oerr ora 1706
oerr ora 1706
01706, 00000, "user function result value was too large"
// *Cause:
// *Action:

--//應該是長度超出範圍.

3.繼續測試:
SCOTT@test01p> delete from t;
2 rows deleted.

SCOTT@test01p> commit ;
Commit complete.

insert into t values (1,'aaaaa');
insert into t values (2,'bbbbb');
commit;

SCOTT@test01p> create index if_t_name on t(name desc);
Index created.

SCOTT@test01p> create index if_t_namex on t(sys_op_descend(name));
create index if_t_namex on t(sys_op_descend(name))
                             *
ERROR at line 1:
ORA-01408: such column list already indexed
--//可以發現實際上降序索引,就是建立sys_op_descend(name)的索引.

SCOTT@test01p> alter session set statistics_level=all;
Session altered.

SCOTT@test01p> select * from t where name='aaaaa';

        ID NAME
---------- --------------------
         1 aaaaa

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5xp1axyac2pgj, child number 0
-------------------------------------
select * from t where name='aaaaa'
Plan hash value: 4146574435
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T         |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN                  | IF_T_NAME |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."SYS_NC00003$"=HEXTORAW('9E9E9E9E9EFF'))
       filter(SYS_OP_UNDESCEND("T"."SYS_NC00003$")='aaaaa')

--//我有點不理解的是為什麼還有加一層fliter.不理解.

SCOTT@test01p> drop index if_t_name;
Index dropped.

SCOTT@test01p> create index if_t_namex on t(sys_op_descend(name));
Index created.

SCOTT@test01p> select * from t where sys_op_descend(name)=hextoraw('9E9E9E9E9EFF');
        ID NAME
---------- --------------------
         1 aaaaa

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cvyst4uqj6rxy, child number 0
-------------------------------------
select * from t where sys_op_descend(name)=hextoraw('9E9E9E9E9EFF')
Plan hash value: 3955378873
---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |        |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T          |      1 |     9 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IF_T_NAMEX |      1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."SYS_NC00003$"=HEXTORAW('9E9E9E9E9EFF'))

--//噢.明白了.實際上如果這樣寫sys_op_descend(name)=hextoraw('9E9E9E9E9EFF')查詢條件,就不會存在過濾了.

SCOTT@test01p> drop index if_t_namex;
Index dropped.

SCOTT@test01p> create index if_t_name on t(name desc);
Index created.


SCOTT@test01p> select * from t where sys_op_descend(name)=hextoraw('9E9E9E9E9EFF');
        ID NAME
---------- --------------------
         1 aaaaa

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cvyst4uqj6rxy, child number 1
-------------------------------------
select * from t where sys_op_descend(name)=hextoraw('9E9E9E9E9EFF')
Plan hash value: 4146574435

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T         |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN                  | IF_T_NAME |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."SYS_NC00003$"=HEXTORAW('9E9E9E9E9EFF'))

--//也可以這樣寫:

SCOTT@test01p> select * from t where sys_op_descend(name)=hextoraw(sys_op_descend('aaaaa'));
        ID NAME
---------- --------------------
         1 aaaaa

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8m2ns5w7bk60d, child number 0
-------------------------------------
select * from t where sys_op_descend(name)=hextoraw(sys_op_descend('aaaa
a'))
Plan hash value: 4146574435
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T         |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN                  | IF_T_NAME |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."SYS_NC00003$"=HEXTORAW('9E9E9E9E9EFF'))