Why is a Full Table Scan Selected – Will the Execution Plan Provide a Clue?

June 20, 2012

I have not had much time to respond in OTN threads recently, although I do still occasionally read threads on the forum.  I was a little surprised by one of the late responses in one of the recent threads, where one of the responders suggested actually testing the problem with the assistance of execution plans.  Additionally, that responder suggested that the earlier responses missed the target.  Ouch!

The OP did not provide DDL to create the table or indexes, or DML to populate the table.  The SQL statement provided by the OP was apparently looking for rows where the VARCHAR2 column A was blank (as in containing a zero length string).  If an index exists on column A, why would the optimizer select to perform a full table scan when attempting to return all rows with a zero length string in column A?  (Raise your hand if you know the answer.)

Let’s create a test table with four indexes so that we are able to test some of the theories (or possible causes) that were proposed in the thread:

DROP TABLE T1 PURGE;

CREATE TABLE T1 AS 
SELECT
  ROWNUM C1,
  MOD(ROWNUM,10) C2,
  TRUNC(ROWNUM/10000) C3,
  RPAD(TO_CHAR(ROWNUM),6,'A') C4,
  LPAD('A',200,'A') C5
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

ALTER TABLE T1 MODIFY (
  C1 NOT NULL,
  C2 NOT NULL,
  C3 NOT NULL);

CREATE INDEX IND_T1_C1 ON T1(C1);
CREATE INDEX IND_T1_C2 ON T1(C2);
CREATE INDEX IND_T1_C3 ON T1(C3);
CREATE INDEX IND_T1_C4 ON T1(C4);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>NULL)

SET LINESIZE 140
SET PAGESIZE 1000 

The above script creates a table with 100,000 rows, with the first 3 columns declared as NOT NULL.  Column C1 contains the numbers 1 through 100,000.  Column C2 contains the numbers 0 through 9 in a repeating pattern.  Column C3 contains the numbers 0 through 10 with most of the rows containing the same value likely closely bunched together (a single row contains the number 10).  Column C4 is a simple VARCHAR2 column that is the number 1 through 100,000 padded to six characters using the letter A.  The statistics for the table and indexes were collected with a 100% sampling percentage.

Using the test table created by the above script, we might simulate the OP’s SQL statement using the following.  One of the last responders in the OTN thread recommended looking at the execution plan, so we will retrieve that also:

SELECT
  *
FROM
  T1
WHERE
  C4='';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL')); 

Unfortunately, the two single quotes (”) in the SQL statement are not interpretted as a test for a zero length string as happens on other DB platforms, and as the OP likely intended (see this AskTom thread).  The Oracle Database 11.2.0.2 output of the above follows:

no rows selected

SQL>

SQL_ID  5tc2j4q52493b, child number 0
-------------------------------------
SELECT   * FROM   T1 WHERE   C4=''

Plan hash value: 3332582666

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     1 (100)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   100K|    20M|   252   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL) 

So, the Predicate Information section shows NULL IS NOT NULL, which never has a result of TRUE.  Notice that the full table scan has a cost of 252, yet plan row 0, which is a grandparent of row 2, has a cost of 1 (costs for parent operations are supposed to include the calculated cost of child operations plus the calculated cost of the work performed at the parent operation).  (Raise your hand if you know the answer why plan row 0 has a cost of 1 when plan row 2 has a cost of 252.)  We will have to come back to this execution plan oddity later.

For a SQL statement like the following, which should return one row, you would expect the index on column C1 to be used:

SELECT
  C1,
  C2,
  C3,
  C4,
  SUBSTR(C5,1,10) C5
FROM
  T1
WHERE
  C1=9;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL')); 

Here is the output of the above:

        C1         C2         C3 C4     C5
---------- ---------- ---------- ------ ----------
         9          9          0 9AAAAA AAAAAAAAAA

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  90d7zdbavdpuu, child number 0
-------------------------------------
SELECT   C1,   C2,   C3,   C4,   SUBSTR(C5,1,10) C5 FROM   T1 WHERE
C1=9

Plan hash value: 683303157

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |   219 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C1 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"=9) 

So, an index range scan was performed to retrieve the one row from table T1.  (Raise your hand if you know the answer why an index range scan operation was selected when every value in column C1 is unique and an equality predicate was used in the WHERE clause.)

For the following SQL statement, where an index on column C2 would likely have a large clustering factor value, should an index be used to retrieve 10% of the rows from the table?

SELECT
  C1,
  C2,
  C3,
  C4,
  SUBSTR(C5,1,10) C5
FROM
  T1
WHERE
  C2=9;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL')); 

Here is a portion of the output from the above SQL statement:

...
     99969          9          9 99969A AAAAAAAAAA
     99979          9          9 99979A AAAAAAAAAA
     99989          9          9 99989A AAAAAAAAAA
     99999          9          9 99999A AAAAAAAAAA

10000 rows selected.

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  459zvkh9p725k, child number 0
-------------------------------------
SELECT   C1,   C2,   C3,   C4,   SUBSTR(C5,1,10) C5 FROM   T1 WHERE
C2=9

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   252 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   | 10000 |  2138K|   252   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=9) 

The optimizer selected to use a full table scan in order to retrieve 10% of the rows from the table.  (Raise your hand if you know the answer why a full table scan was selected.)

A final SQL statement that also retrieves 10% of the rows from the table, this time using column C3:

SELECT
  C1,
  C2,
  C3,
  C4,
  SUBSTR(C5,1,10) C5
FROM
  T1
WHERE
  C3=9;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL')); 

For the above SQL statement, would you expect the optimizer to select the use of an index range scan operation, or a full table scan operation?  Here is the output that I received:

...
     99996          6          9 99996A AAAAAAAAAA
     99997          7          9 99997A AAAAAAAAAA
     99998          8          9 99998A AAAAAAAAAA
     99999          9          9 99999A AAAAAAAAAA

10000 rows selected.

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  4c1s2vujtc1ff, child number 0
-------------------------------------
SELECT   C1,   C2,   C3,   C4,   SUBSTR(C5,1,10) C5 FROM   T1 WHERE
C3=9

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   252 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |  9091 |  1944K|   252   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C3"=9) 

So, once again the optimizer selected to use a full table scan to facilitate the retrieval of 10% of the rows from the table.  But why, the clustering factor should be reasonably low.  (Raise your hand if you know the answer why a full table scan was selected.)

But wait… the optimizer from another 11.2.0.2 database instance decided differently:

...
     99996          6          9 99996A AAAAAAAAAA
     99997          7          9 99997A AAAAAAAAAA
     99998          8          9 99998A AAAAAAAAAA
     99999          9          9 99999A AAAAAAAAAA

10000 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  4c1s2vujtc1ff, child number 0
-------------------------------------
SELECT   C1,   C2,   C3,   C4,   SUBSTR(C5,1,10) C5 FROM   T1 WHERE
C3=9

Plan hash value: 1220227203

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |   304 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |  9091 |  1944K|   304   (1)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C3 |  9091 |       |    18   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C3"=9) 

Why did the optimizer in one of the 11.2.0.2 database instances select to use a full table scan operation, while the optimizer in the other database instance selected to use an index range scan operation?  (Raise your hand if you know the answer why there is a difference between the two execution plan results.)

—

If we take another look at the execution plan for the SQL statement that simulates the problem experienced by the OP, I wonder if we are able to determine why the parent operation has a lower calculated cost than its child (or grandchild’s) calculated cost?

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
WHERE
  C4='';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST')); 

Let’s take a look at the output of the above:

no rows selected

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  63yz0tn58sz4v, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 WHERE   C4=''

Plan hash value: 3332582666

----------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     1 (100)|      0 |00:00:00.01 |
|*  1 |  FILTER            |      |      1 |        |            |      0 |00:00:00.01 |
|   2 |   TABLE ACCESS FULL| T1   |      0 |    100K|   252   (1)|      0 |00:00:00.01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL) 

Do you see it?  Does it matter if a full table scan of table T1 was selected by the optimizer?   (Raise your hand if you know the answer.)

What if we force an index access path:

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1) */
  *
FROM
  T1
WHERE
  C4='';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST')); 

Here is the output of the above:

no rows selected

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID  06ucccd8j8ss0, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1) */   * FROM   T1 WHERE
C4=''

Plan hash value: 1158503954

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |     1 (100)|      0 |00:00:00.01 |
|*  1 |  FILTER                      |           |      1 |        |            |      0 |00:00:00.01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1        |      0 |    100K|  3323   (1)|      0 |00:00:00.01 |
|   3 |    INDEX FULL SCAN           | IND_T1_C3 |      0 |    100K|   196   (1)|      0 |00:00:00.01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL) 

Is the above execution plan even legal because the SQL statement is essentially looking for NULL values?  (Raise your hand if you know the answer.)

—-

OK, put down your hand – the other people in the office are probably laughing at your hand-waving by now.  😉

I wonder if the OP now understands the problem with his SQL statement?

—–

Late Addition June 20, 2012:

Recall that the optimizer selected that a full table scan should be used for the initial SELECT statement.  Part 2 of a previous blog article on this blog pointed to an article on another blog, asking whether or not a couple of statements made on the other blog were true regarding whether or not NULL values were ever stored in a B*tree index structure.  One of my previous articles seemed to offer a counter-point, that in fact it is possible for a B*tree index to contain NULL values in certain situations.

What might happen if we swap in a bind variable in place of the literal in the initial SELECT statement?  If we set the bind variable to have a value of ” (the same value as the literal), will the optimizer select to use a full table scan operation or an index range scan operation?  Will the STARTS column in the execution plan contain 0 for one or more of the rows in the execution plan?  Let’s test:

VARIABLE V1 VARCHAR2
EXEC :V1:=''
 
SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
WHERE
  C4= :V1;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST')); 

Here is the output:

no rows selected
 
SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID  c4faj4cgbdrw1, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 WHERE   C4= :V1
 
Plan hash value: 7035821
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |     2 (100)|      0 |00:00:00.01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |      1 |     2   (0)|      0 |00:00:00.01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C4 |      1 |      1 |     1   (0)|      0 |00:00:00.01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C4"=:V1)

Notice that none of the rows in the execution plan have a STARTS value of 0, and that filter(NULL IS NOT NULL) does not appear in the Predicate Information section of the execution plan as had happened when we used a literal with the same value as the bind variable.  So, if the OP would like to see an index access path in the execution plan, perhaps he should use bind variables rather than literals?  Is the above execution plan more efficient or less efficient than the execution plan with the full table scan operation that was seen when a literal was passed in the SQL statement?

Let’s insert a row into table T4 with our bind variable value being placed in column C4:

INSERT INTO T1 VALUES(
  -1,
  -1,
  -1,
  :V1,
  'A');
 
COMMIT;
 
SELECT /*+ GATHER_PLAN_STATISTICS  FIND_ME */
  *
FROM
  T1
WHERE
  C4= :V1;
  
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST'));

Will a row be returned?  Will the execution plan change?  Here is the output for the above script:

no rows selected
 
SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID  csmkq5csd6f0a, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS  FIND_ME */   * FROM   T1 WHERE   C4=
:V1
 
Plan hash value: 7035821
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |     2 (100)|      0 |00:00:00.01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |      1 |     2   (0)|      0 |00:00:00.01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C4 |      1 |      1 |     1   (0)|      0 |00:00:00.01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C4"=:V1)

No rows selected, even though a row was just inserted into the table with the same value  as what is in bind variable V1.  Let’s DUMP the values of a couple of columns from the row that was just inserted:

COLUMN DUMP_C1 FORMAT A25
COLUMN DUMP_C4 FORMAT A25
 
SELECT
  C1,
  DUMP(C1) DUMP_C1,
  C4,
  DUMP(C4) DUMP_C4
FROM
  T1
WHERE
  C1=-1;
 
        C1 DUMP_C1                   C4     DUMP_C4
---------- ------------------------- ------ -------------------------
        -1 Typ=2 Len=3: 62,100,102          NULL

The above output shows that column C4 of this row contains a NULL value.  I suggest that it might not matter whether or not the execution plan shows an index access path or a full table scan for this SQL statement – if the SQL statement is answering a different question than what the OP expects, then it does not matter whether or not the SQL statement executes efficiently.

However, as a bonus we were able to see the optimizer using an index access path to check for a NULL value.