Простой Oracle pl/sql ?

[Leberecht]

За коррективы (DECODE и другие) спасибо!

На счёт ускорения, хотел заметить интересную деталь: чтоб её правильно замерить, я-бы порекомендовал запускать сравниваемые запросы с локального сервака, да ещё и с программки, которая принимает рекордсет, но никуда не выводит его.
Ну или включить TRACE, чтобы посмотреть скок именно времени каждый запросец отнял у сервака.

А то ежели табличка большая, то на её пересыл и вывод уйдёт куда больше времени, чем на обработку запроса.

[Uzito]

Наверное уже поздно, но есть и другие методы для оригинального запроса. Oracle 11g

Code: Select all

SELECT row_id, acc, acc_prop1, acc_prop2, acc_prop3, acc_prop4, acc_prop5
  FROM bigtable t UNPIVOT ((acc, acc_prop1, acc_prop2, acc_prop3, acc_prop4, acc_prop5)
         FOR row_id
         IN  ((acc1, acc1_prop1, acc1_prop2, acc1_prop3, acc1_prop4, acc1_prop5) AS 'one',
              (acc2, acc2_prop2, acc2_prop2, acc2_prop3, acc2_prop4, acc2_prop5) AS 'two',
              (acc3, acc3_prop3, acc3_prop2, acc3_prop3, acc3_prop4, acc3_prop5) AS 'three',
              (acc4, acc4_prop4, acc4_prop2, acc4_prop3, acc4_prop4, acc4_prop5) AS 'four',
              (acc5, acc5_prop5, acc5_prop2, acc5_prop3, acc5_prop4, acc5_prop5) AS 'five'))
WHERE t.filter = somefilter

[tourist]

как вычисляется такой % ?
наш оптимизатор, который оптимизировал базу МТС упоминал цифру в >1%.

Лучше даже менее 10, just try

так а я что написал 1% и есть меньше 10.
Я полагаю вычисляется исходя из размера+параметров блока и размера записи.
Т.е сначала надо выяснить сколько строк может быть в блоке.
Тогда можно предположить, что если в блоке 1000 записей, но 1млн строк = 1 000 блоков.
значит при доставании 10 000 записей есть хорошая вероятность считать все 1000 блоков.
вобщем из наблюдений на той базе был сделан вывод, что если более 1% то делаем full scan.

just try - не всегда работает, особенно если девелопер а не админ, а на продакшн не везде впускают,
особенно если делать продукт, а не домашние поделки.

clustering factor еще оценивается при выборе index range scan vs. full scan

[tourist]

voilà svp

..
пользуйтест генераторами типа

Code: Select all

SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL < 4

Проверил ваш метод на работе, благо большая таблица имеется. UNION ALL работает чуть быстрее (2.76s) vs 2.83s, оракловый оптимизатор при union all не лажается с вычислением cardinality и execution cost 202 vs 354.

Интересно, что будет быстрее работать на больших данных

-- 1
LEFT JOIN ( SELECT level pos FROM dual CONNECT BY LEVEL<=5 ) p ON 1=1

--2
CROSS JOIN ( SELECT level pos FROM dual CONNECT BY LEVEL<=5 ) p

--3
CROSS JOIN ( SELECT /*+ CARDINALITY (p,5) */ column_value pos FROM TABLE(sys.KU$_OBJNUMSET(1,2,3,4,5) )p ) p

--4
CROSS JOIN (
SELECT 1 AS pos FROM DUAL
UNION
SELECT 2 FROM DUAL
UNION
SELECT 3 FROM DUAL
UNION
SELECT 4 FROM DUAL
UNION
SELECT 5 FROM DUAL ) p

[tourist]

На виртуальных данных разница почти не чуствуется

WITH virt_tbl AS(
SELECT
'a1' acct1, 11 acct1_property1, 12 acct1_property2, 13 acct1_property3, 14 acct1_property4, 15 acct1_property5,
'a2' acct2, 21 acct2_property1, 22 acct2_property2, 23 acct2_property3, 24 acct2_property4, 25 acct2_property5,
'a3' acct3, 31 acct3_property1, 32 acct3_property2, 32 acct3_property3, 34 acct3_property4, 35 acct3_property5,
'a4' acct4, 41 acct4_property1, 42 acct4_property2, 43 acct4_property3, 44 acct4_property4, 45 acct4_property5,
'a5' acct5, 51 acct5_property1, 52 acct5_property2, 53 acct5_property3, 54 acct5_property4, 55 acct5_property5
FROM dual )
SELECT CASE p.pos WHEN 1 THEN t.acct1 WHEN 2 THEN t.acct2 WHEN 3 THEN t.acct3 WHEN 4 THEN t.acct4 WHEN 5 THEN t.acct5 END AS account
, CASE p.pos WHEN 1 THEN t.acct1_property1 WHEN 2 THEN t.acct2_property1 WHEN 3 THEN t.acct3_property1 WHEN 4 THEN t.acct4_property1 WHEN 5 THEN t.acct5_property1 END AS property1
, CASE p.pos WHEN 1 THEN t.acct1_property2 WHEN 2 THEN t.acct2_property2 WHEN 3 THEN t.acct3_property2 WHEN 4 THEN t.acct4_property2 WHEN 5 THEN t.acct5_property2 END AS property2
, CASE p.pos WHEN 1 THEN t.acct1_property3 WHEN 2 THEN t.acct2_property3 WHEN 3 THEN t.acct3_property3 WHEN 4 THEN t.acct4_property3 WHEN 5 THEN t.acct5_property3 END AS property3
, CASE p.pos WHEN 1 THEN t.acct1_property4 WHEN 2 THEN t.acct2_property4 WHEN 3 THEN t.acct3_property4 WHEN 4 THEN t.acct4_property4 WHEN 5 THEN t.acct5_property4 END AS property4
, CASE p.pos WHEN 1 THEN t.acct1_property5 WHEN 2 THEN t.acct2_property5 WHEN 3 THEN t.acct3_property5 WHEN 4 THEN t.acct4_property5 WHEN 5 THEN t.acct5_property5 END AS property5
FROM virt_tbl t
-- LEFT JOIN ( SELECT level pos FROM dual CONNECT BY LEVEL<=5 ) p ON 1=1
-- CROSS JOIN ( SELECT level pos FROM dual CONNECT BY LEVEL<=5 ) p
-- CROSS JOIN ( SELECT /*+ CARDINALITY (p,5) */ column_value pos FROM TABLE(sys.KU$_OBJNUMSET(1,2,3,4,5) )p ) p
CROSS JOIN (
SELECT 1 AS pos FROM DUAL
UNION
SELECT 2 FROM DUAL
UNION
SELECT 3 FROM DUAL
UNION
SELECT 4 FROM DUAL
UNION
SELECT 5 FROM DUAL ) p

[tourist]

Или UnPivot in Oracle 11 ?

WITH virt_tbl AS
(
SELECT
'a1' acct1, 11 acct1_property1, 12 acct1_property2, 13 acct1_property3, 14 acct1_property4, 15 acct1_property5,
'a2' acct2, 21 acct2_property1, 22 acct2_property2, 23 acct2_property3, 24 acct2_property4, 25 acct2_property5,
'a3' acct3, 31 acct3_property1, 32 acct3_property2, 32 acct3_property3, 34 acct3_property4, 35 acct3_property5,
'a4' acct4, 41 acct4_property1, 42 acct4_property2, 43 acct4_property3, 44 acct4_property4, 45 acct4_property5,
'a5' acct5, 51 acct5_property1, 52 acct5_property2, 53 acct5_property3, 54 acct5_property4, 55 acct5_property5
FROM dual
)
SELECT account, property1, property2, property3, property4, property5 --, rw
FROM ( SELECT * FROM virt_tbl )
UnPivot( (account, property1, property2, property3, property4, property5)
FOR rw IN (
( acct1, acct1_property1, acct1_property2, acct1_property3, acct1_property4, acct1_property5 )-- AS 'row1'
,( acct2, acct2_property1, acct2_property2, acct2_property3, acct2_property4, acct2_property5 )-- AS 'row2'
,( acct3, acct3_property1, acct3_property2, acct3_property3, acct3_property4, acct3_property5 )-- AS 'row3'
,( acct4, acct4_property1, acct4_property2, acct4_property3, acct4_property4, acct4_property5 )-- AS 'row4'
,( acct5, acct5_property1, acct5_property2, acct5_property3, acct5_property4, acct5_property5 )-- AS 'row5'
)
)

[Leberecht]

Вау, какую круть расписали

Кстати, в примерах с виртуальными данными я не совсем врубился, а где там реальная табличка фигурирует, каким боком она там джойнится
Я так полагаю, её имечко вместо dual надо воткнуть, так?

[Uzito]

Мне совсем нечего делать было, я сделал табличку из 100k записей, заполнил случайно.
unpivot = 28 секунд
crossjoin + case = 49 секунд
union all = 28 секунд

=> метод cross join сосёт.

[Leberecht]

Я decode не пробовал, но когда топикстартеру выставлял свой код, для пущей надёги на одной многомиллионной табличке сравнивал UNION ALL и CROSS JOIN.
Последний даже без DECODE, a с CASE WHEN был поживее.

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

Специально заточенный под "правый разворот структуры" UNPIVOT, не спорю, всяк побыстрее будет.
Ток если ент 11я версия

[Zorkus]

Че-то я не понял, как эти люди 3 страницы обсуждают всерьез оптимизацию запроса по понятиям "я не уверен, что это будет быстрее", не приводя при этом планов запросов, вывода tkprof, хоть какой-то инфы о статистике и настройках в базе и прочего. Гм.. Кайт и Льюис негодуют.

[APLY]

Упустил я тут многоe, UNPIVOT в частности, оставил Left Outer Join and CASE, ушло в Production вчера.
Если будут жаловаться заменим на UNPIVOT

Спасибо всем БОЛьШОЕ!!!