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 :

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;

Capture 1

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;

Capture2

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 :

Capture 3

That’s it 😀

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s