Re: SQL statement performance in DW environment

An alternative to summary tables are OMNIDEX indexes from DISC, which can provide ultra fast performance on large databases. See http://www.disc.com/agg.html and http://www.disc.com/dwhpaper.html or contact me for more information.

Cheryl Grandy
DISC
303 444-4000
cgrandy_at_disc.com

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.
>

--
Cheryl Grandy
DISC
Get OMNIDEX for the fastest
applications ever


Sent via Deja.com http://www.deja.com/
Before you buy.