Help - Search - Members - Calendar
Full Version: View behaving indifferent
Oracle DBA Forums > Oracle > Oracle Forum
dhillu
Hi All,

I have an issue with a view.

When i am executing a view

SELECT DISTINCT code FROM v_payroll; ------ output giving 4 rows
select distinct code from v_payroll; ------- ouput giving only 1 row!!!!!!!!!!!!


The only change i did for this statement was caps to small letters.

This view is a simple view which is actually using one table ( select colum1,coul2 from table)

Why the same statement showing different output jsut by changing capital to small letters as shown above.

Is there any solution for this

Please help its very urgent.... I am using 10G RAC config. with 2 nodes.
burleson
>> Please help its very urgent....

All URGENT request need to be posted on MetaLnk, not here:

http://metalink.oracle.com

BTW, is ther a WHERE clause in the view?
SteveC
Same user each time? What is the underlying table(s)? Any changes to that between the times the select statements were run? This is not urgent. Changing the case of the SQL statement (in your example) is not going to change the result set.
dhillu
QUOTE (SteveC @ Jul 29 2008, 12:44 PM) *
Same user each time? What is the underlying table(s)? Any changes to that between the times the select statements were run? This is not urgent. Changing the case of the SQL statement (in your example) is not going to change the result set.



Thanks for the reply.

Its only one user . There is no where clause. Its single table, and the view selects all the columns from this table.

Yes.. i know that by oracle theory it shouldn't happen. But still its happening sad.gif
dhillu
QUOTE (dhillu @ Jul 29 2008, 01:11 PM) *
Thanks for the reply.

Its only one user . There is no where clause. Its single table, and the view selects all the columns from this table.

Yes.. i know that by oracle theory it shouldn't happen. But still its happening sad.gif

Please help me if anybody come across this kind of issues.
Asma
QUOTE (dhillu @ Jul 29 2008, 11:49 AM) *
Hi All,

I have an issue with a view.

When i am executing a view

SELECT DISTINCT code FROM v_payroll; ------ output giving 4 rows
select distinct code from v_payroll; ------- ouput giving only 1 row!!!!!!!!!!!!


The only change i did for this statement was caps to small letters.

This view is a simple view which is actually using one table ( select colum1,coul2 from table)

Why the same statement showing different output jsut by changing capital to small letters as shown above.

Is there any solution for this

Please help its very urgent.... I am using 10G RAC config. with 2 nodes.


can you please post explain plan for each statements having two different result sets.
burleson
>> Please help me if anybody come across this kind of issues.

CHECK METALINK!

If this were "innocent", it would be a very serious issue, and I would have heard about it.

- Could you have a VPD that is restricting rows returned?

- Is this a materialized view?

Without data, we are just gressing.

Please post the source statements and execution plans.
SteveC
I am willing to bet a big chicken dinner that you have some inconsistency in your environment and/or what you've told us isn't the complete story. There is absolutely no way in hell you are getting different results if:

-the same user is running this, exactly as is in a SQL*Plus session
-there is no change to the base table

If you are picking this statement out of a procedure/function/package owned by someone else, and comparing it to what another user gets in a different session/context (which allows for each user or invoker to be referencing an object that has the same name, but different owner), then maybe.

Otherwise, the source of the problem is clearly this: PEBCAK.
dhillu
Hi All,

I think steve you suppose to give me a chicken dinner smile.gif

Please see the 1st attachment for the issue.
The explain plan is giving the same result for both the queries.( please see 2nd attachment)

Even if you change the space anywhere in that last query, its going to give 4 rows smile.gif ( You can see 2nd query i changed only space then its giving 4 rows )

one thing is that its having 2 nodes. This issue is happening only if this node is P2.. In P1 its working fine.

One more thing if i do any changes in that view... for example ( give GRANT to that view or just recreate this view).. Then it will work fine for some time... I mean may be for 1 week... After that the issue will come again.

I hope this explains well..
SteveC
Stored outline - query re-write taking place? You have something intercepting the statement. Again, same query, same user, same session, no change to the base table used in the view = guaranteed to get the same results.
dhillu
QUOTE (SteveC @ Jul 31 2008, 03:52 PM) *
Stored outline - query re-write taking place? You have something intercepting the statement. Again, same query, same user, same session, no change to the base table used in the view = guaranteed to get the same results.


Hi Steve,

Thanks for the reply.

Its not stored-outline
and
same query, same user, same session, no change to the base table used in the view = different results.
Jac
QUOTE (dhillu @ Jul 31 2008, 09:01 PM) *
Hi Steve,

Thanks for the reply.

Its not stored-outline
and
same query, same user, same session, no change to the base table used in the view = different results.


is this view comparing some dates? post the query please this view is based on.
dhillu
QUOTE (Jac @ Jul 31 2008, 05:04 PM) *
is this view comparing some dates? post the query please this view is based on.


Here is the query under this view...
CREATE OR REPLACE VIEW V_FDS_PAYROLL_CODE_S
(MCODE, COUNTRY_MCODE, DESCRIPTION, FILE_ID, RECORD_ID)
AS
SELECT "MCODE","COUNTRY_MCODE","DESCRIPTION","FILE_ID","RECORD_ID" FROM payroll

Its not comparing any dates...

You can see attachments from my past reply to see the issue
dhillu
QUOTE (dhillu @ Aug 1 2008, 07:07 AM) *
Here is the query under this view...
CREATE OR REPLACE VIEW V_FDS_PAYROLL_CODE_S
(MCODE, COUNTRY_MCODE, DESCRIPTION, FILE_ID, RECORD_ID)
AS
SELECT "MCODE","COUNTRY_MCODE","DESCRIPTION","FILE_ID","RECORD_ID" FROM payroll

Its not comparing any dates...

You can see attachments from my past reply to see the issue

Please let me know is there any solution for this
dave
well if you are that convinced open an SR with oracle - produce a very small reproduceable test case
dhillu
Hi burleson,

I saw your initial reply.. You said that you heard this kind of dangerous issues before.
Could you please give some more inputs for this issue.. Or is there any way to sort out this issue?
Thanks.
burleson
Hi,

>> is there any way to sort out this issue?

Yes. It's always serious when someone identifies SQL that returns incorrect results, and Oracle technical support will jump all over it. First, check for bugs and workarounds on MetaLink:

- 10.2.0.4 patchset - Bug 5286826 - Wrong results from complex view merge with windowing

Open a service request immediately with Oracle tech support:

http://metalink.oracle.com
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.