The METRIC_DATA table is more than 10G and we have an index including all col except POLL_DATA col.
How to improve the performance is the big trouble.
Alan suggested me to wipe out the ORDER By since we have an index but the tom said we should not rely on the index if we want the record in order.
At the same time ,Alan suggested me query data from only one partition.but when I do this I found the our data is populated from all over the world and the table in database use time zone is +00.So it is hard to tell if the data with time zone is suitable to the partition condition.I think we should modify the partition condition to suit the most data???
Any one have good idea about this?
SELECT FORMULA_ID, PROPERTY_ID, TO_CHAR(DATETIME at time zone '-04:00', 'MM/DD/YYYY HH24:MI') DATETIME, POLL_DATA FROM METRIC_DATA WHERE INVENTORY_ID = 64596 and formula_id in(9,10,11,12) and property_id in(5026,5028,5031) AND (DATETIME BETWEEN CAST(TO_TIMESTAMP_TZ('05/30/2008 00:00 -04:00', 'MM/DD/YYYY HH24:MI tzh:tzm') AS TIMESTAMP WITH LOCAL TIME ZONE) AND CAST(TO_TIMESTAMP_TZ('05/31/2008 00:00 -04:00', 'MM/DD/YYYY HH24:MI tzh:tzm') AS TIMESTAMP WITH LOCAL TIME ZONE)) ORDER BY FORMULA_ID, PROPERTY_ID, DATETIME ASC