Operating System - HP-UX
1748347 Members
5466 Online
108762 Solutions
New Discussion юеВ

Re: Fundamental question about "Full table Scan"

 
SOLVED
Go to solution
Chris Fung
Frequent Advisor

Fundamental question about "Full table Scan"

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,
16 REPLIES 16
Michael Schulte zur Sur
Honored Contributor

Re: Fundamental question about "Full table Scan"

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
Honored Contributor

Re: Fundamental question about "Full table Scan"

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
Honored Contributor

Re: Fundamental question about "Full table Scan"

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
Respected Contributor

Re: Fundamental question about "Full table Scan"

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
Honored Contributor

Re: Fundamental question about "Full table Scan"

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
Honored Contributor

Re: Fundamental question about "Full table Scan"

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.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
Chris Fung
Frequent Advisor

Re: Fundamental question about "Full table Scan"

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
Honored Contributor
Solution

Re: Fundamental question about "Full table Scan"

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
Honored Contributor

Re: Fundamental question about "Full table Scan"

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.