Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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.</p> <p>What I don't get is this:</p> <pre><code>AND BS.ROWID =BG.BOOKINGSTATE; </code></pre> <p>You're storing Oracle-format ROWIDs in a column called BOOKINGSTATE???</p> <hr> <p>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:</p> <p>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"</p> <p>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 <em>without</em> needing to re-read it later. It's like "open the box, process all of the rows in the box, then close the box."</p> <p>(For more details on the above check out <a href="http://docs.oracle.com/cd/B28359_01/server.111/b28274/optimops.htm" rel="nofollow">http://docs.oracle.com/cd/B28359_01/server.111/b28274/optimops.htm</a>, in particular the following sections:</p> <ul> <li>11.5.1 Full Table Scans</li> <li>11.5.3.1 Assessing I/O for Blocks, not Rows</li> <li>11.6.3 Nested Loop Joins</li> <li>11.6.4 Hash Joins )</li> </ul> <p>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.)</p>
    singulars
    1. This table or related slice is empty.
    1. POoracle 11g execution plan behaviour
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USPatrick Marchand
    1. USPatrick Marchand
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POoracle 11g execution plan behaviour
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYes exactly. Please see my comment at the top. I understand the different plan is related to the number of rows for each hotel but what I want is to understand why he thinks a "hash with nested loop" should be faster that a "TABLE ACCESS BY USER ROWID". RowID is a direct pointer to the rows of the table, nothing can be faster!
      singulars
      1. PO
      1. USRenaudBlue
    2. COThank you for your answer. It is very interesting. I added the information about BOOKING_GRID index. I would be happy to have your feeling about it!
      singulars
      1. PO
      1. USRenaudBlue
    3. COSo joining by rowId is not that good if Oracle reads entire blocks! Do you think there is an optimization I can make by creating new index or should we definitely remove the rowId thing and use other optimization such as BG.hotel=BS.hotel...?
      singulars
      1. PO
      1. USRenaudBlue
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload