03 Accessing Tables

What is my Address?

In Oracle RowID is the physical address of a row. Oracle knows uses ROWID to identify the location of  record in the database. RowID’s are binary addresses of the location where the row is stored.

Example: Select rowid, s.salesorder from sales_history s

Types of table Access

Oracle can retrieve data in a number of different ways. The most common techniques are

  • Read the entire table using a full table scan
  • access a specific row using ROWID
  • Using an Index to locate the rows

Full Table Access

Oracle Full table access
Oracle Full table access
  • A full table scan sequentially  reads each row from the table
  • Oracle reads multiple blocks during each database read (64 KB blocks)
  • If there are  million rows in a table then, it will read all the million rows (notice that the  plan specifies “Table Access Full” and there are around 1.3 million rows)

Table access by ROWID

Oracle table access by ROWID
Oracle table access by ROWID
  • Oracle reads rows by their ROWID pseudo column
  • ROWID contains the physical location of the row
  • Oracle uses index to correlate data values with ROWID and help in improving the performance of a query
  • the database finds the ROWID that go with these values and follows them to the precise table blocks, and rows within those blocks, that satisfy the query

EX: Explain plan for finding the Product using product ID

Explain plan to find the product using the productID
Explain plan to find the product using the productID

shows you that table is accessed using the index rowid.

Index Unique Scan

Oracle performs  Index Unique Scan only when there is a unique index on the column.

  • The scan searches the index in order for the specified key. An index unique scan stops processing as soon as it finds the first record because no second record is possible
  • The database obtains the rowID from the index entry, and then retrieves the row specfied by the rowID
Index Unique Scan
Index Unique Scan

The sample execution plan for the above is displayed in the image below

Execution Plan for unique Index Scan
Execution Plan for unique Index Scan

This looks the same as the plan before except it shows that it does an “Index unique Scan” on the productID column

Index Range Scan

An Index Range Scan is used when there is a

Non Unique index 
You are searching for a range of values

oracle index range scan

oracle index range scan

Here is an index range scan example. The image is self explanatory.

Oracle index range scan example
Oracle index range scan example

Choosing between Full Table Scan and Indexed Access

  • Indexed reads examine only a small part of each block, the rows you want, instead of every row in the block, saving CPU time
  • Indexed reads usually scale better as a table grows, giving stable performance, where as a full table scan becomes steadily worse, even while it might start out a little faster than the indexed plan
  • Indexed reads are almost always cached.

Rule of the thumb is

Oracle : Rule of the thumb for table access
Oracle : Rule of the thumb for table access

When you are reading a small amount of data from a large table use index, where as if you are reading a large amount of data then skip the index and do a full table scan.

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link   if you found this article interesting. (This will help the companies Search engine rankings)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: