Расширенные возможности SQL - Команд SQL в рамках урока #17

-- Индексы -  Основа оптимизации SQL-запросов
-- https://youtu.be/0Cs0F9lX47c

-------------------------------------------
-- Расширенные возможности SQL. Примеры команд SQL в рамках урока #17.


--------------
--1. Создание индексов
CREATE TABLE employees_copy AS (SELECT * FROM employees);

--1.1. Создание B-tree индекса
CREATE INDEX dep_idx ON employees_copy (department_id);

--1.2. Создание Bitmap индекса
CREATE BITMAP INDEX job_idx ON employees_copy (job_id);

--1.3. Создание индекса с функциями
CREATE INDEX salary_idx ON employees_copy (salary * 12);
CREATE INDEX substr_first_name_idx ON employees_copy (SUBSTR(first_name, 1, 3));

--1.4. Создание индекса на несколько столбцов
CREATE INDEX last_name_dept_idx ON employees_copy (last_name, department_id);

--1.5. Создание индекса с разделами

CREATE TABLE employees_partitioned
PARTITION BY LIST(department_id) (
    PARTITION p30 VALUES (30),
    PARTITION p50 VALUES (50),
    PARTITION p80 VALUES (80),
    PARTITION p_others VALUES (DEFAULT)
)
AS
SELECT * FROM employees;

SELECT * FROM employees_partitioned

SELECT partition_name, high_value
FROM user_tab_partitions
WHERE TABLE_NAME = 'EMPLOYEES_PARTITIONED';

CREATE INDEX last_name_part_idx ON employees_partitioned (last_name)
LOCAL;


--------------
--2. Обновление индексов
ALTER INDEX [index_name] REBUILD;


--------------
--3. Удаление индексов
DROP INDEX substr_first_name_idx;


--------------
--4. Проверка наличия индексов

--4.1. Этот запрос отобразит все B-tree индексы в текущей схеме пользователя.
SELECT index_name, TABLE_NAME, index_type
FROM user_indexes
WHERE index_type = 'NORMAL';

--4.2. Этот запрос отобразит все Bitmap индексы в вашей схеме.
SELECT index_name, TABLE_NAME, index_type
FROM user_indexes
WHERE index_type = 'BITMAP';

--4.3. Этот запрос покажет все функциональные индексы.
SELECT index_name, TABLE_NAME, index_type
FROM user_indexes
WHERE index_type = 'FUNCTION-BASED NORMAL';

--4.4. Этот запрос выведет составные индексы, т.е. индексы, которые включают несколько столбцов.
SELECT index_name, MAX(TABLE_NAME) AS TABLE_NAME
FROM user_ind_columns
GROUP BY index_name
HAVING COUNT(*) > 1;

--4.5. Этот запрос покажет все индексы с разделами.
SELECT index_name, TABLE_NAME, partitioned
FROM user_indexes
WHERE partitioned = 'YES';


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

--5.1. Проверка использования индексов
ALTER INDEX dep_idx MONITORING USAGE;

SELECT index_name, TABLE_NAME, used
FROM v$object_usage
WHERE index_name = 'DEP_IDX';

ALTER INDEX DEP_IDX NOMONITORING USAGE;

--5.2. Проверка фрагментации индекса

SELECT index_name, TABLE_NAME, blevel, leaf_blocks, avg_leaf_blocks_per_key, avg_data_blocks_per_key
FROM user_indexes
WHERE TABLE_NAME = 'EMPLOYEES_COPY';

--5.3. Проверка индексов с низкой кардинальностью
SELECT
    uic.index_name,
    uic.TABLE_NAME,
    utc.column_name,
    utc.num_distinct
FROM
    user_ind_columns uic
    JOIN user_tab_col_statistics utc ON uic.column_name = utc.column_name AND uic.TABLE_NAME = utc.TABLE_NAME
WHERE
    uic.TABLE_NAME IN (SELECT TABLE_NAME FROM user_tables);

--5.4. Оценка индексов с высоким кластерным фактором
SELECT i.index_name, i.TABLE_NAME, i.clustering_factor, t.num_rows
FROM user_indexes i
JOIN user_tables t ON i.TABLE_NAME = t.TABLE_NAME
WHERE i.table_owner = 'HR';

--5.5. Проверка зависимых объектов
SELECT * FROM USER_DEPENDENCIES
WHERE TYPE = 'INDEX';