Thanks Connor
sona sh, March 09, 2016 - 7:20 am UTC
Thanks Connor for answering my query.
The link that you shared is of sql tuning morever.I am looking for sql query.if i need to write nested query for my project requirement.
March 10, 2016 - 2:34 am UTC
Well, so far you've given us "How to write SQL efficiently?".
That's not really much to go on.
It's like me saying: "I need to buy car - tell me which one I should buy?"
Thanks Connor
sona sh, March 09, 2016 - 7:27 am UTC
Thanks Connor for answering that.But i am looking morever for sql query .how to get a grab on writing nested query based on different project requirement.
Martin Rose, March 17, 2016 - 10:23 am UTC
Why do you have such an obsession with nested queries?
You write the SQL needed to fulfill your requirements, not decide one day to just set out thinking to yourself,
"I'm going to write SQL today, with nested queries. Now let's see what I can write".
March 17, 2016 - 5:25 pm UTC
Well there's some value when you're learning how to use nested queries asking yourself "Could I write this using nested queries? If so how? Is it better than not doing?"
But yes, when you're building an app you should go with the SQL that answers your question.
Chris
A reader, March 17, 2016 - 9:22 pm UTC
Sona -
Does the business requirement need nested queries ? If yes , that's great .
How did you come to a conclusion that "nested queries" are the efficient queries , with out knowing what the nested queries are.
If this requirement comes from your tech lead. I would question that.
As already mentioned , read about SQL ( Database SQL Tuning Guide , Database Data Warehousing Guide - For analytics , pattern matching ) .
March 18, 2016 - 2:07 am UTC
Thanks for your input.
Optimizer Transformation : | To sona
Rajeshwaran Jeyabal, March 18, 2016 - 9:06 am UTC
Well don't forget to read about optimizer transformation from docs
http://docs.oracle.com/database/121/TGSQL/tgsql_transform.htm#TGSQL94896 That helps to understand how the optimizer transforms subqueries into joins. ( and dont be surprised with that, it is an optimization )
rajesh@ORA12C> set autotrace traceonly explain
rajesh@ORA12C> select *
2 from emp e
3 where exists (select *
4 from dept d
5 where e.deptno = d.deptno )
6 /
Execution Plan
----------------------------------------------------------
Plan hash value: 230627304
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 574 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 14 | 574 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 12 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")