8 первоклассных инструкций SQL на каждый день

Предлагаем вашему вниманию 8 инструкций SQL для экономии рабочего времени. Одни из них базовые, другие немного посложнее, но все из них вам пригодятся. Поэтому начнем без лишних разговоров. 

1. Поиск повторяющихся строк по имени столбца 

С помощью этого простого запроса мы получаем список строк с одинаковым значением, указанным в поле column_name. Кроме того, мы видим, сколько раз они повторяются. 

select column_name, count(column_name)
  from table
group by column_name
having count (column_name) > 1;

2. Показ индексов схемы базы данных 

Хотя эту информацию можно получить с помощью клиента базы данных, сделаем это посредством инструкции SQL.

select TABLE_NAME, count(*)
  from all_indexes
where owner = 'OWNER_NAME' or table_owner = 'TABLE_OWNER'
group by TABLE_NAME
order by TABLE_NAME;

3. Показ N-числа наиболее затратных запросов 

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

select 
     st.TEXT AS QueryName,          
     wt.execution_count AS ExecutionCount,
     wt.total_worker_time/1000000 AS TotalCpuTimeInSeconds,
     wt.total_worker_time/wt.execution_count/1000 AS AverageCpuTimeInMs],
     qp.query_plan,
     DB_NAME(st.dbid) AS [Database Name]
from 
    (select top 10 
          qs.execution_count,
          qs.total_worker_time
            from sys.dm_exec_query_stats qs
     order by qs.total_worker_time desc) wt
     
cross apply sys.dm_exec_sql_text(plan_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
order by wt.total_worker_time desc;

4. Мониторинг использования индекса 

Как правило, со временем число индексов в базе данных увеличивается. 

Сколько раз вы проверяли, действительны ли они? Индексы занимают место на диске и немалое, особенно в больших таблицах. Поэтому всегда лучше проверить, какие из них используются, а потом решить, удалять их или нет. 

Для этой цели сначала необходимо включить мониторинг индексов: 

ALTER INDEX INDEX_NAME MONITORING USAGE;

Для отключения этой опции достаточно выполнить следующую инструкцию: 

ALTER INDEX INDEX_NAME NOMONITORING USAGE;

Теперь можно проводить мониторинг данных, обратившись к v$object_usage view:

select * 
from
  v$object_usage view

• Поле START_MONITORING отмечает время начала мониторинга. 
• Поле MONITORING показывает, подвергается индекс проверке или нет. 
• Поле USED словом “YES” указывает на применение индекса с момента активации мониторинга. 

Перед удалением индекса, который, по вашему мнению, бездействует, рекомендуется продолжить работу мониторинга на определенное время. Это нужно для того, чтобы выявить возможные batch-процессы, которые задействуют этот индекс и выполняются нечасто. 

5. Count (1) вместо count (*)

При любой возможности выбирайте count(1) вместо count(*). Оператор count(*) принимает в расчет все столбцы таблицы для выполнения вычислений, тогда как count(1) учитывает только первый столбец. 

Обратите внимание, что результат остается неизменным, будь то count (*) или count (1).

При использовании count(1) движок базы данных задействует меньше ресурсов и работает быстрее. В случае небольших таблиц эта разница будет незаметна, но если дело касается больших из них, то данный фактор существенно отразится на производительности запросов. 

--BETTER
select
  count(1) from big_table;


--WORSE  
select 
  count(*) from big_table;

6. Условные выражения 

Вы можете воспользоваться классом case when, аналогичным оператору if-then-else в других языках программирования, для написания условных выражений в запросах. 

select column1, column2,
       case when price >= 100 then '1'
            when price between 90 and 99 then '2'
            else 'Other case' end price_type
from table

7. Общие табличные выражения (ОТВ)

Этот тип выражения позволяет определять временный именованный набор результатов, доступный в памяти в области выполнения инструкций, таких как SELECT, INSERT, UPDATE, DELETE и MERGE.

Данное выражение также применяется в инструкции CREATE VIEW как часть определяющей ее инструкции SELECT. 

Главным образом ОТВ позволяют замещать подзапросы и табличные переменные. 

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

В случае табличных переменных ОТВ повышают производительность запроса, поэтому всегда будут предпочтительным вариантом. 

--WORSE
select *
  from other_table
where
  name in (select name
            from table 
           where
           condition1 < 1000 and ... )

--BETTER
with CTE_NAME as (
  select name
    from table 
  where condition1 < 1000 and ...  
)

select *
  from other_table
where name in (SELECT name from CTE_NAME)

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

8. OVER и OVER (PARTITION BY)

Выражение OVER позволяет получать агрегированную информацию без GROUP BY и связанных с ним сложностей. Например, можно извлечь набор строк и вместе с ними получить агрегированные данные. 

OVER предоставляет весь набор результатов для агрегации, но вы можете разделить его на части с помощью выражения PARTITION BY.

--OVER
select 
  SUM(column1) OVER () AS sum1,
  column2
from table1

--OVER PARTITION
select 
  SUM(column1) OVER (PARTITION BY client ) AS sum1,
  column2
from table1

В данном примере разделение происходит по клиенту, и каждое “окно” одного клиента будет рассматриваться отдельно от других “окон”. 

Заключение 

Мы рассмотрели небольшой перечень инструкций SQL, которые экономят время или помогают в повседневной работе. 

Одни из них применяются в SQL Server, а другие  —  в Oracle. Но в целом вы можете подобрать их эквивалент для любого другого движка базы данных. 

Читайте также:

• Как выполнять выражения и процедуры PL/SQL в Python
• SQL или NoSQL: как правильно выбрать базу данных?
• Руководство по SQL: команда MySQL INSERT в подробностях

Читайте нас в Telegram, VK и Яндекс.Дзен

---

Перевод статьи Kesk -*-: 8 Super-useful SQL Snippets You’ll Want to Have on Hand