With Oracle 10g the RULE command will not be supported.
So far as good, we removed all RULE statements inside Queries and views.
Mostly it works fine, but in some cases the queries does not work correcly.
No rows are returned. If I put the RULE back in this query everything works
fine.
This happens with 20g2.0.3 and 10g2.0.4

Does someone else has seen this and does exist a patch for this behaviour?

Regards
Nicolas

On Mar 20, 7:41*am, "Nicolas Bronke" <Nicolas_Bro... (AT) web (DOT) de> wrote:
An actual example SQL showing both versions wtih results and the
explain plan would likely be helpful. Also information on the type of
table: heap, IOT, partitioned involved in the query is useful.

We have 10.2.0.3 and have not seen this.

Performance issues are common after upgrading to 10g from a lower
version but changes in the query results are not.

HTH -- Mark D Powell --

On Mar 20, 4:41*am, "Nicolas Bronke" <Nicolas_Bro... (AT) web (DOT) de> wrote:
In addition to what Mark said, see the 10.2.0.4 and 11g notes that
list all the bugfixes for all the patches. There are some CBO wrong
results bugs that aren't fixed until 11g, but they tend to be really
obscure. Are you using dblinks? How _exactly_ are you gathering
statistics?

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...-party-so-far/

Nicolas Bronke wrote:

The patch being to let the rule hint be. Report the bug to Oracle, if you are able to reproduce
it with the emp and dept tables.

Thank you. I could not answer earlier.
The problem is, that the sql is very specific (depends also from session
variables) and it seems, that the problem only occurs with a number of data
(or complexity of data).
I think we will ow look a little more deeper in the explain plan.

Regards
Nicolas

in addition to my answer to mark.
We do not use dblinks. We often let analyze the data.
Regards
Nicolas

On Apr 7, 12:39*am, "Nicolas Bronke" <Nicolas_Bro... (AT) web (DOT) de> wrote:
Not quite parsing that :-) Do you mean you use the default statistics
gathering with 10.2.0.4? That may not be what you want to do.
There's a white paper at Oracle.com about what to expect from the 10g
optimizer, but they changed the site around I don't have time to find
it just now (though google finds it
http://www.oracle.com/technology/pro...10gr2_0208.pdf
) . There's plenty of stuff on the web about it, anyways. Delving
into the plan is what you should be doing now, and you may need to do
some tracing to see what is really going on.
http://tonguc.wordpress.com/2007/01/...53-trace-file/

The RBO is not supported, but the code may still be in there
unchanged, or it may be ignored, I forget. You can still see rule
hints being used by system code.

jg
--
@home.com is bogus.
http://blogs.oracle.com/experience/pacman-chart.jpg

Hallo Nicolas,

"the rule hint is not supported ... " oracle wrote this since 8i, 9i, i
don't know. But imho this hint will work today.

Most the times i found this hint my developer weren't able to implement
a correct index hint. So it was easier for them to implement the rule
hint. It works despite they know why ...

The other problem are the bugs with wrong number of results which
depends on using an index or not.
I'm responsible for a little program to mirror an older IMS database to
a read-only oracle database.
So i have to delete lots of rows in some tables.
First i count the amount of rows which my program has to delete and then
with the next statement my program deletes them.
So i have statement based on a view on a large varchar field with substr
and to_number for extract every column. And then build a where-clause
which compare all these extracted columns with all the columns of the
target table. So i have the same complex statement, one time "select
count(*) from ..." and afterwards "delete from ...". And if the count(*)
doesn't equal the row%count of the delete my program throws an exception.
That worked fine the last 11 years for half of the german people.
Only 2 times we had a mismatch. First was an upgrade within 9i, so i
have implement an index hint. Second was the upgrade from 9 to 10 where
old statistics caused the problems.
And every times my DBAs implement a new Oracle version i'm highly
alerted about the results of my little program.

und tschüss
Kay