Life of an Oracle DBA – The Basics Access Paths: Index Skip Scan

Normally for Oracle to use an index, the leading column in the index must be in the predicate. There are conditions, where the leading edge of the index is not in the predicate but the remaining columns in the index can satisfy the predicate. If this condition is true then Oracle can skip over the leading column in the index and use other columns in the index. Note: If the leading column has a low cardinality (few distinct rows) then it may be cheaper for Oracle to skip the leading edge of the index and read starting at the second value in the index.


Lets start by cleaning up the indexes we have already created and build a composite index where the leading column has a low cardinality.


So we now have a composite index on t1 and the leading edge has a very low cardinality; there is only one distinct value in the status column.



So what happened? The Oracle skipped over the status column in the index and used the owner column. But what happens if you have a high cardinality (many district values) for the leading column in the index. Note the cost of this query.


The index skip scan is still being used but now, note the cost of this query. In the first example with a low cardinality column leading the index we had 8 consistent gets. Then when we used a high cardinality column in the leading column we went up to 274 conditions gets. In all it is much more expensive to do the skip scan if the leading column has a high cardinality.

Life of an Oracle DBA – The Basics Access Paths: Index Range Scan

A index range scan gets the rowid by getting the adjacent index entries.  Once Oracle has gathered the rowid’s the rows are returned in ascending order. Unless you use sort descending.


From our previous example we saw that if you use a equity predicate on a non-unique index, Oracle will do a index range scan.


An index range scan will also be done if your query has a range in the predicate.

Lets drop the normal btree index we created and create a unique index on t1 (object_id).


We used a between clause and would up getting a index range scan on t1_idx.

But what if we did this a little different, lets create an index on owner and use an equity predicate.


The first thing we notice is we are still doing an index range scan.  We analyzed the table, we analyzed the index but still the optimizer got the cardinality estimate all wrong. We are going to come back and revisit this problem in a later post.  You really need to get the cardinality right, Maria Colgan of Oracle Optimizer fame speaks frequently on this subject.

What are the where clauses that can cause the optimizer to do a index range scan?

LIKE, >, <, =, BETWEEN

You should also realize a index range scan can be bound or un-bound.

Examples of bound:

WHERE column = ‘x’

WHERE column > 1 and column < 5

WHERE column LIKE ‘x%’

WHERE column between 1 and 5

Examples of unbound:

WHERE column > 5

WHERE column < 5

We are going to come back after we address access paths, join methods and join orders to talk about histograms and getting cardinality just right.

Life of an Oracle DBA – The Basics Access Paths: Index Unique Scan

The index unique scan is good for getting one row from and index and returning the rowid. You will get a index unique scan if you have a unique index or primary key on a table.

In this example; we created an unique index on object_id.  When we executed the query, the optimizer did a index unique scan.
And the index unique scan did just what it was designed to do, select one row.
Now lets try this with a normal btree index.

SQL> drop index t1_idx;

Index dropped.

SQL> create index t1_idx on t1(object_id);

Index created.

SQL> analyze index t1_idx compute statistics;

Index analyzed.

As you can see here, because we uses a normal index thimagee The optimizer decided to do a index range scan.

Next we will be covering Index range scan.

Life of an Oracle DBA – The Basics Access Paths: Table Access By Rowid.

Dang, that just sounds fast, and it is. Rowid points to the datafile:block:location in block. So, what is this good for? How do you get Oracle to access by rowid? If you want to access a specific row or set of rows in the database and can derive the rowid you can get a lot of speed accessing the data. To get the rowid you need to either pass the rowid to the query like I did here or by accessing it through an index.

But what is a rowed? In Oracle 8 and above rowed is 10 bytes that breaks down to:

Bits 1 – 32: object_id

Bits 33 – 44: file number

Bits 45 – 64: block number

Bits 65 – 80: row number inside of block

You can use the package dbms_rowid to get interesting information from rowid.

SQL> variable vrowid varchar2(20);

SQL> execute :vrowid := ‘AABAkfAAIAAIdZKAAA’

PL/SQL procedure successfully completed.

SQL> select dbms_rowid.rowid_object(:vrowid) “object”,

2 dbms_rowid.rowid_relative_fno(:vrowid) “file”,

3 dbms_rowid.rowid_block_number(:vrowid) “block”,

4 dbms_rowid.rowid_row_number(:vrowid) “row”

5 from dual;

object file block row

———- ———- ———- ———-

264479 8 2217546 0

Lets’ look at getting a row from the database by using rowed. One thing you will notice is we did not have to make a round trip to the index t1_idx to find the rowed because we already had it. Therefore Oracle was able to go directly to the row in the database and return it to us. I’m thinking I could have done this better because using trace files and autotrace will create a lot of additional recursive sql in the trace file; such as delete from plan_table and insert into plan_table.

alter session set timed_statistics = true;

alter session set statistics_level = all ;

alter session set sql_trace = true ;

alter session set max_dump_file_size=unlimited;

alter session set tracefile_identifier = owi_trace;

alter session set events ‘10046 trace name context forever, level 12’;

set autotrace on

select object_type, object_name from t1 where rowid = :vrowid;


———————————————————————-TABLE FILE$

Execution Plan


Plan hash value: 2759577407


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |


| 0 | SELECT STATEMENT | | 1 | 44 | 1 (0)| 00:00:01 |

|* 1 | TABLE ACCESS BY USER ROWID| T1 | 1 | 44 | 1 (0)| 00:00:01 |


Predicate Information (identified by operation id):





6 recursive calls

4 db block gets

5 consistent gets

0 physical reads

760 redo size

596 bytes sent via SQL*Net to client

488 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

select object_type, object_name


t1 where rowid = :vrowid

call count cpu elapsed disk query current rows

——- —— ——– ———- ———- ———- ———- ———-

Parse 1 0.00 0.00 0 0 0 0

Execute 2 0.00 0.00 0 0 13 0

Fetch 2 0.00 0.00 0 1 0 1

——- —— ——– ———- ———- ———- ———- ———-

total 5 0.00 0.00 0 1 13 1

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 56

Rows Row Source Operation

——- —————————————————

1 TABLE ACCESS BY USER ROWID T1 (cr=1 pr=0 pw=0 time=71 us)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

—————————————- Waited ———- ————

SQL*Net message to client 3 0.00 0.00

SQL*Net message from client 3 1.58 1.58


What does this tell me: I got the data in 71 micro seconds. That’s fast. Now I’m leaving out all of the recursive work that Oracle did to execute the command.

Lets look at some more timing information.

mrskew.exe \stage\*.trc > H:\tmp\x.txt


SQL*Net message from client 11.256462 98.9% 4 2.814115 0.001859 9.667408

EXEC 0.120000 1.1% 3 0.040000 0.000000 0.070000

log file sync 0.001551 0.0% 2 0.000776 0.000513 0.001038

SQL*Net message to client 0.000022 0.0% 4 0.000005 0.000004 0.000006

PARSE 0.000000 0.0% 2 0.000000 0.000000 0.000000

FETCH 0.000000 0.0% 2 0.000000 0.000000 0.000000

TOTAL (6) 11.378035 100.0% 17 0.669296 0.000000 9.667408

Using mrskew from Method-R I get some more interesting information from the trace file. What is this telling me? sqlnet message from client was the big wait event; tanking 98.9% of the time. Well that’s network io. After that EXEC was my next highest wait event with 3 calls taking 1.1% of the execution time. BTW: this data includes all recursive sql. So what does the EXEC timing look like?

mrskew.exe \stage\*.trc –name=”EXEC” –ebucket > H:\tmp\x.txt


0.000000 0.000001 0.000000 0.0% 1 0.000000 0.000000 0.000000

0.000001 0.000010 0.000000 0.0% 0

0.000010 0.000100 0.000000 0.0% 0

0.000100 0.001000 0.000000 0.0% 0

0.001000 0.010000 0.000000 0.0% 0

0.010000 0.100000 0.120000 100.0% 2 0.060000 0.050000 0.070000

0.100000 1.000000 0.000000 0.0% 0

1.000000 10.000000 0.000000 0.0% 0

10.000000 100.000000 0.000000 0.0% 0

100.000000 1000.000000 0.000000 0.0% 0

1000.000000 Infinity 0.000000 0.0% 0

TOTAL (2) 0.120000 100.0% 3 0.040000 0.000000 0.070000

Life of an Oracle DBA – The Basics Access Paths: Full Table Scan

For the next few weeks I am going to be going over some of the basics of what you need to know to be a good Oracle DBA. Feel Free to speak up if there is something you think I should cover. I should let you know fast is not always cheap and cheap is not always fast. Think of cheap as Oracle is going to use as few resources as possible to satisfy the query. But if you are using parallel query, then Oracle sets the goal to finish the query as fast as possible and to heck with the resources. Now be carful here; you don’t want to be running an OLTP application with 70+ thousand people hitting it and all of them running parallel query. If you do, you are going to starve the machine of CPU. I’ve seen it happen, it’s not pretty.

Yes Virginia there is a Maria Colgan who is always happy to explain the difference between cheap and fast. If you don’t know Maria, she is one of the smartest people in the Oracle community and heads up the Oracle Optimizer Group. Her blog is at:

For my first set of installments I am going to go over access paths (how does Oracle get to the data you want.)

Full Table Scan otherwise know as “Dang it; why won’t oracle use my indexes” There are a number of factors for the optimizer consider to select the best access path to the data.

What predicates have been passed in the query?

What indexes exists?

What degree of parallelism is being used?

What is db_multi_block_read_count?

What hints have been put on the query?

Cardinality – you are going to be reading a lot about that, so start studying.

The Oracle cost based optimizer determines the fastest or most efficient way to access data, sometimes the optimizer will select the full table scan. If you pass a predicate in the query that is not indexed then Oracle has no choice then to do a full table scan. If the cost based optimizer decides it is cheaper to do a full table scan over using an index then Oracle will use a full table scan. I know this is hard to believe but it’s true. Here is a simple example, if Oracle needs to read 100 blocks of an index then do another 100 IOs’ to the table to get your data as apposed doing a full table scan of 150 blocks; the cost based optimizer may decide it would just be cheaper to do a full table scan.

SQL> create table t1 as select

* from dba_objects;

Table created.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> create index t1_idx on t1(object_type);

Index created.

SQL> analyze index t1_idx compute statistics;

Index analyzed.

SQL> set autotrace on

SQL> select owner, object_name

2 from t1

3 where object_type in (‘TABLE’,’INDEX’);

————snip a lot————-

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |


| 0 | SELECT STATEMENT | | 2440 | 90280 | 109 (34)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T1 | 2440 | 90280 | 109 (34)| 00:00:01 |


Predicate Information (identified by operation id):



Hay Maria, Oracle forgot that I had an index on T1. Waaaaaaaa. Oh, it’s cheaper to just read the whole table.

select /*+ index (t1 t1_idx) */

owner, object_name

from t1

where object_type in (‘TABLE’,’INDEX’);


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |


| 0 | SELECT STATEMENT | | 2440 | 90280 | 121 (3)| 00:00:01 |

| 1 | INLIST ITERATOR | | | | | |

| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 2440 | 90280 | 121 (3)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T1_IDX | 2440 | | 10 (10)| 00:00:01 |


Hay Maria, I got Oracle to use my index but it cost more. Waaaaaaa, Oh I had to do more IOs’ to get my data.

As db_multi_block_read_count increases the cost of a full table scan decreases. As parallelism increases, Oracle makes the determination of executing the query as fast as possible as apposed to a cheap as possible. If you put hints into your sql query then those hints will be used as apposed to what the Oracle optimizer believes would be the best way to access the data.

When oracle does a full table scan, blocks are read into the buffer cache and rows that don’t meet the predicate are rejected.