Method R Discussion Group

Method R helps developers, DBAs, and decision-makers optimize Oracle-based software in every phase of the software life cycle.

Discuss Method R products, techniques, and events.

Ask new questions or post your own responses.

Hotsos Update Volume III Issue 19  - RBO in 10g

Can you post the test case that produced the wrong results in 10g with a RULE hint?

I've been pushing people to stop using the RULE optimizer and usually I've been successful.  However I do have some that are still holding back.  If I can clearly demonstrate it's not just performance features but also reliable results at stake it seems like a slam dunk argument.
Sean D Stuber Send private email
Wednesday, November 7, 2007
This example affects versions >= but the patch set for has a fix implemented (of course, the fix causes another bug which I'm also showing below).  Depending on what version your running, you may or may not be able to reproduce this, but it's the example I have at hand.  I hope it helps!

The problem with this "bug" is that rule based optimization of a query with a range predicate on a concatenated index with NULL data in the index column used in the predicate can give wrong results.

Test case:
create table jci_test (user1 date,lnkey varchar2(20));
create index jci_user1 on jci_test (user1,lnkey);
insert into jci_test values (sysdate - 1.5,'AA1');
insert into jci_test values (sysdate - 1,'AA2');
insert into jci_test values (null,'AA3');
insert into jci_test values (null,null);
insert into jci_test values (sysdate - 1,null);
select /*+ RULE */ user1,lnkey from jci_test
  where user1 >= sysdate - 10;
This returns rows where user1 is NULL which it should not.

If the patch set has been applied with the fix for this problem, it then creates another bug (4925103) such that SQL that includes a MAX() function can return a wrong result (NULL) if the execution plan uses "FIRST ROW" optimization from an "INDEX RANGE SCAN (MIN/MAX)"
and the column contains NULL values in addition to non NULLs.

Test case:
create table TEST(
    col1 VARCHAR(2),
    col2 NUMBER
insert into TEST values('01',1);
insert into TEST values('01',2);
insert into TEST values('01',3);
insert into TEST values('01',NULL);
analyze table TEST compute statistics;

 WHERE col1 = '01'
  AND col2 > 0;
This returns a NULL if FIRST ROW optimization is used in the execution plan.
Karen Morton Send private email
Wednesday, November 7, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Discussion Groups Main Page

Powered by FogBugz