Re: FULL TABLE SCANS...

Wow, I wish my driving tables only contained several thousand records! Thats not many at all. And I believe with tables that are below a certain size, a full table scan is faster than using indexes, and the optimizer will therefore choose the full table scan. Also, if the number of rows returned from you're driving table is more than 20% of the total number of rows, a full table scan will almost certainly be faster than an indexed scan.

You can force an indexed retrieval by using hints, but you may find the performance to be slower. Are you saying that using hints to force an indexed scan still resulted in FULL TABLE SCAN in your explain plan?

Here's an example I just ran on a table containing over half a million rows (using the Cost-Based Optimizer):

SELECT * FROM TABLE_A WHERE PRI_KEY > 0 Running explain plan shows a FULL TABLE SCAN, and a cost of 444.

Now using a hint to force using the index:

SELECT /*+ INDEX(TABLE_A TABLE_A_PK) */ * FROM TABLE_A WHERE PRI_KEY > 0 Explain plan now shows a cost of 209509.

So the goal is not to avoid full table scans, but to attain maximum performance. In my example above, the full table scan is much faster.

Don't forget to use column histograms where necessary, and to run ANALYZE TABLE periodically.

Michael Rosadino wrote in message <34668421.3162_at_connico.net>...
>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
Received on Sun Nov 09 1997 - 00:00:00 CST