beyond
11-22-2005, 12:16 AM
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...
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...