using not exists and subquery in SQL

Question

I have to write an sql query in oracle 11g for the following scenario.

I have a table customers. It has three columns customer_id,cust_cat and cust_deterioration_date. customer_id is the primary key.

customers
(customer_id varchar2(20),
 cust_cat varchar2(1),
 cust_deterioration_date date
);

I have another table Accounts. It has 4 columns loan_account_number,product_code,account_deterioration_date,customer_id. loan_account_number is the primary key.

Accounts
(loan_account_number varchar2(20),
 product_code varchar2(4),
 account_deterioration_date date,
 customer_id  varchar2(20)
 )

I have to select the customers who has loan accounts having product code as 9213 or 9450 in the accounts table and whose cust_cat is 'G'.

If such a customer has multiple loan accounts with product code 9213 or 9450,and Only if the account_deterioration_date for all those loan accounts are null,I should update cust_deterioration_date in customers table as null.

Here is my query to select the reuired customers.

SELECT UNIQUE s1.customer_no
FROM   accounts  a1
      ,customers s1
WHERE  a1.customer_id = s1.customer_no
AND    NVL(s1.cust_cat,
           'G') = 'G'
AND    a1.product_code IN ('9213',
                           '9450')
AND    a1.account_deterioration_date IS NULL
AND    NOT EXISTS (SELECT 1
        FROM   accounts a
        WHERE  a.customer_id = s1.customer_no
        AND    a.account_deterioration_date IS NOT NULL
        AND    a.product_code IN ('9213',
                                  '9450'))

This query is fetching the required result but at the cost of performance. Is there any better way to achieve this functionality?

Thanks in advance

You shouldn't use comma-separated joins. They were replaced by explicit joins (from accounts join customers on ...) a long time ago. As to performance: an index on account(product_code, customer_id) and a function index on customers( nvl(cust_cat,'G') ) might help. — Thorsten Kettner
– Thorsten Kettner, Commented Aug 27, 2015 at 13:44

Thorsten Kettner · Accepted Answer · 2015-08-27 13:38:34Z

2

You can aggregate your 9213/9450 account records per customer and see whether there are multiple entries (count(*) > 1) and no non-null account_deterioration_dates (count(account_deterioration_date) = 0). With the customer IDs thus found, you can access the customers table.

update customers
set cust_deterioration_date = null
where nvl(cust_cat,'G') = 'G'
and customer_id in
(
  select customer_id
  from accounts
  where product_code in ('9213', '9450')
  group by customer_id
  having count(*) > 1
  and count(account_deterioration_date) = 0
);

answered Aug 27, 2015 at 13:38

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Aparna Rajan Over a year ago

Thanks,That was really helpgul. But when I examined the plan for the query which I posted in the quetsionthe one which you suggested,I found that the cost was more for your query.And now as I am asked to enter the customer selected by this query into a new table instead of the customers one,I will have to go for a join condition anyhow this query which u suggested too select customer_id from accounts join customers where product_code in ('9213', '9450') and customer.cust_cat='G' group by customer_id having count(*) > 1 and count(account_deterioration_date) = 0

Thorsten Kettner Over a year ago

Um, the update statement should be fast, as it scans the accounts table once and then accesses the customers table by ID. Did you put your query inside an update statement and compared this to my statement or did you simply compare your query with my update? The mere query will of course cost less than the complete update statement.

Thorsten Kettner Over a year ago

As to getting my update statement faster: You can speed up the accounts query by either running it parallel (e.g. select /*+full(accounts) parallel(accounts,8)*/ customer_id from ...) or by creating an appropriate index (on accounts(product_code, customer_id) or even accounts(product_code, customer_id, account_deterioration_date)). The customers access might gain speed with an additional index on customers(customer_id, nvl(cust_cat,'G')). You can also add and cust_deterioration_date is not null at the end of my statement in order not to update records with null that are null already.

Aparna Rajan Over a year ago

Sorry,I had done some mistake when i checked the plan for both queries. Now I checked it again and yours is more efficient. In the query u have mentioned as having count() > 1. Shouldn't it be having count() > 0.

Aparna Rajan Over a year ago

Also now my team lead has asked me to do a bulk collect with limit clause as there will be millions of records fetched by the subquery. And the he suggested I use a forall statement to do the update. Do you think this is a better approach considering the performance?

|

Collectives™ on Stack Overflow

using not exists and subquery in SQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related