How to understand avoid FULL TABLE SCAN

Question

I have a table that is responsible to store log. The DDL is this:

CREATE TABLE LOG(
    "ID_LOG" NUMBER(12,0) NOT NULL ENABLE, 
    "DATA" DATE NOT NULL ENABLE, 
    "OPERATOR_CODE" VARCHAR2(20 BYTE), 
    "STRUCTURE_CODE" VARCHAR2(20 BYTE), 

     CONSTRAINT "LOG_PK" PRIMARY KEY ("ID_LOG")
 );

with these two indices:

CREATE INDEX STRUCTURE_CODE ON LOG ("OPERATOR_CODE");

CREATE INDEX LOG_01 ON LOG ("STRUCTURE_CODE", "DATA") ;

but this query produce a FULL TABLE SCAN:

SELECT log.data AS data1, 
       OPERATOR_CODE,
       STRUCTURE_CODE
  FROM log
 WHERE data BETWEEN to_date('03/03/2008', 'DD-MM-YYYY')
                AND to_date('08/03/2015', 'DD-MM-YYYY')
   AND STRUCTURE_CODE = '1601';

Why I see always a FULL TABLE SCAN on column DATA and STRUCTURE_CODE?

(I have tried also on create two different index for STRUCTURE_CODE and DATA but I have always a full table scan)

How many rows are in the main table and how many are returned by this `SELECT` query? And as @Necreaux said, gather stats on the index. — ruudvan, Mar 10 '15 at 14:43
You've got a wide date range (2008 to 2015) so an index is unlikely to be selective enough. — Jeffrey Kemp, Mar 11 '15 at 05:30

score 7 · Answer 1 · answered Mar 10 '15 at 14:39

Did you run stats on your new index and the table?

How much data is in that table and what percentage of it is likely to be returned by that query? Sometimes a full table scan is better for small tables or for queries that will return a large percentage of the data.

score 0 · Answer 2 · answered Mar 10 '15 at 16:32

How many rows do you have in that table?
How many is returned by this query?
Please include the explain plan.

If loading the table and doing a full table scan (FTS) is cheaper (in IO cost) than utilizing an index, the table will be loaded and FTS will happen. [Basically the same what Necreaux said]

This can happen either if the table is small, or the expected result set size is big.

What is small? FTS will almost always happen if the table is smaller than DB_FILE_MULTIBLOCK_READ_COUNT. This case, the table usually can be loaded into memory with one big read. It's not always an issue, check the IO cost in the explain plan.

What is big? If the table is pretty big, and you'll return the most of it, it is cheaper to read up the whole table in a few large IO calls, than making some index reads then making a lot of tiny IO calls all around to the table.

Blind guessing from your query (without the explain plan results), I think it would first consider an index range scan (over LOG_01), followed by a table access by rowid (to get the OPERATOR_CODE as it is not in the index), but either it decides that your table is too small, or that there are so many rows to be returned from that date range/structure_code, that rolling through the table is cheaper (in IO Cost terms).

How to understand avoid FULL TABLE SCAN

2 Answers2