Oracle: use two indexes
As you can see Oracle can use two indexes and solve the whole 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 did a test by adding campo47 field to STATO index. Performance increases from 1 '49' 'to 0.6 s.
The pointer to stato is not selective. The index on campo47 (which means field47) is really selective.
source to share
You say that CAMPO47 is very selective. But you are only filtering on IS NOT NULL. Therefore, no matter how many different values it has, the optimizer is not going to use it as an entry point.
And how selective is that? As you can see from the powers of the explain plan, choosing STATO = 'SC' finds 12856 rows in your table. 12702 of these lines have CAMPO47 with a value, so only 154 lines are filtered out by the test for invalidation. If the optimizer pumped the number of rows for the index on CAMPO47, which would be returned? Probably much more.
The optimizer can only use one heap index to access table rows. (The mechanism is different for bitmap indices when they apply star transform). So, if you think extra table accesses are unbearable, then you have one option: a composite index. If STATO is indeed non-selective (relatively few rows), then you are probably safe in replacing the existing index with one (STATO, CAMPO47).
There is an old trick for nudging the database to use an index to access IS NOT NULL operations, and it uses an operand that can only be true where the column contains a value. For example, something like this for string columns (I assume that something called CAMPO47 just hase to be a string):
AND campo47 >= chr(0)
This will match any column containing one or more ascii characters. Not sure if this will lead to the "two index" optimization you describe, but it's worth it. (I would have experienced this myself, but I don't have access to an Oracle database right now, and the SQL Fiddle got thrown when I tried to review the Explain Plan)
source to share