tuning-aspects
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: tuning-aspects

  1. #1
    Join Date
    Nov 2005
    Posts
    1

    tuning-aspects

    Hi,
    i am working on a datawarehouse with following tuning problem:
    maybe you know an alternative way: I ve created a partitioned view, i.e. an amount of 700 tables (data are automatically separated by using values of several dimensions, using constraints to enable the costbased optimizer to find the correct table) and a view V_ALL which combines these tables by using "UNION ALL".

    if i send a query like this
    select * from V_ALL
    where dimension1=a and dimension2=b...
    the costbased optimizer finds the corresponding table which belongs to this special dimension-combination, but it takes very long. Why?

    My aim was, to reduce the amount of data in the result,
    because the accessing frontend don t need more data.

    i have the feeling, if i would create a programm within the frontend, which chooses the correct table to the corresponding selection-critieria, it would be faster than to give this job to the costbased optimizer...but this i don t want, because this is too hard to authomatize (interface-problem)...

    In short words:
    i am looking for a good possibility to organize the big amount of data in a way, which makes selections with a long list in the where-clause as performant as possible...
    any ideas? and maybe any explanantion, why the costbased optimizer is so poor in getting the correct table within a partitioned view...
    Last edited by beyond; 11-21-2005 at 11:29 PM.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What's your Oracle version, and what execution plan are you getting?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    In short words:
    i am looking for a good possibility to organize the big amount of data in a way, which makes selections with a long list in the where-clause as performant as possible...
    any ideas? and maybe any explanantion, why the costbased optimizer is so poor in getting the correct table within a partitioned view...
    CBO has not been designed and developed to take care of "TABLE NAME" before computing the cost as you expected.

    Partition View is a 20th Century technology when table partition was not available.

    Are you still want to use it?

    Tamil

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by tamilselvan
    Partition View is a 20th Century technology when table partition was not available.
    I don't think that it's too bad an option. In many cases the execution plan is superior with partition views.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    I don't think that it's too bad an option. In many cases the execution plan is superior with partition views.
    Can you give some example?

    Tamil

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by tamilselvan
    Can you give some example?

    Tamil
    Of course ... I wouldn't have mentioned it if I couldn't

    http://oraclesponge.blogspot.com/200...e-pruning.html
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width