jump to navigation

Automatic Indexes: Scenarios Where Automatic Indexes NOT Created Part I (“Always Crashing In The Same Car”) April 26, 2022

Posted by Richard Foote in 19c, 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Full Table Scans, MAX, MIN, Oracle, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Performance Tuning.
1 comment so far

As I’ve discussed previously, Oracle has increased the number of scenarios in which it will now create automatic indexes, such as with non-equality predicates and JSON expressions.

However, as of Oracle Database 21c, there are still a number of scenarios where an automatic index will NOT be created, even though an index might prove beneficial.

One such scenario is when the Min/Max of a column is required.

As I’ve discussed a number of times previously, Oracle can very efficiently use an index to determine either the Min or Max value of a column, by (hopefully) just visiting the first or last leaf block in an index. The INDEX FULL SCAN (MIN/MAX) execution plan path can be used explicitly for this purpose.

If I create a simple table as follows:

SQL> create table bowie_min (id number constraint bowie_min_pk primary key, code number, name varchar2(42));

Table created.

SQL> insert into bowie_min select rownum, mod(rownum, 1000000)+1, 'David Bowie' from dual connect by level <= 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_MIN');

PL/SQL procedure successfully completed.

 

And then run the following queries a number of times that return the Min and Max of the CODE column:

SQL> select min(code) from bowie_min;

Execution Plan
----------------------------------------------------------
Plan hash value: 1068446691

----------------------------------------------------------------------------------------
| Id | Operation                 | Name      | Rows | Bytes | Cost (%CPU) | Time       |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |           |    1 |     5 |    6706 (2) | 00:00:01   |
|  1 | SORT AGGREGATE            |           |    1 |     5 |             |            |
|  2 | TABLE ACCESS STORAGE FULL | BOWIE_MIN |  10M |   47M |    6706 (2) | 00:00:01   |
----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      39430 consistent gets
      39421 physical reads
          0 redo size
        569 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> select max(code) from bowie_min;

Execution Plan
----------------------------------------------------------
Plan hash value: 1068446691

----------------------------------------------------------------------------------------
| Id | Operation                 | Name      | Rows | Bytes | Cost (%CPU) | Time       |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |           |    1 |     5 |    6706 (2) | 00:00:01   |
|  1 | SORT AGGREGATE            |           |    1 |     5 |             |            |
|  2 | TABLE ACCESS STORAGE FULL | BOWIE_MIN |  10M |   47M |    6706 (2) | 00:00:01   |
----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      39430 consistent gets
      39421 physical reads
          0 redo size
        569 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

 

Currently, the CBO has no choice but to use a Full Table Scan (FTS) as there is currently no index on the CODE column.

So what does Automatic Indexing (AI) make of things?

Nothing.

Currently, AI will not create an index in this scenario, no matter how many times I execute these queries.

If we look at the indexes on the table after a significant period of time after running these queries:

SQL> select index_name, auto from user_indexes where table_name='BOWIE_MIN';

INDEX_NAME   AUT
------------ ---
BOWIE_MIN_PK NO

No Automatic Indexes. To improve the performance of these queries, we currently have to manually create the associated index:

SQL> create index bowie_min_code_i on bowie_min(code);

Index created.

If we now re-run these queries and look at the execution plan:

SQL> select min(code) from bowie_min;

Execution Plan
----------------------------------------------------------
Plan hash value: 252811132

-----------------------------------------------------------------------------------------------
| Id | Operation                 | Name             | Rows | Bytes | Cost (%CPU) | Time       |
-----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |                  |    1 |     5 |       3 (0) | 00:00:01   |
|  1 | SORT AGGREGATE            |                  |    1 |     5 |             |            |
|  2 | INDEX FULL SCAN (MIN/MAX) | BOWIE_MIN_CODE_I |    1 |     5 |       3 (0) | 00:00:01   |
-----------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          3 consistent gets
          0 physical reads
          0 redo size
        569 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

We can see that the CBO is now indeed using the index to return the Min/Max values with a vastly reduced number of consistent gets (down to just 3 from the previous 38538).

However, a key point here is that Automatic Indexes only works on an Exadata platform and Exadata has various smarts that potentially makes accessing data via a “FTS” in this manner much more efficient than in non-Exadata environments.

Oracle may well take the position that getting Min/Max data on a Exadata is potentially efficient enough and doesn’t on its own warrant the creation of an index.

More on this in future posts…

MIN / MAX Quiz Answer (One Shot) August 31, 2011

Posted by Richard Foote in CBO, Index Full Scan (Min/Max), MAX, MIN, Oracle Indexes.
12 comments

Not only are my regular blog readers a good deal better looking than the average person, but they’re quite a bit smarter as well ūüôā

As most people have correctly identified, the answer I was after to my previous Min/Max Quiz is that Option 1 is indeed the odd one out, as it’s the only option that can’t make use of the Index Full Scan (Min/Max)¬†access path.

If you’re after either the minimum or the maximum of a column value and the column is indexed, the CBO can potentially use¬†the Index Full Scan (Min/Max), which simply navigates to the first¬†OR last leaf block in the index structure looking for the Min or Max value in question. Oracle can of course navigate to the first (left-most) or last (right-most)¬†leaf blocks very easily by simply following the associated first/last pointers in the Root/Branch structure of the index. All things being equal and providing there haven’t been any subsequent deletes to empty out the index entries from these leaf blocks, Oracle can very quickly determine the minimum or maximum value of the column.

However, the Index Full Scan (Min/Max) can only visit one side of the index, not both. Therefore, if you want both the minimum and¬†the maximum column value, an Index Full Scan (Min/Max) is not viable and the CBO¬†is forced to look for other alternatives. It sounds like such a trivial thing to implement but that’s how it goes. I do remember way back when Oracle9i was released and the introduction of the Index Skip Scan I thought perhaps Oracle might also soon introduce an index skip scan version of¬†Min/Max (as it basically just needs to “skip” all the index leaf blocks in the “middle” of the index via another lookup of the index), but it was not to be.

So for a query such as in Option 1, if the column IS NULL and does not have a NOT NULL constraint, then:

  
SQL> select min(id), max(id) from muse;

   MIN(ID)    MAX(ID)
---------- ----------
         1    1000000
Execution Plan
----------------------------------------------------------
Plan hash value: 421245806

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |  2125   (1)| 00:00:26 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL| MUSE |  1000K|  4882K|  2125   (1)| 00:00:26 |
---------------------------------------------------------------------------

 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7795  consistent gets
       7788  physical reads
          0  redo size
        470  bytes sent via SQL*Net to client
        395  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

  

Then an (expensive) Full Table Scan is likely the way to go. However, if the column has a NOT NULL constraint and the index is indeed smaller than the parent table, then:

  
SQL> alter table muse modify id not null;

Table altered.

SQL> select min(id), max(id) from muse;

   MIN(ID)    MAX(ID)
---------- ----------
         1    1000000
Execution Plan
----------------------------------------------------------
Plan hash value: 1592024618

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     5 |   611   (1)| 00:00:08 |
|   1 |  SORT AGGREGATE       |           |     1 |     5 |            |          |
|   2 |   INDEX FAST FULL SCAN| MUSE_ID_I |  1000K|  4882K|   611   (1)| 00:00:08 |
-----------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2242  consistent gets
          0  physical reads
          0  redo size
        470  bytes sent via SQL*Net to client
        395  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

  

Then an Index Fast Full Scan becomes viable.

All the other¬†options I’ve used to return the Min/Max of the column all incorporate two separate SELECT clauses and so all can potentially use an Index Full Scan (Min/Max) access path for each distinct clause.

Be it when using a UNION operation:

  
SQL> select min(id) as "MIN(ID)/MAX(ID)" from muse union all select max(id) from muse;

 MIN(ID)/MAX(ID)
---------------
              1
        1000000
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1370940131

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     2 |    10 |     6  (50)| 00:00:01 |
|   1 |  UNION-ALL                  |           |       |       |            |          |
|   2 |   SORT AGGREGATE            |           |     1 |     5 |            |          |
|   3 |    INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I |     1 |     5 |     3   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE            |           |     1 |     5 |            |          |
|   5 |    INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I |     1 |     5 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        456  bytes sent via SQL*Net to client
        395  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

Although as pointed out in the comments, this does return 2 rows.

Or I could use Scalar Sub-Queries:

  
SQL> select (select min(id) from muse) "MIN(ID)", (select max(id) from muse) "MAX(ID)" from dual;

   MIN(ID)    MAX(ID)
---------- ----------
         1    1000000
Execution Plan
----------------------------------------------------------
Plan hash value: 2177063930

----------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |     1 |       |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |           |     1 |     5 |            |       |
|   2 |   INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I |     1 |     5 |     3   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE            |           |     1 |     5 |            |       |
|   4 |   INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I |     1 |     5 |     3   (0)| 00:00:01 |
|   5 |  FAST DUAL                 |           |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        468  bytes sent via SQL*Net to client
        395  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

  

Or indeed I could use a WITH clause:

  
SQL> with min_id as (select min(id) from muse), max_id as (select max(id) from muse) select * from min_id, max_id;

   MIN(ID)    MAX(ID)
---------- ----------
         1    1000000

 
Execution Plan
----------------------------------------------------------
Plan hash value: 3280440773

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)|Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    26 |     6   (0)|00:00:01 |
|   1 |  NESTED LOOPS                |           |     1 |    26 |     6   (0)|00:00:01 |
|   2 |   VIEW                       |           |     1 |    13 |     3   (0)|00:00:01 |
|   3 |    SORT AGGREGATE            |           |     1 |     5 |            |         |
|   4 |     INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I |     1 |     5 |     3   (0)|00:00:01 |
|   5 |   VIEW                       |           |     1 |    13 |     3   (0)|00:00:01 |
|   6 |    SORT AGGREGATE            |           |     1 |     5 |            |         |
|   7 |     INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I |     1 |     5 |     3   (0)|00:00:01 |
------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        470  bytes sent via SQL*Net to client
        395  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

They’re all subtly different but they all can make use of the Index Full¬†Scan (Min/Max) scan for each separate SELECT clause and they all can perform the necessary resultant work with just 6 consistent gets in my example.

More quizzes to come …

MIN/MAX Quiz (Funtime) August 29, 2011

Posted by Richard Foote in MAX, MIN, Oracle Indexes.
8 comments

At my work I’ve recently introduced a little “Question of the Day”¬†for my fellow DBAs, hopefully to pass on a few interesting little titbits of information and have a bit of fun along the way.

I was going to just write a blog article on the following topic of how Oracle deals with MIN and MAX queries, but decided to give you all the chance to have a bit of a think first via a little quiz. This was a question I asked recently, see what you think:

There are many ways one could write a query to determine the MIN and MAX of a column. That said, which of the following is the odd one out:

1) select min(id), max(id) from muse;

2) select min(id) as “MIN(ID)/MAX(ID)” from muse¬†union all select max(id) from muse;

3) select (select min(id) from muse) “MIN(ID)”, (select max(id) from muse) “MAX(ID)” from dual;

4) with min_id as (select min(id) from muse), max_id as (select max(id) from muse) select * from min_id, max_id;

 

It’s a “big” table, there’s an index on the ID and the ID has a not null constraint (the constraint¬†doesn’t actually matter to the answer of the¬†question, but it can make a subtle difference).

Now, one could come up with lots of different possible answers (eg: option 4 is the only one that uses a WITH clause), however the answer I’m after is one that might be best associated with the common theme of this blog.

You don’t have to make a comment, you can just quietly ponder which option is somewhat different or maybe consider which one you may not want to use.

No more clues ūüôā

More details in a few day’s time.

PS. Folk from my work and Jonathan Lewis may not answer as they already know the answer I’m after ūüôā