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