Hi everyone
I have a simple query that I'm running in Oracle 12c that joins two tables and I specify two query hints to force the optimizer to use the indexes that I specifically created to speed up the query in the joins.
Select
/*+ INDEX(ODS_CONTENT_MSISDN Z_ODS_CONTENT_MSISDN_IDX01 (MNTH_, MSISDN) INDEX(CDW_MULTISET CDW_MULTISET_IDX01 (MNTH_, MSISDN)) */
a.MNTH_,
a.MSISDN,
a.TRAFFIC_TYPE,
a.P2P_PROTOCOL,
a.APP_PROTOCOL,
a.DURATION_MIN,
a.VOLUME_MB,
b.SUBS_ID,
b.PMT_METH_NM,
b.BUS_CONS_CLASS_NM
from
ODS_CONTENT a
Left Join CDW_MULTISET b ON a.MNTH_ = b.MNTH_ and a.MSISDN = b.MSISDN
The query is however very slow. When I run an explain plan I can see that both indexes are being skipped and full tables scans are performed:
Plan hash value: 1449121599
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 394M| 23G| | 1916K (2)| 00:01:15 |
|* 1 | HASH JOIN RIGHT OUTER| | 394M| 23G| 1820M| 1916K (2)| 00:01:15 |
| 2 | TABLE ACCESS FULL | CDW_MULTISET | 54M| 1196M| | 92651 (2)| 00:00:04 |
| 3 | TABLE ACCESS FULL | Z_ODS_CONTENT_MSISDN | 393M| 15G| | 721K (2)| 00:00:29 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."MNTH_"="B"."MNTH_"(+) AND "A"."MSISDN"="B"."MSISDN"(+))
The stats on both tables are up to date. The indexes are used when I specify both columns in the Where clause. I however do not want to use a where clause as there is only one month's data in the tables anyway.
How can I force the query to use the 2 indexes when the query executes.
Here are the index create statements:
Create Index OBIEE.ODS_CONTENT_IDX01 ON OBIEE.ODS_CONTENT_MSISDN (MNTH_, MSISDN) COMPUTE STATISTICS;
Create Index OBIEE.CDW_MULTISET_IDX01 ON OBIEE.CDW_MULTISET (MNTH_, MSISDN) COMPUTE STATISTICS;
Thanks.
Johan