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.
Learn Oracle - 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:
- There are no other access paths available (no indexes that could be used
for your SQL)
- The Oracle optimiser has decided it is cheaper to use a full table scan
rather than available indexes
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. Oracle DBA 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.
Learn Oracle - 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. Oracle DBA 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.
Learn Oracle -Index unique scan
Where a unique index exists on a column and Oracle DBA 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 Oracle DBA can see this in action.
|
