jump to navigation

Oracle 19c Automatic Indexing: Index Created But Not Actually Used (Because Your Young) March 30, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, CBO, Oracle Indexes.
add a comment

 

scary monsters album

 

The following is an interesting example of how Oracle Automatic Indexing is currently implemented that can result in an Automatic Index being created but ultimately ignored by the CBO.

To illustrate, we begin by creating a simple little table that has two columns of particular interest, CODE2 which has 100 distinct values and CODE3 which has only 10 distinct values. The data of both columns is very poorly clustered with data for both columns sprinkled throughout the table:

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

Table created.

SQL> insert into major_tom3 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;

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

 

When we run the following query (a total of 4 times), it returns close to 10K rows per execution:

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

9968 rows selected.

 

When we look at the Automatic Indexing report after the next Automatic Index job run:

 

INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
---------------------------------------------------------------------------------
| Owner | Table      | Index                 | Key         | Type   | Properties|
---------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM3 | SYS_AI_bnyacywycxx8b  | CODE2,CODE3 | B-TREE | NONE      |
---------------------------------------------------------------------------------

 

We notice that Automatic Indexing has indeed created a new index based on the columns CODE2, CODE3.

VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name  : BOWIE
SQL ID               : 1h4j53jruuzht
SQL Text             : select * from major_tom3 where code3=4 and code2=42
Improvement Factor   : 5.1x
Execution Statistics:
-----------------------------
Original Plan                 Auto Index Plan
----------------------------  ----------------------------
Elapsed Time (s):    1276903                         29310
CPU Time (s):        1226240                         25905
Buffer Gets:          183493                          8954
Optimizer Cost:         7355                          8996
Disk Reads:                0                            26
Direct Writes:             0                             0
Rows Processed:        39872                           9968
Executions:                4                              1

 

The reason why the index was created was because the Automatic Indexing process has determined the query will improve by a factor of 5.1 with the new index in place.

This has been calculated by first determining the average number of consistent gets per execution:  183493 total consistent gets / 4 executions = 45873 consistent gets average per execution

This average consistent gets is then divided by the number of consistent gets with the new index 45873 / 8954 = 5.1x.

So this “Improvement Factor” is determined primarily by the ratio of improvement based on consistent gets.

If we look now at the Plans Section of the Automatic Indexing report:

PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value  : 2354969370
---------------------------------------------------------------------------------------
| 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_TOM3 | 10000 | 280000 | 7355 | 00:00:01 |
---------------------------------------------------------------------------------------

- With Auto Indexes
-----------------------------
Plan Hash Value  : 1676847804
---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows  | Bytes  | Cost | Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      | 10820 | 302960 | 8996 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | MAJOR_TOM3           | 10820 | 302960 | 8996 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_bnyacywycxx8b |  9968 |        |   27 | 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

* 2 - access("CODE2"=42 AND "CODE3"=4)

 

We notice with some interest that the new “improved” plan actually has a CBO cost of 8996 which is greater than the original plan cost of just 7355.

This is because the CBO does not simply use Consistent Gets in its calculations, but a combination of anticipated I/O and CPU costs. Noting that Multi-block reads may physically read more blocks more efficiently than a fewer number of Single-block reads, it’s quite conceivable that the CBO would consider an execution plan with more consistent gets to be more efficient if the underlining I/Os are costed as being cheaper.

This is precisely what’s happening with this query…

If we look at the resultant Automatic Index:

SQL> select index_name, auto, constraint_index, visibility, compression, status from user_indexes where table_name='MAJOR_TOM3';

INDEX_NAME           AUT CON VISIBILIT COMPRESSION   STATUS
-------------------- --- --- --------- ------------- --------
SYS_AI_bnyacywycxx8b YES NO  VISIBLE   DISABLED      VALID

SQL> select index_name, column_name, column_position from user_ind_columns where table_name='MAJOR_TOM3' order by index_name, column_position;

INDEX_NAME           COLUMN_NAME          COLUMN_POSITION
-------------------- -------------------- ---------------
SYS_AI_bnyacywycxx8b CODE2                              1
SYS_AI_bnyacywycxx8b CODE3                              2

 

We can see that the newly created Automatic Index is both VISIBLE and VALID and so can potentially be used by any SQL within the database.

If we now re-run the query:

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

9968 rows selected.

Execution Plan
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            | 10045 |   274K|  7355   (7)| 00:00:01 |
|   1 |  PX COORDINATOR              |            |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000   | 10045 |   274K|  7355   (7)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |            | 10045 |   274K|  7355   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| MAJOR_TOM3 | 10045 |   274K|  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
----------------------------------------------------------
        11 recursive calls
         4 db block gets
     45859 consistent gets
         0 physical reads
         0 redo size
    275084 bytes sent via SQL*Net to client
      7892 bytes received via SQL*Net from client
       666 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
      9968 rows processed

 

We notice that the execution plan remains the same as previously, with the newly available index NOT used by the CBO. This is because the index is deemed too inefficient and the result index based execution plan too expensive by the CBO.

If we now re-run the query with a hint to make the CBO use the index:

SQL> select /*+ index(major_tom3) */ * from major_tom3 where code3=4 and code2=42;

9968 rows selected.

Execution Plan
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      | 10045 |  274K |  9065   (1)| 00:00:01 |
|   1 |  PX COORDINATOR                       |                      |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)                 | :TQ10001             | 10045 |  274K |  9065   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| MAJOR_TOM3           | 10045 |  274K |  9065   (1)| 00:00:01 |
|   4 |     BUFFER SORT                       |                      |       |       |            |          |
|   5 |      PX RECEIVE                       |                      | 10045 |       |    27   (4)| 00:00:01 |
|   6 |       PX SEND HASH (BLOCK ADDRESS)    | :TQ10000             | 10045 |       |    27   (4)| 00:00:01 |
|   7 |        PX SELECTOR                    |                      |       |       |            |          |
|*  8 |         INDEX RANGE SCAN              | SYS_AI_bnyacywycxx8b | 10045 |       |    27   (4)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

8 - access("CODE2"=42 AND "CODE3"=4)

Statistics
----------------------------------------------------------
        16 recursive calls
         4 db block gets
      8958 consistent gets
         0 physical reads
         0 redo size
    275084 bytes sent via SQL*Net to client
      7892 bytes received via SQL*Net from client
       666 SQL*Net roundtrips to/from client
         2 sorts (memory)
         0 sorts (disk)
      9968 rows processed

 

The Automatic Index is now used, but it requires a hint for the index to be used. The cost of using the Automatic Index is too great, even though the Automatic Index has only just been created and created to specifically address this query.

Having one criteria (based on Consistent Gets) used by Automatic Indexing and another criteria (based on estimated I/O and CPU costs) as used by the CBO leaves open this possibility of the Automatic Indexing thinking a new index is a great idea, while the CBO thinks not.

In my next post I’ll show you how Automatic Indexing actually does a rather clever job of improving the current index definition with the introduction of a new SQL…

 

ANNOUNCEMENT: Replacement Oracle Indexing Internals and Performance Tuning Webinars 23-26 March 2020 !! March 11, 2020

Posted by Richard Foote in Oracle Indexing Internals Webinar, Oracle Performance Diagnostics and Tuning Webinar, Webinar.
add a comment

 

London March 2020 Seminar Dates Cancelled 😦

Unfortunately, due to the Coronavirus crisis, both seminars scheduled for London during March 2020 have been cancelled. Full refunds have been processed for all attendees.

 


March 2020 Webinar Replacement Dates Announced !!

 

However, as a quick replacement primarily for those disappointed by the cancellation of the London seminars, I will now be running webinar versions of both events as replacements during the same times the seminars were previously scheduled.

Remaining places for the webinars can be purchased by anyone until all remaining places are taken. Simple click on the Buy Now button to purchase your place for this unique training opportunity.

 

23-24 March 2020 (running between 9:00 – 17:00 GMT): “Oracle Indexing Internals and Best Practices” Webinar:

25-26 March 2020 (running between 9:00 – 17:00 GMT): “Oracle Performance Diagnostics and Tuning” Webinar:

 

You can also purchase tickets to both webinars at a special combo discount:

23-26 March 2020 (running between 9:00 – 17:00 GMT)Both “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Webinars:

 

The cost for an individual webinar is $1,800 Australian Dollars.

The cost for the webinar combo is $3,000 Australian Dollars.

 

For full content details of “Oracle Indexing Internals and Best Practices” Webinar: https://richardfooteconsulting.com/indexing-seminar/

For full content details of “Oracle Performance Diagnostics and Tuning” Webinar: https://richardfooteconsulting.com/performance-tuning-seminar/

 

Please contact me directly if you wish to pay via invoice and direct bank transfer or if you have any questions.

 

Although I might not currently be able to run these events in London, hopefully you’ll still have the opportunity to attend this high acclaimed training 🙂

London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars January 21, 2020

Posted by Richard Foote in Richard Foote Consulting, Richard Foote Seminars, Richard's Blog.
add a comment

 

Time is fast running out to enroll for one of my acclaimed seminars I’ll be running in London, UK in March 2020. The dates and registration links are as follows:

23-24 March 2020: “Oracle Indexing Internals and Best Practices” Seminar – Tickets and Registration Link

25-26 March 2020: “Oracle Performance Diagnostics and Tuning” Seminar – Tickets and Registration Link

You can also purchase tickets to both seminars at a special 20% combo discount (all enrollments so far have gone with this option, so it’s obviously the way to go):

23-26 March 2020:  Both “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars – Tickets and Registration Link

 

The cost for each individual seminar is:

  • Early Bird Rate (enrollments prior to 31 January 2020) £990.00 (+ VAT)
  • General Rate  (enrollments post 31 January 2020) £1190 (+VAT)

The cost for the seminar combo is:

  • Early Bird Rate (enrollments prior to 31 January 2020) £1550.00 (+ VAT)
  • General Rate  (enrollments post 31 January 2020) £1900 (+VAT)

 

The venue is the rather nice Hilton London Kensington.

Prices include attendance to the seminar, both soft and hard copy of the extensive seminar materials, lunch and morning/afternoon tea/coffee.

Both seminars are very highly acclaimed, with past attendees universally applauding the quality and educational outcomes of the training.  They’re both aimed at Oracle Professionals (DBAs and Developers) who are interested in Performance Tuning and how to maximise the performance of both Oracle Databases and associated applications.

All the details of the Oracle Indexing Internals and Best Practices Seminar.

All the details of the Oracle Performance Diagnostic and Tuning Seminar.

Both seminars have strictly limited places to ensure a quality event for all attendees with venues booked with only small classes in mind. So I recommend booking early (as it’s cheaper) and to avoid possible disappointment. I don’t get to run these kind of events in the UK very often (it would be over 2 years since I last run seminars in London) so do take advantage of attending what will be a unique training opportunity while you can.

If you have any questions, please leave a comment or contact me at richard@richardfooteconsulting.com.

Hope to see you at one or both of these seminars next year !!

Oracle Database 19c Automatic Indexing: Minimum Number Of Required Indexes (Low) January 20, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, Oracle Indexes.
add a comment

 

As I discussed in my previous posts, Oracle Automatic Indexing will try and create as few indexes as possible to satisfy existing workloads, even if that means reordering the columns in an existing index.

To illustrate how Automatic Indexing creates as few indexes as possible, I’ll create the following table which has a number of columns with differing numbers of distinct values:

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

Table created.

SQL> insert into thin_white_duke select rownum, ceil(dbms_random.value(0, 100)), ceil(dbms_random.value(0, 1000)), ceil(dbms_random.value(0, 10000)), ceil(dbms_random.value(0, 100000)), '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=>'THIN_WHITE_DUKE');

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, density from user_tab_columns where table_name='THIN_WHITE_DUKE';

COLUMN_NAME     NUM_DISTINCT    DENSITY
--------------- ------------ ----------
ID                   9914368 1.0086E-07
CODE1                    100        .01
CODE2                   1000       .001
CODE3                  10000      .0001
CODE4                 100824 9.9183E-06
NAME                       1          1

 

I then run the following workload within a 15 minute window between Automatic Index tasks:

 

SQL> select * from thin_white_duke where id=42;

SQL> select * from thin_white_duke where code1=42;

SQL> select * from thin_white_duke where code2=42;

SQL> select * from thin_white_duke where code3=42;

SQL> select * from thin_white_duke where code4=42;

SQL> select * from thin_white_duke where code1=42 and code2=42;

SQL> select * from thin_white_duke where code1=42 and code3=42;

SQL> select * from thin_white_duke where code1=42 and code4=42;

SQL> select * from thin_white_duke where code2=42 and code1=42;

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

SQL> select * from thin_white_duke where code2=42 and code4=42;

SQL> select * from thin_white_duke where code3=42 and code1=42;

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

SQL> select * from thin_white_duke where code3=42 and code4=42;

SQL> select * from thin_white_duke where code4=42 and code1=42;

SQL> select * from thin_white_duke where code4=42 and code2=42;

SQL> select * from thin_white_duke where code4=42 and code3=42;

SQL> select * from thin_white_duke where code1=42 and code2=42 and code3=42;

SQL> select * from thin_white_duke where code1=42 and code2=42 and code4=42;

SQL> select * from thin_white_duke where code1=42 and code3=42 and code4=42;

SQL> select * from thin_white_duke where code2=42 and code1=42 and code3=42;

SQL> select * from thin_white_duke where code2=42 and code1=42 and code4=42;

SQL> select * from thin_white_duke where code2=42 and code3=42 and code4=42;

SQL> select * from thin_white_duke where code3=42 and code1=42 and code2=42;

SQL> select * from thin_white_duke where code3=42 and code1=42 and code4=42;

SQL> select * from thin_white_duke where code3=42 and code2=42 and code4=42;

SQL> select * from thin_white_duke where code4=42 and code1=42 and code2=42;

SQL> select * from thin_white_duke where code4=42 and code1=42 and code3=42;

SQL> select * from thin_white_duke where code4=42 and code2=42 and code3=42;

SQL> select * from thin_white_duke where code1=42 and code2=42 and code3=42 and code4=42;

 

Each of these queries have no choice but to perform a Full Table Scan as there are currently no indexes defined to the table. Each query uses a different column list, so for the 30 or so SQL statements, one could potentially create 30 or so different indexes to cover each and every SQL predicate combination used above.

But how many different indexes will Automatic Indexing create?

Let’s have a look…

 

SQL> select dbms_auto_index.report_last_activity() report from dual;

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start               : 15-JUL-2019 07:46:25
Activity end                 : 15-JUL-2019 07:48:56
Executions completed         : 1
Executions interrupted       : 0
Executions with fatal error  : 0

-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates                                : 7
Indexes created (visible / invisible)           : 7 (7 / 0)
Space used (visible / invisible)                : 1.8 GB (1.8 GB / 0 B)
Indexes dropped                                 : 0
SQL statements verified                         : 29
SQL statements improved (improvement factor)    : 29 (147.2x)
SQL plan baselines created                      : 0
Overall improvement factor                      : 147.2x

-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------

Unused indexes    : 0
Space used        : 0 B
Unusable indexes  : 0

 

We can see that Automatic Indexing only create 7 different indexes, that’s it !!

If we look at the indexes that have been created:

 

INDEX DETAILS

-------------------------------------------------------------------------------
1.  The following indexes were created:
-------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
| Owner | Table           | Index                | Key                     | Type   | Properties |
--------------------------------------------------------------------------------------------------
| BOWIE | THIN_WHITE_DUKE | SYS_AI_0u1qx8vgtstkb | CODE4,CODE1,CODE2       | B-TREE | NONE       |
| BOWIE | THIN_WHITE_DUKE | SYS_AI_2j5g09nzrhqsw | CODE2,CODE3,CODE4       | B-TREE | NONE       |
| BOWIE | THIN_WHITE_DUKE | SYS_AI_4y26dtkybxq6k | CODE3,CODE4             | B-TREE | NONE       |
| BOWIE | THIN_WHITE_DUKE | SYS_AI_5pmdyk5pjay8a | CODE3,CODE1,CODE4       | B-TREE | NONE       |
| BOWIE | THIN_WHITE_DUKE | SYS_AI_6uqhvvzabg5n8 | ID                      | B-TREE | NONE       |
| BOWIE | THIN_WHITE_DUKE | SYS_AI_bwfbc6nah6uga | CODE2,CODE4             | B-TREE | NONE       |
| BOWIE | THIN_WHITE_DUKE | SYS_AI_fftcb8q17yy6g | CODE1,CODE2,CODE3,CODE4 | B-TREE | NONE       |
--------------------------------------------------------------------------------------------------

 

We can see how the 7 indexes can collectively cover all 30 odd different SQL predicates within the workload, because the leading columns of at least one index has the necessary columns of each SQL predicate.

If we look at but one SQL example within the Automatic Index report, the query with the predicate on just the CODE4 column:

 

Parsing Schema Name   : BOWIE
SQL ID                 : 20h1p88d1u80r
SQL Text               : select * from thin_white_duke where code4=42
Improvement Factor     : 1200.5x

Execution Statistics:
-----------------------------
Original Plan                  Auto Index Plan
----------------------------  ----------------------------
Elapsed Time (s):     679689                          1148
CPU Time (s):         724033                           933
Buffer Gets:          162070                            91
Optimizer Cost:         8617                           103
Disk Reads:                0                             2
Direct Writes:             0                             0
Rows Processed:          264                            88
Executions:                3                             1

PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value  : 2714752625
------------------------------------------------------------------------------------------
| Id | Operation                      | Name            | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |                 |      |       | 8617 |          |
|  1 |  PX COORDINATOR                |                 |      |       |      |          |
|  2 |    PX SEND QC (RANDOM)         | :TQ10000        |   99 |  3366 | 8617 | 00:00:01 |
|  3 |     PX BLOCK ITERATOR          |                 |   99 |  3366 | 8617 | 00:00:01 |
|  4 |      TABLE ACCESS STORAGE FULL | THIN_WHITE_DUKE |   99 |  3366 | 8617 | 00:00:01 |
------------------------------------------------------------------------------------------

- With Auto Indexes

-----------------------------
Plan Hash Value  : 2447525579
-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |   88 |  2992 |  103 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | THIN_WHITE_DUKE      |   88 |  2992 |  103 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | SYS_AI_0u1qx8vgtstkb |   99 |       |    3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------

 

We see it can now be serviced with the new SYS_AI_0u1qx8vgtstkb index, because it has the following columns (CODE4,CODE1,CODE2), with the CODE4 column as the leading column.

If we look at the details of all these new Automatic Indexes:

 

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

INDEX_NAME           AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_6uqhvvzabg5n8 YES NO  VISIBLE   DISABLED      VALID      10000000       23553             53336
SYS_AI_fftcb8q17yy6g YES NO  VISIBLE   DISABLED      VALID      10000000       37322           9999819
SYS_AI_2j5g09nzrhqsw YES NO  VISIBLE   DISABLED      VALID      10000000       33154           9999815
SYS_AI_bwfbc6nah6uga YES NO  VISIBLE   DISABLED      VALID      10000000       27564           9999822
SYS_AI_5pmdyk5pjay8a YES NO  VISIBLE   DISABLED      VALID      10000000       31908           9999804
SYS_AI_4y26dtkybxq6k YES NO  VISIBLE   DISABLED      VALID      10000000       27697           9999818
SYS_AI_0u1qx8vgtstkb YES NO  VISIBLE   DISABLED      VALID      10000000       31783           9999819
         
SQL> select index_name, column_name, column_position from user_ind_columns where table_name='THIN_WHITE_DUKE' order by index_name, column_position;

INDEX_NAME           COLUMN_NAME     COLUMN_POSITION
-------------------- --------------- ---------------
SYS_AI_0u1qx8vgtstkb CODE4                         1
SYS_AI_0u1qx8vgtstkb CODE1                         2
SYS_AI_0u1qx8vgtstkb CODE2                         3
SYS_AI_2j5g09nzrhqsw CODE2                         1
SYS_AI_2j5g09nzrhqsw CODE3                         2
SYS_AI_2j5g09nzrhqsw CODE4                         3
SYS_AI_4y26dtkybxq6k CODE3                         1
SYS_AI_4y26dtkybxq6k CODE4                         2
SYS_AI_5pmdyk5pjay8a CODE3                         1
SYS_AI_5pmdyk5pjay8a CODE1                         2
SYS_AI_5pmdyk5pjay8a CODE4                         3
SYS_AI_6uqhvvzabg5n8 ID                            1
SYS_AI_bwfbc6nah6uga CODE2                         1
SYS_AI_bwfbc6nah6uga CODE4                         2
SYS_AI_fftcb8q17yy6g CODE1                         1
SYS_AI_fftcb8q17yy6g CODE2                         2
SYS_AI_fftcb8q17yy6g CODE3                         3
SYS_AI_fftcb8q17yy6g CODE4                         4

 

The 7 newly created Automatic Indexes are all VISIBLE and VALID and can collectively service all 30 odd different SQL predicates of the captured workload.

I just know from experience that many DBAs and Developers out there would create many more than just these 7 indexes, partly because it’s just easier to create a new index for each new SQL predicate that doesn’t currently have an appropriate index and partly because it’s not always easy to capture and know what all SQL predicate combinations might be in use by an application.

This is one of the really nice capabilities of Automatic Indexing, in that it tries to service the known workloads it captures with as few indexes as possible, that have all be proven first to indeed improve SQL performance.

In my next blog post, I’ll show another trick that Automatic Indexing can do to reduce the number of different indexes it needs to create…

Oracle Database 19c Automatic Indexing – Need Another Index (Another Brick in The Wall Part 2) January 16, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Oracle Indexes.
add a comment

I previously discussed how Automatic Indexing can effectively cleverly reorder an existing index if it means it can now use the new index to satisfy new SQL predicates. In this post, we’ll explore this example further with some new workloads.

So, we previously ran SQL queries with SQL predicates in the following combinations:

  • CODE1=42 and CODE2=42 and CODE3=42
  • CODE2=42 and CODE3=42
  • CODE3=42

 

so an index based on CODE3, CODE2, CODE1 is able to satisfy all 3 existing predicates.

If we now run a new query with the following predicates based on CODE2=42 and CODE1=42:

SQL> select * from major_tom where code2=42 and code1=42;

no rows selected

Execution Plan

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)|Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    28 |  7318   (6)|00:00:01 |
|   1 |  PX COORDINATOR              |           |       |       |            |         |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000  |     1 |    28 |  7318   (6)|00:00:01 |
|   3 |    PX BLOCK ITERATOR         |           |     1 |    28 |  7318   (6)|00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| MAJOR_TOM |     1 |    28 |  7318   (6)|00:00:01 |
-----------------------------------------------------------------------------------------

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

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

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

 

The current previously re-ordered index can’t now satisfy this new predicate as the leading column of the index (CODE3) is not specified in the SQL predicate and the CBO considers an Index Skip Scan as too expensive an operation. So a Full Table Scan is chosen here by the CBO.

If we wait and see what Automatic Indexing decides to now do:

 

SQL> select dbms_auto_index.report_last_activity() report from dual;

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------

Activity start               : 21-JUN-2019 07:41:55
Activity end                 : 21-JUN-2019 07:42:49
Executions completed         : 1
Executions interrupted       : 0
Executions with fatal error  : 0

-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------

Index candidates                              : 1
Indexes created (visible / invisible)         : 1 (1 / 0)
Space used (visible / invisible)              : 201.33 MB (201.33 MB / 0 B)
Indexes dropped                               : 0
SQL statements verified                       : 1
SQL statements improved (improvement factor)  : 1 (5.1x)
SQL plan baselines created                    : 0
Overall improvement factor                    : 5.1x

-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------

Unused indexes    : 0
Space used        : 0 B
Unusable indexes  : 0

-------------------------------------------------------------------------------

We notice that a new index has been created…

 

INDEX DETAILS

-------------------------------------------------------------------------------
The following indexes were created:
--------------------------------------------------------------------------------
| Owner | Table     | Index                | Key         | Type   | Properties |
--------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_gmhdbjx21zcr1 | CODE1,CODE2 | B-TREE | NONE       |
--------------------------------------------------------------------------------

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

Parsing Schema Name  : BOWIE
SQL ID               : 3y246nzwdpybv
SQL Text             : select * from major_tom where code2=42 and code1=4
Improvement Factor   : 5.1x

 

So Automatic Indexing has decided to now create a new, second index (SYS_AI_gmhdbjx21zcr1) on the table based on CODE1, CODE2, as a single index is no longer capable to address all known SQL predicates.

 

SQL> select index_name, column_name, column_position from user_ind_columns 
where table_name='MAJOR_TOM' order by index_name, column_position;

INDEX_NAME                COLUMN_NAME          COLUMN_POSITION
------------------------- -------------------- ---------------
SYS_AI_00hxxxkgb821n      CODE3                              1
SYS_AI_00hxxxkgb821n      CODE2                              2
SYS_AI_00hxxxkgb821n      CODE1                              3
SYS_AI_gmhdbjx21zcr1      CODE1                              1
SYS_AI_gmhdbjx21zcr1      CODE2                              2

 

So we now have two indexes to cater for all known SQL predicates in which an index has been shown to improve performance.

Automatic Indexing will try and create the minimum number of indexes possible to cater for all known SQL workloads.

 

In my next post, we’ll look at a more complex example in which there could potentially be numerous different indexes that could cater for more extensive workloads and how Automatic Indexing copes rather splendidly…

“Oracle Performance Diagnostics and Tuning” Seminar: Berlin, Germany 31 March – 1 April 2020 January 14, 2020

Posted by Richard Foote in Oracle Performance Diagnostics and Tuning Seminar.
add a comment

 

I’m very excited to be running my high acclaimed “Oracle Performance Diagnostics and Tuning” Seminar in Berlin, Germany on 31 March – 1 April 2020 in conjunction with DOUG. If you enjoyed my “Oracle Indexing Internals and Best Practices” Seminar, you will simply love this seminar 🙂

This seminar details how to maximise the performance of both Oracle databases and associated applications and how to diagnose and address any performance issues as quickly and effectively as possible.

When an application suddenly runs “slow” or when people start complaining about the “poor performance” of the database, there’s often some uncertainty in how to most quickly and most accurately determine the “root” cause of any such slowdown and effectively address any associated issues. In this seminar, we explore a Tuning Methodology that helps Oracle professionals to both quickly and reliably determine the actual causes of performance issues and so ensure the effectiveness of any applied resolutions.

Looking at a number of real-world scenarios and numerous actual examples and test cases, this seminar will show participants how to confidently and reliably diagnose performance issues. The seminar explores in much detail the various diagnostics tools and reports available in Oracle to assist in determining any database performance issue and importantly WHEN and HOW to effectively use each approach. Additionally, participants are also invited to share their own database AWR reports where we can apply the principles learnt in diagnosing the performance of their actual databases/applications.

One of the more common reasons for poor Oracle performance is inefficient or poorly running SQL. This seminar explores in much detail how SQL is executed within the Oracle database, the various issues and related concepts important in understanding why SQL might be inefficient and the many capabilities and features Oracle has in helping to both resolve SQL performance issues and to maintain the stability and reliability of SQL execution.

It’s a fun, but intense, content rich seminar that is suitable for people of all experiences (from beginners to seasoned Oracle experts).

 

For all the details regarding seminar content, venue, costs and how to register, please visit: https://www.doag.org/de/eventdetails?tx_doagevents_single[id]=589552

 

Places are already filling up so please book early to avoid disappointment !!

 

Merry Christmas and Happy New Year !! (“Heroes”) December 24, 2019

Posted by Richard Foote in Oracle Indexes.
4 comments

I would like to take this opportunity to wish all my readers a very Merry Christmas and a most happy, peaceful and prosperous New Year.

My gift this year is not David Bowie and Bing Crosby doing their famous Christmas duet but a performance by Bowie of his classic “Heroes” as featured on the same Bing Crosby Christmas Special in 1977.

 

Enjoy 🙂

London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars December 19, 2019

Posted by Richard Foote in Richard Foote Consulting, Richard Foote Seminars.
add a comment

Places are filling up, but there are still some available at both of my acclaimed seminars that I’ll be running in London, UK in March 2020. The dates and registration links are as follows:

23-24 March 2020: “Oracle Indexing Internals and Best Practices” seminar – Tickets and Registration Link

25-26 March 2020: “Oracle Performance Diagnostics and Tuning” Seminar – Tickets and Registration Link

You can also purchase tickets to both seminars at a special 20% combo discount:

23-26 March 2020:  Both “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars – Tickets and Registration Link

The cost for each individual seminar is:

  • Early Bird Rate (enrollments prior to 31 January 2020) £990.00 (+ VAT)
  • General Rate  (enrollments post 31 January 2020) £1190 (+VAT)

The cost for the seminar combo is:

  • Early Bird Rate (enrollments prior to 31 January 2020) £1550.00 (+ VAT)
  • General Rate  (enrollments post 31 January 2020) £1900 (+VAT)

 

The venue is the rather nice Hilton London Kensington.

Prices include attendance to the seminar, both soft and hard copy of the extensive seminar materials, lunch and morning/afternoon tea/coffee.

Both seminars are very highly acclaimed, with past attendees universally applauding the quality and educational outcomes of the training.  They’re both aimed at Oracle Professionals (DBAs and Developers) who are interested in Performance Tuning and how to maximise the performance of both Oracle Databases and associated applications.

All the details of the Oracle Indexing Internals and Best Practices Seminar.

All the details of the Oracle Performance Diagnostic and Tuning Seminar.

Both seminars have strictly limited places to ensure a quality event for all attendees with venues booked with only small classes in mind. So I recommend booking early (as it’s cheaper) and to avoid possible disappointment. I don’t get to run these kind of events in the UK very often (it would be over 2 years since I last run seminars in London) so do take advantage of attending what will be a unique training opportunity while you can.

If you have any questions, please leave a comment or contact me at richard@richardfooteconsulting.com.

Hope to see you at one or both of these seminars next year !!

Oracle Database 19c Automatic Indexing – Indexed Column Reorder (What Shall We Do Now?) December 18, 2019

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Index Column Order, Index Column Reorder.
1 comment so far

 

I previously discussed how the default column order of an Automatic Index (in the absence of other factors) is based on the Column ID, the order in which the columns are defined in the table.

But what if there are “other factors” based on new workloads and the original index column order is no longer optimal or appropriate ?

I’ll begin by creating a table, with the following key column defined in CODE1, CODE2, CODE3 order:

 

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

Table created.

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

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, density from user_tab_columns where table_name='MAJOR_TOM';

COLUMN_NAME          NUM_DISTINCT    DENSITY
-------------------- ------------ ----------
ID                        9914368 1.0086E-07
CODE1                          10  .00000005
CODE2                         100  .00000005
CODE3                       1000        .001
NAME                           1           1

If I now run the following query with predicates based on these three columns:

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

15 rows selected.

Execution Plan

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |    10 |   280 |  7354   (7)| 00:00:01 |
|   1 |  PX COORDINATOR              |           |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000  |    10 |   280 |  7354   (7)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |           |    10 |   280 |  7354   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| MAJOR_TOM |    10 |   280 |  7354   (7)| 00:00:01 |
------------------------------------------------------------------------------------------

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

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

Statistics
----------------------------------------------------------

        34 recursive calls
         5 db block gets
     45861 consistent gets
         0 physical reads
      1044 redo size
      1087 bytes sent via SQL*Net to client
       588 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
        15 rows processed

 

After the default 15 minutes period in which the Automatic Index task is run, if we look at what Automatic Index has been created:

 

INDEX DETAILS

-------------------------------------------------------------------------------

  1. The following indexes were created:

--------------------------------------------------------------------------------------
| Owner | Table     | Index                | Key               | Type   | Properties |
--------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_9mrs058nrg9d5 | CODE1,CODE2,CODE3 | B-TREE | NONE       |
--------------------------------------------------------------------------------------

VERIFICATION DETAILS

-------------------------------------------------------------------------------
 	1. The performance of the following statements improved: 
-------------------------------------------------------------------------------

Parsing Schema Name  : BOWIE
SQL ID               : ayuj12hggwrvc
SQL Text             : select * from major_tom where code3=42 and code2=42 and code1=4
Improvement Factor   : 45853.8x

Execution Statistics:
-----------------------------

                   Original Plan                 Auto Index Plan
                   ----------------------------  ----------------------------

Elapsed Time (s):  3103394                       946
CPU Time (s):      3092860                       1017
Buffer Gets:       596102                        18
Optimizer Cost:    7354                          18
Disk Reads:        0                             2
Direct Writes:     0                             0
Rows Processed:    195                           15
Executions:        13                            1

 

We can see Oracle has indeed created an Automatic Index (SYS_AI_9mrs058nrg9d5) in the default CODE1, CODE2, CODE3 order.

But if we now run a new query, based on a predicate on just the CODE3 column:

 

SQL> select * from major_tom where code3=42; 

9961 rows selected.

Execution Plan

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           | 10000 |   273K|  7354   (7)| 00:00:01 |
|   1 |  PX COORDINATOR              |           |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000  | 10000 |   273K|  7354   (7)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |           | 10000 |   273K|  7354   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| MAJOR_TOM | 10000 |   273K|  7354   (7)| 00:00:01 |
------------------------------------------------------------------------------------------

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

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

Statistics
----------------------------------------------------------
         8 recursive calls
         4 db block gets
     45853 consistent gets
         0 physical reads
         0 redo size
    166137 bytes sent via SQL*Net to client
       599 bytes received via SQL*Net from client
         3 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
      9961 rows processed

 

We can see the CBO has NOT used the index, as the leading column of the existing index is not mentioned in the SQL predicate and the CBO deems an Index Skip Scan as too expensive:

If we now run an SQL with predicates based on just the CODE2 and CODE3 columns:

 

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

101 rows selected.

Execution Plan

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |   100 |  2800 |  7354   (7)| 00:00:01 |
|   1 |  PX COORDINATOR              |           |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000  |   100 |  2800 |  7354   (7)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |           |   100 |  2800 |  7354   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| MAJOR_TOM |   100 |  2800 |  7354   (7)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

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

Statistics
----------------------------------------------------------

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

 

The existing Automatic Index is again not used as the important CODE1 column which is the leading column of the index is not mentioned in the SQL predicates and the CBO deems an Index Skip Scan as too expensive.

I know from experience many DBAs would simply create a new index with CODE3, CODE2 as the leading columns. But what does Automatic Indexing do in the scenario?

 

SQL> select dbms_auto_index.report_last_activity() report from dual;

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------

Activity start               : 21-JUN-2019 02:39:32
Activity end                 : 21-JUN-2019 02:40:18
Executions completed         : 1
Executions interrupted       : 0
Executions with fatal error  : 0

-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------

Index candidates                       : 1
Indexes created (visible / invisible)  : 1 (1 / 0)
Space used (visible / invisible)       : 243.27 MB (243.27 MB / 0 B)
Indexes dropped (space reclaimed)      : 1 (243.27 MB)
SQL statements verified                : 1
SQL statements improved                : 0
SQL plan baselines created             : 0
Overall improvement factor             : 0x

-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------

Unused indexes    : 0
Space used        : 0 B
Unusable indexes  : 0

 

We notice in the Automatic Indexing report it’s stating that one new index has been created BUT that one index has also been dropped.

 

INDEX DETAILS
-------------------------------------------------------------------------------

         1. The following indexes were created:

-------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
| Owner | Table     | Index                | Key               | Type   | Properties |
--------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_00hxxxkgb821n | CODE3,CODE2,CODE1 | B-TREE | NONE       |
--------------------------------------------------------------------------------------
 
 	1. The following indexes were dropped: 

-------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
| Owner | Table     | Index                | Key               | Type   | Properties |
--------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_9mrs058nrg9d5 | CODE1,CODE2,CODE3 | B-TREE | NONE       |
--------------------------------------------------------------------------------------
 


SQL> select index_name, column_name, column_position from user_ind_columns where table_name='MAJOR_TOM' order by column_position;

INDEX_NAME           COLUMN_NAME     COLUMN_POSITION
-------------------- --------------- ---------------
SYS_AI_00hxxxkgb821n CODE3                         1
SYS_AI_00hxxxkgb821n CODE2                         2
SYS_AI_00hxxxkgb821n CODE1                         3

 

Automatic Indexing has created a new index (SYS_AI_00hxxxkgb821n), with the columns in CODE3, CODE2, CODE1 order, as this index is able to service all currently known SQL predicate combinations:

WHERE CODE3 = x AND CODE2 = y AND CODE1= z

WHERE CODE3 = x AND CODE2 = y

WHERE CODE3 = x

as the leading column in the index is listed in all three current scenarios.

 

This means the previous index in CODE1, CODE2, CODE3 order is now redundant as the new index can fully service all know SQL predicate combinations. As a result, Automatic Indexing drops the redundant index.

This is a really nice capability of Automatic Indexing, the ability to effectively reorder the columns within an index based on new workloads.

But what if subsequent new SQL workloads means the new index is not able on its own to service all such workloads. I’ll discuss this scenario in my next post.

Oracle Database 19c Automatic Indexing: Index Compression (Ghosteen) December 9, 2019

Posted by Richard Foote in 19c, 19c New Features, Advanced Index Compression, Automatic Indexing, AUTO_INDEX_COMPRESSION, Index Column Order, Index Compression, Index Internals.
add a comment

 

 

In my previous post on Automatic Indexing, I discussed how the default index column order (in absence of other factors) is column id, the order in which the columns are defined in the table. In this post, I’ll explore if this changes if index compression is also implemented.

By default, Automatic Indexing does NOT use index compression. However, if you have access to the Advanced Compression option, you have the choice to turn on index compression in the following manner:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_COMPRESSION','ON');

PL/SQL procedure successfully completed.

Note: the AUTO_INDEX_COMPRESSION parameter is not actually documented, which could be a documentation bug or that Oracle is not yet ready to release this capability. The above will enable Automatic Indexes to be created with Compress Advanced Low, which is the “no-brain” index compression option as it will compress (deduplicate) safely with negligible CPU overheads. However, index column order is still critical to ensure effective compression as we shall see…

We begin by creating a simple table, that has four columns of interest, CODE1, CODE2, CODE3 and STATUS. They are defined in this order within the table, but CODE1 has the most number of distinct values (5000000 distinct values), then CODE2 (1000), then CODE3 (10) and finally STATUS which only has the 1 distinct value.

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

Table created.

SQL> insert into bowie_compress select rownum, mod(rownum, 5000000)+1, mod(rownum, 1000)+1, mod(rownum, 10)+1, 'COMPLETED’,

'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_COMPRESS');

PL/SQL procedure successfully completed.

 

In terms of being the most efficient from a compression perspective, it would be better to have the index defined in STATUS, CODE3, CODE2, CODE1 order, so that the leading columns in the index have the most duplicated values that enable effective deduplication and hence index compression. I’ve discussed the importance of index column for effective index compression a number of times previously. Arguably, it would be better not to actually index the STATUS column at all as with just 1 distinct value, provides no effective filtering benefits.

Having the CODE1 column as the leading column however with so many distinct values would effectively make the index non-compressible (with LOW compression), as the leading column would have too many distinct values to benefit much from compression.

So how does Automatic Indexing handle this scenario? Does it keep the same default index column order or does it alter the index column order to provide better index compression benefits?

Let’s run the following SQL with all four columns in the predicates and see what index Automatic Indexing creates…

 

SQL> select * from bowie_compress where code1=42 and code2=42 and code3=2 and status='COMPLETED';

Execution Plan

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    41 |   9998  (5)| 00:00:01 |
|   1 |  PX COORDINATOR              |                |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000       |     1 |    41 |   9998  (5)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |                |     1 |    41 |   9998  (5)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| BOWIE_COMPRESS |     1 |    41 |   9998  (5)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - storage("CODE1"=42 AND "CODE2"=42 AND "CODE3"=2 AND "STATUS"='COMPLETED')
     filter("CODE1"=42 AND "CODE2"=42 AND "CODE3"=2 AND "STATUS"='COMPLETED')

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

 

If we look at the Automatic Indexing report for the period in which the above SQL was run:

 

SQL> select dbms_auto_index.report_last_activity() report from dual;

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start               : 18-JUL-2019 00:18:35
Activity end                 : 18-JUL-2019 00:19:58
Executions completed         : 1
Executions interrupted       : 0
Executions with fatal error  : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates                                : 1
Indexes created (visible / invisible)           : 1 (1 / 0)
Space used (visible / invisible)                : 293.6 MB (293.6 MB / 0 B)
Indexes dropped                                 : 0
SQL statements verified                         : 1
SQL statements improved (improvement factor)    : 1 (63563.9x)
SQL plan baselines created                      : 0
Overall improvement factor                      : 63563.9x

-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes    : 0
Space used        : 0 B
Unusable indexes  : 0

-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
--------------------------------------------------------------------------------------------------
| Owner | Table          | Index                | Key                      | Type   | Properties |
--------------------------------------------------------------------------------------------------
| BOWIE | BOWIE_COMPRESS | SYS_AI_bkdhrsd29vd4f | CODE1,CODE2,CODE3,STATUS | B-TREE | NONE       |
--------------------------------------------------------------------------------------------------

 

We see that Automatic Index has created the index with all four columns from the SQL predicate in again the default column order as the column order as defined in the table (CODE1, CODE2, CODE3, STATUS). Even though Automatic Index Compression was enabled, it hasn’t considered the column cardinalities in its determination of best index column order.

Automatic Indexing has the tendency to index ALL columns specified in SQL predicates, regardless of whether all such columns provide filtering benefits AND does not consider the best column order from a compression perspective when determining index column order. So definitely room for improvement here methinks.

If we look at the definition and size of the resultant Automatic Index:

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

INDEX_NAME                   AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
---------------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_bkdhrsd29vd4f         YES NO  VISIBLE   ADVANCED LOW  VALID      10000000       35451          10000000

SQL> select index_name, column_name, column_position
from user_ind_columns where table_name='BOWIE_COMPRESS' order by index_name, column_position;

INDEX_NAME                   COLUMN_NAME     COLUMN_POSITION
---------------------------- --------------- ---------------
SYS_AI_bkdhrsd29vd4f         CODE1                         1
SYS_AI_bkdhrsd29vd4f         CODE2                         2
SYS_AI_bkdhrsd29vd4f         CODE3                         3
SYS_AI_bkdhrsd29vd4f         STATUS                        4

 

We note the index has 35451 leaf blocks.

If we were to create the index manully in a more appropriate manner from a compression perspective, with the index columns defined in reverse order and also with another index without the redundant STATUS column:

SQL> create index bowie_compress_best_order_i on bowie_compress(status, code3, code2, code1) compress advanced low;

Index created.

SQL> create index bowie_compress_best_order2_i on bowie_compress(code3, code2, code1) compress advanced low;

Index created.

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

INDEX_NAME                   AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
---------------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_bkdhrsd29vd4f         YES NO  VISIBLE   ADVANCED LOW  VALID      10000000       35451          10000000
BOWIE_COMPRESS_BEST_ORDER_I  NO  NO  VISIBLE   ADVANCED LOW  VALID      10000000       23509          10000000
BOWIE_COMPRESS_BEST_ORDER2_I NO  NO  VISIBLE   ADVANCED LOW  VALID      10000000       23462          10000000

 

We notice the resultant indexes are substantially smaller, at just 23509 and 23462 leaf blocks respectively.

It could well be that Index Compression is not yet documented because Oracle appreciates that more work yet needs to be done to create indexes optimally from a compression perspective…

Presenting at UKOUG Techfest19 Conference in Brighton, UK September 13, 2019

Posted by Richard Foote in Techfest19.
add a comment

I’m very excited to be attending my 3rd UKOUG Conference, this year re-badged as Techfest19. The fact it’s being held in Brighton is a little disconcerting for a Crystal Palace fan, but really looking forward nonetheless to what has always been one of the very best Oracle conferences on the yearly calendar.

I have a number of presentations and Roundtable sessions that I’ll be participating in (follow the links to find the session abstracts):

 

I was very honoured after my 2nd attendance in Birmingham to have been awarded a “Lifetime Achievement Award” by the UKOUG, so I feel the duty bound to make sure my presentations are “top-shelf”.

Really looking forward to catching up with all my UK/European Oracle friends, please say hello if you see me (I’ll be the one wearing the Crystal Palace football jersey) 🙂

 

 

 

 

Announcement: Australia/NZ “Let’s Talk Database” Events October 2019 !! September 12, 2019

Posted by Richard Foote in 19c, Automatic Indexing, Exadata X8, Let's Talk Database.
add a comment

I’ve very excited to announce the next series of Oracle “Let’s Talk Database” events to be run throughout Australia and New Zealand in October 2019.

I’ll be discussing two exciting topics this series, “Oracle Database 19c New Features” and “Oracle Exadata X8“. As always, these sessions run between 9am-1pm, include a networking lunch and are free, but you MUST register to attend.

Dates, locations and registration links are as follows (Note the Sydney location is NOT the Oracle office in North Ryde):

Canberra:     22 October – Registration Link (Oracle Canberra Office)

Sydney:       23 October – Registration Link (Stone and Chalk, York St)

Melbourne: 24 October – Registration Link (Oracle Melbourne Office)

Brisbane:     29 October – Registration Link (Oracle Brisbane Office)

Auckland:    30 October – Registration Link (Level 13, AMP Centre, 29 Customs Street West)

Wellington: 31 October – Registration Link (Oracle Wellington Office)

 

Session details as follows:

Oracle Database 19c New Features

The latest Oracle Database Release 19c has introduced many exciting new features and enhanced capabilities that will be of much interest to both DBAs and Developers. This session will discuss in some detail a number of these new features with practical examples on how they can assist Oracle professionals maximize the benefits of the Oracle Database, especially in relation to Oracle Cloud and Oracle Engineered Systems deployments. New features discussed include Hybrid Table Partitions, Active Standby DML Redirect, Real Time Statistics, High-Frequency Automatic Optimizer Statistics Collection, SQL Quarantine,  new JSON Enhancements, DISTINCT option for LISTAGG aggregate and the most exciting new feature for some time, Automatic Indexing.

 

Oracle Exadata X8

The Oracle Exadata X8 Database Machine is the latest release in Oracle’s engineered systems platform designed specifically to deliver dramatically better performance, cost effectiveness, and availability for Oracle databases. This session will discuss various expanded and new capabilities introduced with Oracle Exadata X8 such as the new Exadata Extended Storage server, automated CPU, Memory and Network monitoring, advanced intrusion detection and Docker support. The session also examines why the Exadata Platform is so critical for the Autonomous Database Cloud Services and the unique Oracle Database19c capabilities such as Memoptimized Rowstore, Automatic Indexing, Real-Time Statistics and SQL Quarantine that are supported on Exadata.

 

So plenty of exciting Oracle database stuff to discuss. Hope to catch you at one of these events !!

Oracle Database 19c Automatic Indexing: Default Index Column Order Part II (Future Legend) September 11, 2019

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Clustering Factor, Index Column Order.
2 comments

In Part I, we explored some options that Oracle might adopt when ordering the columns within an Automatic Index by default, in the absence of other factors where there is only the one SQL statement to be concerned with.

A point worth making is that if all columns of an index are specified within SQL equality predicates, then the ordering of columns within an index is of little consequence. I’ve discussed this point a number of times previously.

Let’s explore if perhaps the resultant Clustering Factor of an index might be a factor in the default Automatic Index column order.

I begin by creating a table that has two columns of interest, CODE1 which is poorly clustered and CODE2 which is very well clustered:

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

Table created.

SQL> create sequence muse_seq;

Sequence created.

SQL> create or replace procedure pop_muse as
begin
  for code1_value in 1..10000 loop
     for i in 1..100 loop
        insert into muse values (muse_seq.nextval, ceil(dbms_random.value(0,100)), code1_value, 'Back Holes');
     end loop;
   end loop;
   commit;
end;
/

Procedure created.

SQL> exec pop_muse

PL/SQL procedure successfully completed.

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

 

We then run the following query in which to hopefully create an Automatic Index on both CODE1 and CODE2 columns:

 

SQL> select * from muse where code1=406 and code2=83;

15 rows selected.

 

If we wait for the Automatic Index to be created and check out the Automatic Index report:

 

INDEX DETAILS
-------------------------------------------------------------------------------
  1 The following indexes were created:
*: invisible

----------------------------------------------------------------------------
| Owner | Table | Index                | Key         | Type   | Properties |
----------------------------------------------------------------------------
| BOWIE | MUSE  | SYS_AI_c1m8fkukj1368 | CODE2,CODE1 | B-TREE | NONE       |
----------------------------------------------------------------------------

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

Parsing Schema Name  : BOWIE

SQL ID               : 0pdqsvpggupnz

SQL Text             : select * from muse where code1=406 and code2=83

Improvement Factor   : 4092.8x

 

SQL> select index_name, column_name, column_position from user_ind_columns
     where table_name='MUSE' order by index_name, column_position;

INDEX_NAME             COLUMN_NAME          COLUMN_POSITION
---------------------- -------------------- ---------------
SYS_AI_c1m8fkukj1368   CODE2                              1
SYS_AI_c1m8fkukj1368   CODE1                              2

 

We notice the index is created in CODE2, CODE1 column order.

If we create a manual index with the column order reversed:

 

SQL> create index muse_code1_code2_i on muse(code1, code2);

Index created.

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

INDEX_NAME             AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
---------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------
SYS_AI_c1m8fkukj1368   YES NO  VISIBLE   DISABLED      VALID       1000000        2506            362900
MUSE_CODE1_CODE2_I     NO  NO  VISIBLE   DISABLED      VALID       1000000        2510            129878

 

We notice that the manual index has the better resultant Clustering Factor. So the Clustering Factor doesn’t appear to be a factor in Automatic Index column order (no pun intended).

If we re-create the initial table in Part I, but this time with the columns defined in the table in reverse order:

 

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

Table created.

SQL> insert into major_tom3 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  commit;

Commit complete.

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

PL/SQL procedure successfully completed.

 

If we again run the following query:

 

SQL> select * from major_tom3 where code3=4 and code2=42 and code1=42

...

 

And wait for the Automatic Index to be created and look at the resultant report:

 

INDEX DETAILS

-------------------------------------------------------------------------------
   1 The following indexes were created:
---------------------------------------------------------------------------------------
| Owner | Table      | Index                | Key               | Type   | Properties |
---------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM3 | SYS_AI_g6sw030tg5ba9 | CODE3,CODE2,CODE1 | B-TREE | NONE       |
---------------------------------------------------------------------------------------

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

Parsing Schema Name  : BOWIE

SQL ID               : 22kts3uwj7kma

SQL Text             : select * from major_tom3 where code3=4 and code2=42 and code1=42

Improvement Factor   : 45854.1x

 

SQL> select i.index_name, i.column_name, i.column_position, t.num_distinct
from user_ind_columns i, user_tab_columns t
where i.table_name = t.table_name and i.column_name = t.column_name and i.table_name='MAJOR_TOM3'
order by i.index_name, i.column_position;

INDEX_NAME           COLUMN_NAME     COLUMN_POSITION NUM_DISTINCT
-------------------- --------------- --------------- ------------
SYS_AI_g6sw030tg5ba9 CODE3                         1         1000
SYS_AI_g6sw030tg5ba9 CODE2                         2          100
SYS_AI_g6sw030tg5ba9 CODE1                         3           10

 

We notice that the resultant Automatic Index has been created in CODE3, CODE2, CODE1 order.

After creating many many Automatic Indexes under all sorts of different scenarios, the DEFAULT behaviour is for Oracle to create Automatic Indexes in Column ID order (the order in which they are defined in the table definition).

Of course as we’ll see in future posts, if there are several conflicting SQL predicates, there are various other factors that govern a more appropriate Automatic Index order, but the fact that Oracle creates Automatic Indexes in Column ID order in the absence of other factors is useful to know.

As I said previously, if all indexed columns are specified in SQL equality predicates, index column order has little consequence. But as we’ll see in the next post, there are scenarios where index column order can be very important and this default index column order may not be the most optimal…

London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars !! September 3, 2019

Posted by Richard Foote in Oracle Index Seminar, Oracle Indexes, Oracle Performance Diagnostics and Tuning Seminar.
add a comment

seminar photo

It’s with great excitement that I announce I’ll finally be returning to London, UK in March 2020 to run both of my highly acclaimed seminars. The dates and registration links are as follows:

23-24 March 2020: “Oracle Indexing Internals and Best Practices” seminar – Tickets and Registration Link

25-26 March 2020: “Oracle Performance Diagnostics and Tuning” Seminar – Tickets and Registration Link

You can also purchase tickets to both seminars at a special 20% combo discount:

23-26 March 2020:  Both “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars – Tickets and Registration Link

The cost for each individual seminar is:

  • Early Bird Rate (enrollments prior to 31 January 2020) £990.00 (+ VAT)
  • General Rate  (enrollments post 31 January 2020) £1190 (+VAT)

The cost for the seminar combo is:

  • Early Bird Rate (enrollments prior to 31 January 2020) £1550.00 (+ VAT)
  • General Rate  (enrollments post 31 January 2020) £1900 (+VAT)

 

The venue is the rather nice Hilton London Kensington.

Prices include attendance to the seminar, both soft and hard copy of the extensive seminar materials, lunch and morning/afternoon tea/coffee.

Both seminars are very highly acclaimed, with past attendees universally applauding the quality and educational outcomes of the training.  They’re both aimed at Oracle Professionals (DBAs and Developers) who are interested in Performance Tuning and how to maximise the performance of both Oracle Databases and associated applications.

All the details of the Oracle Indexing Internals and Best Practices Seminar.

All the details of the Oracle Performance Diagnostic and Tuning Seminar.

Both seminars have strictly limited places to ensure a quality event for all attendees with venues booked with only small classes in mind. So I recommend booking early (as it’s cheaper) and to avoid possible disappointment. I don’t get to run these kind of events in the UK very often (it would be over 2 years since I last run seminars in London) so do take advantage of attending what will be a unique training opportunity while you can.

If you have any questions, please leave a comment or contact me at richard@richardfooteconsulting.com.

Hope to see you at one or both of these seminars next year !!

Oracle Database 19c Automatic Indexing: Default Index Column Order Part I (Anyway Anyhow Anywhere) September 2, 2019

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Index Column Order, Oracle Indexes.
1 comment so far

pin ups

The next thing I was curious about regarding Automatic Indexing was in which order would Oracle by default order the columns within an index. This can be a crucial decision with respect to the effectiveness of the index (but then again, may not be so crucial as well). Certainly one would expect the index column order be dependent on the SQL predicates running in the database and I’ll discuss all that in future posts, but what is the default behaviour here with regard index column order based (for now) on a single SQL predicate.

I could come up with a number of possible options that Oracle might adopt when determining the default index column order such as:

  • Column Name Order
  • Column ID Order
  • (Reverse) Column Cardinality Order
  • Best Clustering Factor
  • Other (Random even)

So to investigate this, I started with a basic table with 3 columns (CODE1, CODE2, CODE3) that had differing levels of cardinality:

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

Table created.

SQL> insert into major_tom select rownum, mod(rownum, 10)+1, ceil(dbms_random.value(0, 100)), ceil(dbms_random.value(0, 1000)), 'David Bowie' from dual connect by level  commit;

Commit complete.

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

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, density from user_tab_columns where table_name='MAJOR_TOM';

COLUMN_NAME          NUM_DISTINCT    DENSITY
-------------------- ------------ ----------
ID                        9914368 1.0086E-07
CODE1                          10  .00000005
CODE2                         100  .00000005
CODE3                        1000       .001
NAME                            1          1

I then ran the following query with a predicate based on the 3 columns CODE1, CODE2 and CODE3:

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

15 rows selected.

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

If we look at the resultant Automatic Index:

INDEX DETAILS

-------------------------------------------------------------------------------
1 . The following indexes were created:
--------------------------------------------------------------------------------------
| Owner | Table     | Index                | Key               | Type   | Properties |
--------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_9mrs058nrg9d5 | CODE1,CODE2,CODE3 | B-TREE | NONE       |
--------------------------------------------------------------------------------------

 

SQL> select i.index_name, i.column_name, i.column_position, t.num_distinct
from user_ind_columns i, user_tab_columns t
where i.table_name = t.table_name and i.column_name = t.column_name and i.table_name='MAJOR_TOM'
order by i.index_name, i.column_position;

INDEX_NAME           COLUMN_NAME     COLUMN_POSITION NUM_DISTINCT
-------------------- --------------- --------------- ------------
SYS_AI_9mrs058nrg9d5 CODE1                         1           10
SYS_AI_9mrs058nrg9d5 CODE2                         2          100
SYS_AI_9mrs058nrg9d5 CODE3                         3         1000

 

We notice that the Automatic Index is in CODE1, CODE2, CODE3 order.

If we create a similar table, but this time have the columns with a different order of cardinality:

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

Table created.

SQL> insert into major_tom2 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;

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

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

15 rows selected.

 

We notice that the resultant automatic index is still in the same CODE1, CODE2 and CODE3 order:

INDEX DETAILS

-------------------------------------------------------------------------------
1. The following indexes were created:
---------------------------------------------------------------------------------------
| Owner | Table      | Index                | Key               | Type   | Properties |
---------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM2 | SYS_AI_7w9t3tt9u171r | CODE1,CODE2,CODE3 | B-TREE | NONE       |
---------------------------------------------------------------------------------------

 

SQL> select i.index_name, i.column_name, i.column_position, t.num_distinct
from user_ind_columns i, user_tab_columns t
where i.table_name = t.table_name and i.column_name = t.column_name and i.table_name='MAJOR_TOM2'
order by i.index_name, i.column_position;

INDEX_NAME           COLUMN_NAME     COLUMN_POSITION NUM_DISTINCT
-------------------- --------------- --------------- ------------
SYS_AI_7w9t3tt9u171r CODE1                         1         1000
SYS_AI_7w9t3tt9u171r CODE2                         2          100
SYS_AI_7w9t3tt9u171r CODE3                         3           10

 

So we can eliminate column cardinality as being a contributing factor in Oracle deciding in which manner to order the indexed columns.

Which is unfortunate as we’ll see in a future post when we decide to implement Oracle Index Compression with Automatic Indexing.

In the next post, we’ll explore further other considerations and confirm how Oracle does indeed decide to order columns within an Automatic Index by default.