asked on

Improving SQL performance

Hello Experts,

I have the SQL as below:

SELECT concat(lpad('   '
                  ,LEVEL * 3 - 3)
             ,e.ename) ename
      ,LEVEL
      ,e.empno
      ,e.mgr
     ,(SELECT  a.ename FROM emp a WHERE a.empno = e.mgr) manager
      ,d.dname
      ,d.deptno
      ,(CASE
               WHEN d.deptno = 10 THEN
                d.dname
               ELSE
                NULL
       END) case_clause
  FROM emp  e
      ,dept d
 WHERE e.deptno = d.deptno
CONNECT BY PRIOR e.empno = e.mgr
 START WITH e.mgr IS NULL

Open in new window

And here is the plan for the above query :

How to make this query more optimize ?

Geert G

toad isn't the best at giving an execution plan

run this script with F5 in toad (at least version 10.5)

explain plan for
select ... // replace this select with your query
set linesize 2000
set pagesize 999
set trim on
set tab off
select * from table(dms_xplan.display);

PortletPaul

and provide the explain plan result as text not an image please

Swadhin Ray

ASKER

Here is the output:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
Connected as scott

SQL> 
SQL> explain plan for
  2  SELECT concat(lpad('   '
  3                    ,LEVEL * 3 - 3)
  4               ,e.ename) ename
  5        ,LEVEL
  6        ,e.empno
  7        ,e.mgr
  8        ,(SELECT a.ename FROM emp a WHERE a.empno = e.mgr) manager
  9        ,d.dname
 10        ,d.deptno
 11        ,(CASE
 12                 WHEN d.deptno = 10 THEN
 13                  d.dname
 14                 ELSE
 15                  NULL
 16         END) case_clause
 17    FROM emp  e
 18        ,dept d
 19   WHERE e.deptno = d.deptno
 20  CONNECT BY PRIOR e.empno = e.mgr
 21   START WITH e.mgr IS NULL;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2319208209
--------------------------------------------------------------------------------
| Id  | Operation                               | Name    | Rows  | Bytes | Cost
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |         |    14 |   770 |
|   1 |  TABLE ACCESS BY INDEX ROWID            | EMP     |     1 |    10 |
|*  2 |   INDEX UNIQUE SCAN                     | PK_EMP  |     1 |       |
|*  3 |  CONNECT BY NO FILTERING WITH START-WITH|         |       |       |
|   4 |   MERGE JOIN                            |         |    14 |   420 |
|   5 |    TABLE ACCESS BY INDEX ROWID          | DEPT    |     4 |    52 |
|   6 |     INDEX FULL SCAN                     | PK_DEPT |     4 |       |
|*  7 |    SORT JOIN                            |         |    14 |   238 |
|   8 |     TABLE ACCESS FULL                   | EMP     |    14 |   238 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."EMPNO"=:B1)
   3 - access("E"."MGR"=PRIOR "E"."EMPNO")
       filter("E"."MGR" IS NULL)
   7 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")

24 rows selected

SQL>

Open in new window

Alex [***Alex140181***]

@Geert_Gruwez: that screenshot looks like PL/SQL Developer to me ;-)

@slobaray: Maybe you'd gain some (little) more performance by changing the connect-by to an CTE (using recursive with clause)... -> Cost 7 <-> Cost 4

Swadhin Ray

ASKER

@Alex140181 :

Can you please share the code..

ASKER CERTIFIED SOLUTION