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.
Let's start by creating two tables from the data in the SALES table of the SH schema:
Connect to the
SHschema:CONNECT sh@TESTDB/shCreate the
MY_SALES_ALLtable:CREATE TABLE sh.MY_SALES_ALL AS SELECT ROWNUM AS ID, X.* FROM sh.SALES X;
Create the
MY_SALES_2table:CREATE TABLE sh.MY_SALES_2 AS SELECT * FROM sh.MY_SALES_ALL NOLOGGING;
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');
Verify the database blocks used by the two tables:
SELECT BLOCKS FROM DBA_TABLES WHERE TABLE_NAME IN ('MY_SALES_ALL', 'MY_SALES_2');
Delete some rows from
MY_SALES_2, resulting...