隐式转换在执行计划中对Access predicates 和Filter predicates 的影响

In Oracle 10g, when we see the explain plan for a SQL, then there are 2 new items that are of interest - the 'Access predicate' and the 'Filter predicate'.

We know that depending on the predicate information ('where clause') the Oracle optimizer chooses the optimal path for executing the query.

 

So the 'access predicate' information tells us how the Oracle optimiser is accessing the rows of the table - i.e. if the optimizer is using an index, then what predicate made it use the same.

 

The 'filter predicate' tells us what criteria Oracle is using to filter rows from the returned rowset (the rowset was fetched based on the 'access predicate') .

 

例如：

准备数据：表gy_yingyong（数据量较少）中yingyongid是字符类型，并且在yingyongid上建立索引。

 

实验1：如果在where中条件对yingyongid用字符'0501'查询

 

SQL> select * from gy_yingyong WHERE yingyongid = '0501';

 

Elapsed: 00:00:00.06

 

Execution Plan

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

Plan hash value: 3643091036

 

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

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                |     1 |    99 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| GY_YINGYONG    |     1 |    99 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_GY_YINGYONG |     1 |       |     0   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("YINGYONGID"='0501')

 

 

Statistics

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

        751  recursive calls

          0  db block gets

        148  consistent gets

          0  physical reads

          0  redo size

       1933  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         13  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

 

对测试结果的猜测：查询条件是字符，所以没有隐式转换，通过索引查找数据，故用Access predicates

 

实验2：如果在where中条件对yingyongid用数字0501查询

 

SQL> select * from gy_yingyong WHERE yingyongid = 0501;

 

Elapsed: 00:00:00.04

 

Execution Plan

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

Plan hash value: 2286127959

 

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

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |             |     1 |    99 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| GY_YINGYONG |     1 |    99 |     3   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter(TO_NUMBER("YINGYONGID")=0501)

 

 

Statistics

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

       1172  recursive calls

          0  db block gets

        240  consistent gets

          0  physical reads

          0  redo size

       1933  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         27  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

 

对测试结果的猜测：查询条件是数字，与列yingyongid的类型不符，所以隐式转换，To_Number（”YINGYONGID”）=0501会导致索引失效（字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换），只能全表扫描，然后再用Filter predicates 过滤数据。

参考：https://method-r.fogbugz.com/default.asp?method-r.11.552.2

      http://stackoverflow.com/questions/1464469/access-and-filter-predicates-in-oracle-execution-plan