Янв 12 2011

Размножение строк в запросе SQL

Oracle, SQL

Метки: , ,

Очень часто в запросах необходимо увеличить количество строк выборки на заданный множитель. В SQL это делается просто.

Описание задачи:

Каждую запись произвольной выборки T воспроизвести N раз (заданное количество) используя только один SQL с минимальными недостатками. Результат должен содержать полную входную строку и номер копии.

Цель:

Описать возможные варианты и предложить наилучший по критериям:

  • понятность и простота кода,
  • меньшее время выполнения,
  • меньшее использование ресурсов,
  • минимальная зависимость от среды,
  • минимум ограничений алгоритма
Описание среды:
  • СУБД: Oracle 8i и выше
  • Параметр N задается как bind переменная, т.е. значение заранее неизвестно и задается в момент выполнения
  • Входная и выходная выборки представляют собой набор строк:
Входная выборки T:

ID
A
B
C
D
Выходная выборка:

ID SEQ
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3
D 1
D 2
D 3
Варианты решения:
  1. Использование специально подготовленной таблицы,
  2. Использование большой существующей таблицы,
  3. Использование Hierarchical Queries,
  4. Использование табличной функции,
  5. Использование dual union all
Описание решений:
1. Использование специально подготовленной таблицы
    Недостатки:

  • дополнительная служебная таблица занимающая место
  • дополнительное ограничение по максимальному значению множителя N (не более чем строк в таблице Х).

В этом подходе необходимо создать таблицу X с количеством строк не менее чем максимально возможное значение N. Выполнить результат с использованием картезианского соединения.
Запрос:

SELECT T.ID, X.SEQ
FROM T
JOIN (
          SELECT ROWNUM SEQ
          FROM X
          WHERE ROWNUM< =:N
        ) X ON (1=1)
2. Использование большой существующей таблицы
    Недостатки:

  • дополнительная зависимость от привилегий и содержания дополнительной таблицы
  • дополнительное ограничение по максимальному значению множителя N (не более чем строк в таблице Х)
  • менее понятный план, особенно в случае с использованием системных представлений, таких как ALL_OBJECTS и тп

В этом подходе используется любая доступная и достаточно большая таблица или представление.

Запрос:

SELECT T.ID, X.SEQ
FROM T
JOIN (
          SELECT ROWNUM SEQ
          FROM ALL_OBJECTS
          WHERE ROWNUM< =:N
        ) X ON (1=1)
3. Использование Hierarchical Queries

Просто и красиво.
Недостатки:

  • Возможно использовать только с СУБД 8i и выше
  • Возможное ограничение по UGA памяти
SELECT T.ID, LEVEL SEQ
FROM T
CONNECT BY LEVEL< =:N
4. Использование табличной функции

Недостатки:

  • много плохо читабельного кода
  • долгое время выполнения
  • плохое использование СУБД
  • необходимость создания объекта TYPE

Очень плохой вариант, с двумя циклами внутри, описание кода на PLSQL приводить не буду, не хочу, это будет пустой тратой моего и вашего времени.

SELECT ID,SEQ
FROM TABLE( F( :N ) );
5. Использование dual union all

Недостатки:

  • жесткое ограничение множителя N
SELECT T.ID, X.SEQ
FROM T
JOIN (
          SELECT  1 SEQ FROM DUAL
          UNION ALL
          SELECT  2 FROM DUAL
          UNION ALL
          SELECT  3 FROM DUAL
        ) X ON (1=1)

Применимо только при известном множителе, очень часто такой подход используется при операции PIVOT для версий ниже Oracle 11R1.
Одним из вариантов данного подхода — это динамическая генерация SQL, надо сформировать «UNION ALL SELECT”|| i || «FROM DUAL» нужное количество раз и вызвать код в PLSQL.

Вывод

Самый красивый и «быстрый» вариант 3, который использует возможности СУБД по максимуму.

Способ 5 не подходит к постановке задачи, так как в нем жестко ограничен множитель и способ приведен только для наглядности (частный случай).

В дополнении: Всегда проверяйте все возможные способы для Вашего конкретного случая. У меня при реализации алгоритма формирования графика платежей по кредиту на основании заданных правил способ 3 выполнялся медленней способа 2 в 120 раз. Проблема в возникновении ожидания «control file sequential read» на Oracle 10.2.0.4.

PS И ни в коем случае не решайте подобные задачи на PLSQL.
Всегда старайтесь использовать следующие приоритеты, сформулированный Том Кайтом, для выбора подхода по решению задачи средствами СУБД:

  • Используйте SQL
  • Используйте PLSQL
  • Используйте Java и обвязку на PLSQL
  • Если это нельзя решить первыми тремя способами, то и не старайтесь решить данную задачу в СУБД

There are 5 comments

  • ivan on said:


    5. брутфорс: selec from dual union all
    6. следствие из 5 — динамический SQL.

    Если не секрет — зачем такие сложности? Исходная постановка несколько странная — наводит на мысли об использовании partition outer join (правда в 8i этим не побалуешься, да и в 9i тоже).

    • Потапов Евгений on said:

      — Подход «from dual union all» не подходит, так как не соответствует условиям, ограничение по множителю, его надо знать заранее.
      — Подход «динамический SQL» включил в описание, хотя имеется ограничение по множителю (32Кб сгенерированного текста, хотя можно обойти через DBMS_SQL) и задержка на разбор запроса.
      — «partition outer join» используется для плотного вывода результата объединения, если одна из таблиц уже плотная, то есть не в тему данного сообщения, а уже в продолжение возможных вариантов использования.

  • ivan on said:

    Предлагаю следующим топиком рассмотреть задачу преобраpования строки с заранее известным разделителем в sql-выборку.
    В большей части является следствием из этой задачи, но имеет и свои оригинальные решения.
    =)
    Интересно — сколько всего вариантив наберется?
    Думаю не меньше десятка.

    • Кисляков Александр Сергеевич on said:

      1. Табличная функция
      2. Соединение с подзапросом содержащим нужно количество строк
      3. MODEL
      4. Чистый PL/SQL
      5. Иерархический запрос

      Что ещё?

  • facebook.com Александр Черников on said:

    Может пригодится для тех кто ищет… Еще один красивый способ (oracle):

    SELECT * FROM T
    JOIN (SELECT ROWNUM r FROM dual CONNECT BY LEVEL <= 3) — num array 3 rows

  • Leave a Reply

    Ваш e-mail не будет опубликован. Обязательные поля помечены *