Home » SQL & PL/SQL » SQL & PL/SQL » find and delete records without parent key
find and delete records without parent key [message #273335] Wed, 10 October 2007 01:05 Go to next message
annu-agi
Messages: 202
Registered: July 2005
Location: Karachi
Senior Member

hi experts ..

I have two master details tables .. right now i have stop all the constraints ... and i am importing data through sqlloader.. if i make enable constaints of primary key and foreign keys .. it makes errors .. primary key voilated .. primary key doesnot exist in master table ..
so i decide to delete such data which is remain in detail table and have no primary key records in master table .. for that i use given below sql .. in my all other sql it is the fastest .. i have 21 billion records in my detail table and this given sql findout within 1 min that i have around 50000 records .
select srl_no from loadplan_dtl minus select srl_no from loadplan_mst


now when i try to delete these records .. it takes a long time .. i means and after that sometime it deletes and sometime it makes error
ORA-01652: unable to extend temp segment by 256 in tablespace TEMP


well
i am using this sql for delete
delete from loadplan_dtl where srl_no in(select srl_no from loadplan_dtl minus select srl_no from loadplan_mst)




my question is .. is that i am doing wrong or this sql is wrong .. what is the best solution for delete in that type of conditions and what is the best practice for this kind of huge records collection. give me some right idea about SQL.


regards
anwer
Re: find and delete records without parent key [message #273347 is a reply to message #273335] Wed, 10 October 2007 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create an exception table, try to create the constraint using the "exceptions into" clause and then drop the rows that are referenced in the exception table.

SQL> create table p (id integer primary key);

Table created.

SQL> create table c (id integer, pid integer);

Table created.

SQL> insert into p values (0);

1 row created.

SQL> insert into c values (0,0);

1 row created.

SQL> insert into c values (1,1);

1 row created.

SQL> commit;

Commit complete.

SQL> @?/rdbms/admin/UTLEXCPT.SQL 

Table created.

SQL> alter table c add foreign key (pid) references p exceptions into exceptions;
alter table c add foreign key (pid) references p exceptions into exceptions
*
ERROR at line 1:
ORA-02298: cannot validate (MICHEL.SYS_C004794) - parent keys not found


SQL> select row_id from exceptions where owner=user and table_name='C';
ROW_ID
------------------
AAAL4MAAEAAACgMAAB

1 row selected.

SQL> select * from c where rowid in (select row_id from exceptions where owner=user and table_name='C');
        ID        PID
---------- ----------
         1          1

1 row selected.

SQL> delete c  where rowid in (select row_id from exceptions where owner=user and table_name='C');

1 row deleted.

SQL> select * from c;
        ID        PID
---------- ----------
         0          0

1 row selected.

Regards
Michel
Re: find and delete records without parent key [message #273352 is a reply to message #273347] Wed, 10 October 2007 01:59 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Good one Michel! I was thinking about an intermediate table too, but I never thought of this. From what version is this feature available?

MHE
Re: find and delete records without parent key [message #273361 is a reply to message #273352] Wed, 10 October 2007 02:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's available from 9i onwards I believe.
Re: find and delete records without parent key [message #273364 is a reply to message #273352] Wed, 10 October 2007 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From 8.0 .

Regards
Michel
Re: find and delete records without parent key [message #273379 is a reply to message #273364] Wed, 10 October 2007 03:56 Go to previous messageGo to next message
annu-agi
Messages: 202
Registered: July 2005
Location: Karachi
Senior Member

hi respected
experts


please explane me the whole procedure step by step .. actually i tried but cant understand what is going on



regards

Anwer
Re: find and delete records without parent key [message #273381 is a reply to message #273379] Wed, 10 October 2007 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain what you don't understand.

Regards
Michel
Re: find and delete records without parent key [message #273389 is a reply to message #273381] Wed, 10 October 2007 04:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or show us what you tried, and what how it failed.
Re: find and delete records without parent key [message #273405 is a reply to message #273335] Wed, 10 October 2007 06:17 Go to previous messageGo to next message
annu-agi
Messages: 202
Registered: July 2005
Location: Karachi
Senior Member

see the whole procedure


SQL> select count(*) from loadplan_mst;

  COUNT(*)
----------
     16442

SQL> select count(*) from loadplan_dtl;

  COUNT(*)
----------
   2160278

create table exceptions(row_id rowid,
	                owner varchar2(30),
	                table_name varchar2(30),
		        constraint varchar2(30));



SQL>  alter table loadplan_dtl add foreign key (srl_no) references loadplan_mst exceptions into exceptions;
 alter table loadplan_dtl add foreign key (srl_no) references loadplan_mst exceptions into exceptions
*
ERROR at line 1:
ORA-02298: cannot validate (IGLOO.SYS_C004642) - parent keys not found


SQL>  select count(*) from exceptions;

  COUNT(*)
----------
   1657667



ROW_ID             OWNER                          TABLE_NAME                     CONSTRAINT
------------------ ------------------------------ ------------------------------ ------------------------------
AAAIcmAAIAAANT4AA3 IGLOO                          LOADPLAN_DTL                   SYS_C004642
AAAIcmAAIAAANT4AA4 IGLOO                          LOADPLAN_DTL                   SYS_C004642
AAAIcmAAIAAANT4AA5 IGLOO                          LOADPLAN_DTL                   SYS_C004642
AAAIcmAAIAAANT4AA6 IGLOO                          LOADPLAN_DTL                   SYS_C004642
AAAIcmAAIAAANT4AA7 IGLOO                          LOADPLAN_DTL                   SYS_C004642
AAAIcmAAIAAANT4AA8 IGLOO                          LOADPLAN_DTL                   SYS_C004642
AAAIcmAAIAAANT4AA9 IGLOO                          LOADPLAN_DTL                   SYS_C004642
AAAIcmAAIAAANT4AA+ IGLOO                          LOADPLAN_DTL                   SYS_C004642


SQL>  select row_id from exceptions where owner=user and table_name='loadplan_dtl';

no rows selected

SQL> select * from loadplan_dtl where rowid in (select row_id from exceptions where owner=user and table_name='loadplan_dtl');

no rows selected


SQL> delete loadplan_dtl  where rowid in (select row_id from exceptions where owner=user and table_name='loadplan_dtl');

0 rows deleted.

SQL> 





see i did everything as per your instructions. where i am making mistake??


any suggustions


regards

Anwer

Re: find and delete records without parent key [message #273408 is a reply to message #273405] Wed, 10 October 2007 06:21 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
'loadplan_dtl' and 'LOADPLAN_DTL' are different.  


By default all the objects created in oracle will be in uppercase and any string enclosed in quotes is case sensitive.

Hope that helps

Regards

Raj
Re: find and delete records without parent key [message #273417 is a reply to message #273408] Wed, 10 October 2007 06:59 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be honest, if I'd got this problem, my first two approaches would have been
1) to make the TEMP tablespace bigger
2) to rewrite the DELETE to use a NOT EXISTS rather than a MINUS.
Previous Topic: Select where show information from two row in one row
Next Topic: whats wrong with this delete code?
Goto Forum:
  


Current Time: Wed Dec 07 04:41:17 CST 2016

Total time taken to generate the page: 0.10861 seconds