By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,602 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Unable to create index it still says TABLE ACCESS FULL

P: 4
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
Dec 15 '10 #1
Share this Question
Share on Google+
1 Reply


JKing
Expert 100+
P: 1,206
Is this mysql or oracle?
Dec 16 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.