oracle 函数索引替换,[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提示会使用函数索引.