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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2221532/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2221532/