| SQL Query behaving weirdly in 10g [message #309771] |
Fri, 28 March 2008 14:06  |
hrsiddareddy
Messages: 1
Registered: March 2008
|
Junior Member |
|
|
We just upgraded our database server from 9i to 10g and we have a extract that runs every day night. Previously a SQL query in 9i was taking around 16 mins. but in 10g it is taking more than 5 hrs.
The structure of the query is some thing like below
Select * from
( select
DECODE(q2,22,2,DECODE(we,ee,ee,ee)......) d_a
, func_call(23,23) f_a
, func_call(34,45) f_b
, name
, add
FROM (
SELECT name
,add
,sex
from TABLE_A,TABLE_B
)where f_a + f_b <=24
)where func_call_new(f_a,f_b,d_a) < 100;
func_call_new --> a function in the same package
func_call --> a function in the same package
It looks similar to this but more select DECODES and function calls are there in the query.
TABLE_A usually has 3000 records MAX
TABLE_B has 900,000 records MAX
For this what i did was removed the inner bold select statement and insert the values into a temp_table and the accessed those values form the table.
The fix actually solved the issue and now it is running in 10 mins.
But still i feel there is something i am missing in the solution.
Can anybody suggest me what might be the issue.
The plan look same in 9i and 10g.
Thanks for the time
Harish
|
|
|
|
|
|
| Re: SQL Query behaving weirdly in 10g [message #310136 is a reply to message #309771] |
Mon, 31 March 2008 06:59  |
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
|
Senior Member |
|
|
DEAR ,
THE PROBLEM IS WITH THE WHERE CALUSE ,
IT SEEMS THAT YOU ARE DOING TOO MANY CALCULATION AT THE LEFT OF EQUAL SIGN , WHICH IS ALWAYS VERY SLOW FOR THIS FUNCTION WILL BE APPLIED AT EACH AND EVERY ROW.
SECONDLY , MAY BE PREVIOUSLY U HAD ANY INDEX ON THE COLUMNS IN WHERE CLAUSE , BUT NOW AS U ARE APPLYING FUNCTION , SO THAT INDEX WUD BE AUTOMATICALLY DIS BALED.
PROBABLY THATS WHY your QUERY HAS BECOME REALLY SLOW.
[Updated on: Mon, 31 March 2008 07:00] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
