Home » SQL & PL/SQL » SQL & PL/SQL » delete issue
delete issue [message #271848] Wed, 03 October 2007 02:53 Go to next message
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 Go to previous messageGo to next message
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 #271875 is a reply to message #271862] Wed, 03 October 2007 04:26 Go to previous messageGo to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

thanks for reply ..but you didnot mension ..what to do for cure of this situation


regards
anwer
Re: delete issue [message #271876 is a reply to message #271875] Wed, 03 October 2007 04:27 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"I reckon that the problem is that there is no index on Dealer_Trans.DCODE".

Reading this sentence, what do YOU think? What might be the solution?
Re: delete issue [message #271881 is a reply to message #271875] Wed, 03 October 2007 04:45 Go to previous message
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)
Previous Topic: Query data rows
Next Topic: explanation
Goto Forum:
  


Current Time: Sun Nov 03 01:45:32 CST 2024