O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required