jump to navigation

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.
trackback

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…

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: