Advertisement
excel-store

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

Mar 17th, 2024
744
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 4.17 KB | Software | 0 0
  1. -- Индексы -  Основа оптимизации SQL-запросов
  2. -- https://youtu.be/0Cs0F9lX47c
  3.  
  4. -------------------------------------------
  5. -- Расширенные возможности SQL. Примеры команд SQL в рамках урока #17.
  6.  
  7.  
  8. --------------
  9. --1. Создание индексов
  10. CREATE TABLE employees_copy AS (SELECT * FROM employees);
  11.  
  12. --1.1. Создание B-tree индекса
  13. CREATE INDEX dep_idx ON employees_copy (department_id);
  14.  
  15. --1.2. Создание Bitmap индекса
  16. CREATE BITMAP INDEX job_idx ON employees_copy (job_id);
  17.  
  18. --1.3. Создание индекса с функциями
  19. CREATE INDEX salary_idx ON employees_copy (salary * 12);
  20. CREATE INDEX substr_first_name_idx ON employees_copy (SUBSTR(first_name, 1, 3));
  21.  
  22. --1.4. Создание индекса на несколько столбцов
  23. CREATE INDEX last_name_dept_idx ON employees_copy (last_name, department_id);
  24.  
  25. --1.5. Создание индекса с разделами
  26.  
  27. CREATE TABLE employees_partitioned
  28. PARTITION BY LIST(department_id) (
  29.     PARTITION p30 VALUES (30),
  30.     PARTITION p50 VALUES (50),
  31.     PARTITION p80 VALUES (80),
  32.     PARTITION p_others VALUES (DEFAULT)
  33. )
  34. AS
  35. SELECT * FROM employees;
  36.  
  37. SELECT * FROM employees_partitioned
  38.  
  39. SELECT partition_name, high_value
  40. FROM user_tab_partitions
  41. WHERE TABLE_NAME = 'EMPLOYEES_PARTITIONED';
  42.  
  43. CREATE INDEX last_name_part_idx ON employees_partitioned (last_name)
  44. LOCAL;
  45.  
  46.  
  47. --------------
  48. --2. Обновление индексов
  49. ALTER INDEX [index_name] REBUILD;
  50.  
  51.  
  52. --------------
  53. --3. Удаление индексов
  54. DROP INDEX substr_first_name_idx;
  55.  
  56.  
  57. --------------
  58. --4. Проверка наличия индексов
  59.  
  60. --4.1. Этот запрос отобразит все B-tree индексы в текущей схеме пользователя.
  61. SELECT index_name, TABLE_NAME, index_type
  62. FROM user_indexes
  63. WHERE index_type = 'NORMAL';
  64.  
  65. --4.2. Этот запрос отобразит все Bitmap индексы в вашей схеме.
  66. SELECT index_name, TABLE_NAME, index_type
  67. FROM user_indexes
  68. WHERE index_type = 'BITMAP';
  69.  
  70. --4.3. Этот запрос покажет все функциональные индексы.
  71. SELECT index_name, TABLE_NAME, index_type
  72. FROM user_indexes
  73. WHERE index_type = 'FUNCTION-BASED NORMAL';
  74.  
  75. --4.4. Этот запрос выведет составные индексы, т.е. индексы, которые включают несколько столбцов.
  76. SELECT index_name, MAX(TABLE_NAME) AS TABLE_NAME
  77. FROM user_ind_columns
  78. GROUP BY index_name
  79. HAVING COUNT(*) > 1;
  80.  
  81. --4.5. Этот запрос покажет все индексы с разделами.
  82. SELECT index_name, TABLE_NAME, partitioned
  83. FROM user_indexes
  84. WHERE partitioned = 'YES';
  85.  
  86.  
  87. --------------
  88. --5. Проверка на избыточные или неиспользуемые индексы
  89.  
  90. --5.1. Проверка использования индексов
  91. ALTER INDEX dep_idx MONITORING USAGE;
  92.  
  93. SELECT index_name, TABLE_NAME, used
  94. FROM v$object_usage
  95. WHERE index_name = 'DEP_IDX';
  96.  
  97. ALTER INDEX DEP_IDX NOMONITORING USAGE;
  98.  
  99. --5.2. Проверка фрагментации индекса
  100.  
  101. SELECT index_name, TABLE_NAME, blevel, leaf_blocks, avg_leaf_blocks_per_key, avg_data_blocks_per_key
  102. FROM user_indexes
  103. WHERE TABLE_NAME = 'EMPLOYEES_COPY';
  104.  
  105. --5.3. Проверка индексов с низкой кардинальностью
  106. SELECT
  107.     uic.index_name,
  108.     uic.TABLE_NAME,
  109.     utc.column_name,
  110.     utc.num_distinct
  111. FROM
  112.     user_ind_columns uic
  113.     JOIN user_tab_col_statistics utc ON uic.column_name = utc.column_name AND uic.TABLE_NAME = utc.TABLE_NAME
  114. WHERE
  115.     uic.TABLE_NAME IN (SELECT TABLE_NAME FROM user_tables);
  116.  
  117. --5.4. Оценка индексов с высоким кластерным фактором
  118. SELECT i.index_name, i.TABLE_NAME, i.clustering_factor, t.num_rows
  119. FROM user_indexes i
  120. JOIN user_tables t ON i.TABLE_NAME = t.TABLE_NAME
  121. WHERE i.table_owner = 'HR';
  122.  
  123. --5.5. Проверка зависимых объектов
  124. SELECT * FROM USER_DEPENDENCIES
  125. WHERE TYPE = 'INDEX';
  126.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement