| 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: Query returning wrong results.
Waleed,
I did the similar thing what you mentioned in the email. I truncated the plan_table, ran the explain plan for the sql statment and then commited it. That was the reason, I did distinct on statement_id and object_node. I named the statement id as 'Wrong_results' at the time of explain plan for clarity.
Thanks.
Best Regards,
Prasad
860 843 8377
"Khedr, Waleed"
<Waleed.Khedr_at_FMR. To: "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org>
COM> cc:
Sent by: Subject: RE: Query returning wrong results.
oracle-l-bounce_at_fr
eelists.org
03/19/2004 05:26
PM
Please respond to
oracle-l
Run the following:
delete from plan_table;
commit;
explain plan for
<put your sql here>;
commit;
Would be interested if there is any ":Q%" in the object_node column.
Waleed
-----Original Message-----
From: Prasada.Gunda_at_hartfordlife.com
[mailto:Prasada.Gunda_at_hartfordlife.com]
Sent: Friday, March 19, 2004 4:57 PM
To: oracle-l_at_freelists.org
Subject: RE: Query returning wrong results.
No, I did not see anything in object_node column.
Here is the output.
SQL >select distinct statement_id, object_node from plan_table;
STATEMENT_ID OBJECT_NODE ------------------------------ ---------------------------------------------------------------------Wrong_results
Thanks.
Best Regards,
Prasad
860 843 8377
"Khedr, Waleed"
<Waleed.Khedr_at_FMR. To:
"'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org>
COM> cc:
Sent by: Subject: RE: Query
returning wrong results.
oracle-l-bounce_at_fr
eelists.org
03/19/2004 01:41
PM
Please respond to
oracle-l
When you do explain plan for the sql, do you see any ":Q" in the
object_node
column?
Waleed
-----Original Message-----
From: Prasada.Gunda_at_hartfordlife.com
[mailto:Prasada.Gunda_at_hartfordlife.com]
Sent: Friday, March 19, 2004 10:58 AM
To: oracle-l_at_freelists.org
Subject: Re: Query returning wrong results.
Thanks Jared, Waleed and David for your replies.
It is not using PQO (but parallel_automatic_tuning is set to TRUE). Table has degree 1. Also tested with noparallel hint and no_merge hints, it is returning same wrong results.
DB is 8.1.7.4 and It is on Hp-Unix v11.
David : Inline view is returning 3 records and only one record satisfies the outer filter predicate.
I will do more research on metalink and will update you all if I find anything relevant.
Best Regards,
Prasad
860 843 8377
Jared.Still_at_radisy
s.com To:
oracle-l_at_freelists.org
Sent by: cc:
oracle-l-bounce_at_fr Subject: Re: Query
returning wrong results.
eelists.org
03/18/2004 04:31
PM
Please respond to
oracle-l
Looks like a bug. If you provide some platform and version information someone may have an idea as to which bug.
Better yet, check on MetaLink for related bugs.
If it is a parallel query it could be bug 1759227
Jared
Prasada.Gunda_at_hartfordlife.com
To:
Sent by: oracle-l_at_freelists.org
oracle-l-bounce_at_freelists.org cc:
Subject: Query returning
wrong results.
03/18/2004 11:31 AM
Hi,
One of our developer sent me this query that it is returning wrong results. Please see the following query.
select v.col1, v.row_eff_date, v.row_term_date
from
( -- Inline view begins
select a.col1
, trunc(a.col2) row_eff_date
, nvl(lead(trunc(a.col2)) over
(partition by a.col1, a.tblnm, a.clmnnm order by a.col2),to_date ('12/31/2999','mm/dd/yyyy')) row_term_date from table1 a
where a.tblnm = 'INPUT_TABLE' and a.clmnnm = 'INPUT_COL' and a.col1 = '10000151'
where a1.tblnm = 'INPUT_TABLE'
and a1.clmnnm = 'INPUT_COL'
and a1.col1 = '10000151'
and (a1.col1, a1.col2) IN
(select octl1.col1, min(octl1.col2)
from table1 octl1
where octl1.tblnm ='INPUT_TABLE'
and octl1.clmnnm ='INPUT_COL'
group by octl1.col1
)
trunc(v.row_eff_date) > to_date('03/14/2004','MM/DD/YYYY') OR trunc(v.row_term_date) = to_date('12/31/2999','MM/DD/YYYY') )
If I run inline view on its own, it returns 3 records.
COL1 ROW_EFF_DAT ROW_TERM_DA
------------ ----------- ----------- 10000151 01-jan-1900 13-dec-2002 10000151 13-dec-2002 23-jan-2003 10000151 23-jan-2003 31-dec-2999
If I run the entire sql, it suppose to return the following record.
COL1 ROW_EFF_DAT ROW_TERM_DA
------------ ----------- -----------
10000151 23-jan-2003 31-dec-2999
But, the query returns the following two records which is wrong.
COL1 ROW_EFF_DAT ROW_TERM_DA
------------ ----------- ----------- 10000151 13-dec-2002 23-jan-2003 10000151 23-jan-2003 31-dec-2999
Did anybody experience this before. I kind of remember that it is something to do with analytical function but I can not recall. For the purpose of testing, when I created a table out of inner sql and used that table in the inner query, it works fine. The DB is 8.1.7.4 on Hp-unix v11. I also opened a TAR and will update the status to the List.
Thanks in advance.
Best regards,
Prasad
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ************************************************************************* PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. ************************************************************************* ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ************************************************************************* PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. ************************************************************************* ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ************************************************************************* PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. ************************************************************************* ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Mar 22 2004 - 11:33:53 CST
 |
 |