OraFAQ Forum: SQL & PL/SQL » how correlated subquery and a normal subqueryworks?

Home » SQL & PL/SQL » SQL & PL/SQL » how correlated subquery and a normal subqueryworks?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Thu, 16 October 2003 13:35

sancha
Messages: 31
Registered: October 2003

Member

hi

the one which works:
select ename e from emp where exists (select 0 from dual where ename='FORD')

1. the one which is not working:
select ename e from emp where exists (select 0 from dual where e='FORD')

I hv used the alias to check the parent(global) variable to that of the parameter ('FORD') - which the sql immediately rejects.
While the one which is compared with the original variable itself it works.

if it didnt work for alias during parsing then how did is it working for the other.

2. which part is executed first ( during parsing ) in nested sub-queries? (for coorelated subquery? for inner query? for normal (2) table subquery? -i.e select ename from emp where dept = (select deptno from dept where deptname='inv');

pls clarify both the first and second question.

Report message to a moderator

Thu, 16 October 2003 15:23

Thiru
Messages: 1089
Registered: May 2002

Senior Member

Sancha,
As for your 1st question, could you copy and paste here what exactly you did . Also do DESC DUAL

2. In a normal subquery, the inner query is executed first and ONCE and the results are passed off to the parent query.
eg)
thiru@9.2.0:SQL>select empno,ename from emp where sal=(select max(sal) from emp);

EMPNO ENAME
---------- ----------
7839 King

1 row selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=13)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Bytes=13)
2 1 INDEX (RANGE SCAN) OF 'EMP_SAL_IDX' (NON-UNIQUE) (Cost=1 Card=1)
3 2 SORT (AGGREGATE)
4 3 INDEX (FULL SCAN (MIN/MAX)) OF 'EMP_SAL_IDX' (NON-UNIQUE) (Cost=2 Card=15 Bytes=60)

As you see here,first an INDEX full scan is performed against the EMP table to get the max(SAL) using the EMP_SAL_IDX index. The results are then passed to the parent query which accesses the table using the INDEX on SAL (becos of the where sal= ) to get the employee details.

In a correlated subquery,the inner query references one or more columns of the parent query and hence it gets executed once per row returned by the parent query. So the parent query is evaluated first and for every row that is returned, the inner subquery is executed.

eg)
thiru@9.2.0:SQL>select deptno from dept d where exists(
2 select * from emp e where e.deptno=d.deptno);

DEPTNO
----------
10
20
30

3 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=3 Bytes=12)
1 0 NESTED LOOPS (SEMI) (Cost=1 Card=3 Bytes=12)
2 1 INDEX (FULL SCAN) OF 'PK_DEPT' (UNIQUE) (Cost=1 Card=4 Bytes=8)
3 1 BITMAP CONVERSION (TO ROWIDS)
4 3 BITMAP INDEX (SINGLE VALUE) OF 'EMP_DEPTNO_IDX'

AS you see here, the DEPT table is accessed first by the Index full scan of PK_DEPT and for that deptno,employee details are returned via the bitmap index EMP_DEPTNO_IDX ,which makes the subquery return TRUE becos of the EXISTS operator to the parent query.

Hope this clarifies
Thiru

Report message to a moderator

Fri, 17 October 2003 03:26

sancha
Messages: 31
Registered: October 2003

Member

hi thiru,

1) SQL> select ename e from emp where exists (select 0 from dual where e='FORD')
2 ;
select ename e from emp where exists (select 0 from dual where e='FORD')
*
ERROR at line 1:
ORA-00904: invalid column name

SQL> ed
Wrote file afiedt.buf

1* select ename e from emp where exists (select 0 from dual where ename='FORD')
2 /

E
----------
FORD

SQL> desc dual
Name Null? Type
------------------------------- -------- ----
DUMMY VARCHAR2(1)

SQL> select * from dual;

D
-
X

This is the entire story.
Hope this data is enough.

2)
the tracer kind of steps hardly reaches my brain. Sorry about that. Could you please tell me how you performed that tracing of steps. And also please try to give an brief explanation on what those individual steps stand for.

Report message to a moderator

Fri, 17 October 2003 06:32

Thiru
Messages: 1089
Registered: May 2002

Senior Member

1. Yours is a correlated sub query where the subquery refers to the ename column from the outer query. Here only the table alias is passed to the subquery and not the column alias(ie E) which is used for the display.

we can also do this :
thiru@9.2.0:SQL>select ename e from emp X where exists (select 0 from dual where X.ename='Ford');

E
----------
Ford

1 row selected.

Here X is being recognised.

2. Didnt mean to confuse you.I thought it will help you better understand if you see the execution plan(but since the formatting got lost during the display,I think it made it more difficult to understand).
Getting the trace is quite simple.
SQL>set autotrace on explain
SQL > your query here

I did briefly explain the individual steps that the optimizer goes through to execute the normal subquery and correlated one. For more detaild info,pls refer to documentation ..you know a lot to explain ;)

Hope this helps
Thiru

Report message to a moderator

Fri, 17 October 2003 07:55

sancha
Messages: 31
Registered: October 2003

Member

hi

got the faint idea about the working of sub and correlated sub query. but still wondering what "range, scan, and few more jargons are?"

why did u ask me to do
desc dual ? what does the "X" mean anyway?

and pls tell me wht is the error with this statement for trace:

SQL> set autotrace on explain
Unable to verify PLAN_TABLE format or existence
Error enabling EXPLAIN report
SQL>

as for documentation or any book or source in the net, pls suggest some.

bfn

Report message to a moderator

Fri, 17 October 2003 08:37

Thiru
Messages: 1089
Registered: May 2002

Senior Member

Sancha,
Index range scan is basically where multiple Index leaf blocks are traversed for given key or a set of keys to get the list of rowids which are used to get the data from the associated table. For more details refer to the enclosed online documentation URL.

I asked you to do a desc DUAL, just to make sure you dont have your own DUAL table in you schema. X is the table alias we assigned to emp.

You are getting the error relating to plan_table becos you dont have access to PLAN_TABLE.

Run this script to create the plan_Table in your schema
@$ORACLE_HOME/rdbms/admin/utlxplan.sql.
alternatively you can create the PLAN_TABLE in a common schema and grant privileges to public and create a public synonym for access.

Following this, create the PLUSTRACE role as follows:
- Invoke sqlplus and connect as sys user
- Run $ORACLE_HOME/sqlplus/admin/plustrc.sql
- Grant Plustrace to dba with admin option;
- Grant plustrace to 'your user';

-Thiru

online_doc

Report message to a moderator

Previous Topic:	limitation
Next Topic:	Limiting query result : rownum vs cursor loop

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 06:13:22 CDT 2024