Home » RDBMS Server » Performance Tuning » Query execution path (11gR2 Linux)
Query execution path [message #540314] Sat, 21 January 2012 16:45 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
SELECT EMP_ID FROM EMP WHERE WHEN_CREATED <= :B1

-------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |       |       |   490 (100)|          |
|   1 |  VIEW                  | index$_join$_001 |   140K|  2473K|   490   (2)| 00:00:06 |
|   2 |   HASH JOIN            |                  |       |       |            |          |
|   3 |    INDEX RANGE SCAN    | IDX_EMP_WC       |   140K|  2473K|    41   (3)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| EMP_PK           |   140K|  2473K|    40   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


Hi ,can someone explin me why its using "VIEW" , "index$_join$_001 " , HASH JOIN in the above example ?
EXP is just a table
Re: Query execution path [message #540315 is a reply to message #540314] Sat, 21 January 2012 16:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The table EMP is never touched.
SELECT is completed by only using indexes.
Re: Query execution path [message #540316 is a reply to message #540315] Sat, 21 January 2012 16:51 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Thanks.
why its using the term "VIEW" when EMP is just a table ?
what is "index$_join$_001"
why "HASH JOIN" come in picture, when it should be just "TABLE ACCESS USING ROWID"
Re: Query execution path [message #540317 is a reply to message #540316] Sat, 21 January 2012 16:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>why "HASH JOIN" come in picture, when it should be just "TABLE ACCESS USING ROWID"
Both the CBO & I disagree that "it should be just "TABLE ACCESS USING ROWID"",
when the query is satisfied by using only the index.

If/when you SELECT column that is NOT indexed, then ROWID will be used to access the table.
Try it. You'll see & like it!
Re: Query execution path [message #540318 is a reply to message #540317] Sat, 21 January 2012 18:49 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
thanks.
so ID:4 "INDEX RANGE SCAN" >> it gets rowids
ID:5 "INDEX FAST FULL SCAN" >> reads EMP_ID from index ,using rowid from above step. is that correct ?

Or INDEX FAST FULL SCAN is something like FULL TABLE SCAN ? reads index from top to bottom.???
Re: Query execution path [message #540319 is a reply to message #540317] Sat, 21 January 2012 18:54 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Index joins are briefly covered in the Oracle manual http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#PFGRF94628

The Cost Based Optimizer thinks that there will be a lot of rows matching WHEN_CREATED <= :B1. Note the ROWS column, it thinks it will get 140K matches. It also thinks that it will get 140K rows from the full scan of the PK index. ie. most if not all of the table will match WHEN_CREATED <= :B1

If this is true then the Index Join is a good method. If not true (small number of matching rows) then a TABLE ACCESS BY INDEX ROWID would be better. In this case you could try gathering statistics, or like @Blackswan says, add a non-indexed column to the query.

Ross Leishman
Re: Query execution path [message #540320 is a reply to message #540319] Sat, 21 January 2012 19:17 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Thanks Ross, that was a good explanation. one doubt still left .
when it does "INDEX FAST FULL SCAN" ,it does FULL scan on index or is it read just basedon ROWIDs from previous steps?

Thanks
Re: Query execution path [message #540326 is a reply to message #540320] Sun, 22 January 2012 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67948
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All the operations are described in
Performance Tuning Guide
Chapter 13 The Query Optimizer
Section Understanding Access Paths for the Query Optimizer

Regards
Michel

Re: Query execution path [message #540331 is a reply to message #540326] Sun, 22 January 2012 02:56 Go to previous message
John Watson
Messages: 8593
Registered: January 2010
Location: Global Village
Senior Member
This is my interpretation (I would appreciate corrections):

The first step is Id3. The index on WHEN_CREATED is searched to retrieve all rowids for rows where WHEN_CREATED<=:B1. This scan proceeds across the leaf nodes of the index starting at :B1, and will retrieve the rowids in index order. The selected rowids are transformed into a hash table, ideally in memory. Then, step Id4: the entire index on EMP_ID is scanned (not in key order). For each key, the rowid is used to probe the the hash table. This implements the join, Id3. if a matching rowid is found, the EMP_ID is inserted into the view at Id1. So the view will include the relevant EMP_IDs, in random order. Finally, the fetch is carried out at step Id0 by querying the view.

So in effect, there are three phases: first, Id3 to build the hash table. Second, Ids 4, 2, and 1 occur concurrently. Third, Id 0 returns the result set.

Is that right? If so, how does that relate to the cost and timing values? I think I must be wrong, because while my interpretation explains the zero cost for step Id2, it does not explain the high cost for Id1.
Previous Topic: CBO Query re-write
Next Topic: Primary key - unusable
Goto Forum:
  


Current Time: Thu Sep 23 08:19:40 CDT 2021