OCP Blog
Super DBA
Concatenation and filter option in SQL PLAN
by Vazha Mantua Friday, April 8, 2011 8:38 PM
Hello All,
For example we have this statement:
select count(*) from customers
where acc_nbr = nvl(:v,acc_nbr)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=137 Card=1 Bytes=7)
   1    0   SORT (AGGREGATE)
   2    1     CONCATENATION
   3    2       FILTER
   4    3         INDEX (FAST FULL SCAN) OF 'CUST_PK' (UNIQUE) (Cost
   5    2       FILTER
   6    5         INDEX (UNIQUE SCAN) OF 'CUST_PK' (UNIQUE) (Cost=2
In execution plan we see 2 filters and then concatenation. Reason of this is nvl comand, Oracle CBO rule . We have 2 possible scenario bind variable :v is null or is not null.
but for RBO rule change sql plan and filters and concatenation is not appear in SQL PLAN
Many examples say that in this case that CBO is better than RBO(Rule hint), but sometimes RBU rule is better.
How can we avoid 2 filters in concatenation,simple we have 2 solutions:
select /*+ rule */ count(*) from customers where acc_nbr = nvl(:v,acc_nbr)
or
select /*+ no_expand*/ count(*) from customers where acc_nbr = nvl(:v,acc_nbr).
Let describe hint no_expand:
The NO_EXPAND hint prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it.

Tags: , ,

Filter by APML

Calendar

<<  October 2018  >>
MoTuWeThFrSaSu
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234

View posts in large calendar

TextBox