Query optimization question.

This is more of a general RDBMS question, less of an Oracle question, but I am running Oracle 9i so I am asking here for lack of a better forum.

I have a table of 16 million rows that is relatively simple. It is a table of songs played on stations all over the country. The three columns that I query on are:
column type
date_played date

song_id        number
station_id     number

The station_id references a table of 7000 rows, the song_id a table of 2.7 million rows. I am including the table sizes, they may be related to my problem, they may not. Each of these columns has an index on it.

I run two different types of queries, one that returns a set of song_ids given a station id and a date range. One returns a set of station_ids given a song_id and a date range. The former runs fast, the latter very slowly and I am trying to fix that. Actually the greater the number of rows the slower the latter runs. If a song played 50 times in the date period it returns in 2 seconds, if it played 1000 times it returns in 15 seconds, so it seems dependent on the numer of rows.

I use a to_date statement, down to the second to do the date ranges. I also tried using trunc(date) which seemed to run slower. I do have an index on each column as well as on trunc(date_played).

Any thoughts or suggestions on this would be greatly appreciated. Thanks. Received on Mon Jan 05 2004 - 09:05:35 CST