Home » RDBMS Server » Performance Tuning » How i can increase the performance of my query
| How i can increase the performance of my query [message #380594] |
Mon, 12 January 2009 08:19  |
eng.oracle
Messages: 48
Registered: December 2007
|
Member |
|
|
I have a table have 25,000 record or more as Statistics
and I'm using this code to get Statistics every month
for each employee:
SELECT COUNT (ch)+ NVL(COUNT(chn),0) ch,
COUNT (wn) wn,
COUNT (sn) sn,
COUNT (dn) - NVL(COUNT(chn),0) dn,
COUNT(gi) gis,
COUNT(st) stu
INTO vch, vw, vs, vdn,vgi,vbr
FROM (
SELECT ky,DECODE(a.rc,3,3,5,5,10,10,13,13) ch,
DECODE(a.rc,2,2) wn,
DECODE(a.rc,5,5,17,17) sn,
DECODE(a.rc,4,4,9,9,10,10,11,11,12,12,14,14,15,15,16,16) dn,
DECODE(a.rc,10,10) gi,
DECODE(a.rc,17,17) st,
DECODE(a.rc,9,DECODE(A.rt,2,9,9)) chn
FROM rdt a
WHERE a.ky =v_ky
AND trunc(a.str_dt,'MM') <= p_month
);
where v_ky & p_month is variable;
but it is very slow, how i can increase the performance by using
view or others to get the same result, with less time.
thx
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How i can increase the performance of my query [message #380613 is a reply to message #380606] |
Mon, 12 January 2009 13:16  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| Alessandro Rossi wrote on Mon, 12 January 2009 17:39 | could let you use an index on str_dt.
|
Yes, but the question is: does the queried table RDT (if it is not a view) have any indexes? Also according to | eng.oracle wrote on Mon, 12 January 2009 15:19 | and I'm using this code to get Statistics every month for each employee
|
,it would be more important to index "employee" column (whatever it is) as the first one.
Also knowing the number of distinct "employees" would be useful to know (as thousand times nothing tormented a donkey to death, as one proverb says). You also did not post data types of all columns and variables - an implicit conversion may degrade performance too.
Of course, the exact figures about processing may be obtained from tracing the session (see http://www.orafaq.com/wiki/SQL_Trace) and using tkprof to analyze its output - we could save a lot of guesses here.
|
|
|
|
|
|
|
|
| Re: How i can increase the performance of my query [message #380867 is a reply to message #380613] |
Tue, 13 January 2009 11:32 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| flyboy wrote on Mon, 12 January 2009 20:16 | Also knowing the number of distinct "employees" would be useful to know ... You also did not post data types of all columns and variables - an implicit conversion may degrade performance too.
Of course, the exact figures about processing may be obtained from tracing the session (see http://www.orafaq.com/wiki/SQL_Trace) and using tkprof to analyze its output - we could save a lot of guesses here.
|
| eng.oracle wrote on Tue, 13 January 2009 14:21 | indx1-------- ky
indx2---------str_dt
indx3----------(ky,str_dt)
any one have any note about this indexs
|
Only INDX1 may be used by the query you posted, as Alessandro Rossi already remarked. However if the column KY and variable V_KY have different data types, Oracle will not be able to use it, so full table scan would be performed.
|
|
|
|
| Re: How i can increase the performance of my query [message #380887 is a reply to message #380594] |
Tue, 13 January 2009 16:51 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
"and I'm using this code to get Statistics every month
for each employee:"
Then you dont need fast refresh mview for this. A standard mview would do for a monthly job.
"How i can used Materialized View with varablie like p_month & v_ky, please give me example "
Have you tried it? and read the manual on query rewrite? Doesnt sound like it.
Also read up on advanced query rewrite, althugh you should not need that for this example.
[Updated on: Tue, 13 January 2009 16:53] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 01:30:37 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
