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)