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

[APLY]

Привет,
Есть большая таблица со множеством полей, т.ч. performance важен.
columns like this:
acct1, acct1_property1, acct1_property2,acct1_property3, acct1_property4, acct1_property5,acct2, acct2_property1, acct2_property2,acct2_property3, acct2_property4, acct2_property5,acct1, acct3_property1, acct3_property2,acct3_property3, acct3_property4, acct3_property5, и т.д.
Всe в одной row.
Всeго 5 accounts i 5 properties for each account.

Надо сделать 5 rows :
acct1, acct1_property1, acct1_property2,acct1_property3, acct1_property4, acct1_property5
acct2, acct1_property1, acct2_property2,acct2_property3, acct2_property4, acct2_property5
acct3, acct3_property1, acct3_property2,acct3_property3, acct3_property4, acct3_property5
acct4, acct4_property1, acct4_property2,acct4_property3, acct4_property4, acct4_property5
acct5, acct5_property1, acct5_property2,acct5_property3, acct5_property4, acct5_property5

Самое простое:

with accts as ( select acct1, acct1_property1, acct1_property2,acct1_property3, acct1_property4, acct1_property5,
acct2, acct2_property1, acct2_property2,acct2_property3, acct2_property4, acct2_property5,
acct3, acct3_property1, acct3_property2,acct3_property3, acct3_property4, acct3_property5,
acct4, acct4_property1, acct4_property2,acct4_property3, acct4_property4, acct4_property5,
acct5, acct5_property1, acct5_property2,acct5_property3, acct5_property4, acct5_property5
from big_table where ...)
select acct1, acct1_property1, acct1_property2,acct1_property3, acct1_property4, acct1_property5 from accts
union all
select acct2, acct1_property1, acct2_property2,acct2_property3, acct2_property4, acct2_property5 from accts
union all
select acct2, acct2_property1, acct2_property2,acct2_property3, acct2_property4, acct2_property5 from accts
union all
select acct3, acct3_property1, acct3_property2,acct3_property3, acct3_property4, acct3_property5 from accts
union all
select acct4, acct4_property1, acct4_property2,acct4_property3, acct4_property4, acct4_property5 from accts
union all
select acct5, acct5_property1, acct5_property2,acct5_property3, acct5_property4, acct5_property5 from accts

Again performance is critical , any other ideas?

Спасибо всем!

[oleg lebedev]

А зачем вам использовать with вообще?

Кажется

Code: Select all

select acct1, acct1_property1, acct1_property2,acct1_property3, acct1_property4, acct1_property5 from big_table where ...
union all
select acct2, acct1_property1, acct2_property2,acct2_property3, acct2_property4, acct2_property5 from big_table where ...
union all
select acct2, acct2_property1, acct2_property2,acct2_property3, acct2_property4, acct2_property5 from big_table where ...
union all
select acct3, acct3_property1, acct3_property2,acct3_property3, acct3_property4, acct3_property5 from big_table where ...
union all
select acct4, acct4_property1, acct4_property2,acct4_property3, acct4_property4, acct4_property5 from big_table where ...
union all
select acct5, acct5_property1, acct5_property2,acct5_property3, acct5_property4, acct5_property5 from big_table where ...

производит тот же результат.
Основное что может тормозть так это то что в where.
Но я подозреваю, что optimizer произведёт тот же query plan для обоих случаев, если вы пользуетесь 11 ораклом. Для 10 го я бы избегал with, т.к. они его не полностью внедрили и там много проблем с операциями в памяти.

[APLY]

Олег спасибо,

Не хочется много раз select from big_table
Вы думаете что если where clause the same то optimizer сделает ето только один раз для всех selects?
Надо проверить.
А чем with плохо?

Может еше как можно,
например PIVOT использовать?

[Leberecht]

Чтобы не делать нвсколько селектов из огромной таблицы, а попросту "rotate structure", создаёте временную табличку (ну или попросту саб-квери из 5 или скольки там рекордов) и с ней джойните большую.
И всего делов-то

по большой ток один проход получам, никакик унионов

[oleg lebedev]

Олег спасибо,

Не хочется много раз select from big_table
Вы думаете что если where clause the same то optimizer сделает ето только один раз для всех selects?
Надо проверить.
А чем with плохо?

Может еше как можно,
например PIVOT использовать?

Там будет всего один скан, через индекс или full в зависимости, что у вас в where и какие нндексы. Оракл не будет бегать за каждой строчкой несколько раз. Оракл не бегает за строчками вообще, а читает data blocks. Один из параметров в DB - сколько блоков читается за 1 read, но это оффтопик.
Вам не надо пытаться оптимизировать запрос самому - здесь оптимизатор сделает сам, т.к. query примитивное и все ваши поля будут считаны за один раз.
Это ошибка думать, что оракл будет 6 раз, как в этом примере, вычислять условие в where и 6 раз бегать на диск.
Проблемы с with для 10го Оракла в том, что у них много bugs с операциями в памяти и иногда query приносят абсолютно бессмысленный вариант. Если столкнётись с таким, то нужно использовать hint /*+ materialize */, который discourages oт выполнения операций в памяти.
Если в вашем случае with приносит правильный результат, и вам это форма нравится, то используйте её. Я не думаю, что будет какая-то разница. Ваш pеtformance будет определяться только тем, что в where. В select оптимизировать нечего.
Помните, что если select затрагивает, как минимум 40% всех строчек из таблицы, то full scan может дать лучший результат, чем поиск по индексам. Всё зависит от того насколько быстрые ваши диски и насколько хороши индексы и статистика на них.

[Leberecht]

"Это ошибка думать, что оракл будет 6 раз, как в этом примере, вычислять условие в where и 6 раз бегать на диск." - Скорее всего ровно сток раз и будет читать. Мож не с диска, а с памяти, если она другими тяжёлыми запросами не будет занята, но всё равно ровно все 5-6 раз и прочитает.

Не верите, сравните тайминг одиночного запросика с этим юнионом.

Смотрите мой предидыщий пост, элементарно всё пишется через джойник со временной табличкой или подзапросиком из пяти-шести записей

[metaller]

А где тут PL/SQL ???

[oleg lebedev]

"Это ошибка думать, что оракл будет 6 раз, как в этом примере, вычислять условие в where и 6 раз бегать на диск." - Скорее всего ровно сток раз и будет читать. Мож не с диска, а с памяти, если она другими тяжёлыми запросами не будет занята, но всё равно ровно все 5-6 раз и прочитает.

Не верите, сравните тайминг одиночного запросика с этим юнионом.

Смотрите мой предидыщий пост, элементарно всё пишется через джойник со временной табличкой или подзапросиком из пяти-шести записей

Не буду проверять. Этим я занимаюсь на работе и оптимизация запросов - это моя специализация. Я написал, то что знаю, а доказывать вам что-то у меня нет желания. Какие-то 5-6 subquery c join на временной таблице - я это не очень понял. Под временной таблицей вы понимаете inline view или, действительно, какую-то temporary table? Если последнее, то откуда она возьмётся?

[Leberecht]

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

[oleg lebedev]

Гы-гы, моя работа заключается ровно в том-же.
Ча счёт временной таблицы из 6ти записей - догадайтесь

Я не хочу чтобы вы думали, что я придираюсь к ваш словам, но я действительно, не понимаю вашей фразы: "Ча счёт временной таблицы из 6ти записей - догадайтесь". Что вы этим хотели сказать?

[Leberecht]

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

[oleg lebedev]

Это правда, что я вас не понимаю. Напишите своё query и я, наверное, пойму. Намёки намекать здесь не нужно, когда можно просто написать своё решение.

[Leberecht]

Enjoy

Code: Select all

create table BigWithManyColumns(AAA int, i1 int, i2 int, i3 int)

insert BigWithManyColumns values ...
...

Traditional way to "rotate structure":

Code: Select all

select AAA, i1 as i from BigWithManyColumns
union
select AAA, i2 as i from BigWithManyColumns
union
select AAA, i3 as i from BigWithManyColumns

Alternative way:

Code: Select all

create table #T (i int)
insert #T values (1)
insert #T values (2)
insert #T values (3)


select a, i= case b.i 
		when 1 then a.i1 
		when 2 then a.i2 
		when 3 then a.i3 
 	end

from BigWithManyColumns a cross join #T b

drop table #T

и всего-т делов

[oleg lebedev]

Вряд ли это будет быстрее.
Иногда я видел такие конструкции с CASE , WHEN и заменял их UNION, то они работали существенно: в 2-3 раза быстрее. Обратного не помню.
Потом, как я понял из вашего примера, ваш опыт большей частью в SQL Server или Sybase, а это не совсем Оракл.

Code: Select all

Enjoy

К, сожалению не было основания для еnjoy.

[Leberecht]

Подзаливаете Вы, говоря "я видел такие конструкции".
Ибо, каб видели, не писали-б "Какие-то 5-6 subquery c join на временной таблице - я это не очень понял.", ибо ентот метод если не элементарный, то один из базовых.

На "CASE WHEN" ресурсов практически не тратится, зато за счёт такого джоника, большая таблица всяк будет выбрана один разъединственный разик, с оптимизатором или без него, просто каждая запись будет размножена, вот и всё.

Я действительно написал это на синкаксисе SQL Server, ибо у меня их нонче что-т около трёх сот серваков с десятками тыщ баз данных на них, ну и соответственно парой-тройкой сотен терабайт данных.
Оракл серваков раза в два поменьше будет, Сайбэйзов и того меньше.

Так что вместо предположений "Вряд ли это будет быстрее" просто поверьте мне на слово