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

本文链接：https://blog.csdn.net/rudyMatrix/article/details/5265521

本文探讨了在Oracle 10g中，查询执行计划中的Access Predicate和Filter Predicate如何影响查询路径。通过两个实验展示了当查询条件为字符和数字时，对含有字符类型索引字段的查询如何选择访问方式。实验表明，字符条件能有效利用索引，而数字条件会触发隐式转换导致全表扫描。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

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

| 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

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

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

| 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 过滤数据。