| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Urgent problem with query in CBO Vs RBO
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;
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
)
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
a.colb = m.cola
and 1 != 1)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
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).
 |
 |