Optimizing SQL Code - Oracle Database 11gR2 Performance Tuning - page 158

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 in a table with about 1/100 rows

of the original SALES table:
DELETE FROM sh.MY_SALES_2 WHERE MOD(ID,100) <> 0;
COMMIT;