Oracle Live SQL - Script: Impact of Index Column Order on Queries

Statement 1

create table OLYM_MEDALS as 
  select * from olym.OLYM_MEDALS_VIEW

Table created.

Statement 2

exec dbms_stats.gather_table_stats(sys_context('userenv', 'current_user'), 'OLYM_MEDALS')

Statement processed.

Statement 3

create index olym_event_i on olym_medals (event)

Index created.

Statement 4

create index olym_medal_i on olym_medals (medal)

Index created.

Statement 5

create index olym_event_medal_i on olym_medals (event, medal)

Index created.

Statement 6

create index olym_medal_event_i on olym_medals (medal, event)

Index created.

Statement 7

begin 
  for rws in ( 
select /*+ gather_plan_statistics GOLD */listagg(athlete,',' on overflow truncate) within group (order by athlete) 
from   olym_medals 
where  medal = 'Gold') loop 
    null; 
  end loop; 
   
    for rws in ( 
select /*+ gather_plan_statistics 100M */listagg(athlete,',' on overflow truncate) within group (order by athlete) 
from   olym_medals 
where  event = '100m') loop 
    null; 
  end loop; 
   
    for rws in ( 
select /*+ gather_plan_statistics GO100 */listagg(athlete,',' on overflow truncate) within group (order by athlete) 
from   olym_medals 
where  medal = 'Gold' and event = '100m') loop 
    null; 
  end loop; 
end;

Statement processed.

Statement 8

Searching for all the Gold medal winners ever returns ~1/3 of the table's rows. It's rare an index is useful for fetching this much of a table. So as expected this query uses a full table scan instead of any of the indexes available.

select p.*  
from v$sql s, table (  
  dbms_xplan.display_cursor (  
    s.sql_id, s.child_number, 'ALLSTATS LAST'  
  )  
) p  
where s.sql_text like '%GOLD%'  
and   s.sql_text not like '%not this%'  
and   s.sql_text not like 'begin%'


PLAN_TABLE_OUTPUT
SQL_ID  guzmtaapjvpa1, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics GOLD */LISTAGG(ATHLETE,',' ON 
OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY ATHLETE) FROM OLYM_MEDALS 
WHERE MEDAL = 'Gold'
 
Plan hash value: 1115672472
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |      1 |        |      1 |00:00:00.01 |     369 |       |       |          |
|   1 |  SORT GROUP BY     |             |      1 |      1 |      1 |00:00:00.01 |     369 |   903K|   522K|  802K (0)|
|*  2 |   TABLE ACCESS FULL| OLYM_MEDALS |      1 |   9738 |   9849 |00:00:00.02 |     369 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("MEDAL"='Gold')
 

21 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID guzmtaapjvpa1, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics GOLD /LISTAGG(ATHLETE,',' ON
OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY ATHLETE) FROM OLYM_MEDALS
WHERE MEDAL = 'Gold'

Plan hash value: 1115672472

-----------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| OMem \| 1Mem \| Used-Mem \|
-----------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 369 \| \| \| \|
\| 1 \| SORT GROUP BY \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 369 \| 903K\| 522K\| 802K (0)\|
\|* 2 \| TABLE ACCESS FULL\| OLYM_MEDALS \| 1 \| 9738 \| 9849 \|00:00:00.02 \| 369 \| \| \| \|
-----------------------------------------------------------------------------------------------------------------------

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

2 - filter("MEDAL"='Gold')

Statement 9

There have been at most two 100m events at each Games (men's and women's). With over one hundred events in most Games, this is a small fraction of the total rows. So ideally the query will use an index. And it does! There are two available ( event ) and ( event, medal ). In this case it uses the single column index.

select p.*  
from v$sql s, table (  
  dbms_xplan.display_cursor (  
    s.sql_id, s.child_number, 'ALLSTATS LAST'  
  )  
) p  
where s.sql_text like '%100M%'  
and   s.sql_text not like '%not this%'  
and   s.sql_text not like 'begin%'


PLAN_TABLE_OUTPUT
SQL_ID  3ayq17z5aswcm, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics 100M */LISTAGG(ATHLETE,',' ON 
OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY ATHLETE) FROM OLYM_MEDALS 
WHERE EVENT = '100m'
 
Plan hash value: 1651183729
 
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |      1 |00:00:00.01 |       6 |      2 |       |       |          |
|   1 |  SORT GROUP BY                       |              |      1 |      1 |      1 |00:00:00.01 |       6 |      2 | 13312 | 13312 |12288  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS  |      1 |     51 |    136 |00:00:00.01 |       6 |      2 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | OLYM_EVENT_I |      1 |     51 |    136 |00:00:00.01 |       3 |      2 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("EVENT"='100m')
 

22 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 3ayq17z5aswcm, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics 100M /LISTAGG(ATHLETE,',' ON
OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY ATHLETE) FROM OLYM_MEDALS
WHERE EVENT = '100m'

Plan hash value: 1651183729

---------------------------------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| Reads \| OMem \| 1Mem \| Used-Mem \|
---------------------------------------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 6 \| 2 \| \| \| \|
\| 1 \| SORT GROUP BY \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 6 \| 2 \| 13312 \| 13312 \|12288 (0)\|
\| 2 \| TABLE ACCESS BY INDEX ROWID BATCHED\| OLYM_MEDALS \| 1 \| 51 \| 136 \|00:00:00.01 \| 6 \| 2 \| \| \| \|
\|* 3 \| INDEX RANGE SCAN \| OLYM_EVENT_I \| 1 \| 51 \| 136 \|00:00:00.01 \| 3 \| 2 \| \| \| \|
---------------------------------------------------------------------------------------------------------------------------------------------------

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

3 - access("EVENT"='100m')

Statement 10

The query to find all the Gold winners of the 100m uses the most specific index of ( event, medal ).

select p.*  
from v$sql s, table (  
  dbms_xplan.display_cursor (  
    s.sql_id, s.child_number, 'ALLSTATS LAST'  
  )  
) p  
where s.sql_text like '%GO100%'  
and   s.sql_text not like '%not this%'  
and   s.sql_text not like 'begin%'


PLAN_TABLE_OUTPUT
SQL_ID  0dnkjc7mzvd32, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics GO100 */LISTAGG(ATHLETE,',' ON 
OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY ATHLETE) FROM OLYM_MEDALS 
WHERE MEDAL = 'Gold' AND EVENT = '100m'
 
Plan hash value: 363132783
 
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |      1 |        |      1 |00:00:00.01 |       5 |       |       |          |
|   1 |  SORT GROUP BY                       |                    |      1 |      1 |      1 |00:00:00.01 |       5 |  4096 |  4096 | 4096  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS        |      1 |     17 |     44 |00:00:00.01 |       5 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | OLYM_EVENT_MEDAL_I |      1 |     17 |     44 |00:00:00.01 |       2 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("EVENT"='100m' AND "MEDAL"='Gold')
 

22 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 0dnkjc7mzvd32, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics GO100 /LISTAGG(ATHLETE,',' ON
OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY ATHLETE) FROM OLYM_MEDALS
WHERE MEDAL = 'Gold' AND EVENT = '100m'

Plan hash value: 363132783

------------------------------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| OMem \| 1Mem \| Used-Mem \|
------------------------------------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 5 \| \| \| \|
\| 1 \| SORT GROUP BY \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 5 \| 4096 \| 4096 \| 4096 (0)\|
\| 2 \| TABLE ACCESS BY INDEX ROWID BATCHED\| OLYM_MEDALS \| 1 \| 17 \| 44 \|00:00:00.01 \| 5 \| \| \| \|
\|* 3 \| INDEX RANGE SCAN \| OLYM_EVENT_MEDAL_I \| 1 \| 17 \| 44 \|00:00:00.01 \| 2 \| \| \| \|
------------------------------------------------------------------------------------------------------------------------------------------------

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

3 - access("EVENT"='100m' AND "MEDAL"='Gold')

Statement 11

Make this index unavailable.

alter index olym_event_medal_i invisible

Statement processed.

Statement 12

alter index olym_medal_event_i invisible

Statement processed.

Statement 13

begin 
    for rws in (  
select /*+ gather_plan_statistics GO100AGAIN */listagg(athlete,',' on overflow truncate) within group (order by athlete)  
from   olym_medals  
where  medal = 'Gold' and event = '100m') loop  
    null;  
  end loop;  
end;

Statement processed.

Statement 14

To find all the 100m gold medal winners, the single column event index does a tiny bit more work than the composite ( event, medal ). You can see this from the value in the "Buffers" column in the first line of the plan. Here it's 6 (gets) vs 5 in the previous plan. Is this small saving worth the extra overhead of the larger index?

select p.*    
from v$sql s, table (    
  dbms_xplan.display_cursor (    
    s.sql_id, s.child_number, 'ALLSTATS LAST'    
  )    
) p    
where s.sql_text like '%GO100AGAIN%'    
and   s.sql_text not like '%not this%'    
and   s.sql_text not like 'begin%'


PLAN_TABLE_OUTPUT
SQL_ID  5r3z4vtqd0v51, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics GO100AGAIN */LISTAGG(ATHLETE,',' ON 
OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY ATHLETE) FROM OLYM_MEDALS 
WHERE MEDAL = 'Gold' AND EVENT = '100m'
 
Plan hash value: 1651183729
 
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |      1 |00:00:00.01 |       6 |       |       |          |
|   1 |  SORT GROUP BY                       |              |      1 |      1 |      1 |00:00:00.01 |       6 |  4096 |  4096 | 4096  (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS  |      1 |     17 |     44 |00:00:00.01 |       6 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | OLYM_EVENT_I |      1 |     51 |    136 |00:00:00.01 |       3 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("MEDAL"='Gold')
   3 - access("EVENT"='100m')
 

23 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 5r3z4vtqd0v51, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics GO100AGAIN /LISTAGG(ATHLETE,',' ON
OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY ATHLETE) FROM OLYM_MEDALS
WHERE MEDAL = 'Gold' AND EVENT = '100m'

Plan hash value: 1651183729

------------------------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| OMem \| 1Mem \| Used-Mem \|
------------------------------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 6 \| \| \| \|
\| 1 \| SORT GROUP BY \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 6 \| 4096 \| 4096 \| 4096 (0)\|
\|* 2 \| TABLE ACCESS BY INDEX ROWID BATCHED\| OLYM_MEDALS \| 1 \| 17 \| 44 \|00:00:00.01 \| 6 \| \| \| \|
\|* 3 \| INDEX RANGE SCAN \| OLYM_EVENT_I \| 1 \| 51 \| 136 \|00:00:00.01 \| 3 \| \| \| \|
------------------------------------------------------------------------------------------------------------------------------------------

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

2 - filter("MEDAL"='Gold')
3 - access("EVENT"='100m')

Statement 15

The single column indexes have notably lower clustering factors than the corresponding composite indexes. So the optimizer is more likely to choose them. Combine this with the fact that the savings using the composite index gives tiny benefits and in this case you're better off with the single column indexes.

select index_name, leaf_blocks, clustering_factor from user_indexes


INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR
OLYM_EVENT_I 111 916
OLYM_EVENT_MEDAL_I 137 2219
OLYM_MEDAL_EVENT_I 137 2616
OLYM_MEDAL_I 71 1059

4 rows selected.

INDEX_NAME	LEAF_BLOCKS	CLUSTERING_FACTOR
OLYM_EVENT_I	111	916
OLYM_EVENT_MEDAL_I	137	2219
OLYM_MEDAL_EVENT_I	137	2616
OLYM_MEDAL_I	71	1059

Statement 16

drop index olym_event_i

Index dropped.

Statement 17

drop index olym_medal_i

Index dropped.

Statement 18

drop index olym_event_medal_i

Index dropped.

Statement 19

drop index olym_medal_event_i

Index dropped.

Statement 20

create index olym_event_year_i on olym_medals (event, edition)

Index created.

Statement 21

create index olym_year_event_i on olym_medals (edition, event)

Index created.

Statement 22

select index_name, leaf_blocks, clustering_factor 
from   user_indexes 
where  index_name like '%EVENT%' 
and    index_name like '%YEAR%'


INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR
OLYM_EVENT_YEAR_I 127 7158
OLYM_YEAR_EVENT_I 127 7955

2 rows selected.

INDEX_NAME	LEAF_BLOCKS	CLUSTERING_FACTOR
OLYM_EVENT_YEAR_I	127	7158
OLYM_YEAR_EVENT_I	127	7955

Statement 23

This compresses the values in the first column of the index. In this case, the events.

alter index olym_event_year_i rebuild compress 1

Statement processed.

Statement 24

alter index olym_year_event_i rebuild compress 1

Statement processed.

Statement 25

Compressing event shrinks the index more than compressing year. This is because, although there are fewer rows for each event, the events need more storage. So they are more compressible.

select index_name, leaf_blocks, clustering_factor  
from   user_indexes  
where  index_name like '%EVENT%'  
and    index_name like '%YEAR%'


INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR
OLYM_EVENT_YEAR_I 64 7158
OLYM_YEAR_EVENT_I 111 7955

2 rows selected.

INDEX_NAME	LEAF_BLOCKS	CLUSTERING_FACTOR
OLYM_EVENT_YEAR_I	64	7158
OLYM_YEAR_EVENT_I	111	7955

Statement 26

This compresses both columns in the index.

alter index olym_event_year_i rebuild compress 2

Statement processed.

Statement 27

alter index olym_year_event_i rebuild compress 2

Statement processed.

Statement 28

With both columns compressed there are further savings and both indexes are the same size again. As always, test on your data to find the best column order and compression level for space savings.

select index_name, leaf_blocks 
from   user_indexes  
where  index_name like '%EVENT%'  
and    index_name like '%YEAR%'


INDEX_NAME LEAF_BLOCKS
OLYM_EVENT_YEAR_I 59
OLYM_YEAR_EVENT_I 59

2 rows selected.

INDEX_NAME	LEAF_BLOCKS
OLYM_EVENT_YEAR_I	59
OLYM_YEAR_EVENT_I	59

Statement 29

This has an equality on event (= '100m') and an inequality on edition (> 2000). Indexes are typically most effective when the leading columns have equalities on them. So an index on (event, edition) should perform better than (edition, event).

begin  
  for rws in (  
select /*+ gather_plan_statistics EV>2000 */listagg(athlete,',' on overflow truncate) within group (order by athlete)  
from   olym_medals  
where  event = '100m' and edition > 2000) loop  
    null;  
  end loop;  
end;

Statement processed.

Statement 30

This uses the index on (event, edition). This does a total of 6 IOs (see buffers on the top step of the plan).

select p.*  
from v$sql s, table (  
  dbms_xplan.display_cursor (  
    s.sql_id, s.child_number, 'ALLSTATS LAST'  
  )  
) p  
where s.sql_text like '%EV>2000%'  
and   s.sql_text not like '%not this%'  
and   s.sql_text not like 'begin%'


PLAN_TABLE_OUTPUT
SQL_ID  bfyjp4d5nggum, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics EV>2000 */LISTAGG(ATHLETE,',' ON 
OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY ATHLETE) FROM OLYM_MEDALS 
WHERE EVENT = '100m' AND EDITION > 2000
 
Plan hash value: 97534872
 
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |      1 |00:00:00.01 |       6 |       |       |          |
|   1 |  SORT GROUP BY                       |                   |      1 |      1 |      1 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS       |      1 |      4 |     12 |00:00:00.01 |       6 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | OLYM_EVENT_YEAR_I |      1 |      4 |     12 |00:00:00.01 |       2 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("EVENT"='100m' AND "EDITION">2000)
 

22 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID bfyjp4d5nggum, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics EV>2000 /LISTAGG(ATHLETE,',' ON
OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY ATHLETE) FROM OLYM_MEDALS
WHERE EVENT = '100m' AND EDITION > 2000

Plan hash value: 97534872

-----------------------------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| OMem \| 1Mem \| Used-Mem \|
-----------------------------------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 6 \| \| \| \|
\| 1 \| SORT GROUP BY \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 6 \| 2048 \| 2048 \| 2048 (0)\|
\| 2 \| TABLE ACCESS BY INDEX ROWID BATCHED\| OLYM_MEDALS \| 1 \| 4 \| 12 \|00:00:00.01 \| 6 \| \| \| \|
\|* 3 \| INDEX RANGE SCAN \| OLYM_EVENT_YEAR_I \| 1 \| 4 \| 12 \|00:00:00.01 \| 2 \| \| \| \|
-----------------------------------------------------------------------------------------------------------------------------------------------

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

3 - access("EVENT"='100m' AND "EDITION">2000)

Statement 31

alter index olym_event_year_i invisible

Statement processed.

Statement 32

begin  
  for rws in (  
select /*+ gather_plan_statistics EV>2000 */listagg(athlete,',' on overflow truncate) within group (order by athlete)  
from   olym_medals  
where  event = '100m' and edition > 2000) loop  
    null;  
  end loop;  
end;

Statement processed.

Statement 33

This query uses the index on ( edition, event ). This does more work than the previous index, though only marginally (8 buffers compared to 6). This is partly because the table only stores results up to 2008. So there are only two values for edition > 2000. This enables an index skip scan, which effectively bypasses the first column of the index.

select p.*   
from v$sql s, table (   
  dbms_xplan.display_cursor (   
    s.sql_id, s.child_number, 'ALLSTATS LAST'   
  )   
) p   
where s.sql_text like '%EV>2000%'   
and   s.sql_text not like '%not this%'   
and   s.sql_text not like 'begin%'


PLAN_TABLE_OUTPUT
SQL_ID  bfyjp4d5nggum, child number 1
-------------------------------------
SELECT /*+ gather_plan_statistics EV>2000 */LISTAGG(ATHLETE,',' ON 
OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY ATHLETE) FROM OLYM_MEDALS 
WHERE EVENT = '100m' AND EDITION > 2000
 
Plan hash value: 2350661193
 
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |      1 |00:00:00.01 |       8 |      3 |       |       |          |
|   1 |  SORT GROUP BY                       |                   |      1 |      1 |      1 |00:00:00.01 |       8 |      3 |  2048 |  2048 | 2048  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS       |      1 |      4 |     12 |00:00:00.01 |       8 |      3 |       |       |          |
|*  3 |    INDEX SKIP SCAN                   | OLYM_YEAR_EVENT_I |      1 |      4 |     12 |00:00:00.01 |       4 |      3 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("EDITION">2000 AND "EVENT"='100m')
       filter("EVENT"='100m')
 

23 rows selected.

Impact of Index Column Order on Queries

Additional Information