sql – Oracle query is slow

Question:

There is a request:

SELECT zxp.rulon.ukey,
       n, nz, bz, zxp.rulon.ms, tz, ts,
       round(hz,2) HZ, hdm, hdp, zxp.rulon.h1,
       l, round(pf,2) PF, pt, pu, pdm, pdp,
       pbm, pbp, pe, zxp.mc.ukey, naim, 
       LDM, LDP, LBM, LBP, H0, tr, tv, DOP,
       zxp.graph.irez as Broken, 
       K_Z as Brigade
FROM   zxp.rulon, zxp.mc, zxp.graph
WHERE  ts BETWEEN to_date('02.01.2016 7:00:00',  'dd.mm.yyyy hh24:mi:ss')
       AND to_date('02.01.2016 15:00:00', 'dd.mm.yyyy hh24:mi:ss')
AND    zxp.rulon.ms   = zxp.mc.ukey     
AND    zxp.rulon.ukey = zxp.graph.u_rul  
AND    k = (
         SELECT min(k)
         FROM   zxp.Graph
         WHERE  zxp.rulon.ukey = zxp.graph.u_rul
       )
ORDER  BY  ts

But its execution takes 4 seconds or more. Can you optimize it?

zxp.Rulon:

UKEY    NUMBER
N   NUMBER
ADR NUMBER
NZ  NUMBER
MS  NUMBER
TR  DATE
TZ  DATE
TV  DATE
TS  DATE
TM  NUMBER
SK  NUMBER
DOP NUMBER
BZ  NUMBER
HZ  NUMBER
HZZ NUMBER
HDM NUMBER
HDP NUMBER
H0  NUMBER
H1  NUMBER
L   NUMBER
LDM NUMBER
LDP NUMBER
LBM NUMBER
LBP NUMBER
PF  NUMBER
PT  NUMBER
PDM NUMBER
PDP NUMBER
PBM NUMBER
PBP NUMBER
PU  NUMBER
PE  NUMBER
L_1 NUMBER
L_2 NUMBER
L_3 NUMBER
L_4 NUMBER
H1S NUMBER
H2S NUMBER
H3S NUMBER
H4SI    NUMBER
H1SI    NUMBER
T1S NUMBER
T2S NUMBER
T3S NUMBER
T4S NUMBER
D1  NUMBER
D2  NUMBER
D3  NUMBER
D4  NUMBER
GH4N    NUMBER
GH4Z    NUMBER
GH4S    NUMBER
GH1Z    NUMBER
GH1S    NUMBER
L4  NUMBER
D_RR    NUMBER
D_RV    NUMBER
K_T NUMBER
K_Z NUMBER
F_REZ1  NUMBER
F_REZ2  NUMBER
F_REZ3  NUMBER
F_REZ4  NUMBER
I_REZ1  NUMBER
I_REZ2  NUMBER
I_REZ3  NUMBER
I_REZ4  NUMBER

Graph:

T2  NUMBER
T3  NUMBER
T4  NUMBER
DL  NUMBER
IREZ    NUMBER
FREZ    NUMBER
LOGBITS NUMBER
UKEY    NUMBER
U_RUL   NUMBER
K   NUMBER
T   DATE
H4  NUMBER
H1  NUMBER
V1  NUMBER
V2  NUMBER
V3  NUMBER
V4  NUMBER

Is this what you asked for? table structure?

Plan hash value: 515981838

--------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |           |  1653 |   663K|       |   332   (2)| 00:00:04 |
|   1 |  SORT ORDER BY                     |           |  1653 |   663K|  1672K|   332   (2)| 00:00:04 |
|*  2 |   VIEW                             | VW_WIF_1  |  1653 |   663K|       |   184   (2)| 00:00:03 |
|   3 |    WINDOW SORT                     |           |  1653 |   384K|   872K|   184   (2)| 00:00:03 |
|*  4 |     FILTER                         |           |       |       |       |            |          |
|   5 |      TABLE ACCESS BY INDEX ROWID   | GRAPH     |  3320 |   110K|       |    88   (2)| 00:00:02 |
|   6 |       NESTED LOOPS                 |           |  1653 |   384K|       |    94   (2)| 00:00:02 |
|*  7 |        HASH JOIN                   |           |     1 |   204 |       |     7  (15)| 00:00:01 |
|   8 |         TABLE ACCESS BY INDEX ROWID| RULON     |     1 |   195 |       |     3   (0)| 00:00:01 |
|*  9 |          INDEX RANGE SCAN          | I_RUL     |     1 |       |       |     2   (0)| 00:00:01 |
|  10 |         TABLE ACCESS FULL          | MC        |    45 |   405 |       |     3   (0)| 00:00:01 |
|* 11 |        INDEX RANGE SCAN            | GRAPH_RUL |  3391 |       |       |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("VW_COL_33" IS NOT NULL)
   4 - filter(TO_DATE('02.01.2016 15:00:00','dd.mm.yy hh24:mi:ss')>=TO_DATE('2016-01-02 
              07:00:00', 'yyyy-mm-dd hh24:mi:ss'))
   7 - access("RULON"."MS"="MC"."UKEY")
   9 - access("TS">=TO_DATE('2016-01-02 07:00:00', 'yyyy-mm-dd hh24:mi:ss') AND 
              "TS"<=TO_DATE('02.01.2016 15:00:00','dd.mm.yy hh24:mi:ss'))
  11 - access("RULON"."UKEY"="GRAPH"."U_RUL")

RULON table index:

Таблица Индекс  Тип         Колонки
RULON   RUL_UN  UNIQUE      UKEY
RULON   I_RUL   NONUNIQUE   TS

GRAPH:

GRAPH   GRAPH_RUL   NONUNIQUE   U_RUL

Answer:

The Oracle optimizer has built the most efficient plan for the given query. He goes along GRAPH only once, constructing a window function and choosing an entry with a minimum K using it. But to find this minimum, he has to go through many records of the graph and simply changing the query does not cure it.

As solutions I see:

  1. Store the minimum K to the roll (and lead it by triggers) to eliminate the need to search for the minimum in the graph. As a minus – the actual need to create a trigger, which, when adding / modifying records in GRAPH , will have to compare K with the one stored in the roll and change it there, if necessary.

  2. Construct a composite index on columns (U_RUL, K) on GRAPH , then the Oracle should immediately find records with a minimum K , without looking into the data. As a minus – an index on 373kk records will take up a lot of space and will somewhat slow down the creation of new records in the GRAPH table.

Post Views: 3
Scroll to Top