Index monitoring и сбор статистики

В продолжении темы мониторинга индексов провел небольшое исследование про index monitoring + v$object_usage
Когда анализировал использование индексов по dba_hist_sql_plan запросы, которые собирают статистику приходилось отфильтровывать руками.
**Для index monitoring написал небольшой тест, который показал, что
index monitoring показывает только select по индексу и не показывает сбор статистики и insert в таблицу**

DROP TABLE t PURGE;
Table dropped
CREATE TABLE t(ix NUMBER);
Table created
CREATE INDEX ix_t ON t(ix);
Index created
ALTER INDEX ix_t MONITORING USAGE;
Index altered
SELECT * FROM v$object_usage;
INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T                           T                              YES        NO   08/31/2015 19:18:23 
INSERT INTO t
SELECT LEVEL FROM dual CONNECT BY LEVEL <= 1000;
1000 rows inserted
COMMIT;
Commit complete
SELECT * FROM v$object_usage;
INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T                           T                              YES        NO   08/31/2015 19:18:23 
BEGIN
  dbms_stats.gather_table_stats(USER, 'T', CASCADE => TRUE);
END;
/
PL/SQL procedure successfully completed
SELECT * FROM v$object_usage;
INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T                           T                              YES        NO   08/31/2015 19:18:23 
SELECT COUNT(*) FROM t WHERE ix = 10;
  COUNT(*)
----------
         1
SELECT * FROM v$object_usage;
INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T                           T                              YES        YES  08/31/2015 19:18:23 

По теме удаления неиспользуемых индексов в последнюю неделю начали писать все.
Статья Льюиса https://jonathanlewis.wordpress.com/2015/08/17/index-usage/ (обещал продолжение)
Вторая статья Льюиса немного не по теме https://jonathanlewis.wordpress.com/2015/08/29/index-usage-2/ – про то, что с 11.2.0.2 не надо делать индексы по trunc(datetime column),Oracle сам научился добавлять предикаты
Статья на форуме https://jonathanlewis.wordpress.com/2015/08/17/index-usage/
Старая статья Тима Холла про monitoring usage https://oracle-base.com/articles/10g/index-monitoring

Parallel hint

В статье https://blogs.oracle.com/datawarehousing/entry/parallel_execution_precedence_of_hints
интересные результаты получились для строчек, в которых используются хинты PARALLEL, PARALLEL(degree) и PARALLEL(auto) (строки 9, 10 и 11). Получается, что если установлен такой хинт, то делать

ALTER SESSION ENABLE PARALLEL DML

не надо?
Надо провести тесты.

Unusable index monitoring

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

индексы не используются
* проверяем по dba_hist_sql_plan без учета сбора статистики
* проверяем по dba_hist_seg_stat

Главная идея такая: собираем все подознительное и из этого откидываем, что есть в планах или читается.

Мой запрос такой, но его можно подкорректировать в зависимости от нужд (в части bad_idx + поиграться с количеством чтений и записей + еще что-нибудь дописать)

WITH large_idx AS (
    SELECT owner, segment_name, segment_type, ROUND(sum(bytes)/1024/1024/1024, 2) size_gb
    FROM   dba_segments t
    WHERE  segment_type LIKE 'INDEX%'
    GROUP BY owner, segment_name, segment_type
    HAVING ROUND(sum(bytes)/1024/1024/1024, 2) > 5
),
seg_stat AS (
  SELECT o.object_name, o.owner, SUM(s.logical_reads_delta) + SUM(s.physical_read_requests_delta) + SUM(s.physical_reads_direct_delta) + sum(s.physical_reads_delta) READS,
    SUM(s.physical_write_requests_delta) + SUM(s.physical_writes_direct_delta) + sum(s.physical_writes_delta) writes
  FROM dba_hist_seg_stat s, dba_objects o
  WHERE s.obj# = o.object_id
    AND o.object_type LIKE 'INDEX%'
    AND o.owner LIKE 'OWNER%'
  GROUP BY o.object_name, o.owner
  HAVING SUM(s.logical_reads_delta) + SUM(s.physical_read_requests_delta) + SUM(s.physical_reads_direct_delta) + sum(s.physical_reads_delta) = 0
),
sql_plan AS (
SELECT /*+ MATERIALIZE*/
 p.*
FROM   (SELECT DISTINCT o.owner,
                        o.object_name,
                        sql_id
        FROM   dba_objects       o,
               dba_hist_sql_plan p
        WHERE  o.object_type LIKE 'INDEX%'
        AND    owner LIKE 'OWNER%'
        AND    p.object_owner = o.owner
        AND    p.object_name = o.object_name) p
WHERE  EXISTS (SELECT NULL
        FROM   dba_hist_sqltext t
        WHERE  p.sql_id = t.sql_id
        AND    t.sql_text NOT LIKE '%\*%dbms_stats%*\%')
),
bad_idx AS (
SELECT owner, segment_name object_name, 'LARGE' reason FROM large_idx
UNION
SELECT owner, object_name, 'NOT USED' reason FROM seg_stat WHERE READS = 0 AND writes > 10000
)
SELECT /*+ PARALLEL(8)*/i.owner, i.object_name, round((SELECT sum(bytes) FROM dba_segments s WHERE s.owner = i.owner 
  AND s.segment_name = i.object_name)/1024/1024, 2) mb, LISTAGG(reason, '; ') WITHIN GROUP (ORDER BY 1),
  'ALTER INDEX ' || i.owner || '.' || i.object_name || ' MONITORING USAGE;' monitiring_on,
    'ALTER INDEX ' || i.owner || '.' || i.object_name || ' NOMONITORING USAGE;' monitiring_off
FROM bad_idx i
WHERE (i.owner, i.object_name) NOT IN (SELECT owner, object_name FROM sql_plan)
  AND (i.owner, i.object_name) NOT IN (SELECT owner, object_name FROM seg_stat WHERE READS > 0)
GROUP BY i.owner, i.object_name
;

Пока сильные подозрения вызываем правильность заполнения dba_hist_seg_stat

Initial extent

Таблица после MOVE не уменьшилась, хотя по оценкам должна быть в 15 раз меньше.
Выяснилось, что причина в неадекватном initial extents
Уменьшить его можно в самом move

ALTER TABLE test MOVE STORAGE (INITIAL 2097152) PARALLEL 8;

Удаление индексов перед вставкой при наличии constraints

Один INSERT очень сильно тормозил из-за существующих на таблице индексов. Быстрее было их удалить, вставить и построить их заново.
Тут столкнулся с проблемой: на индексах были построены primary key и unique key, а на PK еще и ссылались FK с других таблиц.
Минимальный работающий алгоритм получился такой:
1. Удалить внешние ключи (именно DROP, DISABLE не работает)
2. Удалить или сделать DISABLED PK и UK
3. Удалить индексы. Те, которые поддерживают PK и UK именно DROP, а не UNUSABLE
4. INSERT
5. Создаем назад индексы и FK, делаем PK ENABLE (для скорости можно NOVALIDATE)

Выводы
1. Любой FK мешает удалить PK или UK на который он ссылается. Даже DISABLE.
2. Индекс, поддерживающий PK или UK можно сделать UNUSABLE, но вставить записи в таблицу потом нельзя.
3. Удалить индекс, который поддерживает PK или UK невозможно.
4. Но можно сделать PK UNUSABLE, удалить индекс и вставлять

Скрипт и результаты работы

DROP TABLE chi PURGE;
DROP TABLE par PURGE;

CREATE TABLE par(ID NUMBER, CONSTRAINT par_pk PRIMARY KEY (ID));

CREATE TABLE chi(par_id NUMBER);

ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;

-- тест с первичным ключом
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_pk KEEP INDEX;
-- работает, но записи добавить потом нельзя, т.к. это PK
ALTER INDEX par_pk UNUSABLE;
INSERT INTO par SELECT ROWNUM FROM dual;
-- Удалить индекс нельзя
DROP INDEX par_pk; 

-- подготовка к следующему эксперементу
ALTER TABLE chi DROP CONSTRAINT chi_fk;
ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;

ALTER TABLE par ADD CONSTRAINT par_uk UNIQUE (ID);
ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;

-- тест с уникальным ключом
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_uk DROP INDEX;
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_uk KEEP INDEX;
-- работает, но записи добавить потом нельзя, т.к. это PK
ALTER INDEX par_uk UNUSABLE;
INSERT INTO par SELECT ROWNUM FROM dual;
-- Удалить индекс нельзя
DROP INDEX par_uk; 

-- но можно сделать constraint DISABLE
ALTER TABLE par MODIFY CONSTRAINT par_uk DISABLE KEEP INDEX;
-- с UNUSABLE INDEX все равно нельзя вставить
ALTER INDEX par_uk UNUSABLE;
INSERT INTO par SELECT ROWNUM FROM dual;

--а вот без индекса можно
DROP INDEX par_uk; 
INSERT INTO par SELECT ROWNUM FROM dual;

Скрипт с результатами

SQL> CREATE TABLE par(ID NUMBER, CONSTRAINT par_pk PRIMARY KEY (ID));
Table created
SQL> CREATE TABLE chi(par_id NUMBER);
Table created
SQL> ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;
Table altered
SQL> -- тест с первичным ключом
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;
ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_pk KEEP INDEX;
ALTER TABLE par DROP CONSTRAINT par_pk KEEP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- работает, но записи добавить потом нельзя, т.к. это PK
SQL> ALTER INDEX par_pk UNUSABLE;
Index altered
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
INSERT INTO par SELECT ROWNUM FROM dual
ORA-01502: index 'SPS.PAR_PK' or partition of such index is in unusable state
SQL> -- Удалить индекс нельзя
SQL> DROP INDEX par_pk;
DROP INDEX par_pk
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> -- подготовка к следующему эксперементу
SQL> ALTER TABLE chi DROP CONSTRAINT chi_fk;
Table altered
SQL> ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;
Table altered
SQL> ALTER TABLE par ADD CONSTRAINT par_uk UNIQUE (ID);
Table altered
SQL> ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;
Table altered
SQL> -- тест с уникальным ключом
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_uk DROP INDEX;
ALTER TABLE par DROP CONSTRAINT par_uk DROP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_uk KEEP INDEX;
ALTER TABLE par DROP CONSTRAINT par_uk KEEP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- работает, но записи добавить потом нельзя, т.к. это PK
SQL> ALTER INDEX par_uk UNUSABLE;
Index altered
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
INSERT INTO par SELECT ROWNUM FROM dual
ORA-01502: index 'SPS.PAR_UK' or partition of such index is in unusable state
SQL> -- Удалить индекс нельзя
SQL> DROP INDEX par_uk;
DROP INDEX par_uk
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> -- но можно сделать constraint DISABLE
SQL> ALTER TABLE par MODIFY CONSTRAINT par_uk DISABLE KEEP INDEX;
Table altered
SQL> -- с UNUSABLE INDEX все равно нельзя вставить
SQL> ALTER INDEX par_uk UNUSABLE;
Index altered
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
INSERT INTO par SELECT ROWNUM FROM dual
ORA-01502: index 'SPS.PAR_UK' or partition of such index is in unusable state
SQL> --а вот без индекса можно
SQL> DROP INDEX par_uk;
Index dropped
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
1 row inserted

Fast refreshable materialized view errors

Отличное исследование почему материализованное представление не может быть сделано fast refresh.
Рассмотрены все варианты

General Restrictions
Join restriction
Aggregate restrictions
Union all
Nested MV
dbms_mview.explain_mview
Итого
Документация

Для всех ограничений примеры с исходным кодом.

Борьба с ORA-04030

В системе при удалении строк из огромной вьюхи с триггерами раз в 2 часа выскакивала ошибка

ORA-04030: out of process memory when trying to allocate 32780 bytes (kxs-heap-b,bind var buf)

Параметр в скобках периодически менялся, сообщая о невозможности выделить памяти то в одной, то в другой области.

Исследование ошибки начал с

select * from v$process order by pga_used_mem DESC

В чемпионах сессия, которая удаляет данные.
Далее делаю

SELECT * FROM v$process_memory ORDER BY max_allocated DESC NULLS LAST;

получаю, что больше всего памяти выделено в сессии под CATEGORY SQL
Выполняю

SELECT * FROM v$process_memory_detail;

Пусто :(.
В ходе поисков решения натыкаюсь на отличную статью
Делаю

SELECT * FROM V$SQL_WORKAREA_ACTIVE;

Пусто, не мой вариант. У меня нет ни сортировок, ни хешей, ни битовых индексов.
Но далее в статье решение. Для того, что бы увидеть данные в v$process_memory_detail, нужно сказать ораклу, чтобы он их заполнил

SQL> ORADEBUG SETMYPID
Statement processed.
SQL> ORADEBUG DUMP PGA_DETAIL_GET 24
Statement processed.

и в v$process_memory_detail нахожу, что больше всего места занимает heap kxt.c: PL/SQL pgadef и ноту на метлинке PLSQL performance on 11.1.x much slower than previous DB versions (Doc ID 739064.1)
На лицо мой баг в Oracle 11.1.0.6
После применения рекомендованых

ALTER SYSTEM SET session_cached_cursors=0 SCOPE=SPFILE;
ALTER SYSTEM SET cursor_space_for_time=FALSE SCOPE=SPFILE;

все заработало как надо

Clear empty partitions

Clear empty partitions

DECLARE
    g_owner      VARCHAR2(30) := USER;
    g_table_name VARCHAR2(30) := 'PART_TEST';

    PROCEDURE sp_execute(p_str VARCHAR2) IS
    BEGIN
        dbms_output.put_line(p_str);
        EXECUTE IMMEDIATE p_str;
    END sp_execute;

    FUNCTION is_segment_empty(p_table_owner    VARCHAR2,
                              p_table_name     VARCHAR2,
                              p_partition_name VARCHAR2) RETURN BOOLEAN IS
        l_tmp  NUMBER;
        l_stmt VARCHAR2(32767);
    BEGIN
        l_stmt := 'SELECT count(*) from ' || p_table_owner || '.' || p_table_name || CASE
                      WHEN p_partition_name IS NOT NULL THEN
                       ' partition (' || p_partition_name || ') '
                  END || ' where rownum = 1';
        EXECUTE IMMEDIATE l_stmt
            INTO l_tmp;
        IF l_tmp <> 0
        THEN
            RETURN FALSE;
        ELSE 
            RETURN TRUE;
        END IF;

    END is_segment_empty;

BEGIN
    FOR rec IN (SELECT * FROM all_tab_partitions p WHERE p.table_owner = g_owner AND table_name = g_table_name)
    LOOP
        IF is_segment_empty(rec.table_owner,
                            rec.table_name,
                            rec.partition_name)
        THEN
            sp_execute('ALTER TABLE ' || rec.table_owner || '.' || rec.table_name || ' TRUNCATE PARTITION ' ||
                       rec.partition_name || ' DROP ALL STORAGE UPDATE INDEXES');
        END IF;
    END LOOP;
END;
/

Get index ddl

Извлечение кода создания индексов без мусора.Не подойдет для global партиционированных индексов

BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); 
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PARTITIONING',FALSE); 
END;
/  

with ind AS (
SELECT owner owner, index_name name FROM all_indexes WHERE table_name = '[table_name]'
UNION ALL
SELECT '[index_owner]' owner, '[index_name]' NAME FROM dual
)
SELECT to_char(
  dbms_metadata.get_ddl(object_type => 'INDEX', name => ind.NAME, schema => ind.owner)
  || CASE WHEN i.partitioned = 'YES' THEN ' LOCAL' END
  )
  , ind.*
FROM ind, all_indexes i
WHERE ind.owner = i.owner AND ind.name = i.index_name
;

ORA-01031: insufficient privileges

Приключилась тут у меня на одной из баз ошибка ORA-01031: insufficient privileges
Произошла она в ходе экспериментов с клонированием баз с одного сервера на другой (печальный опыт показал, что в Oracle 11.1 SE не работает Active Database Duplication и что в статье Тима Холла кучка ошибок, а индусы на металинке дают воркэраунды с опечатками в командах). Для Active Database Duplication нужно явно прописывать экземпляр в listener.ll

Я мог подключится к базе через os authentication (т.е. при помощи sqlplus / as sysdba), но никак не мог при помощи LISTENER (т.е. sqlplus sys/password@db1 as sysdba). Поэтому варианты с
невключением в группу dba и SQLNET.AUTHENTICATION_SERVICES были отброшены сразу.

Проверив параметры REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, наличие самого password file в $ORACLE_HOME/dbs

ll $ORACLE_HOME/dbs/ora*
-rw-r----- 1 oracle oinstall 2560 2015-06-15 20:58 /opt/oracle/product/11.1/db/dbs/orapwdb1

и даже на всякий случай его пересоздав password file я уже было совсем расстроился, пока не сравнил имя password file с тем, что прописано в listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB1)
      (ORACLE_HOME = /opt/oracle/product/11.1/db)
      (SID_NAME = DB1)
    )
  )

Передирая у Тима Холла, я не исправил регистр символов!!! При этом в переменной ORACLE_SID у меня имя в нижнем регистре

echo $ORACLE_SID
db1

Исправив listener.ora на нижний регистр

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = db1)
      (ORACLE_HOME = /opt/oracle/product/11.1/db)
      (SID_NAME = db1)
    )
  )

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

dba_tab_modifications + merge

Небольшой тест для определения, как в dba_tab_modifications отображается результат оператора MERGE
1. Для того, что бы результаты оказались в dba_tab_modifications нужно вызвать DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
2. Сбор статистики по таблице убирает (удаляет) строчку из dba_tab_modifications.
3. MERGE учитывается по отдельности, как будто это 2 оператора
Пример

DROP TABLE a PURGE;
CREATE TABLE a(a NUMBER, val VARCHAR2(10));

INSERT INTO a 
SELECT LEVEL, LPAD('x', 10, 'x')
FROM dual
CONNECT BY LEVEL <= 100;

BEGIN
  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
END;
/
SELECT table_name, inserts, updates, deletes FROM dba_tab_modifications t WHERE table_name = 'A';

MERGE INTO a
USING (
SELECT LEVEL + 75 a,  LPAD('Z', 10, 'Z') val
FROM dual
CONNECT BY LEVEL <= 75
) t
ON (a.a = t.a)
WHEN MATCHED THEN UPDATE SET a.val = t.val
WHEN NOT MATCHED THEN INSERT VALUES (t.a, t.val);

BEGIN
  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
END;
/
SELECT table_name, inserts, updates, deletes FROM dba_tab_modifications t WHERE table_name = 'A';

UPDATE a SET val =  LPAD('x', 10, 'x') WHERE ROWNUM <=22;
BEGIN
  DBMS_STATS.gather_table_stats(NULL, 'A');
END;
/
SELECT table_name, inserts, updates, deletes FROM dba_tab_modifications t WHERE table_name = 'A';

Результаты

SQL> 
Table dropped
Table created
100 rows inserted
PL/SQL procedure successfully completed
TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------
A                                     100          0          0
75 rows merged
PL/SQL procedure successfully completed
TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------
A                                     150         25          0
22 rows updated
PL/SQL procedure successfully completed
TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------

SQL> 

Partition pruning monitoring

Зачастую partition pruning не так просто увидеть в плане запроса: если используются bind переменные или подзапрос, то в плане будет стоять pstart и pstop KEY. Для этих случев Oracle сделал отдельный эвент 10128. Для его использования необходимо создать таблицу kkpap_pruning. Результаты можно просматривать в файле, а можно и в таблице. К сожалению внятной информации, как интерпретировать трейсы найти не удалось, так что ниже небольшое исследование:

Ниже приведены примеры для 4 запросов:
* без PP
* PP для одной партиции по равенству
* PP для 2 партиций для случая >=
* отсутствие PP для !=
* пример с bind-переменными, для которого по event мы увидим, какой PP имел место.
* пример с join таблиц
Инициализируем окружение и создаем объекты при помощи скрипта

SET SERVEROUTPUT OFF
SET PAGESIZE 0 FEEDBACK OFF
SET LINESIZE 32000
COL plan_table_output FORMAT a300


drop table kkpap_pruning;
DROP TABLE part_test;

create table kkpap_pruning 
(partition_count  NUMBER
,iterator         VARCHAR2(32)
,partition_level  VARCHAR2(32)
,order_pt         VARCHAR2(12)
,call_time        VARCHAR2(12)
,part#            NUMBER
,subp#            NUMBER
,abs#             NUMBER
);


CREATE TABLE part_test (
  id1 NUMBER NOT NULL,
  pad VARCHAR2(1000),
  val NUMBER)
PARTITION BY RANGE (id1) (
  PARTITION p1 VALUES LESS THAN (1),
  PARTITION p2 VALUES LESS THAN (2),
  PARTITION p3 VALUES LESS THAN (3),
  PARTITION p4 VALUES LESS THAN (4)
);

INSERT INTO part_test
SELECT MOD(ROWNUM, 4) , LPAD('x', 1000, 'x'), ROWNUM
FROM dual
CONNECT BY LEVEL <= 1000;  

BEGIN dbms_stats.gather_table_stats(USER, 'PART_TEST'); END;
/

NB: SET SERVEROUTPUT OFF необходим для того, что бы dbms_xplan.display_cursor работал правильно.

Пример 1. Нет PP, т.к. нет фильтрации

-- Без предикатов
alter session set events '10128 trace name context forever, level 2';
SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
);

SELECT * FROM TABLE(dbms_xplan.display_cursor());
------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |       |    32 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |           |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|           |  1000 |    32   (0)| 00:00:01 |     1 |     4 |
|   3 |    TABLE ACCESS FULL | PART_TEST |  1000 |    32   (0)| 00:00:01 |     1 |     4 |
------------------------------------------------------------------------------------------
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [0, 3]
   index = 0
  current partition: part# = 0, subp# = 1048576, abs# = 0
  current partition: part# = 1, subp# = 1048576, abs# = 1
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3

здесь и далее приведены части трейса, которые относятся к стадии выполнения call time = RUN. Как видно, в первом случае мы посещаем все 4 партиции нашей таблицы, что видно и из плана выполнения и из трейса

Пример 2. Равенство

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1=1
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());
-----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |       |       |     9 (100)|          |       |       |
|   1 |  SORT AGGREGATE         |           |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|           |   250 |   750 |     9   (0)| 00:00:01 |     2 |     2 |
|*  3 |    TABLE ACCESS FULL    | PART_TEST |   250 |   750 |     9   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ID1"=1)
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [1, 1]
   index = 1
  current partition: part# = 1, subp# = 1048576, abs# = 1   

Пример 3. Неравенство с 2 партициями

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1>=2
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());

-------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |       |       |    17 (100)|          |       |       |
|   1 |  SORT AGGREGATE           |           |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|           |   583 |  1749 |    17   (0)| 00:00:01 |     3 |     4 |
|   3 |    TABLE ACCESS FULL      | PART_TEST |   583 |  1749 |    17   (0)| 00:00:01 |     3 |     4 |
-------------------------------------------------------------------------------------------------------
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [2, 3]
   index = 2
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3

Пример 4. Отсутвие PP для !

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1!=2
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());
--------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |       |       |    32 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |           |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|           |   750 |  2250 |    32   (0)| 00:00:01 |     1 |     4 |
|*  3 |    TABLE ACCESS FULL | PART_TEST |   750 |  2250 |    32   (0)| 00:00:01 |     1 |     4 |
--------------------------------------------------------------------------------------------------
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [0, 3]
   index = 0
  current partition: part# = 0, subp# = 1048576, abs# = 0
  current partition: part# = 1, subp# = 1048576, abs# = 1
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3

Пример 6. PP с bind-переменными для 2 партиций

VAR pstart NUMBER
VAR pstop NUMBER
EXEC :pstart := 2;
EXEC :pstop := 3;
SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1 BETWEEN :pstart AND :pstop
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());
--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |       |       |    32 (100)|          |       |       |
|   1 |  SORT AGGREGATE            |           |     1 |     3 |            |          |       |       |
|*  2 |   FILTER                   |           |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|           |   583 |  1749 |    32   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | PART_TEST |   583 |  1749 |    32   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:PSTOP>=:PSTART)
   4 - filter(("ID1">=:PSTART AND "ID1"<=:PSTOP))

Partition Iterator Information:
  partition level = PARTITION
  call time = COMPILE
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [0, 3]
   index = 0
  current partition: part# = 0, subp# = 1048576, abs# = 0
  current partition: part# = 1, subp# = 1048576, abs# = 1
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [2, 3]
   index = 2
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3   

Для этого случая я оставил в трейсе часть от call time = COMPILE. Как видно, из нее вообще ничего не понятно.

Результаты запроса

select * from KKPAP_PRUNING t;

PARTITION_COUNT ITERATOR                         PARTITION_LEVEL                  ORDER_PT     CALL_TIME         PART#      SUBP#       ABS#
--------------- -------------------------------- -------------------------------- ------------ ------------ ---------- ---------- ----------
              2 RANGE                            PARTITION                        ASCENDING    RUN               0        1048576          0
              2 RANGE                            PARTITION                        ASCENDING    RUN               1        1048576          1
              2 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              2 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3
              2 RANGE                            PARTITION                        ASCENDING    RUN               1        1048576          1
              2 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              2 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3
              2 RANGE                            PARTITION                        ASCENDING    RUN               0        1048576          0
              2 RANGE                            PARTITION                        ASCENDING    RUN               1        1048576          1
              2 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              2 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3
              3 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              3 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3

Пример 5. join

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1 IN (
      SELECT ROWNUM FROM dual CONNECT BY LEVEL <=2)
);

SELECT * FROM TABLE(dbms_xplan.display_cursor());

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |           |       |       |    11 (100)|          |       |       |
|   1 |  SORT AGGREGATE                   |           |     1 |    16 |            |          |       |       |
|   2 |   NESTED LOOPS                    |           |   250 |  4000 |    11  (10)| 00:00:01 |       |       |
|   3 |    VIEW                           | VW_NSO_1  |     1 |    13 |     3  (34)| 00:00:01 |       |       |
|   4 |     HASH UNIQUE                   |           |     1 |       |     3  (34)| 00:00:01 |       |       |
|   5 |      COUNT                        |           |       |       |            |          |       |       |
|   6 |       CONNECT BY WITHOUT FILTERING|           |       |       |            |          |       |       |
|   7 |        FAST DUAL                  |           |     1 |       |     2   (0)| 00:00:01 |       |       |
|   8 |    PARTITION RANGE ITERATOR       |           |   250 |   750 |     8   (0)| 00:00:01 |   KEY |   KEY |
|*  9 |     TABLE ACCESS FULL             | PART_TEST |   250 |   750 |     8   (0)| 00:00:01 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - filter("ID1"="ROWNUM")

Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [1, 1]
   index = 1
  current partition: part# = 1, subp# = 1048576, abs# = 1
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [2, 2]
   index = 2
  current partition: part# = 2, subp# = 1048576, abs# = 2

Выводы

Кратко можно заключить следующее:
1. В трейсе смотреть на блоки, относящиеся к call time = RUN
2. Какие партиции посещались видно с строке iterator = RANGE [2, 3] и в строках current partition: part# = 2, subp# = 1048576, abs# = 2.
3. Если нет доступа к файловой системе сервера, то результаты можно посмотреть и в таблице KKPAP_PRUNING.
4. К сожалению, ни названий таблиц, ни номеров объектов в файле и таблице нет, поэтому разобраться со сложным запросом будет очень тяжело. Наверное придется ориентироваться на количество партиций и на порядок операций

Flashback features mindmap

Подборка материалов по управлению планами запросов

Подборка статей про управление планами запросов: SQL profile, SQL Plan Baseline, SQL Patch, Stored Outlines:

1. https://iusoltsev.wordpress.com/2014/12/14/hardcoded-hints-query-plan-spm/
2. http://www.fors.ru/upload/magazine/05/http_texts/russia_ruoug_deev_sql_plans.html
3. https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=108825519711641&id=1931944.1&_afrWindowMode=0&_adf.ctrl-state=y0xdrlgpx_4

dbms_xplan.display

Для тех у кого нет прав вызывать паклеты на prod базе, но есть права на чтение таблиц ниже приведен способ, как с использованием вспомогательной базы данных вывести план запроса в привычном виде.
Основные мысли навеяны статьей http://douggault.blogspot.ru/2009/04/trouble-with-dbmsxplan.html

Итак, план запроса мы будем строть при помощи функции dbms_xplan.display. Описание параметров этой функции такое

DBMS_XPLAN.DISPLAY(
   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',
   statement_id  IN  VARCHAR2  DEFAULT  NULL, 
   format        IN  VARCHAR2  DEFAULT  'TYPICAL',
   filter_preds  IN  VARCHAR2 DEFAULT NULL);

Теперь вместо первого параметра мы передадим имя представления/материализованного представления/таблицы. Представление строим над копией таблицы v$sql_plan с продакшена. Это позволяет обойти следующие неприятности:

• в dba_hist_sql_plan не копируются колонки filter_predicates и access_predicates
• ошибку ORA-22992: cannot use LOB locators selected from remote tables (немного нечестную, т.к. я не тяну LOB колонок в запросах. Второй вариант ее обхода – с использованием материализованного представления см. ниже).

Текст представления:

CREATE OR REPLACE VIEW vw_plan_table_prod AS 
SELECT
sql_id AS statement_id, 
plan_hash_value AS plan_id, 
timestamp, 
remarks, 
operation, 
options, 
object_node, 
object_owner, 
object_name, 
object_alias, 
OBJECT# object_instance, 
object_type, 
optimizer, 
search_columns, 
id, 
parent_id, 
depth, 
position, 
cost, 
cardinality, 
bytes, 
other_tag, 
partition_start, 
partition_stop, 
partition_id, 
other, 
remarks other_xml, 
distribution, 
cpu_cost, 
io_cost, 
temp_space, 
access_predicates, 
filter_predicates, 
projection, 
time, 
qblock_name,
child_number
FROM v$sql_plan_prod

Для получения плана запроса использовать

SET LINESIZE 300 
SET PAGESIZE 0 
SET HEADING OFF
COLUMN PLAN_TABLE_OUTPUT FORMAT A300 TRUNCATE

SELECT *
FROM   TABLE(dbms_xplan.display(table_name   => 'vw_plan_table_prod',
                                statement_id => '0jhz0hkckw4q4',
                                format       => 'ALL',
                                filter_preds => 'plan_id=1275605462 and child_number=0'));

Дополнительный фильтр в параметре filter_preds опционален и используется, если для запроса построено несколько планов.
Объеснение как работает фильтр и как он трансформирует запрос к таблице с планами в статье, указанной выше.

Вариант 2.

Используем материализованное представление

CREATE MATERIALIZED VIEW vw_plan_table_prod 
REFRESH ON DEMAND 
AS 
SELECT
sql_id AS statement_id, 
plan_hash_value AS plan_id, 
timestamp, 
remarks, 
operation, 
options, 
object_node, 
object_owner, 
object_name, 
object_alias, 
OBJECT# object_instance, 
object_type, 
optimizer, 
search_columns, 
id, 
parent_id, 
depth, 
position, 
cost, 
cardinality, 
bytes, 
other_tag, 
partition_start, 
partition_stop, 
partition_id, 
other, 
remarks other_xml, 
distribution, 
cpu_cost, 
io_cost, 
temp_space, 
access_predicates, 
filter_predicates, 
projection, 
time, 
qblock_name,
child_number
FROM v$sql_plan@loopback
;

SET LINESIZE 300 
SET PAGESIZE 0 
SET HEADING OFF
COLUMN PLAN_TABLE_OUTPUT FORMAT A300 TRUNCATE

SELECT * FROM v$diag_info WHERE NAME = 'Default Trace File';
--ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
BEGIN dbms_mview.refresh(list => 'vw_plan_table_prod', method => 'C', atomic_refresh => FALSE); END;
/

SELECT *
FROM   TABLE(dbms_xplan.display(table_name   => 'vw_plan_table_prod',
                                statement_id => 'gd90ygn1j4026',
                                format       => 'ALL',
                                filter_preds => ''));

Аналогично можно построить запрос для dba_hist_sql_plan

Настройки плана счетов в OEBS

Запрос для получения настроек плана счетов в OEBS для набора книг (или всех наборов книг)

select b.set_of_books_id, b.name, b.chart_of_accounts_id, s.application_column_name, s.segment_name, t.flex_value_set_name
from gl_sets_of_books b, 
  FND_ID_FLEX_SEGMENTS s, 
  FND_FLEX_VALUE_SETS t
WHERE b.chart_of_accounts_id = s.id_flex_num
  AND b.set_of_books_id IN (...)
  AND t.flex_value_set_id = s.flex_value_set_id
ORDER BY b.set_of_books_id, segment_num;

Сохраняем html-список в файл при помощи jquery

Для удобства нужно было выкачать список книжек с сайта orelly.
Список представлял из себя следующую html-структуру:

<select class="ebook-select" size="15" name="book">

    <option value=""></option>
    <option data-date="Jan 2015" data-isbn="9781491909300" data-title=" Living Clojure" value="56358">

         Living Clojure 9781491909300 Jan 2015

    </option>

Для красоты нужно было выдернуть только data-title из каждого элемента списка.
Задача решилась простым jquery

$("[name]=books > option").each(function(){
    console.log($(this).attr('data-title'));
})

но вот проблема – ни Chrome (просто не нашел такого пункта), ни FF (почему-то фолдит вывод, предлагая его раскрыть) не дали сохранить вывод в файл.

Вывод получилось сделать при помощи тулзы debugout.js
Как пользоваться

• Заменяем в файле количество сохраняемых строк

self.maxLines = 5000; // if autoTrim is true, this many most recent lines are saved

• Прогоняем файл в консоли (тут файл приводить не буду, вдруг обновят и улучшат)
• Инициируем логгилку

var bugout = new debugout();

• Прогоняем скрипт, заменив с bugout

$("[name]=books > option").each(function(){
    bugout.log($(this).attr('data-title'));
})

• Сохраняем полученный файл

bugout.downloadLog()

Просто, понятно, быстро

Удаление CONSTRAINT и индексов

Помню несколько лет назад надо было почистить схему, удалив CONSTRAINTы из табличек.
При этом база вела себя как хотела: то сама удаляла индексы и ругалась при их повторном удалении, то оставляла их. Тогда дело решилось простой обработкой exception, сейчас пришла пора разобраться что тут к чему.
При удалении CONSTRAINT возможны следующие варианты:
Вариант 1: ALTER TABLE tbl DROP CONSTRAINT cons – базовый вариант, который все обычно и пишут
Вариант 2: ALTER TABLE tbl DROP CONSTRAINT cons CASCADE – расширеный вариант, который на самом деле удаляет связанные foreign key
Вариант 3: ALTER TABLE tbl DROP CONSTRAINT cons CASCADE DROP INDEX – машина для убийств

Опция cascade управляет только foreign key, которые ссылаются на таблицы

-- Инициализация
DROP TABLE chi_tst PURGE;
DROP TABLE tst PURGE;

CREATE TABLE tst (pk NUMBER NOT NULL, CONSTRAINT tst_pk PRIMARY KEY (pk));

INSERT INTO tst VALUES (1);

CREATE TABLE chi_tst(fk NUMBER NOT NULL, CONSTRAINT fk_chi_tst FOREIGN KEY (fk) REFERENCES tst(pk));

INSERT INTO chi_tst VALUES(1);

ALTER TABLE tst DROP CONSTRAINT tst_pk
ORA-02273: this unique/primary key is referenced by some foreign keys

ALTER TABLE tst DROP CONSTRAINT tst_pk CASCADE;
Table altered

Далее мы не будет использовать опцию cascade, т.к. у нас будет одна таблица

Когда нужно добавлять DROP INDEX, а когда индекс удалится сам? Ответ простой: DROP INDEX лучше добавлять всегда, когда хочется гарантировано удалить индекс.

Для того, что бы ответить на вопрос, когда индекс удалится сам, проведем следующий набор тестов:
Тест 1: primary key + атоматически создаваемый (уникальный индекс)
Тест 2: primary key + вручную созданный уникальный индекс
Тест 3: primary key + вручную созданый неуникальный индекс
Для каждого из тестов попробуем удалить индекс без опции (вариант 1) и с опцией (вариант 3)

-- индекс создан автоматически, удаляем без drop index
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL, CONSTRAINT tst_pk PRIMARY KEY (pk));
Table created
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------

-- индекс создан автоматически, удаляем с DROP INDEX
CREATE TABLE tst (pk NUMBER NOT NULL, CONSTRAINT tst_pk PRIMARY KEY (pk));
Table created
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------

как видно, если индекс создается автоматически, то он удаляется в любом случае

-- Уникальный индекс вручную, без drop index
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE UNIQUE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------
PK_TST                         UNIQUE

-- Уникальный индекс вручную, с DROP INDEX
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE UNIQUE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk  DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------

Как видно, если индекс создавался вручную, то он не удаляется, если не указать DROP INDEX

Теперь сделаем constraint на обычном индексе. Этот вариант пробую из-за на достаточно подробном, но немного ошибочном посте Ричарда Фута

-- Неуникальный индекс вручную, без drop index
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------
PK_TST                         NONUNIQUE

-- Неуникальный индекс вручную, с DROP INDEX
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk  DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------

Как видно, если индекс создавался вручную, то он не удаляется, если не указать DROP INDEX

И последний вариант, который узнал из вышеуказанного поста Фута, основанный на DEFFERABLE CONSTRAINT. Для его поддержки Oracle автоматически создает неуникальный индекс

-- DEFFERABLE, автоматически созданный, неуникальный, без drop index
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk) deferrable;
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------
TST_PK                         NONUNIQUE

-- DEFFERABLE, автоматически созданный, неуникальный, c DROP INDEX
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk) deferrable;
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------

Вывод: без DROP INDEX удаление CONSTRAINT не удаляет даже автоматически создаваемые индексы

Итого

Для того, что бы гарантировано удалить индекс – добавляйте DROP INDEX к команде. В противном случае индекс удаляется только если он был автоматически создан и является уникальным (т.е. в одном случае из четырех)