Run-time side effect of dropping a composite unused index

Unused Index although they seem not needed , dropping them must be carefully tested as it can have some side effect. As explained by Jonathan Lewis, due to index sanity check cardinality miss estimate can happen after dropping the unused index.

“The reason for this is the “index sanity check”. When the optimizer is doing its cardinality estimates, if it see equality conditions on the set of columns that make up an index it can use the distinct_keys statistic from the index in the calculation rather than using the standard calculation of multiplying together the num_distinct of the separate columns. ”

There is also a good example of the impact this can have on the query execution plan(switching join method from hash join to nested loop due to the drop of the estimated number of rows returned).Link

In this post i will show another interesting case when this can have an impact on query performance.As Franck Pachot Said :

Good cardinality estimate is not only to choose the best join method, but also how to run it optimally https://t.co/cSHRv9zoYx

— Franck Pachot (@FranckPachot) 3 novembre 2015

So here is a quick example :(ORACLE 12.1.0.2.6)


DROP TABLE test_stat1;
DROP TABLE test_stat2;

CREATE TABLE test_stat1
AS
SELECT LPAD ('*', 2000) || MOD (LEVEL, 50) AS a,
MOD (LEVEL, 2) AS b,
MOD (LEVEL, 2) AS c,
MOD (LEVEL, 2) AS d,
MOD (LEVEL, 2) AS e
FROM DUAL
CONNECT BY LEVEL < 300000;

CREATE TABLE test_stat2
AS
SELECT LPAD ('*', 2000) || MOD (LEVEL, 50) AS a,
MOD (LEVEL, 2) AS b,
MOD (LEVEL, 2) AS c,
MOD (LEVEL, 2) AS d,
MOD (LEVEL, 2) AS e
FROM DUAL
CONNECT BY LEVEL < 300000;
exec dbms_stats.gather_table_stats(null,'TEST_STAT1');
exec dbms_stats.gather_table_stats(null,'TEST_STAT2');

CREATE INDEX test_stat1_idx
ON test_stat1 (b,
c,
d,
e);

&nbsp;

Out test query :


SELECT   /*   req1    */
COUNT (*)
FROM (SELECT *
FROM test_stat1 t1, test_stat2 t2
WHERE     t1.a = t2.a
AND t1.b = 1
AND t1.c = 1
AND t1.d = 1
AND t1.e = 1) h;

So far so good ! The index “test_stat1_idx” seem not needed so let’s drop it and see what happen : DROP INDEX test_stat1_idx;

Due to dropping the composite index and the effect of index sanity check a serious cardinality miss-estimate kicked in.You may say OK but the plan is still unchanged so who care !

In one of my previous post i stated that sufficiently inaccurate statistics can not only influence execution plan but also runtime execution.So let’s do further check !

The two query spilled to disk, the first one with the good cardinality have executed in 4 PASSES hash join and the second with the bad cardinality 10 PASSES and much longer elapsed time ! Using the trace event 10104 we can confirm that the value of Hash-join fanout was not adequately calculated in the later case (128 for the first and 8 for the later).For more info hash-join fanout please check this post.

Extract from trace file :