[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) --//其它大家可以自行测试。