jump to navigation

Automatic Indexes: Automatically Rebuild Unusable Indexes Part IV (“Nothing Has Changed”) May 31, 2022

Posted by Richard Foote in 19c, 19c New Features, 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Full Table Scans, Index Column Order, Index Internals, Local Indexes, Mixing Auto and Manual Indexes, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Oracle19c, Unusable Indexes.
add a comment

In a previous post, I discussed how Automatic Indexing (AI) does not automatically rebuild a manually built index that is in an Unusable state (but will rebuild an Unusable automatically created index).

The demo I used was a simple one, based on manually created indexes referencing a non-partitioned table.

In this post, I’m going to use a demo based on manually created indexes referencing a partitioned table.

I’ll start by creating a rather basic range-based partitioned table, using the RELEASE_DATE column to partition the data by year:

SQL> CREATE TABLE big_bowie (id number, album_id number, country_id number, release_date date,
total_sales number) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2021 VALUES LESS THAN (MAXVALUE));

Table created.

SQL> INSERT INTO big_bowie SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

I’ll next manually create a couple indexes; a non-partitioned index based on just the ALBUM_ID column and a prefixed locally partitioned index, based on the columns RELEASE_DATE, TOTAL_SALES:

 

SQL> create index album_id_i on big_bowie(album_id);

Index created.

SQL> create index release_date_total_sales_i on big_bowie(release_date, total_sales) local;

Index created.

 

If we now re-organise just partition ALBUMS_2017 (without using the ONLINE clause):

SQL> alter table big_bowie move partition albums_2017;

Table altered.

This results in the non-partitioned index and the ALBUMS_2017 local index partition becoming Unusable:

SQL> select index_name, status from user_indexes where table_name='BIG_BOWIE';

INDEX_NAME                     STATUS
------------------------------ --------
ALBUM_ID_I                     UNUSABLE
RELEASE_DATE_TOTAL_SALES_I     N/A

SQL> select index_name, partition_name, status from user_ind_partitions
     where index_name='RELEASE_DATE_TOTAL_SALES_I';

INDEX_NAME                     PARTITION_NAME       STATUS
------------------------------ -------------------- --------
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2014          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2015          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2016          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2017          UNUSABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2018          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2019          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2020          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2021          USABLE

Let’s now run a number of queries a number of times. The first series is based on a predicate on just the ALBUM_ID column, such as:

SQL> select * from big_bowie where album_id=42;

2000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1510748290

-------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows | Bytes | Cost (%CPU) | Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           | 2000 | 52000 |    7959 (2) | 00:00:01 |       |       |
|   1 | PARTITION RANGE ALL |           | 2000 | 52000 |    7959 (2) | 00:00:01 |     1 |     8 |
| * 2 |  TABLE ACCESS FULL  | BIG_BOWIE | 2000 | 52000 |    7959 (2) | 00:00:01 |     1 |     8 |
-------------------------------------------------------------------------------------------------

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

2 - storage("ALBUM_ID"=42)
  - filter("ALBUM_ID"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      48593 consistent gets
      42881 physical reads
          0 redo size
      44289 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)
       2000 rows processed

We’ll also run a series of queries based on both the RELEASE_DATE column using dates from the unusable index partition and the TOTAL_SALES column, such as:

SQL> select * from big_bowie where release_date='01-JUN-2017' and total_sales=42;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3245457041

----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows | Bytes | Cost (%CPU) | Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |    1 |    26 |     986 (2) | 00:00:01 |       |       |
|   1 | PARTITION RANGE SINGLE |           |    1 |    26 |     986 (2) | 00:00:01 |     4 |     4 |
| * 2 |  TABLE ACCESS FULL     | BIG_BOWIE |    1 |    26 |     986 (2) | 00:00:01 |     4 |     4 |
----------------------------------------------------------------------------------------------------

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

2 - storage("TOTAL_SALES"=42 AND "RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
   - filter("TOTAL_SALES"=42 AND "RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))

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

Without a valid/usable index, the CBO currently has no choice but to use a Full Table Scan on the first query, and a Full Partition Scan on the partition with the unusable local index.

So what does AI make of things? Does it rebuild the unusable manually created indexes so the associated indexes can be used to improve these queries?

If we wait until the next AI task completes and check out the indexes on the table:

SQL> select index_name, status, partitioned from user_indexes where table_name='BIG_BOWIE';

INDEX_NAME                     STATUS   PAR
------------------------------ -------- ---
RELEASE_DATE_TOTAL_SALES_I     N/A      YES
ALBUM_ID_I                     UNUSABLE NO
SYS_AI_aw2825ffpus5s           VALID    NO
SYS_AI_2hf33fpvnqztw           VALID    NO

SQL> select index_name, partition_name, status from user_ind_partitions
     where index_name='RELEASE_DATE_TOTAL_SALES_I';

INDEX_NAME                     PARTITION_NAME       STATUS
------------------------------ -------------------- --------
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2014          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2015          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2016          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2017          UNUSABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2018          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2019          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2020          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2021          USABLE

We notice that AI has created two new non-partitioned automatic indexes, while both the manually created indexes remain in the same unusable state. If we look at the columns associated with these new automatic indexes:

SQL> select index_name, column_name, column_position
from user_ind_columns where table_name='BIG_BOWIE';

INDEX_NAME                     COLUMN_NAME          COLUMN_POSITION
------------------------------ -------------------- ---------------
ALBUM_ID_I                     ALBUM_ID                           1
RELEASE_DATE_TOTAL_SALES_I     RELEASE_DATE                       1
RELEASE_DATE_TOTAL_SALES_I     TOTAL_SALES                        2
SYS_AI_aw2825ffpus5s           ALBUM_ID                           1
SYS_AI_aw2825ffpus5s           RELEASE_DATE                       2
SYS_AI_2hf33fpvnqztw           TOTAL_SALES                        1
SYS_AI_2hf33fpvnqztw           RELEASE_DATE                       2

As we can see, AI has logically replaced both unusable indexes.

The manual index based on ALBUM_ID has been replaced with an inferior index based on the ALBUM_ID, RELEASE_DATE columns. Inferior in that the automatic index is both redundant (if only the manual index on ALBUM_ID were rebuilt) and in that it has the logically unnecessary RELEASE_DATE column to inflate the size of the index.

The manual index based on the RELEASE_DATE, TOTAL_SALES columns has been replaced with a redundant automatic index based on the reversed TOTAL_SALES, RELEASE_DATE columns.

Now, AI has indeed automatically addressed the current FTS performance issues associated with these queries by creating these indexes, but a better remedy would have been to rebuild the unusable manual indexes and hence negate the need for these redundant automatic indexes.

But currently (including with version 21.3), AI will NOT rebuild unusable manually created indexes, no matter the scenario, and will instead create additional automatic indexes if it’s viable for it to do so.

A reason why Oracle at times recommends dropping all current manually created secondary indexes before implementing AI (although of course this comes with a range of obvious issues and concerns).

If these manually created indexes didn’t exist, I’ll leave it as an exercise to the discernable reader on what automatic indexes would have been created…

As always, this restriction may change in future releases…

Automatic Indexes: Automatically Rebuild Unusable Indexes Part III (“Waiting For The Man”) May 17, 2022

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, Exadata, Full Table Scans, Manual Indexes, Mixing Auto and Manual Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Unusable Indexes.
1 comment so far

I’ve previously discussed how Automatic Indexing (AI) will not only create missing indexes, but will also rebuild unusable indexes, be it a Global or Local index.

However, all my previous examples have been with Automatic Indexes. How does AI handle unusable indexes in which the indexes were manually created?

In my first demo, I’ll start by creating a basic non-partitioned table:

SQL> create table bowie_stuff (id number, album_id number, country_id number, release_date date, total_sales number);

Table created.

SQL> insert into bowie_stuff select rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

We next manually create an index on the highly selective TOTAL_SALES column:

SQL> create index bowie_stuff_total_sales_i on bowie_stuff(total_sales);

Index created.

Let’s now invalidate the index by re-organising the table without the online clause:

SQL> alter table bowie_stuff move;

Table altered.

SQL> select index_name, status from user_indexes where table_name='BOWIE_STUFF';

INDEX_NAME                     STATUS
------------------------------ --------
BOWIE_STUFF_TOTAL_SALES_I      UNUSABLE

So the index is now in an UNUSABLE state.

To perk up the interest of AI, I’ll run a number of queries such as the following with a predicate condition on TOTAL_SALES:

select * from bowie_stuff where total_sales=42;

18 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 910563088

---------------------------------------------------------------------------------
| Id | Operation          | Name        | Rows | Bytes | Cost (%CPU) | Time     |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |             |   20 |   520 |    7427 (2) | 00:00:01 |
|* 1 |  TABLE ACCESS FULL | BOWIE_STUFF |   20 |   520 |    7427 (2) | 00:00:01 |
---------------------------------------------------------------------------------

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

1 - storage("TOTAL_SALES"=42)
    filter("TOTAL_SALES"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      42746 consistent gets
      42741 physical reads
          0 redo size
       1392 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)
         18 rows processed

Without a valid index, the CBO has no choice but to perform an expensive full table scan.

However, it doesn’t matter how long I wait or how many different queries I run similar to the above, AI currently will never rebuild an unusable index if the index was manually created.

AI will only rebuild unusable automatically created indexes.

I’ve discussed previously how automatic and manually created indexes often don’t gel well together and is one of the key reasons why Oracle recommends dropping all manually created secondary indexes if you wish to implement AI (using the DBMS_AUTO_INDEX.DROP_SECONDARY_INDEXES procedure, which I’ll discuss in a future post).

Things can get a little interesting with AI, if the underlining table is partitioned and you have manually created unusable indexes.

As I’ll discuss in my next post…

Oracle 19c Automatic Indexing: Mixing Manual and Automatic Indexes Part I (I Can’t Read) April 21, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, CBO, Clustering Factor, Mixing Auto and Manual Indexes, Oracle Indexes.
4 comments

tin machine album

In previous articles, I discussed how Automatic Indexing has the capability to add columns or reorder the column list of previously created Automatic Indexes. However, how does Automatic Indexing handle these types of scenarios with regard to existing manually created indexes?

To investigate, let’s create a table identical to the table I created in my previous blog post where Automatic Indexing created an index that was ultimately not used by the CBO because although Automatic Indexing finds the new index more efficient, the CBO costs it as being too expensive and ignores it.

SQL> create table major_tom5 (id number, code1 number, code2 number, code3 number, name varchar2(42));

Table created.

SQL> insert into major_tom5 select rownum, mod(rownum, 1000)+1, ceil(dbms_random.value(0, 100)), ceil(dbms_random.value(0, 10)),  '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=>'MAJOR_TOM5');

PL/SQL procedure successfully completed.

However, in this demo, I’m going to first create a manual index, but with the column list in CODE3, CODE2 order. This is the opposite order in which a default Automatic Index would be created (CODE2, CODE3 order) as this is the order of the columns in the table definition:

SQL&gt; create index major_tom5_code3_code2_i on major_tom5(code3, code2);

Index created.

SQL&gt; select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor <span style="color:var(--color-text);">from user_indexes where table_name='MAJOR_TOM5';</span>

INDEX_NAME                AUT CON VISIBILIT COMPRESSION   STATUS   NUM_ROWS   LEAF_BLOCKS CLUSTERING_FACTOR
------------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
MAJOR_TOM5_CODE3_CODE2_I  NO  NO  VISIBLE   DISABLED      VALID      10000000       24181           8974538

The resultant index has a terrible Clustering Factor of 8974538 on a 10M row table.

If we run the following query with filtering predicates on these 2 indexed columns:

SQL> select * from major_tom5 where code3=4 and code2=42;

10051 rows selected.

Execution Plan
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |  9982 |   272K|  7355   (7)| 00:00:01 |
|   1 |  PX COORDINATOR              |            |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000   |  9982 |   272K|  7355   (7)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |            |  9982 |   272K|  7355   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| MAJOR_TOM5 |  9982 |   272K|  7355   (7)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - storage("CODE2"=42 AND "CODE3"=4)
       filter("CODE2"=42 AND "CODE3"=4)

Statistics
----------------------------------------------------------
         6  recursive calls
         0  db block gets
     45888  consistent gets
        68  physical reads
      5256  redo size
    149822  bytes sent via SQL*Net to client
       610  bytes received via SQL*Net from client
         4  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
     10051  rows processed

The CBO decides to NOT use the available index as it deems it too expensive, especially with such a poor Clustering Factor, to return the resultant 10,051 rows.

But what will Automatic Indexing do now. If we wait the 15 minute period until the next Automatic Indexing period and look at the resultant Automatic Indexing report:

INDEX DETAILS

-------------------------------------------------------------------------------
The following indexes were created:
*: invisible
---------------------------------------------------------------------------------
| Owner | Table      | Index                | Key         | Type   | Properties |
---------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM5 | SYS_AI_2ajmncxsmg189 | CODE2,CODE3 | B-TREE | NONE       |
---------------------------------------------------------------------------------

VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------

Parsing Schema Name  : BOWIE
SQL ID               : fmpwux2ptvasq
SQL Text             : select * from major_tom5 where code2=42 and code3=4
Improvement Factor   : 5.1x

Automatic Indexing has created a new index based on the column list CODE2, CODE3, because it considers such an index would improve performance of the query by a factor of 5.1x.

However, it has not recognised that the existing manual index based the column list CODE3, CODE2 would have done precisely the same job.

If we look further on in the Automatic Indexing report:

Execution Statistics:
-----------------------------
                              Original Plan                 Auto Index Plan
                              ----------------------------  ----------------------------
Elapsed Time (s):             993225                        26436
CPU Time (s):                 963727                        22535
Buffer Gets:                  137756                        9000
Optimizer Cost:               7355                          9069
Disk Reads:                   0                             26
Direct Writes:                0                             0
Rows Processed:               30153                         10051
Executions:                   3                             1

PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------

Plan Hash Value  : 2129981950
---------------------------------------------------------------------------------------
| Id | Operation                      | Name       | Rows   | Bytes  | Cost  | Time    |
---------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |            |        |        |  7355 |         |
|  1 |  PX COORDINATOR                |            |        |        |       |         |
|  2 |    PX SEND QC (RANDOM)         | :TQ10000   |  10000 | 280000 |  7355 | 00:00:01|
|  3 |     PX BLOCK ITERATOR          |            |  10000 | 280000 |  7355 | 00:00:01|
|  4 |      TABLE ACCESS STORAGE FULL | MAJOR_TOM5 |  10000 | 280000 |  7355 | 00:00:01|
---------------------------------------------------------------------------------------

- With Auto Indexes
-----------------------------

Plan Hash Value  : 459198994
---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows  | Bytes  | Cost | Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      | 10159 | 284452 | 9069 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | MAJOR_TOM5           | 10159 | 284452 | 9069 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_2ajmncxsmg189 | 10051 |        |   27 | 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE2"=42 AND "CODE3"=4)

We notice the new execution plan using the newly created Automatic Index actually has a greater CBO cost than the previous FTS execution plan.

As we discussed in the previous post on when Automatic Indexing creating indexes that are not ultimately used by the CBO, although Automatic Indexing has indeed created this index because it has determined it’s going to be more efficient by a factor of 5.1x due to the reduction in Buffer Gets (137756 buffer gets old plan / 3 executions = 45,919 / 9000 buffer gets with index = 5.1), the CBO considers the execution plan using the Automatic Index to have a larger cost at 9069 than the previous FTS cost at just 7355.

Again just as with the existing, logically equivalent manually created index, the reason why the new Automatic Index is deemed too expensive by the CBO is because it likewise has the same terrible Clustering Factor:

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='MAJOR_TOM5';

INDEX_NAME               AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------ --- --- --------- ------------- -------- ---------- ----------- -----------------
MAJOR_TOM5_CODE3_CODE2_I NO  NO  VISIBLE   DISABLED      VALID      10000000       24181           8974538
SYS_AI_2ajmncxsmg189     YES NO  VISIBLE   DISABLED      VALID      10000000       23697           8974538

If we re-run the initial query again with the newly created Visible/Valid Automatic Index:

SQL> select * from major_tom5 where code3=4 and code2=42;

10051 rows selected.

Execution Plan
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |  9982 |   272K|  7355   (7)| 00:00:01 |
|   1 |  PX COORDINATOR              |            |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000   |  9982 |   272K|  7355   (7)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |            |  9982 |   272K|  7355   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| MAJOR_TOM5 |  9982 |   272K|  7355   (7)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

    4 - storage("CODE2"=42 AND "CODE3"=4)
        filter("CODE2"=42 AND "CODE3"=4)

Statistics
----------------------------------------------------------
         6  recursive calls
         0  db block gets
     45888  consistent gets
        68  physical reads
      5256  redo size
    149822  bytes sent via SQL*Net to client
       610  bytes received via SQL*Net from client
         4  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
     10051  rows processed

The CBO ignores the newly created Automatic Index as it did the logically equivalent manually created index and uses the previous, cheaper FTS execution plan.

Automatic Indexing was NOT able to recognise that we already had an equivalent manually created index and so now we have TWO indexes that the CBO simply ignores as being too expensive…

More on mixing Automatic and Manual Indexes on my next post.