| Query execution changes in oracle 11.2.0.4.5 version [message #634750] |
Sun, 15 March 2015 00:57  |
 |
avineshr
Messages: 14
Registered: September 2012
Location: malaysia
|
Junior Member |
|
|
Hi All ,
Please let us know after upgrade of oracle to 11.2.0.4.5 version.
A query in below format is not working as expected.
T1 is table which has no data.
create table T1
(
table_name VARCHAR2(30),
tablespace_name VARCHAR2(30)
)
Let us know why below query is not returning records in version 11.2.0.4.5 in previous version it is working fine.
SELECT 1 FROM (SELECT 1 col1 , (SELECT COUNT(1) FROM t1) col2 FROM dual) WHERE col1-col2>0
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query execution changes in oracle 11.2.0.4.5 version [message #634761 is a reply to message #634750] |
Sun, 15 March 2015 02:11  |
John Watson
Messages: 8985
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I get no rows returned in 11.2.0.4.0 and 12.1.0.2.0:
SQL> create table T1
2 (
3 table_name VARCHAR2(30),
4 tablespace_name VARCHAR2(30)
5 );
Table created.
SQL> SELECT 1 FROM (SELECT 1 col1 , (SELECT COUNT(1) FROM t1) col2 FROM dual) WHERE col1-col2>0;
no rows selected
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SQL>
SQL>
SQL> create table T1
2 (
3 table_name VARCHAR2(30),
4 tablespace_name VARCHAR2(30)
5 );
Table created.
SQL> SELECT 1 FROM (SELECT 1 col1 , (SELECT COUNT(1) FROM t1) col2 FROM dual) WHERE col1-col2>0;
no rows selected
SQL> select * from v$version where rownum=1;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
SQL>
I woud never exect anythng back, because column name aliases are assigned only at column projection time, not at row selection time. So you can use a column alieas in an ORDER BY clause, but not in a predicate:
SQL>
SQL> SELECT 1 col1 , (SELECT COUNT(1) FROM t1) col2 FROM dual order by col1;
COL1 COL2
---------- ----------
1 0
SQL> SELECT 1 col1 , (SELECT COUNT(1) FROM t1) col2 FROM dual where col1=1;
SELECT 1 col1 , (SELECT COUNT(1) FROM t1) col2 FROM dual where col1=1
*
ERROR at line 1:
ORA-00904: "COL1": invalid identifier
SQL>
Can you show an example of your code working as you think it should?
|
|
|
|
| Re: Query execution changes in oracle 11.2.0.4.5 version [message #634762 is a reply to message #634761] |
Sun, 15 March 2015 02:28 |
 |
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
In this case the column aliases are used one query outer from where they are defined, so it is valid.
Or to take your example, it'd be:
SQL> select * from (SELECT 1 col1 , (SELECT COUNT(1) FROM t1) col2 FROM dual) where col1=1;
COL1 COL2
---------- ----------
1 0
In 10.2.0.4 as well as in 11.2.0.1.0, it works as expected:
SQL> SELECT 1 FROM (SELECT 1 col1 , (SELECT COUNT(1) FROM t1) col2 FROM dual) WHERE col1-col2>0;
1
----------
1
1 row selected.
with the inline view as:
SQL> SELECT 1 col1 , (SELECT COUNT(1) FROM t1) col2 FROM dual;
COL1 COL2
---------- ----------
1 0
1 row selected.
I have no version where it does not work, can you test it using "SELECT *" instead of the "SELECT 1" and removing the WHERE clause.
|
|
|
|
| Re: Query execution changes in oracle 11.2.0.4.5 version [message #634764 is a reply to message #634762] |
Sun, 15 March 2015 02:52 |
John Watson
Messages: 8985
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Yes, the projected column names in a subquey are usable in an outer query predicate. THankyou for the correction.
On both 11.2.0.4.0 and 12.1.0.2.0 I get this same result SQL> SELECT * FROM (SELECT 1 col1 , (SELECT COUNT(1) FROM t1) col2 FROM dual) WHERE col1-col2>0;
COL1 COL2
---------- ----------
1 0
SQL> SELECT 1 FROM (SELECT 1 col1 , (SELECT COUNT(1) FROM t1) col2 FROM dual) WHERE col1-col2>0;
no rows selected
SQL>
And the exec plan stats are:SQL> SELECT * FROM (SELECT 1 col1 , (SELECT COUNT(1) FROM t1) col2 FROM dual) WHERE col1-col2>0;
COL1 COL2
---------- ----------
1 0
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last +outline +cost'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 0cs1hzcgp59h4, child number 1
-------------------------------------
SELECT * FROM (SELECT 1 col1 , (SELECT COUNT(1) FROM t1) col2 FROM
dual) WHERE col1-col2>0
Plan hash value: 2068382029
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (100)| 1 |00:00:00.01 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 |
| 2 | TABLE ACCESS FULL| T1 | 1 | 82 | 2 (0)| 0 |00:00:00.01 |
|* 3 | VIEW | | 1 | 1 | 4 (0)| 1 |00:00:00.01 |
| 4 | FAST DUAL | | 1 | 1 | 2 (0)| 1 |00:00:00.01 |
----------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
FULL(@"SEL$3" "T1"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("COL1"-"COL2">0)
40 rows selected.
SQL> SELECT 1 FROM (SELECT 1 col1 , (SELECT COUNT(1) FROM t1) col2 FROM dual) WHERE col1-col2>0;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last +outline +cost'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 79b0sj36qqugx, child number 1
-------------------------------------
SELECT 1 FROM (SELECT 1 col1 , (SELECT COUNT(1) FROM t1) col2 FROM
dual) WHERE col1-col2>0
Plan hash value: 2054409553
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (100)| 0 |00:00:00.01 |
|* 1 | FILTER | | 1 | | | 0 |00:00:00.01 |
| 2 | FAST DUAL | | 0 | 1 | 2 (0)| 0 |00:00:00.01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 1 | 2 (0)| 0 |00:00:00.01 |
----------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$3" "T1"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
40 rows selected.
SQL>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
) can come up with omething.
Then you will see why you are losing the row.