Avoiding full table scans

In this recipe, we will see what a full table scan is, how to avoid it, and when to choose a full table scan over other methods.

How to do it...

Let's start by creating two tables from the data in the SALES table of the SH schema:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create the MY_SALES_ALL table:

   CREATE TABLE sh.MY_SALES_ALL AS
     SELECT ROWNUM AS ID, X.* FROM sh.SALES X;
   

3. Create the MY_SALES_2 table:

   CREATE TABLE sh.MY_SALES_2 AS
     SELECT * FROM sh.MY_SALES_ALL NOLOGGING;
   

4. Compute statistics on the tables we just created:

   EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'MY_SALES_ALL',
     estimate_percent => 100,
     method_opt => 'for all columns size 1');
   EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'MY_SALES_2',
     estimate_percent => 100,
     method_opt => 'for all columns size 1');
   

5. Verify the database blocks used by the two tables:

   SELECT BLOCKS FROM DBA_TABLES
     WHERE TABLE_NAME IN ('MY_SALES_ALL', 'MY_SALES_2');
   

6. Delete some rows from MY_SALES_2, resulting...