Re: FULL TABLE SCANS...

Michael Rosadino wrote:
>
> Here's a quick question which I know probably has a very long answer.
> I develop a number of queries/reports in Oracle Developer 2.0 / Reports
> 2.5 off of a Oracle 7.2.3 DBMS Server. Because many of the reports
> I develop are for management purposes, it is typically necessary that I
> run summations of several hundreds of records in numerous tables which
> indexed joins numbering anywhere from 10-20. As a result, I come
> across a number of "FULL TABLE SCANS" in my execution plan, particularly
> on my driving tables which contain all the indexes necessary for
> retrieving info. from the supporting tables; and of course, as you would
> guess these driving tables contain several thousand records. I've tried
> numerous methods to avoid "FULL TABLE SCANS" such as Optimization hints,
> the use of EXIST and UNION operators, and different orders within my
> WHERE & FROM clauses [tips taken from the orange O'Reilly book], but
> I know I must be missing something. By the way, the databases is fully
> indexed as well, at least for the fields which I use to establish
> joins in my WHERE clause. So here's the question:
>
> WHAT IS THE MAGIC METHOD???
>
> Any help you can provide would be great.
> Thanks,
> --
> Michael Rosadino
> E-mail(H): mrosadino_at_connico.net
> E-mail(W): mrosadino_at_ggtinc.com
> Home: (860)657-2814
> Work: (860)871-8082, x3484

Difficult to say without the SQL examples. However, full table scans aren't inherently bad, perhaps it is the most efficient approach - especially if all the rows from your driving table get cached in memory. Regardless, post some sample SQL, with tables and their row counts and indexes. Somebody will be able to better help then.

Cheers,
Terry Received on Mon Nov 10 1997 - 00:00:00 CET