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