Hi Everyone,
I have executed the below query but the indexes are not being used.
Following are the indexes available for the below tables. I have provided the explain plan generated for the query. Can some one please tell me why the indexes are not being used. I have gathered the table statistics multiple times also.
wms_area_master - Index name: WMS_AREA_MASTER_PK - Index columns: DC_CODE, DC_AREA
wms_bin_master - WMS_BIN_MASTER_IDX - DC_CODE, DC_AREA
EXPLAIN PLAN FOR
SELECT *
from wms_area_master wam ,
wms_bin_master wbm
where WAM.DC_CODE = wBM.DC_CODE
and WAM.DC_AREA = wBM.DC_AREA;
select * from table(dbms_xplan.display);
Plan hash value: 2387754896
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 41079 | 12M| 252 (2)| 00:00:01 |
|* 1 | HASH JOIN | | 41079 | 12M| 252 (2)| 00:00:01 |
| 2 | TABLE ACCESS FULL| WMS_AREA_MASTER | 217 | 32984 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| WMS_BIN_MASTER | 41058 | 6214K| 248 (2)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("WAM"."DC_CODE"="WBM"."DC_CODE" AND
"WAM"."DC_AREA"="WBM"."DC_AREA")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
- 1 Sql Plan Directive used for this statement
Thanks