AN INTRODUCTION TO ORACLE SQL STATEMENT TUNING
Bill Magee
21st Feb 2003

THE BASIC FORMS OF ORACLE DATA RETRIEVAL

The following sections attempt to explain the various manners in which Oracle will read and work with the data available in order to satisfy your queries. It covers the main concepts in order that you may better understand later sections of this document.

Full table scan

The full table scan is generally the biggest performance killer of any server or statement. A full table scan may be used by Oracle for two reasons:

A full table scan means that Oracle will read every single row of the table from disk and compare it against your where clause before continuing. Imagine a table as follows... 
   CREATE TABLE LOTS_OF_NUMBERS
      (
       NUMBER_VALUE NUMBER(10)
      )
If we populated the table with 250,000 rows, the NUMBER_VALUE column being 1..250000 we would have a table such 
   SELECT *
   FROM   LOTS_OF_NUMBERS;

   NUMBER_VALUE
   ============
   1
   2
   3
   ...
   249998
   249999
   250000
The statement to retrieve the data in the entire table forces Oracle to do a full table scan. You want to list the entire table, the entire table must be read. In essence, any statement whatsoever which access this table will force a full table scan. On the system I'm sitting at I know the database block size to be 8K and I can determine from Oracle that the table occupies 384 blocks. This means that each full table scan will force Oracle to read 3Mb of data.

If we changed our query to 

   SELECT *
   FROM   LOTS_OF_NUMBERS
   WHERE  NUMBER_VALUE < 2;
Oracle will still have to perform a full table scan just to return the single row we require.

Index range scan

For the table we created above and the type of statement we are likely to be executing, it would probably be sensible to have an index on the NUMBER_VALUE column. For our purposes it doesn't matter too much whether we build a unique or non unique index. 

   CREATE UNIQUE INDEX LOTS_OF_NUMBERS_NUMVAL_IND ON LOTS_OF_NUMBERS(NUMBER_VALUE)
We run the query again... 
   SELECT *
   FROM   LOTS_OF_NUMBERS
   WHERE  NUMBER_VALUE < 2
This time Oracle has read only two data blocks (16Kb) in order to satisfy the query. Oracle can scan the index very quickly to find the rows which fall within the range.

An index range scan will not have a constant workload for the same type of query. For instance if we change the statement to 

   SELECT *
   FROM   LOTS_OF_NUMBERS
   WHERE  NUMBER_VALUE < 2000
Oracle must scan a larger range of the index (more disk reads) and also return more rows.

* Point to Note: On small tables (I've never found a definition of a 'small' table) Oracle will almost always perform a full table scan. Oracle will often determine that to access a table via an index, when the whole table can be read very quickly, using the index will actually result in a higher workload. I have seen and tested this behaviour on tables up to 50,000 rows on a server with little load. You should note that the point at which Oracle will begin to use the index will vary depending on the actual table (width or number of columns), the current workload on the server, memory available etc.

Index unique scan

Where a unique index exists on a column and you are searching for a single value in the column, Oracle can use the unique index, knowing perfectly well there will be a maximum of one possible value or row. The form of indexing (usually B-Tree) used on Oracle almost guarantees an absolutely definable workload for any possible value regardless of the size of the table.

In an ideal world, every query of every type on every table could be satisfied by an index unique scan. Unfortunately that is not the case and Oracle has to use a combination of methods to satisfy a query. We shall move on to the various forms of optimisation that Oracle uses to determine the best way to perform a query, and then onto how you can see this in action.

Back to Contents