Help required in tuning the Sql for hierarchical query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Help required in tuning the Sql for hierarchical query

  1. #1
    Join Date
    Aug 2000
    Location
    California
    Posts
    3

    Help required in tuning the Sql for hierarchical query

    The below query using the order by is taking lot of time to execute,
    when the order by is ignored then the query execution is fast
    can any one help me to tune this query using the order by clause


    SELECT node_id ,
    (SELECT node_name
    FROM tree
    WHERE level = 1 -- This will fetch the account value
    START WITH node_id = sa.node_id
    CONNECT BY PRIOR parent_node_id = node_id) AS account,
    SELECT node_id ,
    (SELECT node_name
    FROM tree
    WHERE level = 2 -- This will fetch the company value
    START WITH node_id = sa.node_id
    CONNECT BY PRIOR parent_node_id = node_id) AS company,
    SELECT node_id ,
    (SELECT node_name
    FROM tree
    WHERE level = 3 -- This will fetch the state value
    START WITH node_id = sa.node_id
    CONNECT BY PRIOR parent_node_id = node_id) AS state,
    SELECT node_id ,
    (SELECT node_name
    FROM tree
    WHERE level = 4 -- This will fetch the region value
    START WITH node_id = sa.node_id
    CONNECT BY PRIOR parent_node_id = node_id) AS region,
    c.short_name AS id,
    c.name AS title, r.name AS studentid,
    r.last_name || ', ' || r.first_name AS studentname,
    r.email AS email, s.phone AS phone,
    DECODE (s.e_date,
    TO_DATE ('01-Jan-1900'), TO_DATE (''),
    s.edate_key
    ) AS edate,
    DECODE (s.ldate,
    TO_DATE ('01-Jan-1900'), TO_DATE (''),
    s.ldate
    ) AS ldate,
    rc.field_1 AS hiredate, rc.field_2 AS licensedate,
    rc.field_3 AS datepassed,
    DECODE (rc.custom_field_4,
    'UNKNOWN', '',
    rc.custom_field_4
    ) AS lnumber
    FROM admin sa,
    hierarchy ch,
    studentcourse s,
    studentregistration r,
    registration rc,
    course c
    WHERE sa.admin_key = 'UdonmortonSUB2533274790497513'
    AND ch.subdomain_key = sa.subdomain_key
    AND s.subdomain_key = ch.subdomain_key
    AND r.student_registration_key = s.student_registration_key
    AND rc.registration_custom_key = s.registration_custom_key
    AND c.course_key = s.course_key
    AND s.edate >= TO_DATE ('01-Jan-2003')
    AND s.edate <= TO_DATE ('30-Jan-2003')
    AND s.ldate >= TO_DATE ('01-Jan-2003')
    AND s.ldate <= TO_DATE ('30-Jan-2003')
    AND c.name LIKE '%'
    ORDER BY accountname, id, studentid -- this order by is taking lot of time to execute

    How can I tune the above query with order by clause since the order by clause is required

    Thanks in advance
    Honda


    [COLOR=orangered][COLOR=royalblue][COLOR=royalblue][COLOR=royalblue]

  2. #2
    Join Date
    Aug 2001
    Posts
    267
    That means sorting is slow . Sorting starts when Oracle gets all required rows into memory..

    1. bigger Sort_area_size ??
    2. or Pga_agegate_size ?? depending on version
    3. temp tablespace


    Can you post explain plan
    Raghu

  3. #3
    Join Date
    Oct 2002
    Posts
    182
    why even have this line?
    AND c.name LIKE '%'
    - Cookies

  4. #4
    Join Date
    Oct 2002
    Posts
    182
    does this do the same thing?
    PHP Code:
    select     node_id,
        
    max(decodelevel1node_nameNULL)) as account,
        
    max(decodelevel2node_nameNULL)) as company,
        
    max(decodelevel3node_nameNULL)) as state,
        
    max(decodelevel4node_nameNULL)) as region,
        
    idtitlestudentidstudentnameemailphoneedateldate,
        
    hiredatelicensedatedatepassedlnumber
    from 
    (SELECT 
                node_id
    ,
                
    t.level,
                
    t.node_name,
                
    c.short_name AS id
                
    c.name AS titler.name AS studentid
                
    r.last_name || ', ' || r.first_name AS studentname
                
    r.email AS emails.phone AS phone
                
    replace(s.e_date'01-Jan-1900''') AS edate
                
    replace(s.ldate'01-Jan-1900''') AS ldate
                
    rc.field_1 AS hiredaterc.field_2 AS licensedate
                
    rc.field_3 AS datepassed
                
    replace(rc.custom_field_4'UNKNOWN''') AS lnumber 
            FROM 
                admin sa
    hierarchy chstudentcourse s
                
    studentregistration rregistration rccourse c,
                
    tree t
            WHERE sa
    .admin_key 'UdonmortonSUB2533274790497513' 
                
    AND ch.subdomain_key sa.subdomain_key 
                
    AND s.subdomain_key ch.subdomain_key 
                
    AND r.student_registration_key s.student_registration_key 
                
    AND rc.registration_custom_key s.registration_custom_key 
                
    AND c.course_key s.course_key 
                
    AND s.edate >= TO_DATE('01-Jan-2003'
                AND 
    s.edate <= TO_DATE('30-Jan-2003'
                AND 
    s.ldate >= TO_DATE('01-Jan-2003'
                AND 
    s.ldate <= TO_DATE('30-Jan-2003'
                AND 
    sa.node_id t.node_id)
    group by node_ididtitlestudentidstudentnameemailphoneedateldate,
        
    hiredatelicensedatedatepassedlnumber
    order by accountname
    idstudentid 
    - Cookies

  5. #5
    Join Date
    Aug 2000
    Location
    California
    Posts
    3
    No the above query will not serve the purpose, since for the given child existing at the district level i need to fetch those parents from the hierarchy table hence i need to have the hhierarchy connect by clause

    and the explain plan is as below
    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    SELECT STATEMENT Optimizer Mode=CHOOSE 1 35
    SORT ORDER BY 1 424 35
    NESTED LOOPS 1 424 33
    NESTED LOOPS 1 293 31
    NESTED LOOPS 1 238 29
    NESTED LOOPS 1 165 27
    HASH JOIN 4 304 15
    TABLE ACCESS BY INDEX ROWID ADMIN 4 168 2
    INDEX RANGE SCAN ADMIN_KEY 4 1
    TABLE ACCESS FULL HIERARCHY 18 K 625 K 12
    TABLE ACCESS BY INDEX ROWID STUDENTCOURSE 5 K 506 K 3
    INDEX RANGE SCAN SC_SUB_ENROLL_CRS_PCT_X_A 5 K 2
    TABLE ACCESS BY INDEX ROWID COURSE_0_A 278 K 19 M 2
    INDEX UNIQUE SCAN XPKCOURSE_0_A 278 K 1
    TABLE ACCESS BY INDEX ROWID REGISTRATION 551 K 28 M 2
    INDEX UNIQUE SCAN XPKREGISTRATION 551 K 1
    TABLE ACCESS BY INDEX ROWID STUDENTREGISTRATION 1 M 132 M 2
    INDEX UNIQUE SCAN XPKSTUDENTREGISTRATION 1 M 1
    TheD c.name LIKE '%' is required to fetch all the courses based on the filter provided in the paramter, for ex if i give a specific course then c.name ='course_name 'else if in the SQL i want all tha courses then the sql to get all the courses should include and c.name LIKE '%'


    Regds,
    honda

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width