Как улучшить производительность моего SQL-запроса?

Я новичок в SQL и хочу запрашивать данные из базы данных Oracle с помощью SQL. У меня есть табличное пространство, которое записывает позиции многих автомобилей. Каждая запись имеет пробег и время. В табличном пространстве есть три столбца: «SENDTIME», «MILEAGE» и «PLATENO». Они представляют время, пробег (в формате метки времени) и номер автомобиля соответственно. Значения в столбце «SENDTIME» представлены в формате даты и времени. Я хочу узнать, когда конкретный автомобиль не двигался более 30 секунд и менее 300 секунд. Итак, я написал sql-запрос:

WITH gpsinfo_cte AS (
  SELECT plateno, sendtime, longitude, latitude, mileage, createdate,
    FIRST_VALUE(sendtime) OVER (PARTITION BY plateno, mileage ORDER BY sendtime) AS first_sendtime,
    LAST_VALUE(sendtime) OVER (PARTITION BY plateno, mileage ORDER BY sendtime) AS last_sendtime
  FROM GPSINFO 
  WHERE plateno = '京AEW302'
)
SELECT /*+ NO_MERGE(gpsinfo_cte) */ plateno, sendtime, longitude, latitude, mileage, createdate 
FROM gpsinfo_cte
WHERE (last_sendtime - first_sendtime) * 24 * 60 *60 < 300
AND (last_sendtime - first_sendtime) * 24 * 60 *60 > 30;

Однако он работает довольно медленно в базе данных оракула. В соответствии с результатами веб-поиска я попытался использовать оператор EXPLAIN PLAN, чтобы сгенерировать план выполнения вашего запроса и сохранить его в таблице с именем PLAN_TABLE. Вот результат:

У меня до сих пор нет идеи улучшить производительность. Кто-нибудь может помочь? Большое спасибо!

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

— 29.05.2023 14:35

sql oracle sqlperformance

29.05.2023 09:51

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Чтобы получить максимально быстрые результаты, вам необходимо предварительно рассчитать эти значения (время первой и последней отправки). Этого можно достичь с помощью:

подпрограмма, отвечающая за добавление записи - в разрезе одной plateno будет быстрее вычисляться и сохраняться в первый и последний раз
триггер на столе
материализованное представление и процесс обновления его данных

Все это требует некоторого времени разработки и не может быть сложным.

Я также могу предложить вам попробовать сначала вычислить plateno, а затем извлечь его детали. В случае sendtime нас интересуют первое (наименьшее) и последнее (наибольшее) значения. Итак, вы можете использовать:

WITH gpsinfo_cte AS (
  SELECT plateno
  FROM GPSINFO 
  WHERE plateno = '京AEW302'
  GROUP BY plateno
  HAVING (MAX(sendtime) - MIN(sendtime)) * 24 * 60 * 60 < 300
    AND (MAX(sendtime) - MIN(sendtime)) * 24 * 60 * 60 > 30
)
SELECT *
FROM GPSINFO
WHERE plateno IN (SELECT plateno FROM gpsinfo_cte);

Если это работает, вы можете добавить индекс на plateno и sendtime, чтобы оптимизировать его.

29.05.2023 10:59

Ответ принят как подходящий

Это, пожалуй, все, что имеет значение:

 FROM GPSINFO 
  WHERE plateno = '京AEW302'

Предполагая, что у вас много (тысячи, может быть, миллионы) автомобилей и запрашивается только один номерной знак, вы хотите использовать индекс для доступа только к строкам таблицы для этого конкретного автомобиля. Ваш план выполнения показывает, что этого не происходит:

TABLE ACCESS FULL GPSINFO

Просто создайте индекс в столбце plateno, и это должно решить вашу проблему:

CREATE INDEX gpsinfo_plateno_idx1 on gpsinfo(plateno) compress 1

Что касается остальной части вашего запроса, вам придется поработать над логикой, чтобы получить правильные результаты, но маловероятно, что у вас будут заметные проблемы с производительностью, пока вы работаете только с одной машиной. С точки зрения логики, если я правильно понимаю, вы хотите знать, когда между записями положения есть 30-300-секундный промежуток без пробега между ними. Итак, вам нужно сравнить строку с соседней строкой. Для этого используйте LAG (или LEAD):

SELECT x.*,
       (sendtime - last_sentime) * 86400 seconds_elapsed
  FROM (SELECT x.*,
               LAG(sendtime) OVER (ORDER BY sendtime) last_sendtime,
               LAG(mileage) OVER (ORDER BY sendtime) last_mileage
          FROM gpsinfo x
         WHERE plateno = '京AEW302') x
 WHERE (sendtime - last_sentime) * 86400 BETWEEN 30 AND 300
   AND mileage = last_mileage

Примечание. Если вы запрашиваете только одно значение plateno, нет необходимости включать платено в предложение PARTITION BY, это избыточно, поскольку оно только одно. Вы, конечно, можете использовать PARTITION BY mileage в качестве альтернативы моей логике mileage = last_mileage, показанной здесь, но пробег, вероятно, будет иметь много разных значений, и внутренняя группировка по такому количеству значений означает множество крошечных (однострочных) групп, и это не очень эффективно для памяти/времени. Тем не менее, как я уже сказал, индексация — это действительно ваша единственная серьезная проблема.

Оно работает! Большое спасибо

— 14.06.2023 08:35

29.05.2023 13:29