Full table scan vs Index Scan

[Former Member]

Hello all,

Good day. I've a question regarding the access plan using Full table scan and Index Range Scan.

Below is the sample SQL vstatement:

Select * from TABLE1 Where MANDT = 001 and PROJR = 100

There is a index which contains the fields in the above WHERE Clause but the selectivity (or Distinct number) is only 1 for both fields.

INDEX TABLE1~0

==============

Fields, #Distinct

==============

MANDT, 1

PROJR, 1

The table size is 3,200,000KB (3.2GB) and index size for TABLE1~0 is 180,000KB (180MB).

My question is: If using Full table scan is more efficient than index range scan, what could be the reason and is there any document/SAP Note is discussing this?

Thanks,

KP

[stefan_koehler · Accepted solution]

Hello Kim,

> If using Full table scan is more efficient than index range scan, what could be the reason and is there any document/SAP Note is discussing this?

In your specific SQL case a FTS (=Full Table Scan) is much more efficient than an index range scan. Maybe if you add an "ORDER BY" to it or for many other reasons the execution plan can change.

Why is a FTS more efficient in your case?

- No need to read the additional blocks of the index TABLE1~0 (regarding to the statistics the index can not filter any data)

- A FTS is performing a multi block read

This stuff is explained on a very high level in the official documentation:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i44851

Regards

Stefan

[Former Member · Accepted solution]

Hello Samuel, Stefan, Lars,

Good day and thanks a lot for your advices.

Based on Lar's reply, it does remind me of something. Indeed, the WHERE clause selects 'other values' than the one in the table.

The distinct value is the real one (and not the estimated ones). In TABLE1, all the values of both fields are

MANDT = 001

PROJR = 900

The below statements returned 0 record as the values for field 'PROJR' in the WHERE CLAUSE didn't exist in TABLE1.

Select * from TABLE1 Where MANDT = 001 and PROJR IN (100, 101, 102, 103, 104)

Select * from TABLE1 Where MANDT = 001 and PROJR = 100

I've read the document shared by Stefan (http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i44851), my understanding is full table scan (FTS) will be more efficient when a query will access most of the blocks in the table even though indexes might be available. As the size of TABLE1is very large here (3.2GB), I suppose large portion of data will be accessed here.

I am a bit confused here why the index access would of more efficient (than FTS) if the WHERE clause selects other values than the one in the table (as per my above examples).

Hope you can enlighten me on this.

Thanks,

KP

[lbreddemann · Accepted solution]

>

> I am a bit confused here why the index access would of more efficient (than FTS) if the WHERE clause selects other values than the one in the table (as per my above examples).

Here we go:

It's actually easy to understand how the index could largely reduce the number of blocks that need to be visited.

To figure out whether a row matches your conditions, Oracle needs to compare the provided selection criteria with the values in the table.

If you provide a value for e.g. PROJNR that does not exist in the table, scanning the whole table would take at least as much block visits as the table has allocated below the High Water Mark.

By just using the Index B*tree it usually takes about 3 block visits to find that the value is not present in the table.

Maybe the following example makes it clearer:

First we setup a table, load some data and create an index on it.

Finally we collect statistics including histograms for the table:

create table test (col1 number, col2 varchar(20), col3 varchar(120));

insert into test 
    (select 100, 
            case mod(rownum,3)
                when 0 then '100' 
                when 1 then '200' 
                when 2 then '500'
            end,
      lpad ('x', 120)
     from dual 
     connect by level <=100000);

create index test_i on test (col1, col2);

analyze table test compute statistics for all columns size 50 for table;

So far so good, now let's see how the execution plan for a BIND variable statement would look like:

select * from test where col1=:A1 and col2=:A2;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 33333 |  4068K|   544   (1)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| TEST | 33333 |  4068K|   543   (1)| 00:00:11 |
--------------------------------------------------------------------------

[lbreddemann · Accepted solution]

We see, that Oracle uses the full table scan as it assumes that it will have to read a certain part of the table.

Now let's see how this changes if we let Oracle know what values we're after.

Remember: due to the histograms Oracle now has an idea about what values are available.

select * from test where col1=100 and col2='100';
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 33333 |  4068K|   544   (1)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| TEST | 33333 |  4068K|   543   (1)| 00:00:11 |
--------------------------------------------------------------------------

Since we've used a rather common value Oracle again decided that a FTS would be the best option, which is of course correct in this case.

But what happens if we provide a value that does not seem to be common?

select * from test where col1=200 and col2='100';


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 122959093

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   333 | 41625 |    67   (2)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST   |   333 | 41625 |    66   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | TEST_I |   333 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Easy to spot that Oracle now assumes a whole lot less rows to be returned.

Thus the decision for the Index range scan.

regards,

Lars

[Former Member]

Hi Kim Peng,

This question is mainly on database side rather than SAP side. This question deals with CBO (cost based optimizer).

But you can find some documents like note 109034 and if your database is Oracle, note 766349 can also help you.

In your case, if the distinct value that you indicated are the real one (and not the estimated ones), you should have a FULL TABLE SCAN, as in this case, you only have to read the blocks corresponding to the table as in the other case, you will have to read first the whole index and then the whole table...

Best regards,

Samuel

[lbreddemann]

> In your case, if the distinct value that you indicated are the real one (and not the estimated ones), you should have a FULL TABLE SCAN, as in this case, you only have to read the blocks corresponding to the table as in the other case, you will have to read first the whole index and then the whole table...

This is correct under the assumption that the single distinct values are the ones the statement is looking for.

If the WHERE clause selects other values then the ones in the table, then the index access would of course be way more efficient.

To make the Oracle CBO behave accordingly, it's necessary to use LITERALs instead of the standard BIND variables you get when coding ABAP.

In addition column histograms need to be present (or the automatic sampling must be active)

With these preconditions met, you may as well use the index access for quick existence check queries where you expect to NOT find anything.

regards,

Lars