-
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-22-2005 at 12:29 AM.
-
What's your Oracle version, and what execution plan are you getting?
-
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
-
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.
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|