[20180509]函式索引問題.txt

[20180509]函式索引問題.txt

--//重複測試:

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提示會使用函式索引.