| performance enhancement [message #221171] |
Sat, 24 February 2007 12:29  |
dr46014
Messages: 49
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 #221197 is a reply to message #221186] |
Sun, 25 February 2007 02:52  |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
