Well the tables are not small. That is my point.
Station_point:66982
event_range: 73839

only pipe_Segment is the small with 500 records.

-----Original Message-----
From: Randy Patterson [mailto:[Email Address Removed] Friday, February 28, 2003 11:44 AM
To: '[Email Address Removed] RE: question regarding full table scan


Even with indexes and analyzed tables, if Oracle 'thinks' the table is
small enough to do a full table scan, it will.

Randy Patterson
> [Email Address Removed] Anadarko Petroleum Corp.
P.O. Box 1330
Houston, TX 77251-1330
(832)636-1655




> -----Original Message-----
> From: Bernita Joseph [SMTP:[Email Address Removed] Sent: Friday, February 28, 2003 10:41 AM
> To: LazyDBA.com Discussion
> Subject: question regarding full table scan
>
> 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 documentation is here:
> http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> To unsubscribe: send a blank email to
> oracledba-[Email Address Removed] To subscribe: send a blank email to oracledba-[Email Address Removed] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
> Tell yer mates about http://www.farAwayJobs.com
> By using this list you agree to these
> terms:http://www.lazydba.com/legal.html


Anadarko Confidentiality Notice:
This electronic transmission and any attached documents or other writings
are intended only for the person or entity to which it is addressed and may
contain information that is privileged, confidential or otherwise protected
from disclosure. If you have received this communication in error, please
immediately notify sender by return e-mail and destroy the communication.
Any disclosure, copying, distribution or the taking of any action
concerning
the contents of this communication or any attachments by anyone other
than the named recipient is strictly prohibited.


Oracle LazyDBA home page