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.

your first step will be to analyze the query strategies for your
queries. define RDMS$DEBUG_FLAGS to "SORET" to see what the
optimizer is doing for you.

Nico Zigouras wrote:
--
- - - - -
opinions expressed here are mine and mine alone
and certainly are not intended in any way to
express or represent any opinions or commitment
of oracle corporation.

norman lastovica / oracle rdb engineering

nicozigouras (AT) yahoo (DOT) com (Nico Zigouras) wrote in message news:<359baf20.0401050707.490dde8c (AT) posting (DOT) google.com>...
I forgot to mention we are running Oracle 9i on HP-UX running redhat
7.2. Here is the execution plan as requested by some respondents:
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
----------------------------------------------------------
0 recursive calls
0 db block gets
6149 consistent gets
915 physical reads
0 redo size
13788 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)
1108 rows processed

and the 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')

if you are running Oracle then you don't want to be asking
questions in the Rdb news group. please contact Oracle
support directly for questions about Oracle. This news
group is for the Oracle Rdb product family (the database
product purchased from Digital, aka DECRdb or RdbVMS).

Nico Zigouras wrote:
--
- - - - -
opinions expressed here are mine and mine alone
and certainly are not intended in any way to
express or represent any opinions or commitment
of oracle corporation.

norman lastovica / oracle rdb engineering