-
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]
-
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
-
why even have this line?
AND c.name LIKE '%'
- Cookies
-
does this do the same thing?
PHP Code:
select node_id,
max(decode( level, 1, node_name, NULL)) as account,
max(decode( level, 2, node_name, NULL)) as company,
max(decode( level, 3, node_name, NULL)) as state,
max(decode( level, 4, node_name, NULL)) as region,
id, title, studentid, studentname, email, phone, edate, ldate,
hiredate, licensedate, datepassed, lnumber
from (SELECT
node_id,
t.level,
t.node_name,
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,
replace(s.e_date, '01-Jan-1900', '') AS edate,
replace(s.ldate, '01-Jan-1900', '') AS ldate,
rc.field_1 AS hiredate, rc.field_2 AS licensedate,
rc.field_3 AS datepassed,
replace(rc.custom_field_4, 'UNKNOWN', '') AS lnumber
FROM
admin sa, hierarchy ch, studentcourse s,
studentregistration r, registration rc, course 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_id, id, title, studentid, studentname, email, phone, edate, ldate,
hiredate, licensedate, datepassed, lnumber
order by accountname, id, studentid
- Cookies
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|