The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.
Thanks for the question, phani.
Asked: February 06, 2017 - 11:10 am UTC
Last updated: February 06, 2017 - 6:25 pm UTC
Version: 11g
Viewed 1000+ times
SQL> select a.ename, a.empno 2 from dept a 3 where exists(select null from emp b 4 where a.deptno=b.deptno(+)) ; select a.ename, a.empno * ERROR at line 1: ORA-00904: "A"."EMPNO": invalid identifier
Rajeshwaran, Jeyabal, February 07, 2017 - 12:37 pm UTC
demo@ORA11G> create table emp as select * from scott.emp; Table created. demo@ORA11G> create table dept as select * from scott.dept; Table created. demo@ORA11G> exec dbms_stats.gather_table_stats(user,'dept'); PL/SQL procedure successfully completed. demo@ORA11G> exec dbms_stats.gather_table_stats(user,'emp'); PL/SQL procedure successfully completed. demo@ORA11G> set autotrace traceonly explain demo@ORA11G> select a.* 2 from dept a 3 where exists(select null from emp b 4 where a.deptno=b.deptno(+)) ; Execution Plan ---------------------------------------------------------- Plan hash value: 1754319153 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 69 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN SEMI | | 3 | 69 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."DEPTNO"="B"."DEPTNO")
demo@ORA11G> select * 2 from dept d 3 where d.deptno in (select e.deptno from emp e ) ; Execution Plan ---------------------------------------------------------- Plan hash value: 1754319153 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 69 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN SEMI | | 3 | 69 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("D"."DEPTNO"="E"."DEPTNO")
Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!
Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!