SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Deleting records from a recursive table query

kdwolfJul 2 2011 — edited Jul 2 2011

Hi All,

I have the follow query, I would like to ask for your help, please: we use recursive tables for various purposes and one of them may have 100,000's records.
What would be the best approach to delete records from such a table?

I was thinking about two below, but any additional one will be more than welcome as well:

(1) I can DELETE FROM my_table WHERE my_table_id IN (SELECT my_table_id FROM my_table START WITH my_table_parent_id = X CONNECT BY PRIOR my_table_id = my_table_parent_id, but it means that I will run on my_table twice, no?

(2) I thought also to create a Foreign Key of my_table_id REFERENCES my_table_parent_id, but then I am afraid it will effect DML commands on that table.

Thank you in advance

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 30 2011

Added on Jul 2 2011

#delete, #plsql, #table, #xe

3 comments

1,007 views