RE: Urgent problem with query in CBO Vs RBO

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Thu, 17 Oct 2002 05:13:28 -0800
Message-ID: <F001.004EBE4C.20021017051328@fatcity.com>

Here is an example ...

RULE Hint works ... but we can't find all the SQLs that will fail. The only other option suggested by OWS is setting optimizer_features_enable='8.1.6'. It works fine ...

Raj

---------------  start  ----------------------------

set feedback off
set define off
drop table mjc_junk;
drop table mjc_mst;
prompt Creating MJC_JUNK...
-- Create table

create table MJC_JUNK
(
  COLA NUMBER(9)
);
prompt Creating MJC_MST...
-- Create table

create table MJC_MST
(
  COLA NUMBER(9) not null,
  COLB NUMBER(9) not null,
  CDT DATE
);

prompt Loading MJC_MST...
insert into MJC_MST (COLA, COLB, CDT)
values (1284457, 213841, to_date('06-06-2001 09:28:40', 'dd-mm-yyyy hh24:mi:ss'));
commit;
prompt 41 records loaded
declare
begin
  sys.dbms_stats.gather_table_stats(ownname => null,

                                    tabname => 'MJC_JUNK',
                                    estimate_percent => 20,
                                    block_sample => false,
                                    method_opt => 'FOR ALL COLUMNS',
                                    degree => 4,
                                    granularity => 'DEFAULT',
                                    cascade => true);

end;
/
set feedback on
set define on

prompt This query returns invalid results: select sUM(a.colb)
  FROM mjc_mst a
 WHERE a.colb = 213841
   AND a.cdt = (SELECT MAX(b.cdt)

                        FROM mjc_mst b
                       WHERE b.cola = a.cola
                         AND b.colb = a.colb

)
  and exists ( select 1 from mjc_junk b, mjc_junk m where

                a.colb = m.cola
                 and 1 != 1);

prompt This query that returns valid results: select sum(a.colb)
  FROM mjc_mst a
 WHERE a.colb = 213841
   AND a.cdt = (SELECT MAX(b.cdt)

                        FROM mjc_mst b
                       WHERE b.cola = a.cola
                         AND b.colb = a.colb
                        group by 1

)
  and exists ( select 1 from mjc_junk b, mjc_junk m where

                a.colb = m.cola
                 and 1 != 1)

prompt Go figure.

• end ----------------------

  ---

Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Oct 17 2002 - 08:13:28 CDT