473,320 Members | 2,020 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Unable to create index it still says TABLE ACCESS FULL

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
1 2573
JKing
1,206 Expert 1GB
Is this mysql or oracle?
Dec 16 '10 #2

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

Similar topics

12
by: Nohman Ishtiaq | last post by:
MySQL 4.1 Im not very well versed with databases, but as soon as I include 129th row in my table called 'demainpark', I get Table is Full error. I have searched thru the newsgroup, checked the...
11
by: dfurtney | last post by:
SQL Server 7/2000: We have reasonably large tables (3,000,000 rows) that we need to add some indexes for. In a test, it took over 12 hours to CREATE a new INDEX against this table. One of us...
0
by: DanPerlman | last post by:
Hi, I am using the following to write data from an access table to a postgres table. I need to create an index on the "partnumber" field in the Bpb (odbc pg database) so I can seek on that...
2
by: WantedToBeDBA | last post by:
Hi Friends, Is it possible to create index for temporary table? Is so tell me how to acheive that.. Thanks, Thiru WantedToBeDBA@gmail.com
2
by: Hervé Piedvache | last post by:
Bug or problem with PostgreSQL ? version --------------------------------------------------------------- PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4 select * from my_table;...
19
by: Lyle Fairfield | last post by:
MSDN Home > MSDN Library > Win32 and COM Development Data Access Microsoft offers many data access technologies to suit various development needs. This section of the MSDN Library contains...
4
by: Owen Jenkins | last post by:
Hi, No-one replied to this when I sent it last week. Any bites out there today?? ----- My application allows users to create a new back end for separate purposes. It does this by using Make...
0
by: sujith | last post by:
hi, if i use to set the primary key of table some error arises , like this one. Contact_Table' table - Unable to create index 'PK_Contact_Table'. ODBC error: CREATE UNIQUE INDEX terminated...
3
by: PraveenKadkol | last post by:
Hi, I have csv linked table in MSAccess which has 3,50,000 records, before importing into local table i need to validate with static details, and I have only 1 inner join and there is no where...
1
by: jane | last post by:
HI, Just wondering is that OK to create index online ( without down the application servers).. it's OLTP 24*7 production environment.. Is that OK to create index on one table online ? the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.