Простой 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, ибо у меня их нонче что-т около трёх сот серваков с десятками тыщ баз данных на них, ну и соответственно парой-тройкой сотен терабайт данных.
Оракл серваков раза в два поменьше будет, Сайбэйзов и того меньше.

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

[oleg lebedev]

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

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

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

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

Конечно базовый, но я вас плохо понимал в силу того что вы ничего конкретного не говорили, а предлагали играть в угадайку. К тому же вы тае и не написали, как это будет выглядить на Оракле из чего я понял, что вы сами не знаете, несмотря на то что вы сами говорите, что это "базовый метод". Но меня это не удивляет, т.к. свою квалификацию вы измеряете в количестве "серваков с десятками тыщ баз данных на них".
Вы мне напомнили одного чела, который рассказывал про то какая его жена крутая программистка. Там квалификация выражалась в количестве символов, печатаемых на клавиатуре.
Так чем вы на работе занимаетесь, backup-ом ваших терабайтов? Задача непростая, но к написанию queries имеет малое отношение. А если нет, то при чём здесь "пара-тройка сотен терабайт данных"?
Напишите, как будет выглядить на Оракле предложенный вами же "базовый метод" в виде SQL и тогда можно поверить, что вы имеете какое-то отношение к написанию queries. Несмотря на то, что "Оракл серваков раза в два поменьше", но этого всё равно должно хватить у вас для описания "базового метода".

[metaller]

/me взял попкорн и занял место в первом ряду

[oleg lebedev]

Можно я рядом присяду? Я тоже хочу насладиться query от крутого чувака с "тыщами баз данных".

[Leberecht]

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

Похоже, явно не для Вас он базовый.

Хотя для большинства спецов в базах данных растолковывать подобное, всё равно, что шахматисту после фразы "детский мат" пояснять, как он делается

А Вам тут и разжевали даже http://forum.privet.com/viewtopic.php?p ... 9#p5401439
А вы всё равно "непонятно, непонятно! квери напиши "

Ладненько, учите лучше мат часть

[oleg lebedev]

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

Похоже, явно не для Вас он базовый.

Хотя для большинства спецов в базах данных растолковывать подобное, всё равно, что шахматисту после фразы "детский мат" пояснять, как он делается

А Вам тут и разжевали даже http://forum.privet.com/viewtopic.php?p ... 9#p5401439
А вы всё равно "непонятно, непонятно! квери напиши "

Ладненько, учите лучше мат часть

Ладненько, учите лучше мат часть

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

Если же касаться всего остального, то я не буду отвечать на каждую вашу фразу по 3 причинам:
1. Я не всё понимаю, что вы там написали
2. Я не хочу ставить вас в неловкое положение.
3. Я не хочу тратить больше своё время на то что уже не имеет смысл.

Чтобы cut crap, то ответьте в одной фразе, где query о которой вы столько булькали? А ещё лучше, где само query?

Во вторых, я бы не ставил вас в неловкое положение если бы вы сами добровольно не полезли в заLUпу, и не спровоцировали меня на конкретные вопросы, на которые у вас не было конкретных ответов. Я не отношусь к категории людей, получающих удовольствие от унижения других. Поэтому, когда вы предоставили свой код, то я понял с кем имею дело. Вы там где-то сидите в тёмном уголке и проявляете свои skills, когда нужно восстановить файлы, когда кто случайно их удалил. Иначе вы бы не меряли свою квалификацию в терабайтах, хранящихся на каких то серверах.
Вы по всей видимости, даже не подозревали, что temporary table в Оракле и SQL Server - это совершенно разные структуры, имеющие мало общего между собой.
Я не стал вас "дожимать" в этом вопросе, и вы могда уйти и сохранить своё лицо. Вместо этого вы пытались унизить меня и возвысить себя опираясь на то что у вас "тыщы баз данных". Для меня это звучало, как признание, что написанием кода вы не занимались. Никто из программистов не меряет свои достижения такими категориями.
Когда я решил после этого расставить точки над i и спросить про конкретный код, то как и ожидал получил невнятный лепет. Кода не получил, т.к. у вас нет необходимых знаний, опыта и понимания как работает Оракл.
Поэтому не надо ничего писать добавочно, т. к самое лучшее, что вы могли написать, вы уже написали.

Ладненько, учите лучше мат часть

Следуйте своим же рекомендациям и будет вам счастье.
Не уверен, что я захочу вам отвечать больше.

[Leberecht]

Со смеху помираю: сперва изрядно вляпались на незнании элементарщины из разряда "детский мат", а сейчас "кулаками после драки"

[oleg lebedev]

Со смеху помираю: сперва изрядно вляпались на незнании элементарщины из разряда "детский мат", а сейчас "кулаками после драки"

До того как умрёте, где query для Оракл?

[Leberecht]

Да угомонитесь-же наконец, "коллега"
Ент типа "А как этот-же детский мат, но чёрными сделать?"

Я там в Вашу много-абзацовую речь не вчитывался особо.
Надеюсь, Вы там не "вляпались по-второй", заявляя, что подобная затея на PL/SQL не реализуема?

[metaller]

заявляя, что подобная затея на PL/SQL не реализуема?

Да при чём тут pl/sql ? Это процедурный язык и речь не о нём. Ждем решения поставленной задачи для Oracle от великого гуру Leberecht.

[Leberecht]

Народ, да вы попросту поиздеваться, поприкалываться в воскресение вечером порешили что-ли?

Ну ладно, получайте вашу ораклёвую версию

Ент понятно для создания и заполнения якобы большой таблички с кучей записей, которую надо "развернуть"

Code: Select all

create table BigWithManyColumns(AAA int, i1 int, i2 int, i3 int);
insert into BigWithManyColumns values(100,10,20,30);
insert into BigWithManyColumns values(200,10,20,30);
commit;

А вот собственно и "разворот"

Code: Select all

select a.aaa,  
    case b.key when 1 then I1 when 2 then I2 when 3 then I3 end   as I
from BigWithManyColumns a
    cross join
        (select 1 as key from dual
        union
        select 2 from dual
        union
        select 3 from dual) b
 order by a.aaa;

Пследний "order by a.aaa" само собой чисто для наглядности.

Ровно так, как я в самом первом своём ответе здесь и написал
" создаёте временную табличку (ну или попросту саб-квери из 5 или скольки там рекордов) и с ней джойните большую."

Спокойной ночи всем!