Home » SQL & PL/SQL » SQL & PL/SQL » Execution of Query (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production)
Execution of Query [message #654406] Tue, 02 August 2016 07:01 Go to next message
saipradyumn
Messages: 336
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 
or
 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
or
 
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 #654408 is a reply to message #654406] Tue, 02 August 2016 07:06 Go to previous messageGo to next message
BlackSwan
Messages: 25964
Registered: January 2009
Location: SoCal
Senior Member
No!
Produce EXPLAIN PLAN for each & compare them.
Re: Execution of Query [message #654409 is a reply to message #654406] Tue, 02 August 2016 07:06 Go to previous messageGo to next message
John Watson
Messages: 7365
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 #654413 is a reply to message #654409] Tue, 02 August 2016 07:47 Go to previous messageGo to next message
saipradyumn
Messages: 336
Registered: October 2011
Location: Hyderabad
Senior Member
Yes, when we go through the execution plan of the query there are some difference with respect to the cost,cardinality.
I am unable to make a decision (conclude)the based on the outcome.

Is there any rule that driving table(which has more data) has to be at the begining query ?
Re: Execution of Query [message #654414 is a reply to message #654413] Tue, 02 August 2016 07:51 Go to previous message
John Watson
Messages: 7365
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

Previous Topic: how to report oracle internal error
Next Topic: Replace Accented Characters
Goto Forum:
  


Current Time: Wed Apr 25 09:13:20 CDT 2018

Total time taken to generate the page: 0.04639 seconds