Обязательная индексация внешних ключей

Как известно, все внешние ключи должны быть проиндексированы. Об исключениях -- ниже.

Кроме падения производительности, эту проблему можно обнаружить
по большому числу событий ожидания enq: TM - contention в EM

Запрос для поиска неидексированных внешних ключей.
Выполнять под SYSTEM или тем, у кого есть доступ к DBA_*

select a.OWNER, a.TABLE_NAME, a.CONSTRAINT_NAME, b.COLUMN_NAME

    , (select listagg (f.INDEX_OWNER||'.'||f.INDEX_NAME, '; ')
              within group (order by f.INDEX_OWNER, f.INDEX_NAME)
       from DBA_IND_COLUMNS f
       where f.TABLE_OWNER = b.OWNER and f.TABLE_NAME = b.TABLE_NAME
         and f.COLUMN_NAME = b.COLUMN_NAME) LIST_OF_INDEXES
 
from DBA_CONSTRAINTS a, DBA_CONS_COLUMNS b

where a.CONSTRAINT_TYPE = 'R' and a.OWNER = b.OWNER
  and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
  and (select count(*) from DBA_IND_COLUMNS
       where TABLE_OWNER = b.OWNER and TABLE_NAME = b.TABLE_NAME
         and COLUMN_NAME = b.COLUMN_NAME
      ) = 0 -- напишите >1 для поиска более, чем 1 индекса
  and a.OWNER not in ('SYS', 'SYSTEM', 'SYSMAN', 'APEX_030200'
                    , 'APEX_040200', 'CTXSYS', 'EXFSYS', 'MDSYS'
                    , 'ODMRSYS', 'OLAPSYS', 'ORDDATA')

order by OWNER, TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME




Исключения из этого правила -- когда можно не индексировать внешние ключи:

1. Из главной таблицы строки никогда не удаляются
2. В главной таблице первичный ключ никогда не редактируется
3. Главная и подчинённая таблицы никогда не соединяются (редко, но бывает) 

 

Задачка по палиндромы

Задачка для школьников

Палиндром -- это число, которое одинаково читается в обоих направлениях.
Наибольший палиндром, который может быть получен из произведения двух двузначных чисел, это 9009 = 90 * 91

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

В лучших традициях Oracle решим её одним оператором SQL

Решение:

select S as PALINDROME, R1, R2

from (select R1, R2, S
           , trunc (mod (S, power (10, 6)) / power (10, 5)) as N6
           , trunc (mod (S, power (10, 5)) / power (10, 4)) as N5
           , trunc (mod (S, power (10, 4)) / power (10, 3)) as N4
           , trunc (mod (S, power (10, 3)) / power (10, 2)) as N3
           , trunc (mod (S, power (10, 2)) / power (10, 1)) as N2
           , trunc (mod (S, power (10, 1)) / power (10, 0)) as N1
      from (select R1, R2, R1 * R2 as S
            from (select rownum + 99 as R1 from dual connect by level<=900)
               , (select rownum + 99 as R2 from dual connect by level<=900)
            where R1 >= R2
           )
     )
where ((S <= 99999 and N1 = N5 and N2 = N4)
    or (S >  99999 and N1 = N6 and N2 = N5 and N3 = N4))
order by 1 desc

Наибольший: 906609 = 993 * 913
Наименьший: 10201 = 101 * 101

Время выполнения запроса -- 1 секунда





Найдём также палиндромы из произведения двух четырёхзначных чисел:

select S as PALINDROME, R1, R2

from (select R1, R2, S
           , trunc (mod (S, power (10, 8)) / power (10, 7)) as N8
           , trunc (mod (S, power (10, 7)) / power (10, 6)) as N7
           , trunc (mod (S, power (10, 6)) / power (10, 5)) as N6
           , trunc (mod (S, power (10, 5)) / power (10, 4)) as N5
           , trunc (mod (S, power (10, 4)) / power (10, 3)) as N4
           , trunc (mod (S, power (10, 3)) / power (10, 2)) as N3
           , trunc (mod (S, power (10, 2)) / power (10, 1)) as N2
           , trunc (mod (S, power (10, 1)) / power (10, 0)) as N1
      from (select R1, R2, R1 * R2 as S
            from (select rownum + 999 as R1 from dual connect by level<=9000)
               , (select rownum + 999 as R2 from dual connect by level<=9000)
            where R1 >= R2
           )
     )
where ((S <= 9999999 and N1 = N7 and N2 = N6 and N3 = N5)
    or (S >  9999999 and N1 = N8 and N2 = N7 and N3 = N6 and N4 = N5))
order by 1 desc

Наибольший: 99000099 = 9999 * 9901
Наименьший: 1002001 = 1001 * 1001

Время выполнения запроса -- 2 минуты

Если бы для решения был выбран процедурный подход, то время выполнения было бы нереально большим

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 уже не имеет значения, после того, как первичный проект по внедрению был принят к реализации и реализован.