Select statement performance in Oracle

Question

I have this query:

  SELECT a.rextnext,
         a.rextdata,
         a.rextnome,
         a.rextdomi,
         a.rextcdpo,
         a.rextcdps,
         a.rextdscp,
         a.rextnpol,
         a.rextvliq,
         a.rextcdce,
         b.tpobdspo,
         a.rextcoen,
         a.rextanan,
         a.rextibnr,
         a.rextinde,
         a.rextdaan,
         a.rextindp,
         a.rextviva,
         a.rextvirs,
         a.rexttirs,
         a.rexttien,
         a.rextpenh,
         DECODE (a.rextpenh,
                 'S', (SELECT clienif
                         FROM dtclie
                        WHERE cliecdcl = a.rextcoen),
                 NULL)
            rextnif,
         a.rextiban
    FROM MTREXT as a, MTTPOB as b, dtpoce as c
   WHERE     a.rextesta = '01'
         AND b.tpobcdpo = a.rextcdpo
         AND a.rextcdde = 100
         AND a.rextosin = 'R'
         AND a.rextdaic <= TO_CHAR (SYSDATE, 'YYYYMMDD')
         AND NVL (a.rextnif, 'A') = NVL (a.rextnif, 'A')
         AND a.rextcdde = c.pocecdde(+)
         AND a.rextnpol = c.pocenpol(+)
         AND a.rextcdce = c.pocecdce(+)
ORDER BY a.rextcdde, a.rextnpol, TO_NUMBER (c.poceccus)

... with this explain plan:

----------------------------------------------------                            
| Id  | Operation                      | Name      |                            
----------------------------------------------------                            
|   0 | SELECT STATEMENT               |           |                            
|   1 |  TABLE ACCESS BY INDEX ROWID   | DTCLIE    |                            
|   2 |   INDEX UNIQUE SCAN            | PK_DTCLIE |                            
|   3 |  SORT ORDER BY                 |           |                            
|   4 |   NESTED LOOPS OUTER           |           |                            
|   5 |    HASH JOIN                   |           |                            
|   6 |     TABLE ACCESS BY INDEX ROWID| MTREXT    |                            
|   7 |      INDEX RANGE SCAN          | MTREXT99  |                            
|   8 |     TABLE ACCESS FULL          | MTTPOB    |                            
|   9 |    TABLE ACCESS BY INDEX ROWID | DTPOCE    |                            
|  10 |     INDEX UNIQUE SCAN          | PK_DTPOCE |                            
----------------------------------------------------

I'm trying to improve the performance. I've added this where condition AND NVL (rextnif, 'A') = NVL (rextnif, 'A') to force the use of the MTREXT99 index (rextcdde, rextosin, rextnif). Without this we don't use the index. But still have lower performance. The table MTREXT have 5 million records. And this query runs in 2 and half minutes to return only 8 rows.

To test, I've removed the MTTPOB from this query (uses a full table scan) but I don't get any significant improvements.

Any ideas? Many Thanks! Filipe

EDIT: Added table alias

Whats the speed if you remove TO_NUMBER (poceccus) from ORDER BY?Alspo you should add aliases and use proper JOIN syntax,I have no idea to which table the columns in the WHERE belong to. — Mihai
Your index could be expanded,(rextesta,rextcdde ,rextosin,rextdaic ) and even convert poceccus to number definitely.Why using only numbers in VARCHAR?Also AND NVL (a.rextnif, 'A') = NVL (a.rextnif, 'A') ,you might have messed up the aliases here. — Mihai
This condition is ok. I added to force the use of the MITREXT99 index. Bu expand the index it works. Passes from 1 and half minute to milliseconds. — milheiros

Mihail Mihail · Accepted Answer · 2015-08-28T08:30:51

First, check your table statistics and regather them if they are stale. Wrong stats often lead to suboptimal execution plans.

Try moving the subquery to dtclie in the DECODE to a normal join. With your implementation it will be ran for each row, returned by the main query, and will affect performance.

Try using /*+ ordered */ hint and rearrange the table join order.

If the stats are fine and the DTPOCE table is more than few rows, try eliminating the nested loops by using a /*+ use_hash( DTPOCE ) */ hint.

And please use ansi join syntax and table aliases... It should not affect performance, but improves readability :)

Select statement performance in Oracle

1 Answers