9

Automatic Indexing: Non-Equality Predicates Part IV (“Like A Rocket Man”)

 1 year ago
source link: https://richardfoote.wordpress.com/2022/11/29/automatic-indexing-non-equality-predicates-part-iv-like-a-rocket-man/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Automatic Indexing: Non-Equality Predicates Part IV (“Like A Rocket Man”) November 29, 2022

Posted by Richard Foote in 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, LIKE Predicates, Non-Equality Predicates, Oracle, Oracle 21c, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, _EXADATA_FEATURE_ON.
trackback

like-a-rocket-man.jpg?w=300&h=300

Forgive me, it’s been a while since I last posted, but life has so many distractions these days 🙂

I recently had a question on whether a LIKE predicate can generate an Automatic Index now that non-equality predicates are supported since Oracle Database 21c.

Now the answer I initially provided was “well, why don’t you just test it for yourself“. However, his subsequent responses highlighted to me that not everyone might necessarily know how to potentially play with many of the Exadata features, even if you don’t directly have access to an Exadata environment.

So the purpose of this post is not only to answer this question, but also to just highlight HOW to potentially test things out for yourself when you’re not lucky enough to work directly with Exadata.

One obvious manner in which to play on an Exadata environment is to simply create and connect to an Oracle Autonomous Database environment using Oracle’s Cloud services (which are all Exadata-based environments), where you can easily, FOR FREE, and WITH NO TIME RESTRICTIONS play with many Exadata database features. The “Always Free Cloud Services” is truly a fabulous resource provided by Oracle, where you can have a couple of Autonomous Database environments always at your disposal (and very very easily and quickly just drop an existing database environment and re-create a new one).

Follow the link for all the information you need on how to get started with Oracle’s Always Free Cloud Services: https://www.oracle.com/au/autonomous-database/free-trial/

If the version of Oracle Database you like to play with isn’t currently available on the Oracle Autonomous Database platforms, another option is to simply download the database version you want to play with and just make it think it’s actually on an Exadata platform, by setting the following hidden parameter:

SQL> alter system set "_exadata_feature_on"=true scope=spfile;
System altered.

and restart your database.

You can now at least play and learn about many of the Exadata database features (such as Automatic Indexing), without having an actual Exadata machine on hand.

OK, now that you have an Exadata (or Exadata-like) environment on hand, you can go about answering for yourself these types of questions…

So, does Automatic Indexing now work in the case of a LIKE predicate?

First, make sure Automatic Indexing is enabled:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
PL/SQL procedure successfully completed.

Begin by creating and populating a basic table structure to test. The following table just has a few basic columns, with the MIXED_STUFF column simply populated with the rownum concatenated with a constant string:

SQL> create table aladdin_sane (id number, code1 number, grade number, mixed_stuff varchar2(42), name varchar2(42));
Table created.
SQL> insert into aladdin_sane select rownum, mod(rownum,100000), mod(rownum,100), rownum || ' David Bowie ' || rownum, 'ZIGGY STARDUST' from dual connect by level <=10000000;
10000000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ALADDIN_SANE');
PL/SQL procedure successfully completed.

I then run the following query several times with a LIKE predicate that returns just the one row from my 10M row table:

SQL> select * from aladdin_sane where mixed_stuff like '4242 %';
Execution Plan
----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |    1 |    57 |   14805 (2)| 00:00:01 |
| * 1 |  TABLE ACCESS FULL | ALADDIN_SANE |    1 |    57 |   14805 (2)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("MIXED_STUFF" LIKE '4242 %')
filter("MIXED_STUFF" LIKE '4242 %')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
169813 consistent gets
84940 physical reads
0 redo size
912 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Without any indexes currently in place, the CBO has no choice but to use a FTS. But, with only 1 row returned from this 10M table, an appropriate index would almost certainly be beneficial. So what does Automatic Index do in this scenario?

Once we wait for the next running of the Automatic Indexing jobs to complete, we can check:

SQL> select dbms_auto_index.report_last_activity() report from dual;
REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start              : 29-NOV-2022 12:52:30
Activity end                : 29-NOV-2022 12:53:50
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)             : 452.98 MB (452.98 MB / 0 B)
Indexes dropped                              : 0
SQL statements verified                      : 6
SQL statements improved (improvement factor) : 1 (169815.2x)
SQL plan baselines created (SQL statements)  : 1 (1)
Overall improvement factor                   : 2x
-------------------------------------------------------------------------------
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 | ALADDIN_SANE | SYS_AI_dzhahcw1cf0mw | MIXED_STUFF | B-TREE | NONE       |
-----------------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID              : fgvdbfsfwb9jv
SQL Text            : select * from aladdin_sane where mixed_stuff like '4242%'
Improvement Factor  : 169815.2x
Execution Statistics:
-----------------------------
Original Plan                Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 10869872                     433
CPU Time (s):     9778626                      433
Buffer Gets:      2377413                      4
Optimizer Cost:   14805                        4
Disk Reads:       1189160                      2
Direct Writes:    0                            0
Rows Processed:   14                           1
Executions:       14                           1

So, it appears that Automatic Indexing has indeed created a new index. We can now check out the new index details:

select index_name, auto, constraint_index, visibility, compression, status
from user_indexes where table_name='ALADDIN_SANE';
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='ALADDIN_SANE' order by index_name, column_position;
INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
SYS_AI_dzhahcw1cf0mw           MIXED_STUFF                                  1

Automatic Indexing has indeed created a VALID/VISIBLE index on the MIXED_STUFF column.

If we now re-run the query:

SQL> select * from aladdin_sane where mixed_stuff like '4242 %';
Execution Plan
------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |    1 |    57 |       4 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | ALADDIN_SANE         |    1 |    57 |       4 (0)| 00:00:01 |
| * 2 |   INDEX RANGE SCAN                   | SYS_AI_dzhahcw1cf0mw |    1 |       |       3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MIXED_STUFF" LIKE '4242 %')
filter("MIXED_STUFF" LIKE '4242 %')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
116204 consistent gets
84940 physical reads
0 redo size
912 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

We can see the newly generated plan now uses the new Automatic Index.

But due to Deferred Invalidations (which I’ve discussed previously), which in Oracle 21c delay the invalidation of SQL cursors due to new indexes, we may need to (for example) flush the shared_pool for the new plan to actually be used (a safe enough option in our play/test environment):

SQL> alter system flush shared_pool;
System altered.
SQL> select * from aladdin_sane where mixed_stuff like '4242 %';
Execution Plan
------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |    1 |    57 |       4 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | ALADDIN_SANE         |    1 |    57 |       4 (0)| 00:00:01 |
| * 2 |   INDEX RANGE SCAN                   | SYS_AI_dzhahcw1cf0mw |    1 |       |       3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MIXED_STUFF" LIKE '4242 %')
filter("MIXED_STUFF" LIKE '4242 %')
Statistics
----------------------------------------------------------
263 recursive calls
0 db block gets
508 consistent gets
0 physical reads
0 redo size
916 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
70 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from aladdin_sane where mixed_stuff like '4242 %';
Execution Plan
------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |    1 |    57 |       4 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | ALADDIN_SANE         |    1 |    57 |       4 (0)| 00:00:01 |
| * 2 |   INDEX RANGE SCAN                   | SYS_AI_dzhahcw1cf0mw |    1 |       |       3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MIXED_STUFF" LIKE '4242 %')
filter("MIXED_STUFF" LIKE '4242 %')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
916 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

We can now see that the new plan has indeed been adopted with the substantial decrease in consistent gets, down to just 5 from the previous 169813 with the FTS.

So yes, Automatic Indexing does indeed now work with LIKE predicates, but most importantly, it’s very easy for you to test and see these things for yourself.

In which case, you won’t need blogs such as this in the future to show you the way… 🙂


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK