Re: 10g DELETE does not match SELECT START WITH CONNECT BY AND root

"Del N. Quent" <delnquent_at_gmail.com> a écrit dans le message de news: 1168887179.032069.3660_at_11g2000cwr.googlegroups.com...
| No time to mess with this, I don't need a response ... jest reporting
| what I found in case someone else needs it.
|
| This particular problem involves the use of a somewhat recursive table
| ... not truly recursive (the parent key is within the table which
| specifies the child key as its primary key). In my case, children may
| have multiple parents ... as such, an *orphan* simply does not exist in
| my *structure* table as a child.
|
| In my case, in order to DELETE related rows from yet another table for
| both the *root*, as well as all its children, I used:
|
| WHERE other_id = ? AND (inst_id = ? OR inst_id IN (SELECT child_id FROM
| structure_table START WITH parent_id = ? CONNECT BY PRIOR child_id =
| parent_id))
|
| When I substitute DELETE with SELECT * FROM, it returns exactly the
| rows I expect. But the DELETE simply indicates it deleted 0 rows ...
| on 10g. On an identical 7.3 server, the DELETE deletes the same rows
| the SELECT returns ... as expected.
|
| IOW, this is inconsistent from 7.3 to 10g.
|
| I have resolved this particular situation for myself another way ...
| mostly due to the nature of my data which afforded that opportunity.
|
| I have done no other investigation as to whether our 10g is the latest
| ... hang on ... it says 10.2.0.20.0 (64-bit) ... FWIW.
|
| If anyone knows of a specific patch to correct this, I would be
| interested in that. Otherwise, I don't care to waste any more time on
| this than I already have.
|
| Thanks!
|

Check if there is some differences between both execution plans.

Try the /*+ NO_FILTERING */ hint or use
alter session set "_old_connect_by_enabled" = true; to use previous connect by algorithm.

Regards
Michel Cadot Received on Mon Jan 15 2007 - 13:28:49 CST