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 :

Explain Plan
How to make this query more optimize ?
LVL 16
Swadhin RaySenior Technical Engineer Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
If those row count estimates are correct, then I don't see how you are going to improve the performance much on a query that is only dealing with tables that have 14 rows at a maximum.  If those tables have a lot more rows, I would suggest gathering statistics on them.  The second thing I would try is a FIRST_ROWS hint.
0
 
Geert GruwezOracle dbaCommented:
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);
0
 
PortletPaulCommented:
and provide the explain plan result as text not an image please
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Swadhin RaySenior Technical Engineer Author Commented:
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

0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
@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
0
 
Swadhin RaySenior Technical Engineer Author Commented:
@Alex140181 :

Can you please share the code..
0
 
Alexander Eßer [Alex140181]Connect With a Mentor Software DeveloperCommented:
There are tons of examples out there regarding the EMP and DEPT tables, this one's from http://technology.amis.nl/2009/09/01/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it/:

it looks like...

with departments as
( select deptno dept_id
  ,      dname  dept_name
  from   dept
  where  loc in ('NEW YORK' ,'DALLAS')
)
, employees (empno, name, job, mgr, hierlevel) as
( select empno, ename, job, mgr, 1
  from   emp
  where  mgr is null
  union all
  select e.empno, e.ename, e.job
  ,      e.mgr, m.hierlevel + 1
  from   emp e
         join
         employees m
         on (m.empno = e.mgr)
         join
         departments
         on (e.deptno = departments.dept_id)
)
, subordinate_numbers as
( select mgr emp_id
  ,      count(*) number_of_subordinates
  from   employees
  group by mgr
)
select *
from   employees e
       left outer join
       subordinate_numbers s
       on (e.empno = s.emp_id)

Open in new window

0
 
Swadhin RaySenior Technical Engineer Author Commented:
Thanks..
0
All Courses

From novice to tech pro — start learning today.