Full Table Scans and the Buffer Cache in 11.2 – What is Wrong with this Quote?

February 26, 2012 (Modified February 27, 2012)

I found another interesting quote in the “Oracle Database 11gR2 Performance Tuning Cookbook“, this time related to tables and full table scans.  This quote is found on page 170 of the book:

“If we do an FTS [full table scan], database buffers are used to read all the table data, and this situation may lead to flushing the buffer cache data to make room for the FTS data. To avoid this situation and to limit the consequences on the database buffer cache, the database blocks from FTS operations are put on the top of the LRU (Least Recently Used) list.”

What, if anything, is wrong (and/or right) with the above quote from the book?

—

Added February 27, 2012:

Part 2:

An additional interesting quote is found on page 176 related to full table scans when indexes are present.  The test case that follows is slightly different than what is presented in the book, however the outcome is the same.  Consider the following table and indexes (note that histograms will be created on columns C1 and C2, and that column C2 will have a single row with a 1 value and 999,999 rows with a 0 value):

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  DECODE(ROWNUM,1,1,0) C2,
  LPAD('A',255,'A') C3
FROM
  DUAL
CONNECT BY
  LEVEL<=1000000;

CREATE UNIQUE INDEX IND_T1_C1 ON T1(C1);
CREATE INDEX IND_T1_C2 ON T1(C2);

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

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254') 

The test case script:

SET LINESIZE 120
SET PAGESIZE 1000

SELECT
  C1,
  C2
FROM
  T1
WHERE
  C2=1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
 
SELECT
  C1,
  C2
FROM
  T1
WHERE
  C2<>0;

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

The (slightly reformatted) output from my execution of the above script on Oracle Database 11.2.0.2:

SQL> SELECT
  2    C1,
  3    C2
  4  FROM
  5    T1
  6  WHERE
  7    C2=1;

        C1         C2
---------- ----------
         1          1

SQL_ID  8fv30tbr8jdds, child number 0
-------------------------------------
SELECT   C1,   C2 FROM   T1 WHERE   C2=1

Plan hash value: 236868917

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

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C2"=1) 

---

SQL> SELECT
  2    C1,
  3    C2
  4  FROM
  5    T1
  6  WHERE
  7    C2<>0;

        C1         C2
---------- ----------
         1          1

SQL_ID  bu17044puyhkx, child number 0
-------------------------------------
SELECT   C1,   C2 FROM   T1 WHERE   C2<>0

Plan hash value: 3617692013

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

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"<>0)

Notice in the above execution plans that the Oracle query optimizer correctly determined that only 1 row would be returned in both cases, yet in the first case an index was used, and in the second case a full table scan.  The book states the following:

“Why did the database optimizer switch back to a long-running FTS operation, instead of the previous Index Range Scan? The answer is simple – indexes cannot be used when we compare values with a not equal operator.”

I have seen the above answer, with slight variations, provided in at least two other books.  What, if anything, is wrong (and/or right) with the above quote from the book?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true. It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past). If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.