Re: How to improve query performance of a large table
Posted by:
Rick James
Date: September 12, 2013 09:30PM
It is touching only the one partition -- good.
It is touching the entire partition -- time consuming.
The WHERE clause asks to touch the entire partition.
> Basically every row in a particular partition will need to be processed. Would it perform better instead of processing everything all at once, divide the data into multiple trunks and process one chunk at a time?
No, rearranging/splitting/etc. won't help. "Count the disk hits" -- you have to touch all 40GB; that is what takes the time.
But... What is the problem? That the query takes a long time? Or that the query finishes after a long time? There is a subtle difference. If you summed up each hour after it came in, it would take the same amount of time overall, but you would be finished sooner. Instead of finishing at 08:00, it might finish about 00:20.
To do that...
PRIMARY KEY (`fct_ts`,`device_key`,`component_key`) -- good.
AVG --> SUM and COUNT; then the average is SUM(sums)/SUM(counts)
I suggest you consider doing the summarization, then tossing the raw data. Or do you have some use for the raw data?
Describe things further; I suspect there are more issues we have not discussed yet.
Subject
Views
Written By
Posted
6639
September 05, 2013 11:32AM
2101
September 06, 2013 08:47AM
1874
September 06, 2013 11:49AM
1280
September 07, 2013 08:11AM
1215
September 09, 2013 01:29PM
Re: How to improve query performance of a large table
1272
September 12, 2013 09:30PM
1208
September 17, 2013 07:35AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.