Home » RDBMS Server » Performance Tuning » Tuning a small but complicated query (Oracle)
Tuning a small but complicated query [message #537619] Thu, 29 December 2011 14:55 Go to next message
neel_Tannu
Messages: 1
Registered: December 2011
Junior Member
select distinct b.pid, d.bo_name, d.fname, d.lname,
e.ss_user_type, x.latshrtname
from rsf_suser_vw b, person c, NAME d,
rsf_suser e, item x
where
exists (select 'x' from NAME y where y.id = c.id and
(y.fname_srch like '%' and y.lname_srch like '%'))
and b.pid = c.pid
and c.id = d.id
and c.pid = e.pid
and e.sales_user_type=x.fieldvalue
and x.fieldname='SUSER_TYPE'
and
exists (select 1 from
(SELECT DISTINCT P1.HIERARCHY_NODE FROM HIERARCHYNODE P1 WHERE P1.HIERARCHY_NAME = 'GLOBE' AND
CONNECT_BY_ISLEAF = 1 START WITH P1.HIERARCHY_NODE IN (select TERRITORY_ID from TEAM WHERE
pid = '558') CONNECT BY PRIOR P1.HIERARCHY_NODE = P1.HIERARCHY_NODE_NAME
INTERSECT SELECT DISTINCT
P1.HIERARCHY_NODE FROM HIERARCHYNODE P1 WHERE P1.HIERARCHY_NAME = 'GLOBE' AND
CONNECT_BY_ISLEAF = 1 START WITH
P1.HIERARCHY_NODE = '1093' CONNECT BY PRIOR P1.HIERARCHY_NODE = P1.HIERARCHY_NODE_NAME)
where P1.territory_id);
Re: Tuning a small but complicated query [message #537621 is a reply to message #537619] Thu, 29 December 2011 15:17 Go to previous messageGo to next message
John Watson
Messages: 8592
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum - I hope you will both learn and contribute (as I try to do). Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

I've put your code through a formatter and applied the correct tags - do you see that it is a bit easier to read now? But you haven't actually asked a question. What is the problem?

SELECT DISTINCT b.pid,
                d.bo_name,
                d.fname,
                d.lname,
                e.ss_user_type,
                x.latshrtname
FROM   rsf_suser_vw b,
       person c,
       NAME d,
       rsf_suser e,
       item x
WHERE  EXISTS (SELECT 'x'
               FROM   NAME y
               WHERE  y.id = c.id
                      AND ( y.fname_srch LIKE '%'
                            AND y.lname_srch LIKE '%' ))
       AND b.pid = c.pid
       AND c.id = d.id
       AND c.pid = e.pid
       AND e.sales_user_type = x.fieldvalue
       AND x.fieldname = 'SUSER_TYPE'
       AND EXISTS (SELECT 1
                   FROM   (SELECT DISTINCT p1.hierarchy_node
                           FROM   hierarchynode p1
                           WHERE  p1.hierarchy_name = 'GLOBE'
                                  AND connect_by_isleaf = 1
                           START WITH p1.hierarchy_node IN (SELECT territory_id
                                                            FROM   team
                                                            WHERE  pid = '558')
                           CONNECT BY PRIOR p1.hierarchy_node =
                                            p1.hierarchy_node_name
                           INTERSECT
                           SELECT DISTINCT p1.hierarchy_node
                           FROM   hierarchynode p1
                           WHERE  p1.hierarchy_name = 'GLOBE'
                                  AND connect_by_isleaf = 1
                           START WITH p1.hierarchy_node = '1093'
                           CONNECT BY PRIOR p1.hierarchy_node =
                                            p1.hierarchy_node_name)
                   WHERE  p1.territory_id); 


Re: Tuning a small but complicated query [message #537624 is a reply to message #537621] Thu, 29 December 2011 17:35 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Previous Topic: Bitmap Conversion
Next Topic: performace tunning
Goto Forum:
  


Current Time: Fri Sep 17 12:15:05 CDT 2021