Predicate Information

Oracle SQL执行计划中出现未知PredicateInformation原因探究

最新推荐文章于 2023-05-19 09:36:55 发布

浅笑雨忆

最新推荐文章于 2023-05-19 09:36:55 发布

阅读量2.5k

点赞数

分类专栏： sql优化文章标签： date access table filter sql oracle

sql优化专栏收录该内容

7 篇文章

订阅专栏

本文深入探讨了在Oracle数据库环境中，如何理解并解释SQL执行计划中出现的未知PredicateInformation现象，以及如何排查导致这种现象的原因。

今天看SQL执行计划时发现一条额外的Predicate Information。一时找不到该Predicate Information来自何处。一般情况下对Oracletable或者View增加行授权（即调用dbms_rls.add_policy创建policy后），对该表的查询会自动增加Predicate Information。
   但是这次查找DBA_POLICIES视图也没找到相关policy。不知道大家有没遇到这种情况。

附上代码如下：
EXPLAIN PLAN FOR
SELECT EXPIRATION_DATE, FREEZE_FLAG, LAST_COUNT_DATE, LOCATION_TYPE,
   QTY_IN_TRANSIT, QTY_ONHAND, QTY_RESERVED, RECEIPT_DATE,
   AVAILABILITY_CONTROL_ID, ROTABLE_PART_POOL_ID
FROM INVENTORY_PART_IN_STOCK_TAB
WHERE PART_NO = :B8 AND CONTRACT = :B7
AND CONFIGURATION_ID = :B6 AND LOCATION_NO = :B5
AND LOT_BATCH_NO = :B4 AND SERIAL_NO = :B3
AND ENG_CHG_LEVEL = :B2 AND WAIV_DEV_REJ_NO = :B1
;
COMMIT;
SELECT *
FROM TABLE(dbms_xplan.display)
;
执行计划及Predicate Information如下：
Plan hash value: 1802187973

------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                      | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |                            |    1 | 95 |    4 (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| INVENTORY_PART_IN_STOCK_TAB  |    1 | 95 |    4 (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN       | INVENTORY_PART_IN_STOCK_PX01 |    3 |    |    3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("PART_NO"=:B8 AND "LOCATION_NO"=:B5 AND "CONTRACT"=:B7 AND "ENG_CHG_LEVEL"=:B2 AND
            SUBSTR("INVENTORY_PART_IN_STOCK_TAB"."PART_NO",1,17)=SUBSTR(:B8,1,17) AND "CONFIGURATION_ID"=:B6
            AND "LOT_BATCH_NO"=:B4 AND "SERIAL_NO"=:B3)
2 - access("WAIV_DEV_REJ_NO"=:B1)

其中SUBSTR("INVENTORY_PART_IN_STOCK_TAB"."PART_NO",1,17)=SUBSTR(:B8,1,17)
为额外的谓词信息。

这条谓词信息足以改变该SQL的执行计划，从原本主键的INDEX UNIQUE SCAN
变成一个普通索引的INDEX RANGE SCAN

table, 计划, Oracle, View, 调用