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 and more. Is it possible to 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 that 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
  • Add to the question the output of the command EXPLAIN <тело запроса> . - ߊߚߤߘ
  • 2
    Add another table and index structure so that you can understand what is happening with the query. - Viktorov
  • @IDrakonl you excuse me, I'm just learning where to get what you ask for? Or I still do not quite understand simple things from ignorance, I can give you a little more detail and I will give you everything that is required. Thank you - Ethernets
  • The table structure is needed complete. With all indices. You need some kind of development tool that can show the structure of tables and indexes. Hands to pull out all the necessary info from the database pretty hemorrhoid - Mike
  • for example oracle.com/technetwork/developer-tools/sql-developer/overview/… - Mike

1 answer 1

Optimizer orakla campaign built the most effective plan for this request. It is on GRAPH only once, building a window function and choosing a record with a minimum K . But in order to search for this minimum, he has to go through many records of the graph and this is not treated by changing the query.

How do I see the solutions:

  1. Keep the minimum K to the roll (and keep it triggers), which would eliminate the need to find a 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 stored in a roll and change it there, if necessary.

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

  • Thanks, you probably have to leave as is. - Ethernets