Home » RDBMS Server » Performance Tuning » How to tune this sql? (Oracle 10.0.2.3)
How to tune this sql? [message #437665] Tue, 05 January 2010 07:43 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member

  update table3 d set d.flag_status ='Y'
            where  
            (select count(*) 
                from table1 a
                where exists
                    (select   1
                        from table1 b
                        where b.ci_id = t.ci_id
                        and a.c_id=b.c_id))
                                        =( select count(*)  from table1 ci
                                                                 where exists 
                                                                    (select  1 
                                                                                from table1 a,table2 b
                                                                                where a.ci_id = b.ci1_id
                                                                                and a.c_id=ci.c_id
                                                                                and b.ci_id = t.ci_id                                                                                
                                                                                and b.status_cd = 'NA')); 


Is there another way to write this sql for better performance.
Thanks!
Re: How to tune this sql? [message #437673 is a reply to message #437665] Tue, 05 January 2010 08:08 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you sure that update is correct? The where clause does not reference the table being updated at any point. Plus I doubt table1 should be listed 4 times - it'd have been simpler if you if hadn't renamed the tables in the select. In fact why don't you just copy and paste the original select.
Also next time you post code can you format it properly please, indentation is important.
Re: How to tune this sql? [message #437674 is a reply to message #437673] Tue, 05 January 2010 08:16 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Yes the replacement is correct for table1
I want to update table3  and set flag=y where count(*) from some table = count(*) from some table 


  update table3 d set d.flag_status ='Y'
         where  
            (select count(*) 
                     from table1 a
                          where exists
                                    (select   1
                                         from table1 b
                                                where b.ci_id = d.ci_id
                                                and a.c_id=b.c_id))
                                                        =( select count(*)  from table1 ci
                                                                                where exists 
                                                                                         (select  1 
                                                                                           from table1 a,table2 b
                                                                                                where a.ci_id = b.ci1_id
                                                                                                and a.c_id=ci.c_id
                                                                                                and b.ci_id = d.ci_id                                                                                
                                                                                                and b.status_cd = 'NA')); 

[Updated on: Tue, 05 January 2010 08:32]

Report message to a moderator

Re: How to tune this sql? [message #437680 is a reply to message #437674] Tue, 05 January 2010 08:33 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
prachij593 wrote on Tue, 05 January 2010 14:16
Yes the replacement is correct for table1

Really?

In that case what is the point of this exists clause:
select count(*) 
from table1 a
where exists (select 1
              from table1 b
              where b.ci_id = d.ci_id
              and a.c_id=b.c_id)

You're joining a table to itself using the same columns. That'll always be true unless the table is empty.

And if the where clause really isn't supposed to reference the table being updated then I wouldn't do this as a single update. Write a seperate select to check the counts and if that gives the correct result do the update.
Re: How to tune this sql? [message #437681 is a reply to message #437680] Tue, 05 January 2010 08:48 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
select 1
              from table1 b
              where b.ci_id = d.ci_id

gives the c_id for the join of tables table1,table3;

and then getting the count for the c_id from table1 that are in the above resulset.


Re: How to tune this sql? [message #437688 is a reply to message #437665] Tue, 05 January 2010 08:57 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Ok, I misread that.

I really think you need to explain in english and in detail what this update is supposed to do. Preferably with a test case (create table and insert statements) so we can play with it.
Because with understanding what it's supposed to do we really can't suggest alternatives and this is a complicated update.
Re: How to tune this sql? [message #437690 is a reply to message #437674] Tue, 05 January 2010 09:14 Go to previous messageGo to next message
narsap
Messages: 8
Registered: December 2009
Junior Member
Prachi,

Few questions :
1. You said you want to tune this query but from the looks of it it appears to be kind-of one-time activity? Is it?

2) What is the relation between tables table1, table2 and table3?
From the sql, it appears that table3 has one-to-many relationship with table1 and table2. Is it the case? Also table2 appears to have two columns ci_id and ci1_id but both appear to contain the same value (which is same as the value of ci_id in table3 or table1). Is that the case? Also, what is the relationship between table1 and table2? Many-to-Many?
Re: How to tune this sql? [message #437692 is a reply to message #437665] Tue, 05 January 2010 09:31 Go to previous message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>Is there another way to write this sql for better performance.
Just curious, from where does alias "t" (as in t.ci_id) originate [depending upon which code morph you obfuscate]

It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

[Updated on: Tue, 05 January 2010 11:51]

Report message to a moderator

Previous Topic: Instance profiling
Next Topic: Re-structuring the SQL query
Goto Forum:
  


Current Time: Wed Sep 28 07:31:48 CDT 2016

Total time taken to generate the page: 0.07207 seconds