Home » RDBMS Server » Performance Tuning » how to tune this query?
how to tune this query? [message #76154] Wed, 08 December 2004 23:07 Go to next message
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
);
Re: how to tune this query? [message #76155 is a reply to message #76154] Thu, 09 December 2004 04:59 Go to previous messageGo to next message
Art Metzer
Messages: 2478
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
/
Re: how to tune this query? [message #76157 is a reply to message #76155] Thu, 09 December 2004 19:58 Go to previous messageGo to next message
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?
Re: how to tune this query? [message #364305 is a reply to message #76157] Sun, 07 December 2008 22:45 Go to previous message
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
Previous Topic: Help with SQL Optimization (merged)
Next Topic: USE_HASH
Goto Forum:
  


Current Time: Sun Dec 11 04:24:44 CST 2016

Total time taken to generate the page: 0.22128 seconds