Slow indexExists performance in Oracle

Description

From http://forum.liquibase.org/topic/indexexists-performance#49382000001090001

I'm using Liquibase 3.2.0 snapshot and also suffering from severely slow performance in the index checks. I did some digging and found the query (Oracle) and stack trace is where the slowness appears to be. In my environment I was finding this particular query was taking between 20 and 30 minutes.

SQL Query:

Environment

Oracle

Activity

Show:
Daniel Lawry
August 20, 2015, 4:50 PM

I should also note that when I use the indexExists precondition without the not precondition, things are quite fast. That's what leads me to think it's a combination of the two tags.

Daniel Lawry
August 20, 2015, 6:36 PM

After further testing, the issue is not the "not" precondition (I looked inside the NotPrecondition.class file to see what it was doing) but the actual "indexExists" precondition, whether or not it is wrapped in a "not" precondition.

The only difference between the slow / fast indexExists preconditions are the number of columns the index uses (an indexExists for a three column index is extremely slow, while an indexExists for a single column is fast)

Hope that helps!

Daniel Lawry
August 20, 2015, 7:18 PM

I'm not familiar with all the intricacies of different types of constraints in Oracle, but it seems like the check to see if an index exists is overly complex. I quickly came up with the following SQL which ran in milliseconds and found the index from my example above

I'm sure it could be cleaned up some more but it seems to do the job, whether looking for a unique constraint, foreign key, or primary key. I could be wrong though!

Nathan Voxland
August 21, 2015, 8:37 PM

I'll look at making the exists check quicker

Björn Kautler
July 25, 2019, 3:25 PM

Any news on this Nathan?
I still think at least the rule-hint should be removed again immediately.
The rule based optimizer is very old, outdated and not supported.

From Oracle 7 (released through the 90s) documentation (https://docs.oracle.com/cd/A57673_01/DOC/server/doc/A48506/optimiz.htm#2090):

The RULE hint, along with the rule-based approach, will not be available in future versions of Oracle.

From Oracle 8 (released end of 90s) documentation (https://docs.oracle.com/cd/A83908_02/NT816EE/DOC/server.816/a76992/hints.htm#4415):

The RULE hint, along with the rule-based approach, may not be supported in future releases of Oracle.

From Oracle 9 (released beginning of 00s) documentation (https://docs.oracle.com/cd/B10501_01/server.920/a96533/hintsref.htm#4991):

Oracle Corporation strongly advises the use of cost-based optimization. Rule-based optimization will be deprecated in a future release.

From Oracle 10 (released mid of 00s) documentation (https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements006.htm#BABCBDCB):

The RULE hint disables the use of the optimizer. This hint is not supported and should not be used.

In Oracle 11 and newer documentation (https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#SQLRF50503, https://docs.oracle.com/database/121/SQLRF/sql_elements006.htm#SQLRF50503, https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Comments.html#GUID-F9B87932-5149-4CA6-9FAF-E66410E66F5C, https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comments.html#GUID-F9B87932-5149-4CA6-9FAF-E66410E66F5C) the rule hint was completely removed from the documentation.

 

The same guy from which Petr Masopust got the idea to use the rule hint (http://www.dba-oracle.com/t_tuning_oracle_dictionary_sql.htm) also said years ago that the RBO should not be used anymore as it can be removed any time now that it is not supported anymore:
http://www.dba-oracle.com/oracle_tips_rbo_cbo.htm
http://www.dba-oracle.com/s_rule_based_optimizer_migration.htm
http://www.dba-oracle.com/s_rbo_to_cbo_migration.htm

 

Furthermore one of our customers DBA complained about a foreignKeyConstraintExists precondition that was running for more than 6 hours and using more than 200 GiB temp tablespace. According to his investigations this was caused by this exact rule-hint which is anyway only still present for backwards compatibility and the chance to migrate to cost-based optimizer (removing the hint) smoothly without a big bang.

Reporter

Nathan Voxland

Fix versions

Affects versions

Priority

Major
Configure