I have a query.When i try to create a index on one of the table the index gets created but when i take the output it stuill shows "TABLE ACCESS FULL"
Please see below what i did
QUERY
======
SELECT b.DPT_EVNT_ID,
b.DPT_ENTITY_ID,
a.EDD_DT,
b.DPT_DPT_ID,
b.OU_ID,
b.PART_ID
FROM EDD_EVNT_DT_DTLS a,
DPT_DPOT_EVNT_DTLS b,
SBD_SYS_BSNS_DTS c,
NGP_NOTF_GNT_PMTR e
WHERE EXISTS
(SELECT 1
FROM ELG_ELGBLTY f
WHERE f.ELG_ENTITY_ID = b.DPT_ENTITY_ID AND
f.ELG_DPT_ID = b.DPT_DPT_ID AND f.ELG_EVNT_ID = b.DPT_EVNT_ID) AND
b.DPT_ENTITY_ID = a.EDD_ENTITY_ID AND b.DPT_EVNT_ID = a.EDD_EVNT_ID AND
b.DPT_DPT_ID = a.EDD_DPT_ID AND SBD_ENTITY_ID = b.DPT_ENTITY_ID AND
c.OU_ID = b.OU_ID AND e.OU_ID = b.OU_ID AND SBD_BTCH_IND = 1 AND
a.EDD_DT_TYP = 'FELD' AND e.NGP_NOTF_TYPE = 'EVNTANNC' AND
e.NGP_INTERNAL_DT = SBD_BSNS_DT AND a.STATUS = 'AUTHD' AND
b.STATUS = 'AUTHD' AND e.STATUS = 'AUTHRIZE' AND e.NGP_GNT = 1 AND
b.DPT_ENTITY_ID = e.NGP_ENTITY_ID AND b.DPT_EVNT_ID = e.NGP_EVNT_ID AND
b.DPT_DPT_ID = e.NGP_DPT_ID AND b.PART_ID = 1 AND
b.PART_ID = e.PART_ID AND b.DPT_EVNT_ID >= '00000000' AND
b.DPT_EVNT_ID <= '9999999999'
ORDER BY b.DPT_ENTITY_ID, b.DPT_EVNT_ID, b.DPT_DPT_ID
1. took the explain plan
elect plan_table_output from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Byt
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1
| 1 | SORT ORDER BY | | 1 | 1
| 2 | TABLE ACCESS BY INDEX ROWID | EDD_EVNT_DT_DTLS | 1 |
| 3 | NESTED LOOPS | | 1 | 1
| 4 | NESTED LOOPS | | 1 | 1
| 5 | NESTED LOOPS | | 1 | 1
| 6 | HASH JOIN SEMI | | 1 |
| 7 | TABLE ACCESS FULL | DPT_DPOT_EVNT_DTLS | 30190 | 10
| 8 | TABLE ACCESS FULL | ELG_ELGBLTY | 181K| 46
| 9 | TABLE ACCESS BY INDEX ROWID| NGP_NOTF_GNT_PMTR | 1 |
| 10 | INDEX RANGE SCAN | INDEX_NGP_NOTF_GNT_PMTR | 2 |
| 11 | TABLE ACCESS BY INDEX ROWID | SBD_SYS_BSNS_DTS | 1 |
| 12 | INDEX RANGE SCAN | SBD_SYS_BSNS_DTS_INDEX | 1 |
| 13 | INDEX RANGE SCAN | SYS_C00459846 | 1 |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
23 rows selected
create index DPT_DPOT_EVNT_DTLS_INDEX on DPT_DPOT_EVNT_DTLS (DPT_ENTITY_ID,DPT_DPT_ID,DPT_EVNT_ID,OU_ID,STATUS ,PART_ID)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Byte
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17
| 1 | SORT ORDER BY | | 1 | 17
| 2 | TABLE ACCESS BY INDEX ROWID | NGP_NOTF_GNT_PMTR | 1 | 6
| 3 | NESTED LOOPS | | 1 | 17
| 4 | NESTED LOOPS | | 1 | 10
| 5 | HASH JOIN SEMI | | 1 | 8
| 6 | TABLE ACCESS FULL | DPT_DPOT_EVNT_DTLS | 30190 | 185
| 7 | INDEX FAST FULL SCAN | ELG_ELGBLTY_IND | 181K| 460
| 8 | TABLE ACCESS BY INDEX ROWID| SBD_SYS_BSNS_DTS | 1 | 1
| 9 | INDEX UNIQUE SCAN | SYS_C00459464 | 1 |
| 10 | INDEX RANGE SCAN | INDEX_NGP_NOTF_GNT_PMTR | 2 |
--------------------------------------------------------------------------------
still says table access full for DPT_DPOT_EVNT_DTLS table
kindly advice