Загадочные функции FIRST и LAST (не путать с FIRST_VALUE и LAST_VALUE)

Давно собирался посмотреть, зачем они нужны. Разобрался.
Весьма замысловатые функции. Полагаю, применяться будут редко.

Пишу для информации.

Первое:
Текст примера запроса, который содержится в документации не совсем точен, он не помогает понять, как это работает.
http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions065.htm
А именно, если исключить в первом запросе обе фразы
KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
то результат выполнения запроса не меняется.


Правильно так (здесь слово last исправлено на first):

select DEPARTMENT_ID

     , min (SALARY) keep (dense_rank first order by COMMISSION_PCT) "Worst"

     , max (SALARY) keep (dense_rank first order by COMMISSION_PCT) "Best"

from HR.EMPLOYEES

group by DEPARTMENT_ID

order by DEPARTMENT_ID, "Worst", "Best";

Для понимания текста ниже откройте в другом окне таблицу HR.EMPLOYEES и отсортируйте по DEPARTMENT_ID, COMMISSION_PCT, я не буду здесь приводить таблицу

select DEPARTMENT_ID, COMMISSION_PCT, SALARY

from HR.EMPLOYEES order by DEPARTMENT_ID, COMMISSION_PCT

Первый запрос из документации работает так:

1. Каждый департамент обсчитывается независимо, благодаря group by DEPARTMENT_ID
2. Здесь будем смотреть только департамент 80, поскольку только у него ненулевые COMMISSION_PCT
3. В пределах департамента 80 строки сортируются по COMMISSION_PCT. Определяется минимальное значение этого поля (иным словами применяется функция аналитическая функция DENSE_RANK). Это фраза dense_rank first order by COMMISSION_PCT
4. Для всех строк в группе DEPARTMENT_ID, которые обладают этим минимальным значением COMMISSION_PCT рассчитывается min (SALARY)
5. Рассчитанное значение распространяется на все строки группы DEPARTMENT_ID

Зеленым фоном выделил то, что делает фраза после KEEP

Что хотел сказать вендор разработкой этой функции, сказать трудно, пока вижу такие варианты:

Во первых, вместо SUM можно подставить любую агрегатную функцию.

Во вторых, подставив например COUNT и LAST получим количество людей в департаменте, которые обладают максимальной COMMISSION_PCT (премией с продаж) относительно общей численности департамента -- поделите / вычтите общий COUNT

Аналитическая форма работает схожим образом. GROUP BY не используется, а группировочное выражение пишется в фразу PARTITION BY

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

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

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

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