Fundamental question about "Full table Scan"

[Henrik Palmqvist]

Hi Cris,

The rule of thumb is that if Oracle can not use an index (for various reasons) it will make a full table scan.

If you use an index in the predicate (that is the WHERE part of the statement), the optimizer (if it is cost-based) will consider using the index.

If all the columns needed to satisfy the query, in the selection (after SELECT) and in the predicate (after WHERE) is in the index Oracle can use a method known as fast full scan. This means that the index is read as a table in multiblock mode. This result set is unordered and the selection of rows is made, not by walking the b-tree but by filtering the rows during scan. Another access method for Oracle is to use index range scan and not do a table access by rowid, since all the information needed to satisfy the query is in the index. Sometimes a fast full scan can be more efficient than a range scan, since it is possible to read the datablocks in multiblock mode.

Even if an index exists the cost based optimizer can do a full table scan (even on a large table) because that operation may be cheaper in terms of computing resources than a combination of index and table scans.

If you want to check the execution plan of a statement you can use the EXPLAIN PLAN command or in sql*plus you can use SET AUTOTRACE ON

[Yogeeraj_1]

hi,

i would rather think in terms of the optimiser. Cost-based (CBO) or rule-based (RBO).

You should also note that "full scans are not always evil, indexes are not always good"

you really need to ANALYZE your tables and use the CBO to its fullest.

Consider the following simple examples (2):
===========================================
B*Tree index + predicate does not use the leading edge of index

Table: T Index: T(x,y)

Query 1: SELECT * FROM T WHERE Y=5;
The optimizer will tend not to use the index since your predicate did not involve the column X - it must inspect each and every index entry in this case. It will typically opt for a full table scan of T instead.

Query 2: SELECT X,Y FROM T WHERE Y=5;
The optimizer would notice that it did not have to go to the table in order to get either X or Y (they are in the index) and may very well opt for a Fast Full Scan of the index itself, as the index is typically much smaller than the underlying table.
=========================================================


hope this helps too!

regards
Yogeeraj

[Chris Fung]

Hi All,

Thanks for the detailed explanations !!

One more questions about Full table scan !!

We have some large tables each contains more than 30 millions of records. The full table scan for those tables take really quite a while !!

After I deleted 1/3 no. of records from those table, I would expect the speed for the full table scan should be faster to some degrees.

However, the real case is not like that !! What's wrong ?

1. Do the table extents need to be deallocated ? (we are using dictionary tablespace)

2. Does an anaylze table compute statistics help ? Why ?

3. Or do I need to re-organized the table through exp/imp

4. Will a local management tablespace help in this case ?

Many thanks,

Chris,

[Michael Schulte zur Sur]

Hi Chris,

1: When you delete rows, the percentage used in a block gets less. But as long as there are rows in it, the block has to be read. Export, drop and import the table, then you will have it compact.
2: Statistics only help, when you work with indexes.
3: good idea
4: local management is a good idea, It reduces the contention on the dictionary because the extent management is moved to the tablespace files.

greetings,

Michael

[Volker Borowski]

Hi Chris,

I have seen the optimizer not using indexes if used with "<" or ">". Index is always a good choice for equals "=" in WHERE.

Ways out of this, depending on what you want to access:
1) Create the index on order_date and change the where-clause to
... where order_date between 'your date' and '31-Dec-9999';
the optimizer is more likely to use the index with a BETWEEN than with "<" and ">"

This would be a good approach, if you want to access only or often the last month of your table. It might be worth to check, if a descending index gives you better performance in this case.
Try to use a hint (see manual) if the optimizer does not use the index.
Consider to use histograms to make this index more attractive for the optimizer.

2) If you need to access monthly data for any month in the past, consider to reorganize the table and create partitions on this column. This would cut down the FULL TABLE SCAN to a FULL PARTITION SCAN, which seems the best choice to me if you need all data on a single month.

Hope this helps
Volker

[Yogeeraj_1]

hi,

1. Do the table extents need to be deallocated ? (we are using dictionary tablespace)
alter table move is the BEST way to do it. You can specify all new storage parameters at that point if you want.
You can also use this technique to move the table to a LMT.

2. Does an anaylze table compute statistics help ? Why ?
You can automate this process by enabling monitoring on the table and run:
exec dbms_stats.gather_schema_stats( ownname => user, options => 'GATHER STALE' );

3. Or do I need to re-organized the table through exp/imp
No need. use "Alter table move" as in (1) above.

4. Will a local management tablespace help in this case ?
Indeed, you will no longer have to worry about extent management.


Also, concerning the deleting of records.

If 1/3 of the table is a very large, it is sometimes better to:

create table temp nologging as select * from t where id not in ( select id from a );

(keep the rows you want)

index temp (unrecoverable, in parrallel )
grant on temp (as you had for t)
drop table t;
rename temp to t;

hope this helps too!

regards
Yogeeraj

[Chris Fung]

Thx for all your great ideas !!

Cheers,

Chris