Re: Query optimization question.

Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<btbuqg$5f4vu$1_at_ID-82536.news.uni-berlin.de>...
> > 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

SQL:
select call_letters from detections d, station s, colo_channel c where d.ent_id = 8839746 and c.channel_id = d.location_code and c.station_id = d.station_id and c.station_id = s.station_id and c.active = 1 and d.date_played > to_date('01/05/04:00:00:01','MM/DD/YY:HH24:MI:SS') and d.date_played <= to_date('01/05/04:23:59:59','MM/DD/YY:HH24:MI:SS')

Here is the execution plan:
Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=68)    1 0 FILTER

   2    1     NESTED LOOPS (Cost=6 Card=1 Bytes=68)
   3    2       NESTED LOOPS (Cost=5 Card=1 Bytes=57)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'DETECTIONS' (Cost=
          4 Card=1 Bytes=34)

   5    4           INDEX (RANGE SCAN) OF 'DETECTIONS_IDX1' (NON-UNIQU
          E) (Cost=3 Card=1)

   6    3         TABLE ACCESS (BY INDEX ROWID) OF 'COLO_CHANNEL' (Cos
          t=1 Card=1 Bytes=23)

   7    6           INDEX (UNIQUE SCAN) OF 'COLO_CHANNEL_PK' (UNIQUE)
   8    2       TABLE ACCESS (BY INDEX ROWID) OF 'STATION' (Cost=1 Car
          d=1 Bytes=11)

   9    8         INDEX (UNIQUE SCAN) OF 'STATIONS_PK' (UNIQUE)

Statistics

         41  recursive calls
          0  db block gets
       6129  consistent gets
          0  physical reads
          0  redo size
      13216  bytes sent via SQL*Net to client
       3176  bytes received via SQL*Net from client
         75  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1102  rows processed