Query Performance Help

Question

A brief history. I have an oracle sql query which I'm looking to pull data when a certain preference is enabled. In this query I had a date range to filter the data to a specific range. This was changed due to security of the service account having access to specific tables. My DBA suggested a view. This query is for this view, but currently takes several minutes to populate. Any suggestions on how to optimize this query?

SELECT
(SELECT o.SALESFORCE_ID from org_unit o WHERE o.ORG_UNIT_ID = a.vendor_id AND o.delete_flag = '0') as SenderId,
(SELECT o.SALESFORCE_ID from org_unit o WHERE o.ORG_UNIT_ID = a.CUSTOMER_ID AND o.delete_flag = '0') as ReceiverId,
a.invoice_id,
a.INVOICE_DATETIME
FROM invoice a
WHERE a.invoice_id IN (
    SELECT document_id 
    FROM oi_action oi 
    WHERE oi.action = 'Submit'
    and oi.status='Submitted'
    )
and a.sub_type not in ( 10008,10007)
and EXISTS (
    SELECT 1
    FROM org_preference op 
    WHERE op.ORG_UNIT_ID = a.customer_id 
    AND op.PREFERENCE_TYPE_ID = 60276
    AND op.preference_value = 1
    AND op.modified_by_id IS NULL
    )
AND EXISTS (
    SELECT 1
    FROM org_unit o 
    WHERE o.ORG_UNIT_ID = a.vendor_id 
    AND o.delete_flag = '0'
    AND o.salesforce_id IS NOT NULL
)

GROUP BY a.vendor_id, a.customer_id, a.invoice_id, a.invoice_datetime;

If there is a way to only display the view when the preference is turned on example:

AND a.CREATED_DATETIME >= (SELECT o.CREATED_DATETIME
FROM org_unit o 
WHERE o.ORG_UNIT_ID = a.vendor_id 
AND o.delete_flag = '0'
AND o.salesforce_id IS NOT NULL
)

Any thoughts suggestions on the best route I can take?

Here is the Explain plan:

UPDATE:

Here is my new query which displays MUCH faster, within a second:

SELECT
sender.salesforce_id as SenderId,
buyer.salesforce_id as ReceiverId,
a.invoice_id,
a.INVOICE_DATETIME
FROM invoice a
INNER JOIN org_unit sender on sender.org_unit_id = a.vendor_id
INNER JOIN org_unit buyer on buyer.org_unit_id = a.CUSTOMER_ID
WHERE EXISTS (
    SELECT 1
    FROM oi_action oi
    WHERE oi.document_id = a.invoice_id 
    AND oi.action = 'Submit'
    AND oi.status='Submitted'
    )
and a.sub_type not in ( 10008,10007)
and EXISTS (
    SELECT 1
    FROM org_preference op 
    WHERE op.ORG_UNIT_ID = a.customer_id 
    AND op.PREFERENCE_TYPE_ID = 60276
    AND op.preference_value = 1
    AND op.modified_by_id IS NULL
    )
AND EXISTS (
    SELECT 1
    FROM org_unit o 
    WHERE o.ORG_UNIT_ID = a.vendor_id 
    AND o.delete_flag = '0'
    AND o.salesforce_id IS NOT NULL
);

On the explain plan the only difference is in the Nested Loops from 20944 to 15365.

If this looks okay I think this has solved my problem

Answer 1

@ehubba, welcome to stackexchange!

Here are some considerations:

• Is there a reason for the GROUP BY?

• Why do you use one IN and two EXISTS? Generally you can use IN for smaller and EXISTS for larger results. IN will result in lots of ORs which is not the best idea in large numbers.

• I don't like the two sub-selects at the beginning of the statement. Can you replace them with joins?

If you want to improve performance, there are a lot more things to consider...

• Are your statistics up-to-date?

• constraints help the optimizer to find the best join order

• indexes? (please post the explain plan!)

When I write SQL statements I always ask myself "how can I minimize the resultset as fast and efficient as possible".