Re: Query optimization question.

you only posted one of your two queries. Is this the fast one or the slow one? rest of my responses are inline...

im assuming its station_id right?

also what is your goal? how fast do you want them returned?

"Nico Zigouras" <nicozigouras_at_yahoo.com> wrote in message news:359baf20.0401051150.6670a20b_at_posting.google.com...
> 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.

It takes time to return data across the network, so more rows = more time. A cheap way to speed this up is to only return 25-50 rows at a time, then re-requery. Who ever looks at 1000 records anyway?

http://asktom.oracle.com/pls/ask/f?p=4950:8:1165644148118508239::NO::F4950_P 8_DISPLAYID,F4950_P8_CRITERIA:127412348064,
> > >
> > > 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

looks like the majority of your response time is the 13216 bytes sent across the network to the client. Only way to speed that up is to see what i posted above about getting only 25-50 records at a time and re-querying or getting more bandwidth... Received on Mon Jan 05 2004 - 14:47:44 CST