ORA-01791: 不是 SELECTed 表达式
在oracle 11.2.0.3上执行是完全没有问题的，但是11.2.0.4（RAC）上执行就是会报0ra-01791select distinct dd.id, b.proj_name, dd.chrg_card, dd.holder_name, b.proj_id
from tk_mem_creditcard_his_dt dd, bd_datadeta_dt b
where dd.chrg_cardnm = b.proj_id
and b.datatype_id = '00008'
and dd.paytype_id = '0000300001'
and dd.member_card_no = '105664728'
order by dd.update_time desc
ORA-01791: 不是 SELECTed 表达式
当然我测试如果在select里加上 dd.update_time 在11.2.0.4也不会报错，如下：
select distinct dd.id, b.proj_name, dd.chrg_card, dd.holder_name, b.proj_id,dd.update_time
from tk_mem_creditcard_his_dt dd, bd_datadeta_dt b
where dd.chrg_cardnm = b.proj_id
and b.datatype_id = '00008'
and dd.paytype_id = '0000300001'
and dd.member_card_no = '105664728'
order by dd.update_time desc
但是奇怪的是在高版本里为什么倒还报错呢，应该向后兼容才对呀，11.2.0.4版本为什么不行呢？
我们现在上一个升级的系统，如果旧版里存在这样的语句会出潜在的问题。。。
谢谢！ ORA-01791: not a SELECTed expression after upgrade to 11.2.0.4 (Doc ID 1600974.1) 做一个hard parse +10053 我看下 odm finding:
This issue is fixed in
12.1.0.1 (Base Release)
11.2.0.4 (Server Patch Set)
Symptoms:
Related To:
Wrong Results
ORA-1791
Optimizer
Constraint Related
_optimizer_eliminate_filtering_join
Description
Invalid query which should raise ORA-1791 is working fine without any error starting from 11.2.0.1.
There are 2 cases this fix addresses
1) A combination of NOT NULL constraint , UNIQUE index and invalid SELECT DISTINCT
create table t (x varchar2(10),y date) ;
create unique index i on t(x) ;
alter table t modify (x not null enable);
prompt Invalid SELECT DISTINCT raises no error
select distinct x
from t
order by y;
2) An invalid query undergoing "optimizer_eliminate_filtering_join" transformation.
prompt Invalid SELECT DISTINCT raises no error
select distinct t2.col1
from tab1 t0,
tab2 t2,
tab2 t3
where t0.col1=t2.col2
and t0.col1=t3.col2
order by t3.col1;
In the above DISTINCT statement case the ORDER BY clause cannot refer to column (t3.col1)
unless they appear in the SELECT list item. This is a documented restriction of the ORDER BY clause.
Workaround
Correct the query , as it will fail with this bug fixed anyway. SQL> alter session set tracefile_identifier='licz';
Session altered
SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
Session altered
SQL> select distinct sal, empno from scott.emp order by deptno;
select distinct sal, empno from scott.emp order by deptno
ORA-01791: 不是 SELECTed 表达式
SQL> select distinct sal, empno,deptno from scott.emp order by deptno;
SAL EMPNO DEPTNO
--------- ----- ------
2450.00 7782 10
5000.00 7839 10
1300.00 7934 10
2975.00 7566 20
3000.00 7902 20
1100.00 7876 20
800.00 7369 20
3000.00 7788 20
1250.00 7521 30
1500.00 7844 30
1600.00 7499 30
950.00 7900 30
2850.00 7698 30
1250.00 7654 30
14 rows selected
SQL> ALTER SESSION SET EVENTS '10053 trace name context off';
Session altered
SQL>
在11.2.0.3中发现，如果select 中去掉主键empno 也依然会报错，也就是说在旧版本中这种情况没有报错，说明select后中列组本身就是唯一的，所以完成可以去年distinct的用法，结果是一样的！
SQL> select distinct sal, empno from scott.emp order by deptno;
SAL EMPNO
--------- -----
2450.00 7782
5000.00 7839
1300.00 7934
2975.00 7566
3000.00 7902
1100.00 7876
800.00 7369
3000.00 7788
1250.00 7521
1500.00 7844
1600.00 7499
950.00 7900
2850.00 7698
1250.00 7654
14 rows selected
SQL> select distinct sal from scott.emp order by deptno;
select distinct sal from scott.emp order by deptno
ORA-01791: 不是 SELECTed 表达式
这时我们去掉distinct用法，其实和第一次结果是一样的
SQL> select sal, empno from scott.emp order by deptno;
SAL EMPNO
--------- -----
2450.00 7782
5000.00 7839
1300.00 7934
2975.00 7566
3000.00 7902
1100.00 7876
800.00 7369
3000.00 7788
1250.00 7521
1500.00 7844
1600.00 7499
950.00 7900
2850.00 7698
1250.00 7654
14 rows selected 没有搞懂 你5楼 的内容，具体过程请给直接的日志 不要手工处理。
_optimizer_eliminate_filtering_join = true
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 4 | |
| 1 | SORT ORDER BY | | 14 | 154 | 4 | 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 | 00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
Content of other_xml column
===========================
db_version : 11.2.0.4
parse_schema : SYSTEM
plan_hash : 150391907
plan_hash_2 : 3338220364
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/
页:
[1]