Home » SQL & PL/SQL » SQL & PL/SQL » delete issue
delete issue [message #271848] |
Wed, 03 October 2007 02:53 |
annu-agi
Messages: 238 Registered: July 2005 Location: Karachi
|
Senior Member |
|
|
hi experts,
I have two tables .. one is Dealer_info ( its a master) and second one is Dealer_trans ( its detail) . Both the tables has more then 500,000 records. Only one constraint is lying between these tables .. one is pk .. which is fk in another table .. and pk also has only one feild.
Now When i try to delete records from dealer_trans nothing happins and it delete all the records in seconds. after that i commit and then after when i try to delete dealer_info my sqlplus application is stuck, computer processor performance is shown 100%, The L.E.D. of HDD is always shows working. and my process of delete is in stuck condition. Well after a very long time of working it deletes all the records .or sometime i have to kill the session to get beck in normall condition .. when i see the session SQL it is like
SELECT count(1)
FROM igloo.dealer_trans
WHERE d_code = :1
my question is what goes wrong.. why it happins and what the reasons behind.. and what should i do to aviod this conditions. Please help me out with solutions and advises.
CREATE TABLE DEALER_INFO (
CAT_ID VARCHAR2 (3),
D_CODE VARCHAR2 (15),
MEM_DT DATE,
NAME VARCHAR2 (40),
PERSON VARCHAR2 (30),
ADDRESS1 VARCHAR2 (40),
ADDRESS2 VARCHAR2 (40),
ADDRESS3 VARCHAR2 (40),
PHONE VARCHAR2 (8),
CLASS VARCHAR2 (1),
CR_APP VARCHAR2 (1),
CR_TERMS VARCHAR2 (1),
ACCOUNT VARCHAR2 (5),
CR_LIMIT NUMBER (8,2),
OPENING NUMBER (8,0),
TARGET NUMBER (9,2),
TOT_LIAB NUMBER (10,2),
VISIT1 VARCHAR2 (1),
VISIT2 VARCHAR2 (1),
VISIT3 VARCHAR2 (1),
VISIT4 VARCHAR2 (1),
VISIT5 VARCHAR2 (1),
VISIT6 VARCHAR2 (1),
VISIT7 VARCHAR2 (1),
TOT_AMT NUMBER (12,2),
SHOP_ON VARCHAR2 (8),
SHOP_OFF VARCHAR2 (8),
SHOP_POS VARCHAR2 (1),
CLOSE_D DATE,
START_D DATE,
FAX VARCHAR2 (8),
D_K_NO VARCHAR2 (25),
DEEP_FREZ VARCHAR2 (1),
CASH_FREE VARCHAR2 (1),
NEW_FLAG VARCHAR2 (1),
GST_NO VARCHAR2 (20),
NTN_NO VARCHAR2 (15)
);
ALTER TABLE "IGLOO"."DEALER_INFO"
ADD (CONSTRAINT "DEALER_INFO_PK11191308173375" PRIMARY KEY("D_CODE"));
CREATE TABLE DEALER_trans (
D_CODE varCHAR2 (15),
SRL_NO NUMBER (3,0),
trans_DATE DATE,
DESCRIPT varCHAR2 (60),
AMOUNT NUMBER (14,2),
I_CODE varchar2 (10),
QTY NUMBER (14,2),
POS_DEEP varCHAR2 (1)
);
ALTER TABLE "IGLOO"."DEALER_TRANS"
ADD (CONSTRAINT "DEALER_TRANS_FK11191319102078" FOREIGN KEY("D_CODE")
REFERENCES "IGLOO"."DEALER_INFO"("D_CODE")) ;
|
|
|
Re: delete issue [message #271862 is a reply to message #271848] |
Wed, 03 October 2007 03:37 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I reckon that the problem is that there is no index on Dealer_Trans.DCODE (Called a Unindexed Foreign Key)
When you delete a record from DEALER, Oracle knows that there is a FK constraint that referes to that table, and needs to check that there are no FK values referencing the rows you have deleted.
As there is no index on the FK table that it can use to check for the existance of Child records, it is forced to do a Full table scan of Dealer_Trans for each record deleted in Dealer.
|
|
|
|
|
Re: delete issue [message #271881 is a reply to message #271875] |
Wed, 03 October 2007 04:45 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Well, rereading my answer, you are correct in that I did not give you a piece of SQL that you could run. Tut tut - how slack I am, expecting you to do some of the work.
I think the line the problem is that there is no index on Dealer_Trans.DCODE tells you everything you need to know about how to fix this (assuming I'm right about the causes of the problem)
|
|
|
Goto Forum:
Current Time: Sun Nov 03 01:45:32 CST 2024
|