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: , ,

Comments (15) -

9/20/2011 2:55:43 AM #

leopard dress

Just letting you know that I really like your site, and I come here every day for the useful information you post. Keep up the great work!

leopard dress United States

10/2/2011 11:50:15 PM #

handlebar tape

Greetings! This is a real sweet blog post.

handlebar tape United States

10/11/2011 1:37:07 PM #

Credit repair

This is truly an awesome bit of reading, not many blogs compare to the amount of clear info given away here for free!

Credit repair United States

10/11/2011 1:55:30 PM #

Natural snack

I definitely loved this brilliant article. Please continue this awesome work.

Natural snack United States

10/15/2011 7:55:08 AM #

credit restoration review

Thank you for another wonderful article. Wherever else could anyone get that kind of info in this type of an ideal way of writing? I’ve got a presentation next week, and Im round the search for these info.

credit restoration review United States

11/22/2011 5:58:35 AM #

Dsi solutions review

Your website is really informative. Just telling you that I book marked your website to a couple of my personal favorite bookmarking websites; I am hoping that it will get you additional well deserved visitors.

Dsi solutions review United States

12/20/2011 12:20:22 PM #

bedroom furniture

HI
concatenation and filter option in sql plan
nice post
I truly loved reading your post.
Thanks!

bedroom furniture United Kingdom

1/17/2012 7:43:51 AM #

lockmith melbourne

You made some good points there. I did a search on the topic and found most people will agree with your blog.

lockmith melbourne United States

1/28/2012 4:45:45 AM #

Felony DUI

Very nice post. I just stumbled upon your blog and wished to say that I’ve truly enjoyed browsing your blog posts. After all I’ll be subscribing to your feed and I hope you write again very soon!

Felony DUI United States

3/1/2012 4:02:05 PM #

building inspections sydney

Impressive, definitely wonderful material. This blog is absolutely awesome. I bookmarked it but will certainly return again.

building inspections sydney United States

6/3/2012 2:27:13 AM #

Directory Submission Service

Many examples say that in this case that CBO is better than RBO(Rule hint), but sometimes RBU rule is better.

Directory Submission Service United Kingdom

8/27/2012 4:12:13 AM #

Upholstery Cleaning Winnipeg

This is really interesting, You're a very skilled blogger. I've joined your rss feed and look forward to seeking more of your wonderful post. Also, I've shared your site in my social networks!

Upholstery Cleaning Winnipeg United States

8/28/2012 5:38:28 AM #

Real Estate Medicine Hat

Thank you for the good writeup. It in fact was a amusement account it. Look advanced to more added agreeable from you! By the way, how can we communicate?

Real Estate Medicine Hat United States

8/29/2012 3:45:08 AM #

Austin Rug Cleaning

This is my first time i visit here. I found so many entertaining stuff in your blog, especially its discussion. From the tons of comments on your articles,

Austin Rug Cleaning United States

8/30/2012 2:58:40 AM #

contour profile mount

This is one technology that I would love to be able to use for myself. It’s definitely a cut above the rest and I can’t wait until my provider has it. Your insight was what I needed. Thanks

contour profile mount United States

Pingbacks and trackbacks (1)+

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Filter by APML

Calendar

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

View posts in large calendar

TextBox