Performabce tuning in SQL [message #419348] |
Fri, 21 August 2009 06:58  |
sundarfaq
Messages: 235 Registered: October 2007 Location: Chennai
|
Senior Member |
|
|
Hi,
i have executed below SQL statements.it takes more times to execute
SELECT COUNT (1)
FROM rt_rate_udf r1, rt_rate_udf r2
WHERE r1.rate_id = 132733
AND r2.rate_id = 132733
AND r1.udf_id = r2.udf_id
[b] AND NVL (r1.udf_value_str, '***') = NVL(r2.udf_value_str, '***')[/b]
AND NVL (r1.udf_value_int, -0.1) = NVL (r2.udf_value_int, -0.1)
AND NVL (r1.udf_value_dbl, -0.1) = NVL (r2.udf_value_dbl, -0.1)
AND NVL (r1.udf_value_dt, TO_DATE ('01/01/1000', 'DD/MM/YYYY')) =
NVL (r2.udf_value_dt, TO_DATE ('01/01/1000', 'DD/MM/YYYY'))
AND NVL (r1.inclusive_flag, 'Y') = 'Y'
AND NVL (r2.inclusive_flag, 'Y') = 'Y'
AND r1.udf_id = 'BILLTO'
I have removed udf_value_str column in where conditions from above SQL statements.After that it takes less time to execute.the code listed below
SELECT COUNT (1)
FROM rt_rate_udf r1, rt_rate_udf r2
WHERE r1.rate_id = 132733
AND r2.rate_id = 132733
AND r1.udf_id = r2.udf_id
AND NVL (r1.udf_value_int, -0.1) = NVL (r2.udf_value_int, -0.1)
AND NVL (r1.udf_value_dbl, -0.1) = NVL (r2.udf_value_dbl, -0.1)
AND NVL (r1.udf_value_dt, TO_DATE ('01/01/1000', 'DD/MM/YYYY')) =
NVL (r2.udf_value_dt, TO_DATE ('01/01/1000', 'DD/MM/YYYY'))
AND NVL (r1.inclusive_flag, 'Y') = 'Y'
AND NVL (r2.inclusive_flag, 'Y') = 'Y'
AND r1.udf_id = 'BILLTO'
I have enclosed RT_RATE_UDF table desc file with it.
the RT_RATE_UDF table has million of records.
Please give an idea how to resolve it.
I have tried hundred of times but i am unable to correct this sql statements.
[Updated on: Fri, 21 August 2009 09:14] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Re: Performabce tuning in SQL [message #419364 is a reply to message #419348] |
Fri, 21 August 2009 09:23   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Unless you have an index on nvl(udf_value_string, '***'), the only reason I can think of why removing such a where-clause would result in a faster result, would be the executing your first query did the Disk-IO necessary for the second one.
|
|
|
|
|
|
|