Блог

У меня есть таблица под названием SAMPLE_TABLE, в которой есть следующие столбцы с CAR_TYPE, COLOR и CAR_BRAND, составляющие составной индекс.

  VALUE_ID      VALUE      CAR_TYPE     COLOR     SUBMIT_DT      CAR_BRAND
   1            10        Sedan        Blue      3/7/2019       Ford
   2            70        Sedan        Blue      3/6/2019       Ford
   3            20        Sedan        Blue      3/5/2019       Ford
   4            77         SUV         Red       3/7/2019       Volvo
   5           100         SUV         Red       3/1/2019       Volvo
  

Могу ли я написать более эффективный способ запроса значения, соответствующего ПОСЛЕДНЕМУ SUBMIT_DT ? В будущем ТАБЛИЦА будет содержать миллионы строк данных, поэтому мне нужно будет найти запрос с наименьшим временем выполнения / стоимостью, который может запрашивать.

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

  VALUE
 10
  

Ниже то, что у меня есть на данный момент:

  SELECT value 
   FROM (
          SELECT *
            FROM TABLE
           WHERE CAR_TYPE = rCar_Type
             AND COLOR = rColor
             AND CAR_BRAND = rCar_Brand
            ORDER by submit_dt desc
        )
   WHERE rownum = 1;
  

Это неэффективно?

Заранее спасибо

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

В вашей таблице будут миллионы строк, и ваш составной индекс (CAR_TYPE, COLOR, CAR_BRAND) не будет очень избирательным. Вы ищете способ получить одну строку с последним SUBMIT_DT для данной записи в вашем составном индексе без необходимости считывать ВСЕ совпадения из этого индекса.

Ответ: добавить SUBMIT_DT DESC в свой составной индекс

Давайте настроим тест:

 create table matt_objects as select * from dba_objects;

-- This is our analog of your composite index
create index matt_objects_n1 on matt_objects ( object_type, owner );

exec dbms_stats.gather_table_stats(user,'MATT_OBJECTS');
  

Теперь давайте проведем автоматическую трассировку этого оператора:

 select object_name
from   matt_objects
where  object_type = 'TABLE'
and    owner = 'INV'
order by last_ddl_time desc
fetch first 1 row only;
  

 ---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |     1 |    88 |    17   (6)| 00:00:01 |
|*  1 |  VIEW                                 |                 |     1 |    88 |    17   (6)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK             |                 |   162 |  7290 |    17   (6)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| MATT_OBJECTS    |   162 |  7290 |    16   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | MATT_OBJECTS_N1 |   162 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("LAST_DDL_TIME") DESC )<=1)
   4 - access("OBJECT_TYPE"='TABLE' AND "OWNER"='INV')
  

Результат (из автоматической трассировки): получено 72 согласованных буфера чтения

Теперь давайте заменим ваш составной индекс на тот, который поможет нам больше:

 drop index matt_objects_n1;

create index matt_objects_n1 on matt_objects ( object_type, owner, last_ddl_time desc );

exec dbms_stats.gather_table_stats(user,'MATT_OBJECTS');
  

.. и давайте снова проведем автоматическую трассировку того же оператора:

 --------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |     1 |    88 |    54   (2)| 00:00:01 |
|   1 |  SORT ORDER BY                 |                 |     1 |    88 |    54   (2)| 00:00:01 |
|*  2 |   VIEW                         |                 |     1 |    88 |    53   (0)| 00:00:01 |
|*  3 |    WINDOW NOSORT STOPKEY       |                 |   162 |  7290 |    53   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| MATT_OBJECTS    |   162 |  7290 |    53   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | MATT_OBJECTS_N1 |   162 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1)
   3 - filter(ROW_NUMBER() OVER ( ORDER BY SYS_OP_DESCEND("LAST_DDL_TIME"))<=1)
   5 - access("OBJECT_TYPE"='TABLE' AND "OWNER"='INV')
  

Результат (из автоматической трассировки): получено 5 последовательных результатов чтения

Этот индекс очень помог. Заметили, что план отличается? «СОРТИРОВКА ОКНА По РАНЖИРОВАННОМУ РАНГУ» заменена на «КНОПКА остановки БЕЗ сортировки ОКНА». Поскольку индекс уже отсортирован так, как вы хотите (в порядке убывания), Oracle знает, что может читать строки индекса по порядку и останавливаться после первой, завершая запрос с гораздо меньшими усилиями.

Интересно отметить, что стоимость второго запроса выше стоимости первого запроса, даже несмотря на то, что производительность второго запроса более чем в 10 раз выше. Это просто показывает вам, что «стоимость» — это оценка, и к ней иногда следует относиться с недоверием.

Ну, запрос, который вы написали, в данном контексте можно назвать не «неэффективным», а «бесполезным», поскольку он вернет одну случайную строку. Вероятно, вы что-то упускаете ORDER BY в подзапросе.

В любом случае: посмотрите, как это ведет себя:

 select value
  from (select row_number() over (partition by car_type, color, car_brand
                                  order by submit_dt desc) rn,
               value
        from sample_table
        where car_type = rcar_type
          and color = rcolor
          and car_brand = rcar_brand
       )
where rn = 1;       
  

Не забудьте создать индекс для столбцов, используемых в WHERE предложении.

Я предполагаю, что вы имели в виду «индекс» вместо «ключа» в вашем вопросе. Если это так, то я бы создал индекс:

 create index ix1 on sample_table (car_type, color, car_brand, submit_dt);
  

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

 select max(submit_dt)
from sample_table
where CAR_TYPE = rCar_Type
  and COLOR = rColor
  and CAR_BRAND = rCar_Brand
  

Вы ищете последнее значение для car_type , color car_brand . Oracle предлагает KEEP LAST для этого:

 SELECT MAX(value) KEEP (DENSE_RANK LAST ORDER BY submit_dt)
  FROM table
 WHERE car_type = :rcar_type
   AND color = :rcolor
   AND car_brand = :rcar_brand;
  

Просто используйте FETCH FIRST :

 SELECT *
FROM TABLE
WHERE CAR_TYPE = rCar_Type
  AND COLOR = rColor
  AND CAR_BRAND = rCar_Brand
ORDER BY submit_dt DESC
FETCH FIRST 1 ROW ONLY
  

Если ваша версия базы данных 12c .

Вы можете использовать row_number для решения этой проблемы.

Например

 SELECT x.value 
FROM (
  SELECT VALUE, 
         ROW_NUMBER() OVER (PARTITION BY CAR_TYPE, CAR_COLOR, CAR_BRAND ORDER BY SUBMIT_DATE DESC) AS RN
  FROM table
) x
WHERE x.RN = 1