[20180509]函數索引問題.txt

[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

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$1 / T1@SEL$1

   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

------------------------------ -------------- ----------------------------------------------------------------

x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

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 ;

SCOTT@test> @ &r/dpc '' ''

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)

       * 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 ;

|   0 | SELECT STATEMENT            |       |

   1 - filter(TRUNC("N1")<=10 AND TRUNC("N1")>=1)

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