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!

"Del N. Quent" <delnquent@gmail.com> a écrit dans le message de news: 1168887179.032069.3660@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