Prev: Streams Replication: Is Supplemental logging mandatory?
Next: Multi-Source streams Replication
From: Nicolas Bronke on 7 Apr 2010 03:39
>> 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?
>
>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?

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

From: joel garry on 7 Apr 2010 16:24
On Apr 7, 12:39 am, "Nicolas Bronke" <Nicolas_Bro...(a)web.de> wrote:
> >> 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?
>
> >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?
>
> in addition to my answer to mark.
> We do not use dblinks. We often let analyze the data.
> Regards
> Nicolas

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/products/bi/db/10g/pdf/twp_bidw_optimizer_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/20/optimizer-debug-trace-event-10053-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
From: Kay Kanekowski on 7 Apr 2010 18:18
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
First  |  Prev  | 
Pages: 1 2
Prev: Streams Replication: Is Supplemental logging mandatory?
Next: Multi-Source streams Replication