I'm having a headache understanding why my indexes are not used in one of my queries.
I've no problem with these two following queries. The first one here
SELECT XHHA.* , XSAIL.*
FROM TABLE_A XSAIL,
xxd_hist_headers_all XHHA
WHERE XSAIL.id = 'XXXX'
AND XHHA.CONTRACT_NUMBER = XSAIL.CONTRACT_NUMBER
AND XHHA.history_flag = 'VALID'
AND XHHA.buff_flag = 'N'
Gives me the following execution plan where we see indexes being used :
Plan
SELECT STATEMENT CHOOSECost: 451 Bytes: 1 332 628 342 Cardinality: 4 087 817
5 NESTED LOOPS Cost: 451 Bytes: 1 332 628 342 Cardinality: 4 087 817
2 TABLE ACCESS BY INDEX ROWID APPS.TABLE_A_N1 Cost: 1 Bytes: 266 911 Cardinality: 2 999
1 INDEX RANGE SCAN NON-UNIQUE APPS.TABLE_A_N1 Cost: 1 Cardinality: 2 999
4 TABLE ACCESS BY INDEX ROWID XXD.XXD_HIST_HEADERS_ALL Cost: 1 Bytes: 32 304 522 Cardinality: 136 306
3 INDEX RANGE SCAN NON-UNIQUE XXD.XXD_HIST_HEADERS_N1 Cost: 2 Cardinality: 136 306
The second request below :
SELECT XHHA.*, XPHA.*
FROM
xxd_hist_headers_all XHHA,
XXD_POLICY_HIST_ALL XPHA
WHERE XHHA.CONTRACT_NUMBER = 'XXXX'
AND XHHA.history_flag = 'VALID'
AND XHHA.buff_flag = 'N'
AND XPHA.CONTRACT_NUMBER = XHHA.CONTRACT_NUMBER
Gives me the following execution plan where we still see indexes being used :
Plan
SELECT STATEMENT CHOOSECost: 2 Bytes: 302 Cardinality: 1
5 NESTED LOOPS Cost: 2 Bytes: 302 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID XXD.XXD_POLICY_HIST_ALL Cost: 1 Bytes: 65 Cardinality: 1
1 INDEX UNIQUE SCAN UNIQUE XXD.XXD_POLICY_HIST_U1 Cost: 2 Cardinality: 2
4 TABLE ACCESS BY INDEX ROWID XXD.XXD_HIST_HEADERS_ALL Cost: 1 Bytes: 237 Cardinality: 1
3 INDEX RANGE SCAN NON-UNIQUE XXD.XXD_HIST_HEADERS_N1 Cost: 2 Cardinality: 1
Then I write this third query, which is more or less the join of the 2 previous ones :
SELECT XHHA.* , XSAIL.*, XPHA.*
FROM TABLE_A XSAIL,
xxd_hist_headers_all XHHA,
XXD_POLICY_HIST_ALL XPHA
WHERE XSAIL.id = 'XXXX'
AND XHHA.CONTRACT_NUMBER = XSAIL.CONTRACT_NUMBER
AND XHHA.history_flag = 'VALID'
AND XHHA.buff_flag = 'N'
AND XPHA.CONTRACT_NUMBER = XHHA.CONTRACT_NUMBER
No more indexes used, full scans on 2 of the 3 tables involved :
Plan
SELECT STATEMENT CHOOSECost: 9 695 Bytes: 2 014 546 788 Cardinality: 4 145 158
6 HASH JOIN Cost: 9 695 Bytes: 2 014 546 788 Cardinality: 4 145 158
2 TABLE ACCESS BY INDEX ROWID APPS.TABLE_A_N1 Cost: 1 Bytes: 551 816 Cardinality: 2 999
1 INDEX RANGE SCAN NON-UNIQUE APPS.TABLE_A_N1 Cost: 1 Cardinality: 2 999
5 HASH JOIN Cost: 9 004 Bytes: 41 741 836 Cardinality: 138 218
3 TABLE ACCESS FULL XXD.XXD_POLICY_HIST_ALL Cost: 114 Bytes: 18 903 625 Cardinality: 290 825
4 TABLE ACCESS FULL XXD.XXD_HIST_HEADERS_ALL Cost: 821 Bytes: 32 757 903 Cardinality: 138 219
Do you see any reason why indexes are not used in this third request ? If this has any influence, the table I renamed "TABLE_A" (for confidentiality issues) is in fact a materialized view.
Thanks for you kind answers/questions/suggestions.
(Please forgive me if editing is not optimal, first time posting here).
Most of us who are at least 9i and above, and have CBO, i.e. cost based optimizer, have less tension since CBO does a brilliant job over RBO i.e. rule based optimizer.
Since, you are on 8i, my suggestion is to check the cardinalities properly. As in performance tuning I always say, It's all about cardinalities.
Please note that, a FTS, i.e. full table scan is not always bad. When you want to fetch more than, lets say ~10-15% or more rows, the optimizer would rather estimate an execution plan to randomly pick the rows from data block rather than using an Index. Indexes are for a purpose and not always help while retrieving data. For more in depth details, just search for "Why isn't my index being used".