Fetching a record in oracle

All posts relating to Oracle database administration.

Moderator: Tim...

Fetching a record in oracle

Postby Rijo Roy » Mon Jul 29, 2013 9:18 am

Hi Tim

I am really confused with certain things and I know you can clear them as always.

* How does oracle engine executes a query -- i mean to ask will it use row id always to fetch the records

For example, select * from employees; -- * here will it be looking for rowid ?

And if employee_id in employees has a primary key index then

in the below query
select emp_id,dept_name from employees; -- * will oracle be using the index?

When exactly does this table access by index row-id / row-id comes into picture?

Thanking you in advance.

Rijo
Rijo Roy
Member
 
Posts: 35
Joined: Wed Apr 17, 2013 5:39 am

Re: Fetching a record in oracle

Postby Tim... » Mon Jul 29, 2013 9:41 am

Hi.

Neither of those situation will use ROWID to accomplish the data retrieval.

1) SELECT * FROM employees;

This will do a full table scan (FTS) of the table, so the table blocks will be read directly. There will be no use of an index. Why? Because you are asking for all the rows.

2) SELECT emp_id, dept_name FROM employees;

Once again, this is just pulling back all the rows, so there is no advantage to using an index, so Oracle will FTS.

3) SELECT emp_id FROM employees;

Since you are only asking for the PK column, and there is an index containing that column, it is more efficient to do an index scan to get the data. Since all the data you asked for is in the index, there is no need to access the table blocks, so ROWID is irrelevant. This is basically the same as a full table scan, but because an indexes is smaller, the index scan is a quicker option than the FTS.

4) SELECT ???? FROM employees WHERE emp_id = ?;

Now ROWID becomes important. The predicate in the WHERE clause is referencing an indexed column. You are asking for a subset of the total number of rows, so an index becomes more appealing. Oracle will find the matching key in the index. The key is associated with the ROWID of the data row in the table. The ROWID will be used to identify the block containing the row to read into the buffer cache. The specific row is then returned by the query.

So ROWID only becomes important when an index is being used to find the data and the data is being returned from the table.

Indexes are all about ways to efficiently find small amounts of data in big tables. Queries with no joins and/or WHERE clause are unlikely to use indexes, since you are asking for all the data.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Fetching a record in oracle

Postby Tim... » Mon Jul 29, 2013 9:46 am

Hi.

Probably the easiest way to figure this stuff out is by looking at the execution plans...

Code: Select all
SET AUTOTRACE TRACE EXPLAIN

SELECT * FROM emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------



Code: Select all
SELECT empno, deptno FROM emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |    98 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Code: Select all
SELECT empno FROM emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 179099197

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    14 |    56 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------


Code: Select all
SELECT * FROM emp WHERE empno = 9999;

Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=9999)

SQL>


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Fetching a record in oracle

Postby Rijo Roy » Mon Jul 29, 2013 10:08 am

Thanks a lot Tim..

You have cleared my confusions..


:D

Warm Regards

Rijo
Rijo Roy
Member
 
Posts: 35
Joined: Wed Apr 17, 2013 5:39 am

Re: Fetching a record in oracle

Postby Tim... » Mon Jul 29, 2013 10:35 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 3 guests