Regd: Tuning required [message #319629] |
Mon, 12 May 2008 06:54 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
VEDDETA
Messages: 54 Registered: May 2008
|
Member |
|
|
I have created the below query to get the count based on certain conditions.
But is the below query is usefull ( from performance perspective) while there are millions of records.
Is there any convenient way to optimize this query?
SELECT
COUNT(CASE WHEN( A.TMS1> SYSTIMESTAMP - INTERVAL '30' MINUTE) THEN 1 END),
COUNT(CASE WHEN A.LAST_UPD_TMS> SYSTIMESTAMP - INTERVAL '2' hour AND A.TMS1<= SYSTIMESTAMP - INTERVAL '1' hour THEN 1 END),
COUNT(CASE WHEN A.TMS1>= SYSTIMESTAMP - INTERVAL '24' hour AND A.TMS1<= SYSTIMESTAMP - INTERVAL '2' hour THEN 1 END),
COUNT(CASE WHEN ( A.TMS1< SYSTIMESTAMP - INTERVAL '1' DAY) THEN 1 END),
COUNT(*) AS CNT
FROM MQ A,CODE B
WHERE SUBSTR(A.PROCESS_NAME,1,4) = B.INDICATOR_TXT
--// Pass application name
AND B.IND_TXT = p_application_nm
AND A.LOCATION_ID = p_loc_id;
[Updated on: Mon, 12 May 2008 06:58] by Moderator Report message to a moderator
|
|
|
|
|
Re: Regd: Tuning required [message #319661 is a reply to message #319629] |
Mon, 12 May 2008 09:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
VEDDETA
Messages: 54 Registered: May 2008
|
Member |
|
|
Thanks for the quick responses.
Process_name and Indicator_txt are the two indexes that has been created on the tables MO & Code respectively.
Right now I am trying with a less amount of data. But if there are millions of rowns then do I need to optimize the query?
In addition to the condition in where clause, does not the comparison in case statement will imapact on performance?
Regards,
Veddeta
|
|
|
|
Re: Regd: Tuning required [message #319666 is a reply to message #319664] |
Mon, 12 May 2008 09:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
VEDDETA
Messages: 54 Registered: May 2008
|
Member |
|
|
Michel Cadot wrote on Mon, 12 May 2008 09:25 | Quote: | does not the comparison in case statement will imapact on performance?
|
Do you need these results or not?
If yes, then you have to put them.
If no, then remove them.
There is nothing you can do.
Regards
Michel
|
Thanks Michel, for making me understand. I need the count. Ok, that means there is no other option to modify in case comparison statement, right?
Can we modify the substring funtion in where clause?
|
|
|
|
|
Re: Regd: Tuning required [message #319797 is a reply to message #319669] |
Tue, 13 May 2008 01:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
VEDDETA
Messages: 54 Registered: May 2008
|
Member |
|
|
JRowbottom wrote on Mon, 12 May 2008 09:44 | Can you give us an idea of the number of rows in the two tables that will be looked at by this query?
|
Thanks for giving your valuable time..
Right now, Its not possible for me to give u adjactly how many rows are there....
But yah, the MQ table is going to have millions of records...
Thanks,
Veddeta
|
|
|
Re: Regd: Tuning required [message #319803 is a reply to message #319680] |
Tue, 13 May 2008 01:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
VEDDETA
Messages: 54 Registered: May 2008
|
Member |
|
|
Michel Cadot wrote on Mon, 12 May 2008 10:13 | Quote: | Can we modify the substring funtion in where clause?
|
I don't know if your join condition is based upon the first 4 characters you either have to use:
SUBSTR(A.PROCESS_NAME,1,4) = B.INDICATOR_TXT
A.PROCESS_NAME LIKE B.INDICATOR_TXT || '%'
(assuming B.INDICATOR_TXT is a 4 characters string)
The index on A.PROCESS_NAME is useless in this case.
Regards
Michel
|
Thanks Michel ..
The join condition is based upon the first 4 characters...
Michel, can I get some document link where I can study more about Oracle SQL Tuning?
If I am not wrong, when we use Like operator with '%' Full table scan is not done..right? I want to know more abount
tuning..where can I get that?
|
|
|
|
|
Re: Regd: Tuning required [message #319861 is a reply to message #319856] |
Tue, 13 May 2008 04:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote: | On which conditions ( factor) it depends?
|
Indexes, number of rows, values distribution and many other statistics like system ones without forgetting some configuration parameters in init.ora/spfile.
Regards
Michel
|
|
|