OraFAQ Forum: Performance Tuning » how to tune this query?

Home » RDBMS Server » Performance Tuning » how to tune this query?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to tune this query? [message #76154]

Wed, 08 December 2004 23:07

Selva
Messages: 15
Registered: June 2001

Junior Member

hi all,

i want to use the optimized query,can anybody tune this query to increase the performance?

elect *
from tblsap ts
where
(
(ts.css_database,ts.sap) in
(select SUBSTR(source_account, 1, 2),SUBSTR(source_account, 3, 18)
from ds_account a where a.source_system = 1)
and ts.taig='ISY002'
and (sysdate-required_by) >= 180
)
union all
select *
from tblsap ts
where
(
(ts.css_database,ts.sap) not in
(select SUBSTR(source_account, 1, 2),SUBSTR(source_account, 3, 18)
from ds_account a where a.source_system = 1)
and
(sysdate-required_by) >= 180
);

Report message to a moderator

Re: how to tune this query? [message #76155 is a reply to message #76154]

Thu, 09 December 2004 04:59

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Does this query yield equivalent results?
----------------------------------------------------------------------
SELECT ts.*
FROM��tblsap��ts
,��ds_account��a
WHERE��a.source_system��= 1
AND��SUBSTR(a.source_account,1,2)��= ts.css_database (+)
AND��SUBSTR(a.source_account,3,18) =�ts.sap (+)
AND��'ISY002'��= ts.taig (+)
AND��a.required_by��<= SYSDATE -�180
/

Report message to a moderator

Re: how to tune this query? [message #76157 is a reply to message #76155]

Thu, 09 December 2004 19:58

Selva
Messages: 15
Registered: June 2001

Junior Member

Thanks for ur reply,
it is ok,but where is my 2nd select query
if this condition not present
[[ a.source_system = 1
AND SUBSTR(a.source_account,1,2) = ts.css_database (+)
AND SUBSTR(a.source_account,3,18) = ts.sap (+)]]
no need to check for
'ISY002'=ts.taig
in 1 st select if [[this mention int [[]] brackets]] condition present need to check the taig value..
but i need the both records from 2 select queries.
i think now you get my requirement...
can you please tune this query now?

Report message to a moderator

Re: how to tune this query? [message #364305 is a reply to message #76157]

Sun, 07 December 2008 22:45

dmerin
Messages: 30
Registered: December 2007

Member

Hi Friend

I know this is a late reply
I have tuned ure query as below .
Please check out and revert back whether its correct or not
I mean yielding the correct result as u expected

select *
from tblsap ts
where exists
       (((select 1 
        from ds_account a 
        where ts.css_database = SUBSTR(source_account, 1, 2)
        and   ts.sap          = SUBSTR(source_account, 3, 18)
        and   a.source_system = 1) and ts.taig='ISY002')
OR NOT EXISTS(select 1 
        from ds_account a 
        where ts.css_database = SUBSTR(source_account, 1, 2)
        and   ts.sap          = SUBSTR(source_account, 3, 18)
        and   a.source_system = 1)) 
AND     sysdate-required_by >= 180

Now to all
If anyone has any tuning docs please sent to me
I mean not the theory part
i wanna to know like using "IN instead of EXISTS CLAUSE"
Such type pf tuning queries

Thanks in advance

Report message to a moderator

Previous Topic:	Help with SQL Optimization (merged)
Next Topic:	USE_HASH

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 04:38:07 CDT 2024