the below select statement takes a long in running

Question

This select statement takes a long time running, after my investigation I found that the problem un subquery, stored procedure, please I appreciate your help.

SELECT DISTINCT
    COKE_CHQ_NUMBER,
    COKE_PAY_SUPPLIER
FROM
    apps.Q_COKE_AP_CHECKS_SIGN_STATUS_V
WHERE 
    plan_id = 40192
    AND COKE_SIGNATURE__A = 'YES'
    AND COKE_SIGNATURE__B = 'YES'
    AND COKE_AUDIT = 'YES'
    AND COKE_CHQ_NUMBER NOT IN (SELECT DISTINCT COKE_CHQ_NUMBER_DELIVER
                                FROM apps.Q_COKE_AP_CHECKS_DELIVERY_ST_V
                                WHERE UPPER(COKE_CHQ_NUMBER_DELIVER_STATUS) <> 'DELIVERED')
    AND COKE_CHQ_NUMBER NOT IN (SELECT COKE_CHQ_NUMBER_DELIVER
                                FROM apps.Q_COKE_AP_CHECKS_DELIVERY_ST_V)

That's a rather **bold** question, no pun intended. Can you add an execution plan, and maybe some data, to your question? — Tim Biegeleisen, Dec 30 '18 at 13:49
@mohamedbarakat . . . Presumably, the `_v` means that that referenced "tables" are really views. There is no way for an outside to help with the query without knowing the view definitions. I would add that I strongly recommend avoiding `not in` with subqueries and using `not exists` instead. — Gordon Linoff, Dec 30 '18 at 13:52
See [here](https://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database?answertab=votes#tab-top) so inspiration what information you should provide — Marmite Bomber, Dec 30 '18 at 15:05
@MarmiteBomber is right about the extra info which should be provided. — Barbaros Özhan, Dec 30 '18 at 15:12
Is `q_coke_ap_checks_delivery_st_v.coke_chq_number_deliver` defined as `NOT NULL`? Nullable columns don't tend to mix with `not in` subqueries. This is a wild guess though. Also the first subquery seems to be made redundant by the second one. — William Robertson, Dec 30 '18 at 17:10

score 0 · Answer 1 · answered Dec 30 '18 at 22:57

Well there are a few issues with your SELECT statement that you should address:

First let's look at this condition:

COKE_CHQ_NUMBER NOT IN (SELECT DISTINCT COKE_CHQ_NUMBER_DELIVER
                         FROM apps.Q_COKE_AP_CHECKS_DELIVERY_ST_V
                        WHERE UPPER(COKE_CHQ_NUMBER_DELIVER_STATUS) <> 'DELIVERED')

First you select DISTINCT cheque numbers with a not delivered status then you say you don't want this. Rather than saying I don't want non delivered it is much more readable to say I want delivered ones. However this is not really an issue but rather it would make your SELECT easier to read and understand.

Second let's look at your second cheque condition:

COKE_CHQ_NUMBER NOT IN (SELECT COKE_CHQ_NUMBER_DELIVER
                         FROM apps.Q_COKE_AP_CHECKS_DELIVERY_ST_V)

Here you want to exclude all cheques that have an entry in Q_COKE_AP_CHECKS_DELIVERY_ST_V. This makes your first DISTINCT condition redundant as whatever cheques numbers will bring back would be rejected by this second condition of yours. I do't know if Oracle SQL engine is clever enough to work out this redundancy but this could cause your slowness as SELECT distinct can take longer to run

In addition to this if you don't have them already I would recommend adding the following indexes:

CREATE INDEX index_1 ON q_coke_ap_checks_sign_status_v(coke_chq_number, coke_pay_supplier);
CREATE INDEX index_2 ON q_coke_ap_checks_sign_status_v(plan_id, coke_signature__a, coke_signature__b, coke_audit);
CREATE INDEX index_3 ON q_coke_ap_checks_delivery_st_v(coke_chq_number_deliver);

I called the index_1,2,3 for easy to read obviously not a good naming convention.

With this in place your select should be optimized to retrieve you your data in an acceptable performance. But of course it all depends on the size and the distribution of your data which is hard to control without performing specific data analysis.

score 0 · Answer 2 · answered Dec 31 '18 at 06:47

looking to you code .. seems you have redundant where condition the second NOT IN implies the firts so you could avoid

you could also transform you NOT IN clause in a MINUS clause .. join the same query with INNER join of you not in subquery

and last be careful you have proper composite index on table

Q_COKE_AP_CHECKS_SIGN_STATUS_V 
cols (plan_id,COKE_SIGNATURE__A , COKE_SIGNATURE__B, COKE_AUDIT, COKE_CHQ_NUMBER, COKE_PAY_SUPPLIER)


SELECT DISTINCT
    COKE_CHQ_NUMBER,
    COKE_PAY_SUPPLIER
FROM
    apps.Q_COKE_AP_CHECKS_SIGN_STATUS_V
WHERE 
    plan_id = 40192
    AND COKE_SIGNATURE__A = 'YES'
    AND COKE_SIGNATURE__B = 'YES'
    AND COKE_AUDIT = 'YES'

MINUS 


SELECT DISTINCT
    COKE_CHQ_NUMBER,
    COKE_PAY_SUPPLIER
FROM  apps.Q_COKE_AP_CHECKS_SIGN_STATUS_V
INNER JOIN (
    SELECT COKE_CHQ_NUMBER_DELIVER
    FROM apps.Q_COKE_AP_CHECKS_DELIVERY_ST_V
) T ON T.COKE_CHQ_NUMBER_DELIVER = apps.Q_COKE_AP_CHECKS_SIGN_STATUS_V
WHERE 
    plan_id = 40192
    AND COKE_SIGNATURE__A = 'YES'
    AND COKE_SIGNATURE__B = 'YES'
    AND COKE_AUDIT = 'YES'

the below select statement takes a long in running

2 Answers2