Fundamental question about "Full table Scan"

[Chris Fung]

Hi all,

I just want to confirm the following cases:

1. select * from sales;
2. select * from sales where order_date > '01-Nov-2003';

In the no index build situation, is that 1 and 2 will perform a full table scan ?

Besides, for 2, is that only the "order_date" colume will be scanned completely rather than all the columes ?

On the other hand, if index is built on order_date" colume, is that 2 will be benefited by the index scan through the b-tree structure ?

Cheers,

Chris,

[Michael Schulte zur Sur]

Hi Chris,

without index 1 and 2 produce a full table scan. Since there is no Index, where it can compare the search text to, it has to read every row anw thus with it every column. If an index is used depends on a few things. If the table is small enough, it will be a full table access anyway, because the overhead to use an index is not justified. Also, when the expected number of rows is a substantial portion of the table, because there are not many different values in the key, the index will not be used.

hope, you get an idea,

Michael

[Steve Lewis]

Yes 1 and 2 will produce a full table scan if you have no indexes. If the table is small, you may get a full table scan anyway.

Please not that in modern RDBMSs all i/o is done by the page, not by the record. So if your record is 1kb long and your page size is 4kb, you will get 1 i/o per 4 records. This will then be reduced by the read-ahead parameters you have configured for your instance.

If you have an index on the order_date column, it will be able to scan the index pages directly, starting with the first relevent page. Of course each index leaf will then point to the data page (from disk if it is not already in buffers) so that it can read the result set sales.*

[Hein van den Heuvel]

Table scan in both cases, as explained before.

Now if there is an index on date, that still does not garantuee storage order. So Oracle may have to go back and forward over the data pages to agther all matching rows. Therefor Oracle will decided to jsut read all (tablescan) evern with index (unless it knows only a few rows will results.

Now if the table is organized by that index, then it can do a partial scan.

Hein.

[doug mielke]

Also, in theory, if the table started small, and grew large over time, there will be many extents, each of which requires it's own i/o overhead, and limits the positive impact of cacheing and look ahead, when doing a full table scan.

[Michael Schulte zur Sur]

Hi Chris,

one more thing. For the optimizer to choose the right path, you have to keep the statistics of the tables and indexes up todate.

greetings,

Michael

[Thierry Poels_1]

hi,

a. no index: FTS for both queries

b. with specified index:
-1. again FTS
-2. - the index will be scanned for matching order_dates, and for matching values the row will be retrieved in the table by rowid.
- if cost-based optimizer is used & statistics are available a Full Table Scan will be chosen if it's a small table.

regards,
Thierry.

[Chris Fung]

Hi All,

Thanks for the input,

What about if the following statement is issued ?

select order_date from sales where order_date > '01-Nov-2003';

Please evalute 2 cases - with index and without index on the order_date field !!

What I am thinking for without index on the above case will be a full table scan on just the "order_date" column !! Is that correct ??

Please clarify,

Cheers,

Chris,

[Michael Schulte zur Sur · Accepted solution]

Hi Chris,

even if you select only one column from a row, Oracle has to get the entire row into memory to extract the column and without index to minimize the number of rows to check this means full table access.

greetings,

Michael

[Steve Lewis]

If you create an index on sales(order_date), update the statistics, then only select order_date from sales where order date > "some-date" , then there is a good chance that you will avoid a full table scan.

But see the responses above, which tell you that you may still get a full table scan regardless of the index - you must test it.

Without an index it will read in the whole table page by page, then just look at the order_date column (by offset) in each record in each page. Its caused by the logical separation of records from physical data pages.

But, why only select order_date when you should know what the dates are between then and now? Are people pre-placing orders for the future? It sounds like you really want something useful, like the order_id, which you need an index on order_date for, to avoid FTS.

[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