Differents SELECTs clauses change the number of rows (with same WHERE condition)

0
votes

I have this issue (maybe not an error).

On an Oracle View, the columns in SELECT change the number of rows obtained from DB.

It's an error with the definition of the VIEW?

I think this incident only could be possible with VIEWS but never in TABLES, or not?

--16 ROWS
SELECT *
FROM MY_ORACLE_VIEW
WHERE CONDITION_1 = 'A' AND CONDITION_2 = 'B';

--112 ROWS
SELECT COLUMN_X, COLUMN_Y
FROM MY_ORACLE_VIEW
WHERE CONDITION_1 = 'A' AND CONDITION_2 = 'B';

Note: The real view are very complex

Oracle Version from (SELECT * FROM V$VERSION):

  • Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  • PL/SQL Release 11.2.0.4.0 - Production
  • CORE 11.2.0.4.0 Production
  • TNS for HPUX: Version 11.2.0.4.0 - Production
  • NLSRTL Version 11.2.0.4.0 - Production
1
sqloracle
No, that shouldn't even happen in views. That's pretty weird. There must be more to it. – Nick.McDermaid
Agreed, this should never happen. Did you compare the plans for both queries? – dnoeth
The complexity of the view might be an issue. Also, some optimizer statements in your view might lead Oracle to trip over the query. I have seen this issues with complex views, group by's and unions combined. – Patrick Hofman
Can you specify the query, version of database, platform, patches, etc.? – Patrick Hofman
No matter why, this is a wrong result and should be treated as such. Open an incident with your Oracle support. – dnoeth

1 Answers

0
votes

This can happen if the VIEW target a growing table. Or maybe if the VIEW is using a TIME variable in the where clause.

You also can try create your own simple view to see if that is a recurrent issue.

Anyway unless you provide the view code we can only speculate about it.