oracle11g - Recursive Delete SQL Oracle -

i'm searching way recursive delete on table. situation table have 3 foreign key 1 on , 2 others, want delete depending on date of occurrence.

    table1 --> id1,    dateocc,   parentid                1,  13-12-26,   null                2,  13-07-18,   null                3,  14-12-31,   1                4,  13-06-26,   1                5,  14-07-23,   null                6,  13-07-22,   2     table2--> id, stuff     table3 --> id, stuff 

the id of table 2 , table 3 linked directly on id of table1. amount of data inside table 1 approximately 20 000 000 row , others table approximately same amount.

here on of request tried(its inside of cursor delete data returned.

select eo.id,      eo.dateocc,      eo.parentid     table1 eo     eo.dateocc <= to_date ('2013-12-31','yyyy-mm-dd')     , not exists(select 1 table2 id = eo.id)     , not exists( select 1 table3 id = eo.id)     start eo.parentid null     connect prior eo.id = eo.parentid; 

this request really slow output data want. , seems not return data need delete.

edit #1 ok heres example of need do(in example suppose table 2 , table 3 have no matching id on table 1)

table1 --> id1,    dateocc,   parentid                    1,  13-12-26,   null                    2,  13-07-18,   null                    3,  14-12-31,   1                    4,  13-06-26,   1                    5,  14-07-23,   null                    6,  13-07-22,   2  

after delete sequence table have if >= date 13-12-31

table1 --> id1,    dateocc,   parentid                        1,  13-12-26,   null                        3,  14-12-31,   1                        5,  14-07-23,   null 

so can see delte child can delete parent if possible. if cant delete parent because child exist , cant delete dont delete de parent(delete child can).

in hierarchical query, where clause applied after start with , connect by used build hierarchy. syntactically comes first, makes intuitively seem applied first.

if want apply where clause first, build hierarchy, can use subquery this:

select eo.id,      eo.dateocc,      eo.parentid     (       select * table1 eo       eo.dateocc <= to_date ('2013-12-31','yyyy-mm-dd')       , not exists(select 1 table2 id = eo.id)       , not exists( select 1 table3 id = eo.id)     ) eo     start eo.parentid null     connect prior eo.id = eo.parentid; 

but not clear whether want. give top-level parents within desired date range, , without children in other tables, build entire hierarchy parents. it's possible lower nodes in hierarchy have children in other tables, cause delete fail.

if that's not want, think need describe requirements more clearly.