Оптимизация запроса с соединением таблиц

Question

Есть такой запрос:

select o.OPERATION_ID, o.SERVICE_ID, o.PARTNER_OPERATION_ID, o.AMOUNT_IN, o.USER_ID, o.TRY_STATUS, o.FIN_DATE, o.FACT_DATE,
       o.TRY_LATER_COUNT, o.CANCELLED_OPERATION_ID, o.CUSTOMER_ALIAS, o.CANCELLING_OPERATION_ID, o.SUBTYPE, o.LAST_CHANGE,
       o.CORRECTED, o.SOLUTION_E, s.PARTNER_ID 
from OPERATIONS o 
join SERVICES s on o.SERVICE_ID = s.SERVICE_ID
where o.FACT_DATE between TO_DATE('2021-06-25','yyyy-mm-dd') and TO_DATE('2021-06-28','yyyy-mm-dd')
order by o.OPERATION_ID  OFFSET 0 ROWS FETCH NEXT 134 ROWS ONLY

Колонка FACT_DATE имеет тип timestamp и хранит дату, например:

fromDate -> "2019-06-25 00:00:00.0"

Kогда число выборок превышает 57-100к (в примере я подставил 827678), время выполенния увеличивается с 10 минут до нескольких часов.

Я запрос смотрю и, честно сказать, не вижу как его можно оптимизировать. Вроде нигде индекс не переиспользуется.

БД Oracle 10g.

Answer 1

Для оптимизации SQL запроса часто требуется гораздо больше информации, чем для его написания и устранения логических ошибок.

Скопировать и опубликовать здесь сам запрос явно недостаточно. В большинстве случаев потребуются следующая информация:

Структура таблиц в виде запросов на их создание, или как минимум, описание таблиц:

create table SERVICES (
    SERVICE_ID number primary key, 
    PARTNER_ID number
);
create table OPERATIONS (
    OPERATION_ID number primary key, 
    SERVICE_ID number references SERVICES, 
    TRY_STATUS varchar2 (8), 
    FACT_DATE timestamp
);

desc OPERATIONS

Name         Null?    Type         
------------ -------- ------------ 
OPERATION_ID NOT NULL NUMBER       
SERVICE_ID            NUMBER       
TRY_STATUS            VARCHAR2(8)  
FACT_DATE             TIMESTAMP(6)

Все существующие индексы созданые на таблицах:

create index OPERATIONS_factdate_idx on OPERATIONS (FACT_DATE);

По возможности, написать генератор тестовых данных, которые наиболее приближенны к реальным данным, и в заключение, обновить статистику:

insert all 
    when 1=1 then into SERVICES   values (rn, mod (rn,9)+1)
    when 1=1 then into OPERATIONS values (rn, rn, 'complete', ts)
    select rownum rn, timestamp'2021-06-20 00:00:00'+((rownum-1)*10/(24*60*60)) ts
    from dual connect by level<=1e5;
commit;

exec dbms_stats.gather_table_stats (user, 'SERVICES', cascade=>true)

exec dbms_stats.gather_table_stats (user, 'OPERATIONS', cascade=>true)

Сам запрос и, что важно, план его выполнения. Как получить план выполнения, наиболее полно показано, например, в этой теме. Субъективно предпочитаю - SQL*Plus с autotrace:

SQL> set lines 999 autotrace traceonly
SQL> select o.OPERATION_ID, o.SERVICE_ID, o.FACT_DATE, s.PARTNER_ID
  2  from OPERATIONS o
  3  join SERVICES s on o.SERVICE_ID = s.SERVICE_ID
  4  where o.FACT_DATE between DATE'2021-06-25' and DATE'2021-06-28'
  5  order by o.OPERATION_ID
  6  OFFSET 0 ROWS FETCH NEXT 134 ROWS ONLY;

134 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2383740585

------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |   134 | 10452 |   142   (0)| 00:00:01 |
|*  1 |  VIEW                           |              |   134 | 10452 |   142   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY         |              |   137 |  3973 |   142   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                 |              |   137 |  3973 |   142   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |              |   137 |  3973 |   142   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| OPERATIONS   | 25922 |   531K|     5   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN           | SYS_C0013710 |   521 |       |     2   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN          | SYS_C0013709 |     1 |       |     0   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID | SERVICES     |     1 |     8 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_004"."rowlimit_$$_rownumber"<=134 AND
              "from$_subquery$_004"."rowlimit_$$_rownumber">0)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "O"."OPERATION_ID")<=134)
   5 - filter("O"."FACT_DATE">=TIMESTAMP' 2021-06-25 00:00:00' AND
              "O"."FACT_DATE"<=TIMESTAMP' 2021-06-28 00:00:00')
   7 - access("O"."SERVICE_ID"="S"."SERVICE_ID")

Следует упомянуть, что информации может быть достаточно много, и часть её можно разместить на внешнем SQL fiddle, например, на db<>fiddle. В вопросе же уделить внимание другим деталям, например, попытке самостоятельной оптимизировать запрос - что было сделано, что не получилось, предположения - почему не получилось.

В заключение, информацию следует публиковать в текстовом виде, а не картинками.

БЛОГ НА HUSL

Оптимизация запроса с соединением таблиц

Ответы (1 шт):