Re: SQL statement performance in DW environment

Depending on what version of Oracle you're using it may help if you created the fact table with range partitions based on the values of the iteration. This would allow the query to scan just the partition that contains the data and not the whole table.

AT

In article <8huh9h$enn$1_at_nnrp1.deja.com>,   abhijit_bhattach_at_hotmail.com wrote:
> My data warehousing application has a fact table containing 10 million
> records. The table schema is as follows -
> iteration_id int /*used to access information for a specific
 iteration*/
> distribution_entity_id int
> ...
> ...
> oem_revenue number /* field storing metric information */
> <160 other different number fields holding metric information>
>
> Most of the table access is via iteration_id.The table is partitioned
> using iteration_id so that specific parition can be hit directly if
 the
> query references an iteration number e.g. iteration_id = 2075
>
> Sample query -
> SELECT distribution_entity_id,sum(nvl(oem_revenue,0))
> FROM ods_facts
> WHERE iteration_id = 2075
> group by distribution_entity_id
>
> At times single iteration is so large that it hits 0.4 million
 records.
> So in those cases the above query has very poor performance. Is there
> any way to improve the performance other than creating aggregate
> table(s) ? Any insight is appreciated .
> Thanks
> Abhijit Bhattacharya
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Jun 12 2000 - 00:00:00 CDT