Home » RDBMS Server » Performance Tuning » performance enhancement
performance enhancement [message #221171] Sat, 24 February 2007 12:29 Go to next message
dr46014
Messages: 48
Registered: February 2007
Member
hi all..
i am having a table which is having 230 million of records.i need to select the rcords with latest load_date from the table.
For that i am using the query :
select * from table_name where load_date in (select max(load_date) from table_name)
here load_date is in the format YYYYMMDD.
but the response to this query is very slow.How can i design the query so that i can improve the performance.
i also want the records from last 13 load_dates means latest 13 load_dates.How can use the same SQL statement for both the cases.Means the query will ask which month data shold be selected last 13 months or latest month and fetched the results accordingly
Re: performance enhancement [message #221178 is a reply to message #221171] Sat, 24 February 2007 13:33 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
Does "load_date" have an index on this column?
Are the statistics current?
In any case I don't think the posted query produces the result set desired.
Re: performance enhancement [message #221186 is a reply to message #221171] Sat, 24 February 2007 21:50 Go to previous messageGo to next message
dr46014
Messages: 48
Registered: February 2007
Member
no load_date is not indexed.rather the table is indexed on week_code.
Re: performance enhancement [message #221197 is a reply to message #221186] Sun, 25 February 2007 02:52 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
dr46014 wrote on Sun, 25 February 2007 14:50
no load_date is not indexed.rather the table is indexed on week_code.



Perhaps @anacedent was being a bit indirect. It seems likely that his intent in asking "Does load_date have an index on this column?", was to suggest that top-line performance can hardly be expected without an index on load_date because of the 2 Full Table Scans it would have to do.

How many different load-dates are there? If fewer than - say - 1000, you might consider a BITMAP index on load_date. Then to get the latest 13 load_dates, you could search this site for TOP-N to find some nice methods - the bitmap index should support this nicely.

Another suggestion would be to partition the table on load-date if it is not already.

Don't forget to gather statistics.

Ross Leishman
Previous Topic: DBMS_STATS.GATHER_SCHEMA_STATS - Suggestion
Next Topic: Slow Performance of query
Goto Forum:
  


Current Time: Fri Dec 09 10:08:21 CST 2016

Total time taken to generate the page: 0.09299 seconds