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

• 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 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

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

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

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 
or 
You are searching for a range of values

oracle index range scan

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

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

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)