Example
create table tq84_tab(
nm number,
tx varchar2(10)
);
explain plan for
select
max(nm) max_nm,
tx
from
tq84_tab
group by
tx
having
max(nm) in (2,3,4)
;
select * from table(dbms_xplan.display(format => 'basic predicate'));
--
-- ----------------------------------------
-- | Id | Operation | Name |
-- ----------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | FILTER | |
-- | 2 | HASH GROUP BY | |
-- | 3 | TABLE ACCESS FULL| TQ84_TAB |
-- ----------------------------------------
--
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--
-- 1 - filter(MAX("NM")=2 OR MAX("NM")=3 OR MAX("NM")=4)
The following query is semantically equal to the previous one and produces the same plan
explain plan for
select * from (
select
max(nm) max_nm,
tx
from
tq84_tab
group by
tx
)
where
max_nm in (2,3,4)
;
Cleaning up:
drop table tq84_tab;
Pecularities in the PLAN_TABLE
select *
from
v$sql_plan
where
operation = 'FILTER' and (
--
object_name is not null or
object_owner is not null or
object_alias is not null or
object_type is not null or
--
cost is not null or
cpu_cost is not null or
io_cost is not null or
--
optimizer is not null or
cardinality is not null or
bytes is not null or
--
partition_start is not null or
partition_stop is not null or
partition_id is not null or
--
other is not null or
distribution is not null or
--
temp_space is not null or
access_predicates is not null or
time is not null or
remarks is not null
-- filter_predicates is not null or
-- object_node is not null
)
;