| Query execution path [message #540314] |
Sat, 21 January 2012 16:45  |
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 #540316 is a reply to message #540315] |
Sat, 21 January 2012 16:51  |
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 #540318 is a reply to message #540317] |
Sat, 21 January 2012 18:49 |
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 |
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 |
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 #540331 is a reply to message #540326] |
Sun, 22 January 2012 02:56 |
John Watson
Messages: 8929
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
