CODEWITHSUNDEEP
sqloracle-databaseindexing
Revious
Revious

Reputation: 8146

Oracle: take advantage of using two index

As you can see Oracle could exploit two indexes and solve the entire query without accessing the table by rowid?

SELECT  'Scarti letture GAS' tipo, campo47 pdf, COUNT (1) n
  FROM out.CONSUMI_GEE_LC_T500_00 v
  WHERE stato = 'SC'
  AND stato is not null
  AND campo47 is not null
  GROUP BY 'Scarti letture GAS', campo47;

I've made a test adding the field campo47 to the STATO index. Performance boosts from 1' 49'' to 0,6s.

Index on stato is not selective. Index on campo47 (it means field47) is really selective.

enter image description here

enter image description here

enter image description here

Upvotes: 3

Views: 286

Answers (1)

APC
APC

Reputation: 146239

You say CAMPO47 is highly selective. But you are only filtering on IS NOT NULL. So it doesn't matter how many distinct values it has, the optimizer isn't going to use it as the entry point.

And how selective is it? As you can see from the cardinalities in the explain plan, selecting on STATO='SC' finds 12856 rows in your table. 12702 of those rows obvious have CAMPO47 with a value, so only 154 rows are filtered out by the test for nullity. If the optimizer had plumped for the index on CAMPO47 how many rows would that have returned? Probably a lot more.

The optimizer can only use one heap index to access rows on a table. (The mechanism is different for bitmap indexes when they are applying a star transformation). So, if you think the additional table accesses are an insufferable burden then you have one option: a compound index. If STATO is truly unselective (relatively few rows) then you are probably safe in replacing the existing index with one on (STATO, CAMPO47).

There is an old trick for nudging the database into using an index to access IS NOT NULL operations, and that is to use an operand which can only be true where the column contains a value. For instance, something like this for string columns (I'm assuming something called CAMPO47 just hase to be a string):

AND campo47 >= chr(0)

That will match any column which contains one or more ascii characters. Not sure whether it will lead to the "two index" optimisation you describe but it's worth a shot. (I would test this myself but I don't have access to an Oracle database right now, and SQL Fiddle hurled when I tried to look at the Explain Plan)

Upvotes: 7

Related Questions