Re: SQL statement performance in DW environment

You can try running the query in parallel like this:

SELECT /*+ FULL(ods_facts) PARALLEL(ods_facts, 4) */

       distribution_entity_id,sum(nvl(oem_revenue,0)) FROM ods_facts
WHERE iteration_id = 2075
group by distribution_entity_id

This will run the query in 4 separate processes. You can substitute number 4 for something else (less or more) to see if timing improves. The more processors you have in your machine the better, although this parallel query should improve performance even if you only have one processor.

Note that in order to take advantage of parallelism, I specified a full table scan - that's how it works, and it still may provide much better results than using any indexes because paging a large index in and out of memory can take a lot of time in itself.

Also, make sure that execution plan shows that the specific partition is indeed being used. If not you can specify it in the from clause like this:

...
FROM ods_facts partition (partition_name) ...

hth

Sergey

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