[20180509]函数索引问题.txt
https://jonathanlewis.wordpress.com/2018/05/07/fbis-dont-exist/
--//重复测试:
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
create table t1 as
select
rownum id,
dbms_random.value(0,500) n1,
rpad('x',10) small_vc,
rpad('x',100) padding
from
all_objects
where
rownum <= 3000
;
create index t1_i1 on t1(id, trunc(n1));
2.测试:
SCOTT@book> set autotrace traceonly explain
SCOTT@book> select /*+ rule */ small_vc from t1 where id = 55 and trunc(n1) between 1 and 10 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T1 |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC("N1")<=10 AND TRUNC("N1")>=1 AND "ID"=55)
Note
-----
- rule based optimizer used (consider using cbo)
SCOTT@book> set autotrace off
--//并没有出现作者的情况在11.2.0.4版本.
3.换一种方式:
SCOTT@book> Select /*+ rule */ small_vc from t1 where id = 55 and trunc(n1) between 1 and 10 ;
no rows selected
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID avqjpb1yh1pq4, child number 0
-------------------------------------
Select /*+ rule */ small_vc from t1 where id = 55 and trunc(n1) between 1 and 10
Plan hash value: 3617692013
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T1 |
----------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((TRUNC("N1")<=10 AND TRUNC("N1")>=1 AND "ID"=55))
Note
-----
- rule based optimizer used (consider using cbo)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
--//在11.2.0.3测试依旧全表扫描
4.换1个版本测试:
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
create table t1 as
select
rownum id,
dbms_random.value(0,500) n1,
rpad('x',10) small_vc,
rpad('x',100) padding
from
all_objects
where
rownum <= 3000
;
create index t1_i1 on t1(id, trunc(n1));
SCOTT@test> Select /*+ rule */ small_vc from t1 where id = 55 and trunc(n1) between 1 and 10 ;
no rows selected
SCOTT@test> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID avqjpb1yh1pq4, child number 0
-------------------------------------
Select /*+ rule */ small_vc from t1 where id = 55 and trunc(n1) between
1 and 10
Plan hash value: 1429545322
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 |
|* 2 | INDEX RANGE SCAN | T1_I1 |
---------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
2 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((TRUNC("N1")<=10 AND TRUNC("N1")>=1))
2 - access("ID"=55)
Note
-----
- rule based optimizer used (consider using cbo)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
SCOTT@test> set autot traceonly
SCOTT@test> Select /*+ rule */ small_vc from t1 where id = 55 and trunc(n1) between 1 and 10 ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1429545322
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 |
|* 2 | INDEX RANGE SCAN | T1_I1 |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC("N1")<=10 AND TRUNC("N1")>=1)
2 - access("ID"=55)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
325 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
--//可以发现在这个版本下10.2.0.3 使用rule提示会使用函数索引.
