[20211231]函式索引測試.txt

[20211231]函式索引測試.txt

--//開發在sql語句中使用10000000+ztbh之類的表示式,必須建立函式索引,測試看看ztbh+10000000之類的索引是否還可以使用.

1.環境：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試：
create table deptx as select * from dept;

SCOTT@book> create index if_deptx_dept on deptx (1000+deptno);
Index created.

SCOTT@book> select * from deptx where deptno+1000=1010;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5r5un34cwmsu5, child number 0
-------------------------------------
select * from deptx where deptno+1000=1010
Plan hash value: 4094583743
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |        |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPTX         |      1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IF_DEPTX_DEPT |      1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPTX@SEL$1
   2 - SEL$1 / DEPTX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTX"."SYS_NC00004$"=1010)
        
SCOTT@book> select * from deptx where 1000+deptno=1010;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  52r3nzuy9tjx8, child number 0
-------------------------------------
select * from deptx where 1000+deptno=1010
Plan hash value: 4094583743
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |        |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPTX         |      1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IF_DEPTX_DEPT |      1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPTX@SEL$1
   2 - SEL$1 / DEPTX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTX"."SYS_NC00004$"=1010)

--//可以使用我建立的函式索引。
select * from deptx where 999+1+deptno=1010;
select * from deptx where deptno+1e3=1010;
select * from deptx where 1e3+deptno=1010;

--//也可以使用。但是減0，減其它不行,例子：
SCOTT@book> select * from deptx where 1e3+deptno-0=1010;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7j57j3ybzmfk9, child number 0
-------------------------------------
select * from deptx where 1e3+deptno-0=1010
Plan hash value: 428208148
----------------------------------------------------------------------------
| Id  | Operation         | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| DEPTX |      1 |    24 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPTX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(1000+"DEPTNO"-0=1010)

--//其它大家可以自行測試。