Databases
StackOverflow2013
Postsdbo
POOracle plan difference with "in" and "between"
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle plan difference with "in" and "between"
    primarykey
    data
    text
    <p>Can you help me with this plan I don't understand. I am running a very simple request:</p> <pre><code>SELECT * FROM BOOKING.BOOKING_GRID BG WHERE BG.hotel=128 and day between 8600 and 8602; </code></pre> <p>and another version:</p> <pre><code>SELECT * FROM BOOKING.BOOKING_GRID BG WHERE BG.hotel=128 and day in (8600,8601,8602); </code></pre> <p>I don't get why the "between" version is making use of BOOKING_GRID_INDEX5 when the "in" version does not? The performance is very different! Is it because IOT index can not perform "range scan"? </p> <p>Thank you for your help!</p> <hr> <p>Table index are:</p> <pre><code>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 </code></pre> <p>And this is what I get:</p> <pre><code>SQL&gt; SELECT * 2 FROM BOOKING.BOOKING_GRID BG 3 WHERE BG.hotel=128 and day between 8600 and 8602; 3584 ligne(s) sÚlectionnÚe(s). Plan d'exÚcution ---------------------------------------------------------- Plan hash value: 1060169596 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6504 | 508K| 845 (5)| 00:00:11 | |* 1 | INDEX UNIQUE SCAN| BOOKING_GRIDPK | 6504 | 508K| 845 (5)| 00:00:11 | |* 2 | INDEX SKIP SCAN | BOOKING_GRID_INDEX5 | 464 | | 480 (7)| 00:00:06 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("BG"."HOTEL"=128 AND "DAY"&gt;=8600 AND "DAY"&lt;=8602) 2 - access("BG"."HOTEL"=128 AND "DAY"&gt;=8600 AND "DAY"&lt;=8602) filter("DAY"&gt;=8600 AND "DAY"&lt;=8602) Statistiques ---------------------------------------------------------- 58 recursive calls 0 db block gets 6961 consistent gets 0 physical reads 0 redo size 200030 bytes sent via SQL*Net to client 3142 bytes received via SQL*Net from client 240 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3584 rows processed SQL&gt; SELECT * 2 FROM BOOKING.BOOKING_GRID BG 3 WHERE BG.hotel=128 and day in(8600,8601,8602); 3584 ligne(s) sÚlectionnÚe(s). Plan d'exÚcution ---------------------------------------------------------- Plan hash value: 1233826949 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1869 | 146K| 681 (0)| 00:00:09 | | 1 | INLIST ITERATOR | | | | | | |* 2 | INDEX RANGE SCAN| BOOKING_GRIDPK | 1869 | 146K| 36 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("BG"."HOTEL"=128 AND ("DAY"=8600 OR "DAY"=8601 OR "DAY"=8602)) Statistiques ---------------------------------------------------------- 57 recursive calls 0 db block gets 687 consistent gets 0 physical reads 0 redo size 200192 bytes sent via SQL*Net to client 3142 bytes received via SQL*Net from client 240 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3584 rows processed </code></pre> <hr> <p>Added: 09/09/2013</p> <p>Clustering factor details for each index:</p> <pre><code>Table Index Cluster. factor Table NRows BOOKING_GRID BOOKING_GRID_INDEX7 276 816 902 313 290 258 BOOKING_GRID BOOKING_GRID_UNIQ 131 055 933 313 290 258 BOOKING_GRID BOOKING_GRID_INDEX10 121 702 397 313 290 258 BOOKING_GRID BOOKING_GRIDPK 108 115 708 313 290 258 BOOKING_GRID BOOKING_GRID_INDEX5 24 238 404 313 290 258 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRenaudBlue
    1. USRenaudBlue
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOracle plan difference with "in" and "between"
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POOracle plan difference with "in" and "between"
      1. USJon Heller
      1. VTFavorite
    1. This table or related slice is empty.
 

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