Tuning Collections in Queries [Introduction]

[To Next Article [1]] [To Performance Study]

If you never heard of using collections instead of regular IN-LIST expressions, you might want to read varying elements in IN list article from AskTom. It unfortunately has little information about the way CBO treats collections. So, if you have problems with tuning queries that employ nested tables or just evaluating what is in the way please read on. Here is one of the basic forms of SQL queries that employ collections.

select *
  from t1
 where t1.object_id in
     (
       select *
         from table(cast(:1 as table_of_number))
     );

There are several optimization issues related to tuning queries that use collections. Many of them can be attributed to the CBO statistical assumptions. The CBO calculations and figures are a little different in 9i and 10g. However the issue might have change the appearance, but not the essence. There will be few more posts on some basic forms of queries with collections.

Over the past three years I’ve engaged with the Oracle TS several times to find a good resolution for this nuisance. That generated couple bugs: 4484645, 4235962. One of them is closed as “NOT A BUG”.

Advertisements

5 Responses to “Tuning Collections in Queries [Introduction]”

  1. sandrar Says:

    Hi! I was surfing and found your blog post… nice! I love your blog. 🙂 Cheers! Sandra. R.

  2. www.youtube.com Says:

    Wow! After all I got a blog from where I be able to actually take valuable information concerning my study
    and knowledge.

  3. Youtube.Com Says:

    I’m not sure exactly why but this site is loading extremely slow
    for me. Is anyone else having this problem or is
    it a issue on my end? I’ll check back later on and see if the problem still exists.

  4. youtube.com Says:

    Hey there! I’ve been following your site for a long time now and
    finally got the courage to go ahead and give you a shout out from New
    Caney Tx! Just wanted to say keep up the great work!

  5. tour De france stationary bike Says:

    Its not my first time to go to see this website, i
    am browsing this website dailly and take fastidious data from here
    everyday.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: