Hi all,

I'm trying to tune three tables.
Now the er.event_id.ps.event_id and sp.station_id are all pks.
But the er.station_id_begin and station_id_end are keys as
I created a b-tree index on station_id_begin and station_id_end and these
are fk's to station_id on station_point.
Also I have analyzed the tables and the indexes. Now I'm not sure why then
I'm getting a full table scan on the event_range table;

Any thoughts?


This what the executing plan tells me:

SQL> select count(*) from event_range er,pipe_Segment ps,station_point sp
2 where er.event_id = ps.event_id and er.station_id_begin = sp.station_id
and er.station_id_end =
sp.station_id;

COUNT(*)
----------
0

Elapsed: 00:00:00.71

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=92 Card=1 Bytes=35)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=92 Card=1 Bytes=35)
3 2 NESTED LOOPS (Cost=92 Card=486 Bytes=13608)
4 3 TABLE ACCESS (FULL) OF 'EVENT_RANGE' (Cost=92 Card=7
3839 Bytes=1550619)

5 3 INDEX (UNIQUE SCAN) OF 'PK_PIPE_SEGMENT' (UNIQUE)
6 2 INDEX (UNIQUE SCAN) OF 'SERIES_STATION_POINT_X_REF_PK'
(UNIQUE)





Statistics
----------------------------------------------------------
0 recursive calls
7 db block gets
149249 consistent gets
108 physical reads
0 redo size
366 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed


Oracle LazyDBA home page