CODEWITHSUNDEEP
performanceoracle-databasequery-optimizationdata-warehouse
Avi
Avi

Reputation: 1145

Querying Table containg millions of rows from Oracle database

I have a table finance (column : date,month,quarter,amount_usd,col1,col2,col3).

Simply querying ...

select sum(amount_usd) from finance
group by date

...takes 7 sec

select sum(amount_usd) from finance
group by month

...takes 6 sec

select sum(amount_usd) from finance
group by quarter

...takes 5 sec

So I have thought of Data Warehouse

I have created time dimention and hierarchy as

Now I am stuck...

What will be my fact table? And how to query data from fact table?

This might be very basic question. But I am new to this.

Thanks

Upvotes: 1

Views: 2306

Answers (1)

APC
APC

Reputation: 146239

"so my simple querys is taking time and thought of having Data warehouse concept..."

Seven seconds is not slow. At least, not "need a data warehouse" slow. A Data Warehouse is a strategic (usually enterprise level) environment, not a tuning technique.

However, I agree that it might be irritating for an OLTP user waiting for a screen to refresh. What you need to do is tune the query. You say you have indexes: but if you are summing the whole FINANCE table then you probably don't want to use indexes. If you have an Enterprise Edition licence and multiple CPUs with spare capacity then parallel query might be a better idea.

If you run these finance queries many times a day you may derive some benefit from pre-aggregation but it seems unlikely that the reduction in elapsed time would justify the overhead of maintaining materialized views.

Upvotes: 6

Related Questions