Reputation: 2129
I have this very simple query performing filtering and join by rowid.
SELECT *
FROM BOOKING.BOOKING_GRID BG,
BOOKING.BOOKING_STATES BS
WHERE BG.hotel=128
AND BS.ROWID =BG.BOOKINGSTATE;
when I explain plan I get:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1597031677
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6137K| 1041M| | 1763K (1)| 05:48:27 |
|* 1 | HASH JOIN | | 6137K| 1041M| 538M| 1763K (1)| 05:48:27 |
|* 2 | INDEX UNIQUE SCAN| BOOKING_GRIDPK | 6137K| 468M| | 547K (1)| 01:48:05 |
|* 3 | INDEX RANGE SCAN| BOOKING_GRID_INDEX5 | 6137K| | | 90388 (1)| 00:17:52 |
| 4 | TABLE ACCESS FULL| BOOKING_STATES | 158M| 14G| | 365K (2)| 01:12:14 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("BS".ROWID="BG"."BOOKINGSTATE")
2 - access("BG"."HOTEL"=128)
3 - access("BG"."HOTEL"=128)
Index for BOOKING_GRID are:
BOOKING BOOKING_GRIDPK UNIQUE VALID IOT - TOP N NO NO HOTEL, DAY, BOOKINGSTATE
BOOKING BOOKING_GRID_UNIQ UNIQUE VALID NORMAL N NO NO HOTEL, DAY, BOOKING, VALIDITYSTART
BOOKING BOOKING_GRID_INDEX5 NONUNIQUE VALID NORMAL N NO NO HOTEL, BOOKINGSTATUS, ISDAYUSE, DAY
BOOKING BOOKING_GRID_INDEX7 NONUNIQUE VALID NORMAL N NO NO HOTEL, BOOKING, VALIDITYSTART
BOOKING BOOKING_GRID_INDEX10 NONUNIQUE VALID NORMAL N NO NO HOTEL, ISDAYUSE, BOOKINGSTATUS, DAY
Index for BOOKING_STATES are:
BOOKING BOOKING_STATES_PK UNIQUE VALID NORMAL N NO NO HOTEL, BOOKING, VALIDITYSTART
BOOKING BOOKING_STATES_INDEX2 NONUNIQUE VALID NORMAL N NO NO HOTEL, YIELDROOMTYPE, BOOKEDROOMTYPE, ROOMTYPE
BOOKING BOOKING_STATES_BOOKING NONUNIQUE VALID NORMAL N NO NO HOTEL, BOOKING, BOOKINGSTATUS
BOOKING BOOKING_NOSEGMENT_INDEX NONUNIQUE VALID FUNCTION-BASED NORMAL N NO ENABLED NO SYS_NC00034$ TO_NUMBER(DECODE(TO_CHAR("MARKETSEGMENT"),NULL,DECODE("BOOK",0,NULL,TO_CHAR(DECODE("ISDAYUSE",'N',DECODE("ISSHARED",'N',DECODE("BOOKINGSTATUS",'B',"HOTEL"*10000+LEAST("DEPARTURE","VALIDITYEND"),'I',"HOTEL"*10000+LEAST("DEPARTURE","VALIDITYEND"),'W',"HOTEL"*10000+LEAST("DEPARTURE","VALIDITYEND"))))))))
BOOKING BOOKING_NORATE_CODE_INDEX NONUNIQUE VALID FUNCTION-BASED NORMAL N NO ENABLED NO SYS_NC00033$ TO_NUMBER(DECODE(TO_CHAR("RATECODE"),NULL,DECODE("BOOK",0,NULL,TO_CHAR(DECODE("ISDAYUSE",'N',DECODE("ISSHARED",'N',DECODE("BOOKINGSTATUS",'B',"HOTEL"*10000+LEAST("DEPARTURE","VALIDITYEND"),'I',"HOTEL"*10000+LEAST("DEPARTURE","VALIDITYEND"),'W',"HOTEL"*10000+LEAST("DEPARTURE","VALIDITYEND"))))))))
BOOKING BOOKING_NOBOOKINGTYPE_INDEX NONUNIQUE VALID FUNCTION-BASED NORMAL N NO ENABLED NO SYS_NC00032$ TO_NUMBER(DECODE(TO_CHAR("BOOKINGTYPE"),NULL,DECODE("BOOK",0,NULL,TO_CHAR(DECODE("ISDAYUSE",'N',DECODE("ISSHARED",'N',DECODE("BOOKINGSTATUS",'B',"HOTEL"*10000+LEAST("DEPARTURE","VALIDITYEND"))))))))
BOOKING BOOKING_STATES_BOOKING_TYPE NONUNIQUE VALID NORMAL N NO NO HOTEL, BOOKINGTYPE, ISDAYUSE, BOOKINGSTATUS
BOOKING BOOKING_STATES_CANCEL_INDEX NONUNIQUE VALID FUNCTION-BASED NORMAL N NO ENABLED NO SYS_NC00035$, SYS_NC00036$ DECODE("BOOKINGSTATUS",'c',"HOTEL",'C',"HOTEL")
BOOKING BOOKING_STATES_CANCEL_INDEX NONUNIQUE VALID FUNCTION-BASED NORMAL N NO ENABLED NO SYS_NC00035$, SYS_NC00036$ DECODE("BOOKINGSTATUS",'c',"CANCELREASON",'C',"CANCELREASON")
I don't understand two things:
The weird thing is that when I run the same exact request with hotel=201, it is perfectly fine:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4251203092
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 591K| 100M| 643K (1)| 02:07:12 |
| 1 | NESTED LOOPS | | 591K| 100M| 643K (1)| 02:07:12 |
|* 2 | INDEX UNIQUE SCAN | BOOKING_GRIDPK | 591K| 45M| 52686 (1)| 00:10:25 |
|* 3 | INDEX RANGE SCAN | BOOKING_GRID_INDEX5 | 591K| | 8707 (1)| 00:01:44 |
| 4 | TABLE ACCESS BY USER ROWID| BOOKING_STATES | 1 | 98 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BG"."HOTEL"=201)
3 - access("BG"."HOTEL"=201)
Any idea about what's going on there?
Thank you,
Renaud
Upvotes: 1
Views: 309
Reputation: 3445
The reason for the different execution paths is because Oracle thinks that there are about 6 million rows with hotel=128 but only 591,000 with hotel=201. In the case of the bigger intermediate set, Oracle chose a hash join over nested loops.
What I don't get is this:
AND BS.ROWID =BG.BOOKINGSTATE;
You're storing Oracle-format ROWIDs in a column called BOOKINGSTATE???
Ok, given your confirmation that BOOKINGSTATE really does contain Oracle ROWIDs, here's why I'd say you're getting a HASH JOIN instead of a NESTED LOOP join:
First, when Oracle reads a row, it doesn't just read a row at a time, it reads blocks at a time. So to do a NESTED LOOP join with a TABLE ACCESS BY USER ROWID lookup, it's going to find a row in BOOKING_GRID and then go read the block that has the row in BOOKING_STATES with that ROWID. The catch is, if later on there's another row with a rowid in a block that it's already read before, it's going to re-read that block (sure, it may be cached) to get the other row. Kind of like "open the block, get a row, close the box....then later on open the same box again, get another row, close the box, move on to the next box"
On the other hand, your hash join is: - sorting the rows in the smaller set (in this case the rows in BOOKING_GRID where hotel=128), put them in memory - full table scan BOOKING_STATES - and here's the kicker - using multiblock reads. it reads many blocks at a time and processes all of the rows in a block without needing to re-read it later. It's like "open the box, process all of the rows in the box, then close the box."
(For more details on the above check out http://docs.oracle.com/cd/B28359_01/server.111/b28274/optimops.htm, in particular the following sections:
By the way, it's a bit curious that it's doing the "access("BG"."HOTEL"=128)" step twice using two indexes -how are the BOOKING_GRIDPK and BOOKING_GRID_INDEX5 indexes defined? You're asking for all columns from both tables, but the plan never touches the BOOKING_GRID table.)
Upvotes: 1