Re: 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).

Now, you have gone into great lengths to try to explain which index exists on what column. Yet, better would have been if you had posted the _actual_ sql statements. Anyone capable of answering your question is no doupt equally capable of reading create table .. create index statements as well as to interpret primary-foreign key relationships.

Do an execute plan on your query and if this doesn't help, post the execute plan's output here as well as the crt/cri statements.

Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch