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

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

Кроме падения производительности, эту проблему можно обнаружить
по большому числу событий ожидания 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 минуты

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