CODEWITHSUNDEEP
oracle-databaseoracle10gquery-optimizationoracle11g
user472625
user472625

Reputation: 163

Performance issue with my query.Please suggest

MERGE INTO Analysis a
USING ( SELECT * FROM  Data ) b
ON ( a.User_Id = b.User_Id AND a.Upgrade_Flag = 0 )
WHEN MATCHED THEN
UPDATE SET Status = NVL(a.Status, 'ACTIVATE');

The above query works fine.But when millions of records are present this may lead to performance issue.Is there any alternative for the above query to get a better performace.Please suggest me on this.Thanks for your time

Upvotes: 0

Views: 85

Answers (3)

APC
APC

Reputation: 146349

It's really not at all obvious why you are using MERGE instead of a simple UPDATE.

update analysis a
set a.status = 'ACTIVATE'
where a.status is null
and a.upgrade_flag = 0 
and a.user_id in ( select b.user_id from  data b )
/

Selecting only the USER_ID from DATA instead of the entire record could speed things up, presuming DATA.USER_ID is indexed.

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48131

Without seeing the execution plans I can't predict whether an alternative would be any more efficient. But I note that you are not using the merge source in the update, which indicates that this could rewritten as a simple update statement:

UPDATE Analysis a
SET Status = NVL(a.Status, 'ACTIVATE')
WHERE a.Upgrade_Flag = 0
AND a.User_Id IN (
  SELECT b.User_Id FROM Data b
)

As is always the case with these things, you have a choice between using an IN clause, as I have shown, or an EXISTS clause with a correlated subquery. Usually it's worth testing both options when trying to tune performance, although in at least some cases the optimizer will try that transformation on its own.

Upvotes: 1

Juho
Juho

Reputation: 953

The query looks fine to me. Maybe you should create some indexes?

Create index for Analysis.User_Id and index for Data.User_Id if not yet created (primary key creates index automatically).

Or maybe also create index for Analysis containing both columns User_Id and Upgrade_Flag.

Upvotes: 0

Related Questions