Ну по поводу обычной группировки: если start_date=01.01.2010 а end_date=01.10.2010 то нужно чтобы во всех десяти месяцах запись была учтена. В обычной группировке так не получится, более того если записей нет с начальной или конечно датой за определенный месяц, то этот месяц вообще не будет выведен.
Кроме того, я зачем условие "DATE_FORMAT(t.start_date, '%m') = DATE_FORMAT(t.end_date-1, '%m')"? В задаче не говорилось, что длительность записи всего 1 день. Более того, в таком случае смысла end_date вообще нет…
В чем он работает неправильно? Единственно, что в нем нужно заменить ">" на ">=".
И в какой ступор вы впадаете увидев:
SELECT
sum(CASE when t.`start_date`<'2010-02-01' and t.end_date>='2010-01-01' then 1 else 0 end) AS jan,
sum(CASE when t.`start_date`<'2010-03-01' and t.end_date>='2010-02-01' then 1 else 0 end) AS feb,
sum(CASE when t.`start_date`<'2010-04-01' and t.end_date>='2010-03-01' then 1 else 0 end) AS mar,
sum(CASE when t.`start_date`<'2010-05-01' and t.end_date>='2010-04-01' then 1 else 0 end) AS apr,
sum(CASE when t.`start_date`<'2010-06-01' and t.end_date>='2010-05-01' then 1 else 0 end) AS may,
sum(CASE when t.`start_date`<'2010-07-01' and t.end_date>='2010-06-01' then 1 else 0 end) AS jun,
sum(CASE when t.`start_date`<'2010-08-01' and t.end_date>='2010-07-01' then 1 else 0 end) AS jul,
sum(CASE when t.`start_date`<'2010-09-01' and t.end_date>='2010-08-01' then 1 else 0 end) AS aug,
sum(CASE when t.`start_date`<'2010-10-01' and t.end_date>='2010-09-01' then 1 else 0 end) AS sep,
sum(CASE when t.`start_date`<'2010-11-01' and t.end_date>='2010-10-01' then 1 else 0 end) AS oct,
sum(CASE when t.`start_date`<'2010-12-01' and t.end_date>='2010-11-01' then 1 else 0 end) AS nov,
sum(CASE when t.`start_date`<'2011-01-01' and t.end_date>='2010-12-01' then 1 else 0 end) AS dec
FROM test t
Функция изначально будет работать дольше. Наоборот, она не сложная, она просто не нужна — достаточно одного простого case. В крайнем случае, если уж сильно хочется, то могли бы создать процедуру с курсорами, которая делала бы сложные однопроходные выборки, которые нужны. Или создали бы аггрегирующую UDF.
В поставленной задаче требуется лишь один проход, так что 12 проходов явно будет дольше, если таблица не секционирована по датам так чтобы запросы можно было выполнять параллельно, но это уже отдельная и достаточно большая тема.
И, кстати, функция не нужна. Достаточно «case when then» — так будет быстрее.
пример:
SELECT
sum(
CASE
when t.`start_date`<'2010-02-01' and t.end_date>'2010-01-01'
then 1
else 0
end
)
AS jan,
sum(
CASE
when t.`start_date`<'2010-03-01' and t.end_date>'2010-02-01'
then 1
else 0
end
)
AS feb,
sum(
CASE
when t.`start_date`<'2010-04-01' and t.end_date>'2010-03-01'
then 1
else 0
end
)
AS mar
...
FROM test t
В таких случаях лучше сначала генерировать таблицу для группировки. Покажу на примере для 10 месяцев с начала 2010:
--set @rownumber:=0;
select
case
when @rownumber is null
then @rownumber:=1
else @rownumber:=@rownumber+1
end n,
DATE_FORMAT(
date_add('2010-01-01', interval @rownumber month),
'%Y.%m') month
from
information_schema.columns t
limit 10
Здесь используется просто в качестве генератора строк табличка information_schema.column, которая значения не имеет — я ее использую просто как пустышку. Первая закомментированная строка должна быть выполнена для обнуления переменной-счетчика.
Получим:
Теперь эту таблицу вы можете сджойнить с вашей таблицей по вашим условиям. В случае, если заранее не знаете необходимого кол-ва месяцев(строк), то добавьте условия минимальной и максимальной даты.
Собственно, я про это и сказал в предыдущем комментарии, но раз уж настаиваете, то давайте разберемся для данного конкретного случая:
Если вы все-таки хотите использовать триггер и получаете ora-54, у вас два варианта:
увеличить ddl_lock_timeout
повторно выполнять ddl до выполнения
Пояснения: DDL будет выполнен, как только закончатся текущие блокирующие транзакции. Сама же процедура создания новой пустой секции очень быстрая, она гораздо быстрее разбиения maxvalued или default секции. Те же транзакции, которые будут начаты во время ddl, будут выполнены сразу после этой процедуры.
Следующий вариант — автоматизированное создание секций в определенное время. Например, если ночью у вас значительное снижение нагрузки, то вы можете создать задание, в котором пройтись циклом по результату функции get_maxvalued_partitions, создавая секции для каждой из возвращенных таблиц.
Если же и этот вариант вас не устраивает, то вы можете как я и описал, просто настроить джоб с уведомлением ДБА с помощью send_partitions_report
Спасибо за напоминание, забыл обновить скрипт — я позже его изменял на select count(1) where rownum<2, т.к. проверка нужна существования хоть одной записи. И добавил пункт про сбор статистики, хотя это гораздо более дорогостоящая операция, но в случае если статистика нужна не только для этого(для CBO, например), то использование um_rows действительно все упрощает :)
Совершенно верно, если используются сиквенсы пропуски будут, т.к. используется шаг для кэширования. В таких случаях придется устанавливать when (mod(NEW.id,10000) between 6000 and 6100) — соответственно триггер будет вызываться н-ое количество «лишних» раз, но выполняться будет быстро.
Вообще большой незачет oracle за то, что не придумали механизм формирования нормального имени новой автоматически добавляемой партиции.
Ну мне кажется это не особо важно, все равно при автоматизации брать данные из data dictionary.
В литературе? Например, в каких книгах? Все книги, которые я читал содержат секционирование. Транслитированное «партицирование» используют крайне редко и только на форумах.
fyi:
«партицирование таблиц» — Результатов: примерно 1 470
«секционирование таблиц» — Результатов: примерно 19 700
Считаю что могут и получше, но, поверьте, установщик oracle db это совсем не «лицо» продукта. Вы когда поработаете побольше с oracle, поймете сколько у него всяких вкусных плюшек, а возможностей конфигурирования столько, что все эти инсталляторы и прочие гламурности это только на потеху.
Кроме того, я зачем условие "
DATE_FORMAT(t.start_date, '%m') = DATE_FORMAT(t.end_date-1, '%m')"? В задаче не говорилось, что длительность записи всего 1 день. Более того, в таком случае смысла end_date вообще нет…И в какой ступор вы впадаете увидев:
Тут не более страшно, чем у вас. С If будет еще и короче.
пример:
Здесь используется просто в качестве генератора строк табличка information_schema.column, которая значения не имеет — я ее использую просто как пустышку. Первая закомментированная строка должна быть выполнена для обнуления переменной-счетчика.
Получим:
Теперь эту таблицу вы можете сджойнить с вашей таблицей по вашим условиям. В случае, если заранее не знаете необходимого кол-ва месяцев(строк), то добавьте условия минимальной и максимальной даты.
Пояснения: DDL будет выполнен, как только закончатся текущие блокирующие транзакции. Сама же процедура создания новой пустой секции очень быстрая, она гораздо быстрее разбиения maxvalued или default секции. Те же транзакции, которые будут начаты во время ddl, будут выполнены сразу после этой процедуры.
Все эти варианты я описал в статье.
Ну мне кажется это не особо важно, все равно при автоматизации брать данные из data dictionary.
fyi:
Кроме того:
ru.wikipedia.org/wiki/Секционирование а не партицирование
oracle.com — используют только «Секционирование»
msdn.microsoft.com — используют только «Секционирование»
Postgresql.org — используют только «Секционирование»
Я еще вот такую задачку гольфил на с :)