Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Andrew.

Asked: January 29, 2017 - 8:20 am UTC

Last updated: January 30, 2017 - 8:37 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Tom,

Is there a trick you can use in a WHERE clause to get the optimiser to spot that you are trying to do a range scan on a composite key? For example, if I have a table which contains YEAR and PERIOD and I have an index on those columns what can I put in a WHERE clause to get a single INDEX_RS_ASC in the execution plan?

Under mysql you can do the following:

select * from yrperiod where (2014,11)<=(year,period) and (year,period)<=(2016,4);

The best equivalent for oracle I have is:

select * from yrperiod where (year=2014 and period>=11) or (year>2014 and year<2016) or (year=2016 and period<=4);

but this does not result in a single INDEX_RS_ASC. The 'best' plan I can come up with is by using the USE_CONCAT hint which expands the ORs into three separate INDEX_RS_ASC sections and concatenates the result sets.

Many thanks!

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/ehwk7elqvcxdgp8e76sbeb60x

and Connor said...

Yes, thats a limitation of Oracle.

But be careful what you wish for :-)


SQL> create table t ( yr int not null, per int not null, c char(100));

Table created.

SQL>
SQL> insert into t
  2  select 2000+y,
  3         p,
  4         r
  5  from
  6    ( select rownum y from dual connect by level <= 20 ),
  7    ( select rownum p from dual connect by level <= 12 ),
  8    ( select rownum r from dual connect by level < 1000 )
  9  order by 1,2,3;

239760 rows created.

SQL>
SQL> create index ix on t ( yr, per, c );

Index created.

SQL>
SQL> set autotrace traceonly
SQL> select *
  2  from t
  3  where ( yr = 2005 and per >= 6) or
  4        ( yr > 2005 and yr < 2007 ) or
  5        ( yr = 2007 and per <= 3 );

21978 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 359151304

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      | 34487 |  4310K|   213   (2)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    | 34487 |  4310K|   213   (2)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |      |       |       |            |          |
|   3 |    BITMAP OR                        |      |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |      |       |       |            |          |
|   5 |      SORT ORDER BY                  |      |       |       |            |          |
|*  6 |       INDEX RANGE SCAN              | IX   | 34487 |       |    21   (0)| 00:00:01 |
|   7 |     BITMAP CONVERSION FROM ROWIDS   |      |       |       |            |          |
|   8 |      SORT ORDER BY                  |      |       |       |            |          |
|*  9 |       INDEX RANGE SCAN              | IX   | 34487 |       |     3   (0)| 00:00:01 |
|  10 |     BITMAP CONVERSION FROM ROWIDS   |      |       |       |            |          |
|  11 |      SORT ORDER BY                  |      |       |       |            |          |
|* 12 |       INDEX RANGE SCAN              | IX   | 34487 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("YR">2005 AND "YR"<2007)
       filter("YR"<2007 AND "YR">2005)
   9 - access("YR"=2005 AND "PER">=6)
       filter("PER">=6 AND "YR"=2005)
  12 - access("YR"=2007 AND "PER"<=3)
       filter("PER"<=3 AND "YR"=2007)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
       2367  consistent gets
        369  physical reads
          0  redo size
    2768434  bytes sent via SQL*Net to client
      16667  bytes received via SQL*Net from client
       1467  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      21978  rows processed

SQL> set autotrace off
SQL>
SQL> create index ix1 on t ( yr||':'||lpad(per,2,'0'));

Index created.

SQL>
SQL> set autotrace traceonly
SQL> select *
  2  from t
  3  where yr||':'||lpad(per,2,'0') between '2005:06' and '2007:03';

21978 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2932790928

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      | 34487 |  5186K|   608   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    | 34487 |  5186K|   608   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IX1  | 34487 |       |    88   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(TO_CHAR("YR")||':'||LPAD(TO_CHAR("PER"),2,'0')>='2005:06' AND
              TO_CHAR("YR")||':'||LPAD(TO_CHAR("PER"),2,'0')<='2007:03')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         16  recursive calls
          0  db block gets
       4228  consistent gets
         64  physical reads
          0  redo size
    2610270  bytes sent via SQL*Net to client
      16667  bytes received via SQL*Net from client
       1467  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      21978  rows processed

SQL> set autotrace off
SQL>
SQL>


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Andrew Ward, January 30, 2017 - 5:59 pm UTC

Thanks for the fast response!

Using your example, I can't see why oracle chooses this plan:

SQL> explain plan for select /*+ first_rows */ * from t
  2  where ( yr = 2005 and per >= 6) or
  3  ( yr > 2005 and yr < 2007 ) or
  4  ( yr = 2007 and per <= 3 )
  5  order by yr,per,c;

Explained.

SQL> select * from table(dbms_xplan.display());

Plan hash value: 3784397718

-------------------------------------------------------------------------
| Id  | Operation  | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT | | 11496 |  1437K|  4083   (1)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IX | 11496 |  1437K|  4083   (1)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("YR">2005 AND "YR"<2007 OR "YR"=2005 AND "PER">=6 OR
       "YR"=2007 AND "PER"<=3)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


over this one:

SQL> explain plan for select /*+ first_rows use_concat */ * from t
  2  where ( yr = 2005 and per >= 6) or
  3  ( yr > 2005 and yr < 2007 ) or
  4  ( yr = 2007 and per <= 3 )
  5  order by yr,per,c;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2738964052

--------------------------------------------------------------------------
| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  | 13996 |  1749K|     3   (0)| 00:00:01 |
|   1 |  CONCATENATION   |  |  |  |       |   |
|*  2 |   INDEX RANGE SCAN| IX  |  7664 |   958K|     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN| IX  |  3832 |   479K|    20   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN| IX  |  2500 |   312K|     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("YR"=2005 AND "PER">=6)
   3 - access("YR">2005 AND "YR"<2007)
   4 - access("YR"=2007 AND "PER"<=3)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Connor McDonald
January 30, 2017 - 8:37 pm UTC

"FIRST_ROWS" is a bad idea because its a heuristic, ie, apply some 'hard and fast' rules about how to optimize a query. Better to use FIRST_ROWS(1), which is saying use a *costing* model for the first row, not a costing model + plus some special overrides.

So we'll do that, and the plans you'll see are:

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 38319 |  4789K|     3   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IX   | 38319 |  4789K|     3   (0)| 00:00:01 |
-------------------------------------------------------------------------

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   128 |     9   (0)| 00:00:01 |
|   1 |  CONCATENATION    |      |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IX   | 11496 |  1437K|     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN| IX   | 15327 |  1915K|     3   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN| IX   | 11496 |  1437K|     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


so you can see why we choose the former. Having said that, its probably a shortcoming of the optimizer algorithm in that its assuming that the full scan will be cheap no matter why the data lies "within" the index, ie, it seems to not take into account that we'll need to scan a lot of "dead" data before getting to year 2005.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.