Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Urgent problem with query in CBO Vs RBO

RE: Urgent problem with query in CBO Vs RBO

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Thu, 17 Oct 2002 15:33:30 -0800
Message-ID: <F001.004ECE8E.20021017153330@fatcity.com>


Raj,

It's early in the morning and I may not be thinking straight yet, but...

It's certainly interesting that the group by in the location shown changes the results - obviously a max() function should return 1 row only and therefore not require a group by. I'm more interested though in the second clause of your query... You have a second table aliased to "b" which isn't even used in the subquery, and you have a "and 1 != 1" clause.

Is this a trick to get a particular result set? I would have thought that "and 1 != 1" will always return false and therefore the subquery will never return a row. Does the fact that one table is not joined to (ie: is a cartesian instead) somehow affect this? What business rule would a query like this implement? I ask purely because I'm always looking for new ways to express business rules in queries, just recently I learnt how to use an analytical function to return only the first two rows from a child table for each different requested parent in a single query.

I'm wondering if you have hit a bug by reusing the alias "b" as a cartesian in a subquery - just trying to think of things the Oracle folk may never have tested for. If adding the group by changes those results though then yes, you seem to have found a bug of some sort, regardless of the query.

Cheers.

                                                                                                                        
                    "Jamadagni,                                                                                         
                    Rajendra"                 To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    <Rajendra.Jamadagni       cc:                                                                       
                    @espn.com>                Subject:     RE: Urgent problem with query in CBO Vs RBO                  
                    Sent by:                                                                                            
                    root_at_fatcity.com                                                                                    
                                                                                                                        
                                                                                                                        
                    17/10/2002 23:13                                                                                    
                    Please respond to                                                                                   
                    ORACLE-L                                                                                            
                                                                                                                        
                                                                                                                        




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.
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! (See attached file: ESPN_Disclaimer.txt)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   Privileged/Confidential information may be contained in this message.
          If you are not the addressee indicated in this message
       (or responsible for delivery of the message to such person),
            you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
           by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
                Internet e-mail for messages of this kind.
        Opinions, conclusions and other information in this message
              that do not relate to the official business of
                         Transurban City Link Ltd
         shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

--

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

Author: Mark Richard
  INET: mrichard_at_transurban.com.au

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 - 18:33:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US