Oracle - Where clause parameter change query speed

Question

Using a query with a table and a View, however if i change the where column the speed drastically changes. What can be the problem? its quite hard to google "performance changes on where clause" since all exemples are not even close to this.

SELECT ADRE.*
FROM INVOICE N
INNER JOIN ENTRY_INVOICE NAF
     ON NAF.COMPANY = N.COMPANY
     AND NAF.ID_INVOICE = N.ID_INVOICE
INNER JOIN UV_ENTRY_ALL ADRE
    ON ADRE.COMPANY = NAF.COMPANY
    AND ADRE.ID_ENTRY = NAF.ID_ENTRY
WHERE
    NAF.COMPANY = 1
    /*
    AND NAF.ID_INVOICE = 113806
        => 40 SECONDS
        
    AND  NAF.ID_ENTRY = 387473
        => 0,6 SECONDS
        
    AND EXISTS (SELECT 1 FROM ENTRY_INVOICE WHERE COMPANY = 1 AND ID_INVOICE=113806)
        => 1,6 SECONDS
    */

ENTRY_INVOICE IT HAS AT MOST 2 RECORDS FOR EACH INVOICE.

COMPANY	ID_INVOICE	ID_ENTRY
1	113706	387224
1	113706	387225
1	113707	387226
1	113806	387473

PLAN EXPLANATION https://exists-stack.tiiny.site/

As questioned, even if remove the table INVOICE, and select especific columns, it wont change the speed.

For this exemple i took all columns, but i'll get values from it. but even if i remove speed wont change. the problem is with the view, but i dont know why it changes so much because of the where clause. — Hommee, Feb 11 '22 at 14:13
Well... that's makes a difference for te optimizer. I'll assume you are retrieving `N.*` too. — The Impaler, Feb 11 '22 at 14:14
Please learn how to post the execution plan in [text](https://stackoverflow.com/a/34975420/4808122) form also with the *Predicate Information*. Also *how many rows* your query returns? Oracle estimates between 20K-50K, is it correct and do you count the elapsed time until you get the *first page*? — Marmite Bomber, Feb 11 '22 at 15:04
Thx for the feedback, will do next time. already found a solution for my problem. — Hommee, Feb 11 '22 at 15:38
Obviously, queries that join and return different numbers of rows according to different selection criteria leading to different execution plans will have different performance characteristics, so I don't understand the question. Why would you be surprised that changing the WHERE clause affects performance? Also, is INVOICE_ENTRY the same as ENTRY_INVOICE? — William Robertson, Feb 12 '22 at 22:54
1st of all sorry invoice_entry was a typo. However thats the reason i found so hard to even search the matter.On the where clause the only difference is the column. `ENTRY_INVOICE.INVOICE` X `ENTRY_INVOICE.ID_ENTRY` the tables are indexed. both would return the same amount of rows. UV_ENTRY_ALL is a view,however i dont see how it would hurt the performance since runing with the parameters directly it takes less than 0.5 sec. and its quite hard to post the view here since it would need a really long to mask the data.Adding id_invoice to the view made it worse standalone, but faster on join. — Hommee, Feb 14 '22 at 12:13

score 0 · Answer 1 · answered Feb 11 '22 at 14:20

I would change the filtering criteria to WHERE N.COMPANY = 1 to nudge the optimizer to use that table as the driving table. The query could look like:

SELECT ADRE.*, N.*
FROM INVOICE N
INNER JOIN ENTRY_INVOICE NAF
     ON NAF.COMPANY = N.COMPANY
     AND NAF.ID_INVOICE = N.ID_INVOICE
INNER JOIN UV_ENTRY_ALL ADRE
    ON ADRE.COMPANY = NAF.COMPANY
    AND ADRE.ID_ENTRY = NAF.ID_ENTRY
WHERE
    N.COMPANY = 1 -- changed here

Once that is changed, the query could benefit from the following indexes:

create index ix1 on invoice (company);

create index ix2 on entry_invoice (company, id_invoice);

create index ix3 on uv_entry_all (company, id_entry);

Keep in mind that any extra search criteria may impact performance. In particular, I ignored the predicates in comments at the end of the query.

All tables have indexes, and uv_entry_all was a view. however i made a new view for this exact scenario that looses a bit performance standalone for better join. — Hommee, Feb 11 '22 at 15:10
@Hommee For performance tweaking it's critical to understand the view definition. Please include it in the question. — The Impaler, Feb 11 '22 at 15:55

score 0 · Answer 2 · answered Feb 11 '22 at 14:23

I would rewrite the query based on the primary table in question you are trying to get your data from based on the criteria. Make sure to have an index that will best help in those situations. THEN Join to the other tables. Also, if dealing with the condition of a specific invoice, I would just add that to the JOIN clause as well. Hopefully the engine will get best match for criteria and run with that. In this case, your Entry Invoice table would have TWO indexes... one based on the company + invoice, the other based on company + id entry.

As for your invoice table, that is not even being used within your output and would otherwise just be like that of a lookup table, so I added that as a second join

Table           Index
Entry_Invoice   ( Company, ID_Invoice )
Entry_Invoice   ( Company, ID_Entry )
Invoice         ( Company, ID_Invoice )
UV_Entry_All    ( Company, ID_Entry )

SELECT 
        ADRE.*
    FROM 
        ENTRY_INVOICE NAF
            JOIN UV_ENTRY_ALL ADRE
                ON NAF.COMPANY = ADRE.COMPANY
                AND NAF.ID_ENTRY = ADRE.ID_ENTRY
            -- you dont actually NEED the invoice table here
            INVOICE N
                ON NAF.COMPANY = N.COMPANY
                AND NAF.ID_INVOICE = N.ID_INVOICE
    WHERE
            NAF.COMPANY = 1
        AND
        /*
            NAF.ID_INVOICE = 113806
                 vs
            NAF.ID_ENTRY = 387473
        
            -- dont think this would even be necessary any more
            EXISTS (SELECT 1 FROM ENTRY_INVOICE WHERE COMPANY = 1 AND ID_INVOICE=113806)
        */

score 0 · Answer 3 · answered Feb 11 '22 at 15:07

UV_ENTRY_ALL Was a View that returned all payments from invoice however i tought removing some tables would make it perform better, and it does, but i need to filter the exact entry.
By adding back on each Union (union all) ENTRY_INVOICE and returning "ID_INVOICE" i added it on the Join and now works like a charm.
on the other hand i lost Performance while i run UV_ENTRY_ALL standalone

SELECT ADRE.*
FROM INVOICE N
INNER JOIN ENTRY_INVOICE NAF
    ON NAF.COMPANY = N.COMPANY
    AND NAF.ID_INVOICE = N.ID_INVOICE
INNER JOIN UV_ENTRY_ALL ADRE
   ON ADRE.COMPANY = NAF.COMPANY
   AND ADRE.ID_ENTRY = NAF.ID_ENTRY
   AND ADRE.ID_INVOICE = NAF.ID_INVOICE
WHERE
   NAF.COMPANY = 1 AND NAF.ID_INVOICE = 113806

Oracle - Where clause parameter change query speed

3 Answers3