Oracle Spatial. Пример 2

Проверим аналитические возможности модуля Oracle Spatial для решения маркетиновых задач.

Легенда:

Среди крупнейших сетей супермаркетов в Москве выберем две -- Пятёрочку и Седьмой континент (коллеги подсказывают, что эти две сети не похожи по формату и в реальной жизни они между собой не конкурируют, но сейчас для нас это не имеет значения -- подставив адреса магазинов других сетей мы получим реальные результаты, а это -- только пример).

Магазины этих сетей находятся в спальных районах, в окружении жилых домой.
Жителям приходится выбирать, в какой магазин идти. Скорее всего, жители пойдут в ближайший.

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

Как мы будем их искать?

1. Найдём пары магазинов разных сетей, расположенных на расстоянии не более 2 км друг от друга.
2. Определим расстояние между такими парами магазинов.
3. Нарисуем на карте окружности, с центрами в каждом магазине пары и радиусом в 2 / 3 от расстояния между магазинами пары.
4. Пересечение окружностей будет иметь вид "мяча для регби". Дома, находящиеся внутри этой области, равноудалены от обоих магазинов. Маркетинговые усилия нужно сосредоточить на жителях именно этих домов.
5. Определим адреса домов и напечатаем карту -- рекламные листовки нужно бросать в первую очередь в почтовые ящики этих домов. Если мы располагаем базой покупателей (адреса для выданных дисконтных карт) -- то лучшие скидки предлагаем имено таким покупателям.

Данные импортированы с сайта GisLab http://gis-lab.info/projects/osm_dump/, когда-то Александр Рындин (Oracle, http://www.oraclegis.com/blog/?p=2629) рекомендовал этот источник как открытый

Вот результат выполнения расчёта:

Жёлтый цвет -- крупные магистрали,
Чёрный -- границы Москвы,
Синие маркеры и пунктирные окружности -- отметки магазинов Седьмой континент,
Красные маркеры и окружности -- магазины сети Пятёрочка

Вот как выглядит увеличенное изображение карты:
Не надо регулировать ваши мониторы -- круги не круглые из-за того, что их MapBuilder неправильно отображает

Ещё крупнее, со всеми номерами домов

Эта карта с номерами домов -- прямое руководство к действию. Сосредотачиваем маркетинговые усилия на этих территориях.

 

Некоторый опыт работы с Big Data

Согласно отчёту McKinsey в ближайшее время планируется рост объёма данных 40% в год, при росте затрат на IT лишь в 5% год.
http://www.mckinsey.com/~/media/McKinsey/dotcom/Insights%20and%20pubs/MGI/Research/Technology%20and%20Innovation/Big%20Data/MGI_big_data_exec_summary.ashx
(Весьма популярный документ по цитируемости в инете)

В последнее время пришлось столкнуться с задачей, которая касалась большого объёма данных. Что же с ними делать ?

Некоторые размышения:

Обычно предприятия сталкиваются с тремя видами (условно) данных (из http://www.oraclegis.com/blog/?p=2501):

1. Традиционные корпоративные данных – включая информацию о клиента из CRM-систем, транзакционные данные из ERP, транзакции веб-магазинов, записи главной бухгалтерской книги
2. Сгенерированные машинами данные/данные с датчиков – например, Call Detail Records (“CDR”), журналы веб-серверов, умные датчики, датчики на производстве, журналы различного оборудования, данные с биржевых систем
3. Социальные данные – например, отзывы клиентов, сайты микро-блогов таких как Twitter, социальных сетей таких как Facebook, ВКонтакте

Я остановлюсь на втором типе из этой классификации.

Столкнувшись с данными такого типа, нужно принять решение, как их хранить.

Этот тип можно условно разделить на 2 подтипа:

а) данные, которые точно понадобятся

б) данные, о которых неизвестно, понадобятся ли они

 

Моё мнение таково:

Для типа а) -- данные обогащать.
Согласно Knowledge management -- получать информацию из данных. Обычно это возможно. Нужно всего лишь отбросить лишнее (то, в чём мы уверены, что это лишнее), а оставшиеся данные -- нормализовать и положить в реляционную структуру.

Лишнее -- может быть как по горизонтали (повторяющийся текст), так и по вертикали (строки, не несущие смысловой нагрузки).
Обогащать данные нужно для того, чтобы скормить их потом какой-либо аналитической системе либо отчётам. Никакая аналитическая система (насколько я знаю) не примет на вход текст (например, строку лога web-сервера или лога smtp). Чтобы аналитическая система поняла текстовые данные, их нужно преобразовать к реляционному виду.
Так лучше сделать это один раз и хранить нормализованные данные, чем транслировать данные каждый раз, когда выполняется отчёт. Это сэкономит и время и место.

Нормализовать -- здесь ключевой тезис. Как нормализовать -- в одном из следующих постов.

 

Ну а для типа б) -- хранить, если нужно. Это как чемодан с оторванной ручкой -- и нести неудобно, и выбросить жалко.

Но нужно постараться привести тип б) к типу а)

 

Вернусь к первой цитате:
"В ближайшее время планируется рост объёма данных 40% в год, при росте затрат на IT лишь в 5% год."
Считаю, что правильным решением в условиях роста объёма данных при недостатке инвестиций будет максимизировать фондооотдачу от ИТ (от средств хранения и обработки данных).
Я применяю термин "фондоотдача", хотя в ИТ укрепился термин ROI. Термин "фондоотдача" мне импонирует тем, что допускает качественную оценку, в то время, как ROI -- необходимо считать по формуле.
Формула, в общем то, проста -- прибыль к затратам с дисконтированием -- но затраты посчитать можно, а вот прибыль в ИТ -- затруднительно.
Поэтому нужно искать существующие резервы в уже внедрённом софте, и реализовывать их --  такие резервы обычно находятся, если их как следует поискать. Реализовав резервы фондоотдача определённо возрастает, а вот ROI уже не имеет значения, после того, как первичный проект по внедрению был принят к реализации и реализован.
 

Как сослаться на PARTITION в операторе DML через переменную ? Способы

Задача: Указать название partition в виде переменной в операторе DML. Например

update INVENT_ITEMS partititon (p_Partition_Name) set QUANTITY = 0;

Я столкнулся с этим, когда мне в хранимую процедуру для апдейта партиционированной таблицы имя раздела поступало в виде строки через Advanced Queing (AQ).

Что показал анализ: штатными средствами указать название partition в виде переменной невозможно. Дискуссия была на сайте Тома Кайта, ссылка внизу.

Решение:

1. Использовать DATAOBJ_TO_PARTITION (функция из Data Cartridge), на мой взгляд -- предпочтительный вариант. Я стараюсь использовать его. Я проверил этот вариант для всех пяти типов партиционирования HEAP-таблиц -- работает везде.
2. Определить границы partition по ALL_TAB_PARTITIONS.HIGH_VALUE и построить фразу WHERE (тоже вариант)
3. Использовать динамический SQL (но он обычно хуже оптимизируется)
4. Использовать CASE (если количество partition ограничено и невелико)

Убедимся, что указание названия partition в виде переменной не работает

Создадим таблицу, строки можно не добавлять

SYS@ORTE> create table T (N number) partition by hash (N) (partition P1, partition P2, partition P3, partition P4);
Table created.

Обычный update для одной partition работает:
SYS@ORTE> update T partition (P1) set N = N + 1;
0 rows updated.

Укажем название строки в виде литерала -- не работает
SYS@ORTE> update T partition ('P1') set N = N + 1;

update T partition ('P1') set N = N + 1

                   *

ERROR at line 1:

ORA-00971: missing SET keyword

Укажем название строки в виде переменной -- не работает
SYS@ORTE> var PARTNAME varchar2(8);

SYS@ORTE> exec :PARTNAME := 'P1';

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00

SYS@ORTE> print :PARTNAME

PARTNAME

--------------------------------

P1

SYS@ORTE> update T partition (:PARTNAME) set N = N + 1;
update T partition (:PARTNAME) set N = N + 1

                    *

ERROR at line 1:

ORA-14108: illegal partition-extended table name syntax

Elapsed: 00:00:00.02
В коде PL/SQL ошибка будет точно такая-же.

Решение с помощью DATAOBJ_TO_PARTITION (я выполняю в SQLPLUS, но в PL/SQL тоже будет работать).

Определим ID сегмента данных partition P1;
SYS@ORTE> select DATA_OBJECT_ID from ALL_OBJECTS where OWNER = 'SYS' and OBJECT_NAME = 'T' and SUBOBJECT_NAME = 'P1';
DATA_OBJECT_ID

--------------

         77140

Elapsed: 00:00:00.09

Создадим связываемую переменную и сошлёмся на partition через ID сегмента.
SYS@ORTE> var PART_SEG_ID number;

SYS@ORTE> exec :PART_SEG_ID := 77140
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00

SYS@ORTE> update T partition (DATAOBJ_TO_PARTITION (T, :PART_SEG_ID)) set N = N + 1;
0 rows updated.
Elapsed: 00:00:00.02

Это работает. В PL/SQL будет работать тоже
Обратите внимание, что название таблицы не надо заключать в кавычки
Полная дискуссия -- здесь http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:953139476145

Оперативное сжатие ARCHIVELOG на NTFS

Если Oracle работает под Windows и в ARCHIVELOG режиме (в мирное время не существует достаточных оснований для работы БД в режиме NOARCHIVELOG), и архивлоги лежат в FAST_RECOVERY_AREA на NTFS, то для папки ARCHIVELOG можно установить NTFS-атрибут COMPRESSED.

Архивлоги обычно сжимаются в 2 раза, нагрузка на сервер не возрастает (а иногда снижается, поскольку у процессора обычно есть резерв, а у дисков -- нет).

При этом нельзя делать размер REDO-логов больше 8GB -- когда лог заархивируется, то к файлам больше 8GB на NTFS сжатие не применяется.
Установив атрибут COMPRESSED на папку ARCHIVELOG можно указать размер FAST_RECOVERY_AREA в 2 раза больше, чем объём диска, на котором она расположена.
Это параметр DB_RECOVERY_FILE_DEST_SIZE
http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams065.htm#REFRN10235

Не устанавливайте атрибут COMPRESSED на папку BACKUPSET -- эффект будет хуже, чем если выполнять сжатие средствами RMAN -- (compressed backupset) как написано в посте Оптимальное нарезание файлов бэкапов (откроется в новом окне)

Компрессия файлов ARCHIVELOG (как для BACKUPSET) ожидается в версии 12.

Oracle Spatial - это просто ! Решаем, где будем ужинать сегодня :-)

Начать работу со Spatial -- очень легко. Я потратил на это один день. Но это только "начать", модуль серьёзный, для серьёзных задач -- разбираться придётся глубоко.

Spatial -- пространственные данные (ГИС). Модуль позволяет работать с плоскими и трёхмерными объектами (точки, линии, многоугольники, поверхности и пр.) и выполнять над ними операции -- найти пересечения, найти ближайшие и пр.
Пространственные данные хранятся как тип MDSYS.SGO_GEOMETRY. База при этом не перестаёт быть реляционной
Для информации -- http://ru.wikipedia.org/wiki/Пространственная_база_данных

Я поясню на следующих примерах:

1. Найдем ближайшие рестораны, места развлечений, достопримечательности к трем точкам: дому, офису и заказчику. Координаты своих мест вы наберете сами -- или пользуйтесь моими дефолтными (для обзора модуля этого хватит).
2. Найдем все объекты внутри Садового кольца (изменив условие, получим ВНЕ).

Список объектов далее -- это только пример, этот список неполный и может быть устаревшим. Я собрал его всего лишь из двух источников в Интернете -- из двух KML файлов.

I. Пробуем под любым пользователем
Например, можем создать SPTEST
create user SPTEST identified by SPTEST default tablespace USERS;
grant connect, resource, create view, unlimited tablespace to SPTEST;

II. Выполняем скрипт
Скрипт здесь http://yaroslavbat.narod.ru/sql/spatial_test.sql
sqlplus SPTEST/SPTEST@ORTE_LOCALHOST @spatial_test

Создание таблиц завершено. Делаем отчёты:

III. Ищем ближайшие объекты к дому, офису, заказчику (по три ближайших объекта).

select * from SP_NEAREST_PLACES_V

Выборка отсортирована по типу стартовой точки, типу объекта и возрастанию расстояния в метрах

POINT_ TYP      PLACE_NAME              ADDRESS                 DISTANCE
------ -------- ----------------------- ----------------------- --------
1.Home Развлече Бутик "Конфаэль" на Про ул.Вавилова, д.66 стр.1 3364   
                Нескучный сад           Ленинский проспект      4242   
                Пушкинская набережная   Пушкинская наб.         4456   
       Ресторан Ресторан "Султан"       Орджоникидзе ул., д.3   3032   
                Ресторан "Скай Лонж"    Ленинский пр., д. 32а   3405   
                Кафе "Мята"             Лужнецкий проезд, д. 1А 5537   
2.Offi Развлече Фирменный магазин «Рот  2-й Новокузнецкий переу 895    
                Лужков мост             м. Третьяковская  напро 2076   
                Болотная площадь        Болотная площадь        2224   
       Ресторан Ресторан "Семь пятниц"  Воронцовская ул., 6     1117   
                Ресторан "Море времени" Б. Полянка., д. 51 А/9  1434   
                Ресторан "Балчуг"       Балчуг ул., д. 1        2121   
3.Cust Развлече Патриаршие пруды        Патриаршие пруды        476    
                Московский зоопарк      Б. Грузинская, 1        742    
                Салон приключений и под ул. Тверская, д.22а, ст 913    
       Ресторан Кафе "Воск"             ул. Красина, д. 7, стр. 51     
                Ресторан "China Club"   Красина ул., 21         181    
                Ресторан "Грандъ Алекса Тверская ул., д. 27/1   639    

IV. Получаем список объектов внутри Садового кольца

select * from SP_PLACES_INSIDE_V

Обратите внимание, что в тексте этого VIEW есть лишний DECODE. Но без него не работает. Похоже баг.

where decode (<FUNCTION>, 'TRUE', 'TRUE', 'FALSE', 'FALSE') = 'TRUE'

Исправьте в тексте запроса условие на  = 'FALSE' и получим список ВНЕ Садового кольца

to be continued...

Создаём свою агрегатную функцию

Мы можем разрабатывать собственные агрегатные функции.
Расскажу на реальном на примере:

Задача: посчитать суммарную годовую инфляцию по годам на основании месячных данных.
Статистические данные по инфляции приводятся в процентах по отношению к прошлому месяцу.

Штатные агрегатные функции не подходят, SUM -- суммирует, AVG -- суммирует и делит, нам нужна фукция MUL (multiplication), чтоб перемножить проценты.

Пишем функцию сами:

1. Создаем тип

create type MulImpl as object (
AccValue number,
static function ODCIAggregateInitialize (sctx in out MulImpl)
return number, -- инициализирует значение агрегата перед началом обработки

member function ODCIAggregateIterate (self in out MulImpl, value in number)
return number, -- обновляет значение агрегата для новой строки данных

member function ODCIAggregateTerminate (self in MulImpl, returnValue out number, flags in number)
return number, -- возвращает значение агрегата и завершает обработку

member function ODCIAggregateMerge (self in out MulImpl, ctx2 in MulImpl)
return number -- используется для поддержки параллельных вычислений агрегата, если параллелизма нет -- без неё всё равно не работает
);

2. Создаем тело типа

create or replace type body MulImpl is
static function ODCIAggregateInitialize(sctx in out MulImpl)
return number is
begin
  sctx := MulImpl(1);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self in out MulImpl, value in number) return number is
begin
  self.AccValue := self.AccValue * value;
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self in MulImpl, returnValue out number, flags in number) return number is
begin
  returnValue := self.AccValue;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self in out MulImpl, ctx2 in MulImpl) return number is
begin
  self.AccValue := self.AccValue * ctx2.AccValue;
  return ODCIConst.Success;
end;

end;
/
Цветом выделены строки, выполняющие собственно расчёт

3. Создаем собственно функцию

create or replace function MUL (input number) return number deterministic aggregate using MulImpl;

Слово DETERMINISTIC означает, что результат функции зависит только от её аргументов и не зависит ни от чего вне функции (не обращается к системной дате, последовательностям, не читает БД и пр.) -- при одних и тех-же аргументах будет один и тот-же результат. Oracle попытается прокешировать эту функцию -- в некоторых случаях результат будет не вычислять, я брать из кэша.

Можно включить фразу PARALLEL_ENABLE (очень актуально для хранилищ), но сейчас у меня нет возможности протестировать массивный параллелизм.

4. Для тестирования создаём таблицу:

create table INFL (MON date, INFL_VAL number)
/

5. Заполняем
(данные взяты отсюда http://ukrstat.gov.ua/operativ/operativ2006/ct/cn_rik/isc/isc_u/isc_m_u.htm):

insert into INFL (MON, INFL_VAL) values (to_date ('01.01.2010', 'DD.MM.YYYY'), 101.8);
insert into INFL (MON, INFL_VAL) values (to_date ('01.02.2010', 'DD.MM.YYYY'), 101.9);
insert into INFL (MON, INFL_VAL) values (to_date ('01.03.2010', 'DD.MM.YYYY'), 100.9);
insert into INFL (MON, INFL_VAL) values (to_date ('01.04.2010', 'DD.MM.YYYY'), 99.7);
insert into INFL (MON, INFL_VAL) values (to_date ('01.05.2010', 'DD.MM.YYYY'), 99.4);
insert into INFL (MON, INFL_VAL) values (to_date ('01.06.2010', 'DD.MM.YYYY'), 99.6);
insert into INFL (MON, INFL_VAL) values (to_date ('01.07.2010', 'DD.MM.YYYY'), 99.8);
insert into INFL (MON, INFL_VAL) values (to_date ('01.08.2010', 'DD.MM.YYYY'), 101.2);
insert into INFL (MON, INFL_VAL) values (to_date ('01.09.2010', 'DD.MM.YYYY'), 102.9);
insert into INFL (MON, INFL_VAL) values (to_date ('01.10.2010', 'DD.MM.YYYY'), 100.5);
insert into INFL (MON, INFL_VAL) values (to_date ('01.11.2010', 'DD.MM.YYYY'), 100.3);
insert into INFL (MON, INFL_VAL) values (to_date ('01.12.2010', 'DD.MM.YYYY'), 100.8);
insert into INFL (MON, INFL_VAL) values (to_date ('01.01.2011', 'DD.MM.YYYY'), 101); 
insert into INFL (MON, INFL_VAL) values (to_date ('01.02.2011', 'DD.MM.YYYY'), 100.9);
insert into INFL (MON, INFL_VAL) values (to_date ('01.03.2011', 'DD.MM.YYYY'), 101.4);
insert into INFL (MON, INFL_VAL) values (to_date ('01.04.2011', 'DD.MM.YYYY'), 101.3);
insert into INFL (MON, INFL_VAL) values (to_date ('01.05.2011', 'DD.MM.YYYY'), 100.8);
insert into INFL (MON, INFL_VAL) values (to_date ('01.06.2011', 'DD.MM.YYYY'), 100.4);
insert into INFL (MON, INFL_VAL) values (to_date ('01.07.2011', 'DD.MM.YYYY'), 98.7);
insert into INFL (MON, INFL_VAL) values (to_date ('01.08.2011', 'DD.MM.YYYY'), 99.6);
insert into INFL (MON, INFL_VAL) values (to_date ('01.09.2011', 'DD.MM.YYYY'), 100.1);
insert into INFL (MON, INFL_VAL) values (to_date ('01.10.2011', 'DD.MM.YYYY'), 100); 
insert into INFL (MON, INFL_VAL) values (to_date ('01.11.2011', 'DD.MM.YYYY'), 100.1);
insert into INFL (MON, INFL_VAL) values (to_date ('01.12.2011', 'DD.MM.YYYY'), 100.2);
commit;

6. Проверяем функцию как агрегатную

select '31.12.' || to_char (trunc (MON, 'Year'), 'YYYY') as YEAR_NUM, to_char (round (MUL (INFL_VAL / 100) * 100, 1), '999G999D0') as ACCUM_INFLATION
from INFL
group by trunc(MON, 'Year')

YEAR_NUM   ACCUM_INFLATION
---------- ---------------
31.12.2010      109.1    
31.12.2011      104.6    

7. Проверяем функцию как аналитическую

select to_char (last_day(MON), 'DD.MM.YYYY') as RMON
, to_char (INFL_VAL, '999G999D0') as MONTH_INFLATION
, to_char (round ((MUL (INFL_VAL / 100) over (order by MON range unbounded preceding) )* 100, 1), '999G999D0') as ACCUMULATED_INFLATION
from INFL
order by MON

Во второй колонке инфляция текущего месяца, в третьей -- накопленная с начала выборки.

RMON       MONTH_INFL ACCUMULATE
---------- ---------- ----------
31.01.2010      101.8      101.8
28.02.2010      101.9      103.7
31.03.2010      100.9      104.7
30.04.2010       99.7      104.4
31.05.2010       99.4      103.7
30.06.2010       99.6      103.3
31.07.2010       99.8      103.1
31.08.2010      101.2      104.3
30.09.2010      102.9      107.4
31.10.2010      100.5      107.9
30.11.2010      100.3      108.2
31.12.2010      100.8      109.1
31.01.2011      101.0      110.2
28.02.2011      100.9      111.2
31.03.2011      101.4      112.7
30.04.2011      101.3      114.2
31.05.2011      100.8      115.1
30.06.2011      100.4      115.6
31.07.2011       98.7      114.1
31.08.2011       99.6      113.6
30.09.2011      100.1      113.7
31.10.2011      100.0      113.7
30.11.2011      100.1      113.8
31.12.2011      100.2      114.1


Если в таблицу загрузить все данные с сайта, то при подсчёте итогов за год мы увидим расхождения в долях процентов с нашей функцией по некоторым годам. Это может быть связано со следующими причинами:
1. Возможно, нам надо в расчётные строки (которые выделил синим)  включить функцию TRUNC или ROUND, чтобы не считать слишком точно.
2. Потому, что на сайте укрстата последняя строка -- это не перемножение месяцев, а декабрь к прошлому декабрю.
3. Менялась методика расчёта.

Подробно -- Oracle® Database Data Cartridge Developer's Guide
http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/toc.htm
Главы 11 и 22
Также использовалась книга "Oracle9/R2. Разработка и эксплуатация хранилищ баз данных", Хоббс, Хилсон, Лоуенд

Не загнётся. Часть 2. Решение

"Архисложная" проблема "уложить 40 миллионов измерений в минуту" решается отказом от Первой нормальной формы БД.

Надо просто транспонировать исходную таблицу. Я попробовал в 200 раз

Там том же компьютере я уложил объём измерений одного часа (40 000 000 * 2 * 60 минут) за 12 минут.
При этом потенциал ускорения не исчерпан, не стал пробовать даже /*+APPEND_VALUES*/

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

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

Для решения этой задачи, весьма вероятно, можно применять и подходы Big Data (а "сырые" поступившие измерения таковыми и являются).

http://www.oracle.com/us/technologies/big-data/index.html

Не загнётся !

Встретил интересное сообщение в дискуссии
Речь, скорее, идёт о системе типа той, что собирает данные с электросчётчиков в Германии. Несколько десятков миллионов штук, данные отправляются ежеминутно (!). Ораклом там и не пахнет -- загнётся, болезный. Отсюда -- http://www.cnews.ru/news/top/index.shtml?2012/07/06/495657

Последнее утверждение автора мне показалось неочевидным.

Проверим на модели... Будем использовать мой десктоп, выделим Ораклу 1ГБ оперативы, датафайлы на одном диске, редологи -- на другом. Всё остальное по дефолту.

Легенда.
Имеем сервер, на которой поступают данные телеметрии (в нашем случае -- результаты измерений потребляемой мощности, с частотой 1 минута). В Германии 80 млн жителей. Автор пишет, что счетчиков там десятки милиионов штук. Будем считать, что 40 миллионов, что означает по одному счётчику на каждых двух жителей, включая младенцев.

Для заполнения таблиц будем использовать следующую процедуру. Добавляем 10 миллионов строк, измеряем время (многократно, усредняем)

procedure F is
  T timestamp with local time zone default systimestamp;
begin
  for i in 1..10000000
  loop

    insert into MEASUREMENTS1 (SNAPSHOT_TSZ, METER_ID, MEASUREMENT_VALUE)
    values (i, T, 500);
--    insert into MEASUREMENTS2 (SNAPSHOT_ID, METER_ID, MEASUREMENT_VALUE)
--    values (1, i, 500);

  end loop;
  commit;
exception when others then
  rollback;
  raise;
end;

Тест 1. Дефолтная таблица, причем без первичного ключа

create table MEASUREMENTS1
    (SNAPSHOT_TSZ       timestamp (0) with local time zone not null,
     METER_ID           number not null,
     MEASUREMENT_VALUE  number not null)
  pctfree 10 storage   (initial 65536 next 1048576)
  nocache monitoring noparallel logging

Результат -- 5 минут 25 секунд

Тест 2.
Приложение проектировали, данные старались уложить плотно, сессию записи вынесли в отдельную мастер таблицу, т.е. храним не длинный таймстэмп, а короткий ID сессии (минуты снятия показаний) загрузки. PCTFREE -- 0. Тип -- IOT, compress 1

create table MEASUREMENTS2
    (SNAPSHOT_ID        number not null,
     METER_ID           number not null,
     MEASUREMENT_VALUE  number not null,
     constraint MEASUREMENTS2_PKIOT primary key (SNAPSHOT_ID, METER_ID))
organization index compress 1 pctthreshold 50
pctfree 0 storage (initial 32M next 32M)
noparallel logging

Результат -- 6 минут. Это больше, чем в прошлом тесте, но у нас есть первичный ключ, плюс данные лежат плотно, за счёт COMPRESS 1. Плотно -- значит читаться будут быстрее.

Т.о. 40 млн записей будут добавятся за 24 минуты, а нам надо обеспечить 1 минуту, т.е. ускорить процесс в 24 раза.

Но пробовал я это на обычном десктопе с двумя винчестерами, без параллелизма и с 1 ГБ памяти. Причем диски не сильно и напрягались, вообще не слышно. Параллелизма не было, одно ядро процессора -- 100%, второе в айдле.

По моим ощущениям (предположениям), Exadata с таким объемом справится. И добавлять измерения, и прореживать, и отчеты строить, и прогнозы. Ведь писать в датафайлы надо всего лишь со скоростью 10 МБ в секунду (нетто). В чем же тут проблема ? Что должно загнуться ?


В системах подобного рода (SCADA) также используется "прореживание" данных: сегодня нам нужны минутные срезы, завтра -- пятиминутные. Через год -- часовые.
Одна строка измерений во второй таблице занимает 16 байт. В нашем гипотетическом примере за год это будет 305 ТБ БЕЗ "ПРОРЕЖИВАНИЯ".
Без прореживания -- это много.
А с прореживанием можно сократить объем раз в сто или больше. А это уже совсем немного.
Плюс, в Exadata есть гибридная колоночная компрессия.

Как итог, мне трудно согласиться с автором что оракл там "загнётся, болезный"
Подобная задача на Оракле вполне решаема, и всё это можно сделать компактно и аккуратно -- может быть даже и без Exadata.

Для особых скептиков -- посмотрите презентацию CERN
http://canali.web.cern.ch/canali/docs/Compressing_VLDS_Oracle_UKOUG09_LC_CERN.ppt

UPDATE
Есть решение для ускорения работыhttp://yaroslavbat.blogspot.com/2012/07/2.html

IOT, фактор кластеризации

Вывод: IOT-таблицы неоходимо проектировать и заполнять так, чтобы обращения к сегменту OVERFLOW происходили редко. Если мы выносим в сегмент OVERFLOW поля, к которым обращаемся всегда или часто, то при большом объёме данных скорость её чтения по мере заполнения будет падать лавинообразно. В таком случае IOT-таблица вырождается в обычную HEAP и её лучше было бы с самого начала создавать как HEAP.


Вот как это обнаружил...
Как известно, для B-tree индексов одним из параметров, которые определяются на сборе статистики, является фактор кластеризации (clustering factor). Этот числовой параметр показывает, сколько раз приходилось читать другой блок таблицы при переходе к следующему значению индекса.

Так вот, этот параметр оказался ненулевым для некоторых IOT-таблиц. Это стало для меня сюрпризом. Об этом не написано в документации.
Потребовалось некоторое время, чтобы сообразить, что имеется ввиду кластеризация сегмента OVERFLOW по отношению к основному сегменту IOT-таблиц.

Этот имеет практическое применение. Для больших таблиц время полного просмотра (с извлечением полей, которые находятся в OVERFLOW) и время сбора статистики будет расти лавинообразно и может стать неприемлемым -- сессия "зависнет" (так это и было обнаружено).
Большой таблицей здесь можно считать такую, которая не помещается полностью в буферный кэш.
Это связано с тем, что при переходе к следующему значению индекса в IOT сервер вынужден снова и снова читать блоки OVERFLOW, которые уже читал ранее. Пока блоки есть в кэше -- все нормально, но при достижении некоторого порога -- начнётся лавина.

На тестовом экземпляре при увеличении количества строк в 4 раза время сбора статисики увеличилось с 70 секунд до 2222 секунд (в 31 раз). Полный просмотр даёт тот-же результат

Как такие таблицы найти ?
select * from ALL_INDEXES
where INDEX_TYPE = 'IOT - TOP' and CLUSTERING_FACTOR > 0
Небольшое значение CF приемлемо.
Также, необходимо поискать в DBA_SEGMENTS размеры сегментов -- основного и OVERFLOW.

Как исправить ?
Фактор кластеризации и сама таблица и её OVERFLOW улучшаются (реорганизуются) если выполнить

alter table <iot_table_name> move
alter table <iot_table_name> move overflow

При этом IOT–таблицу НЕ придётся переиндексировать, поскольку B–tree индексы IOT–таблиц строятся на логических rowid, которые не изменяются при MOVE, в отличие от физических.

После подобной операции MOVE время сбора статистики и полных просмотров IOT–таблицы сокращается (возможно, очень существенно, в 5 раз, но зависит от данных).

SEG$

Мой некоторый опыт по этой таблице

Хронология такова:

Событие 1.

Пытаюсь дропнуть tablespace, получаю ORA-01561: failed to remove all objects in the tablespace specified
Хотя там точно ничего нет. DBA_SEGMENTS и DBA_EXTENTS пусты для этого тэйблспейса и его датафайлов.
Данная проблема решена и описана в http://www.sql.ru/forum/actualthread.aspx?tid=243553
Именно так её и решил -- удалил 2 записи из SEG$, тэйблспейс дропнулся
Но, отметил для себя, что такая проблема может быть не одна -- надо будет как-то поискать подобные проблемы. При этом DBV говорит, что все ок, backup validate check logical -- тоже ок

Событие 2.


Восстанавливался из бэкапа, сделал RESTORE, RECOVERY until SCN, прошло нормально.
Но в итоге получил более 100 corrupted блоков. Тип повреждения--NOLOGGING, поэтому, не страшно, но исправлять надо.

Часть блоков была занята данными -- поправил путем move на другое табличное пространство и обратно. Починилось.

Но некоторая часть битых блоков, согласно DBA_EXTENTS, была распределена под такой сегмент, для которого _не_ существовало таблицы.
А дело было в чем.
Это была IOT-таблица.
Я их именую определенным образом, если таблица называется QDEMANDS, то её первичный ключ -- QDEMANDS_PKIOT.
Я вижу экстент сегмента QDEMANDS_PKIOT, пробую подвинуть таблицу
alter table move QDEMANDS tablespace <другое TS>
вижу, что такой таблицы нет. Я предположил, что опять столкнулся с ситуацией, которую описал в "событии 1". Ну и решил её таким же способом. Я жестоко ошибался.
Таблица от этого сегмента называлась QDEMAND, я её переименовывал.

При обычном экспорте на этой таблице получил EXP-00003: no storage definition found for segment(number, number).
Интернет рекомендует решать эту проблему вот так
http://www.dbacorner.com/2007/02/exp-00003-no-storage-definition-found.html

Я посчитал данный метод опасным.
Восстановил удалённую строку из SEG$ путём flashback

select * from SEG$ as of timestamp systimestamp - numtodsinterval (1, 'HOUR')
minus
select * from SEG$

Там оказалось 6 строк, свою удалённую я узнал, и сделал её insert обратно в SEG$
Проблему экспорта решил, таблицу помувал туда-сюда и её блоки перестали быть CORRUPTED NOLOGGING

Мощная вещь -- flashback

Не лишне будет напомнить, что самостоятеьно редактировать SEG$ -- последнее дело. Надо в саппорт обращаться

Здесь буду писать интересные фишки, которые нашел в Oracle Database, как решал некоторые проблемы