| Execution of Query [message #654406] |
Tue, 02 August 2016 07:01  |
 |
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
|
Senior Member |
|
|
Hi
1: Does the order of the tables , order of joining conditions will impact the execution of the Query.
If so, what are factors that need to be considered for the effecient execution of the query when we have the large volume of data?
Example
select * from table_1 t1 , table_2 t2 , table_3 t3 where
t1.id= t2.id and t1.id=t3.id
select * from table_3 t3 , table_1 t1 , table_2 t2 where
t3.id= t1.id and t1.id=t2.id
2: Does the way how we write the condition will impact query ?
Example :
SELECT * FROM emp e , dept d WHERE e.deptno = d.deptno
SELECT * FROM emp e , dept d WHERE d.deptno = e.deptno
Both are having the same results. Only the difference is first query using e.deptno = d.deptno where as second query using d.deptno = e.deptno
|
|
|
|
|
|
| Re: Execution of Query [message #654409 is a reply to message #654406] |
Tue, 02 August 2016 07:06  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Easy to find out: using SQL*Plus, use the autotrace facility to display the execution plan, and see if it is differs for different statements. Example:orclz>
orclz> set autotrace on explain
orclz> select * from emp natural join dept;
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- -------------- -------------
10 7782 CLARK MANAGER 7839 1981-06-09:00:00:00 2450 ACCOUNTING NEW YORK
10 7839 KING PRESIDENT 1981-11-17:00:00:00 5000 ACCOUNTING NEW YORK
10 7934 MILLER CLERK 7782 1982-01-23:00:00:00 1300 ACCOUNTING NEW YORK
20 7566 JONES MANAGER 7839 1981-04-02:00:00:00 2975 RESEARCH DALLAS
20 7902 FORD ANALYST 7566 1981-12-03:00:00:00 3000 RESEARCH DALLAS
20 7876 ADAMS CLERK 7788 1987-05-23:00:00:00 1100 RESEARCH DALLAS
20 7369 SMITH CLERK 7902 1980-12-17:00:00:00 800 RESEARCH DALLAS
20 7788 SCOTT ANALYST 7566 1987-04-19:00:00:00 3000 RESEARCH DALLAS
30 7521 WARD SALESMAN 7698 1981-02-22:00:00:00 1250 500 SALES CHICAGO
30 7844 TURNER SALESMAN 7698 1981-09-08:00:00:00 1500 0 SALES CHICAGO
30 7499 ALLEN SALESMAN 7698 1981-02-20:00:00:00 1600 300 SALES CHICAGO
30 7900 JAMES CLERK 7698 1981-12-03:00:00:00 950 SALES CHICAGO
30 7698 BLAKE MANAGER 7839 1981-05-01:00:00:00 2850 SALES CHICAGO
30 7654 MARTIN SALESMAN 7698 1981-09-28:00:00:00 1250 1400 SALES CHICAGO
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
orclz> |
|
|
|
|
|
| Re: Execution of Query [message #654414 is a reply to message #654413] |
Tue, 02 August 2016 07:51 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote: when we go through the execution plan of the query there are some difference with respect to the cost,cardinality. Really? That is highly unusual. It suggests that your queries are not in fact equivalent. You had better post them and their plans here.
[Updated on: Tue, 02 August 2016 07:51] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
