Sql предикаты: Предикаты — SQL Server | Microsoft Learn

Предикаты поиска / Хабр

По материалам статьи Craig Freedman

Перед тем, как SQL Server приступит к поиску по индексу, он должен определить, являются ли ключи индекса подходящими для оценки предиката запроса.

Индексы по одному столбцу

С индексами по одному столбцу всё довольно просто. SQL Server может их использовать для самых простых сравнений, например, равенства и неравенства (больше чем, меньше чем, и т.д.). Более сложные выражения, такие как функции по столбцу и предикаты «LIKE» с символами подстановки, будут в таких случаях создавать трудности для использования поиска по индексу.

Например, предположим, что мы имеем индекс по одному столбцу, созданный по полю «a». Этот индекс может использоваться для поиска при следующих предикатах:

a = 3.14
a > 100
a between 0   and 99   
a like 'abc%'
a in (2,   3, 5,   7)

Однако, поиск по индексу не будет задействован если использовать вот такие предикаты:

ABS(a) = 1
a + 1 = 9
a like '%abc'

Индексы по нескольким столбцам

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

Для того, чтобы проще понять важность порядка ключей, представьте себе телефонную книгу. Для телефонной книги походит индекс с ключами: «фамилия» и «имя». Содержание телефонной книги отсортировано по фамилии, что упрощает поиск кого-нибудь, если мы знаем его фамилию. Однако, если мы знаем только имя, очень трудно получить список людей с необходимым нам именем. В таком случае, нам бы лучше подошла другая телефонная книга, в которой абоненты отсортированы по имени.

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

Например, предположим, что у нас есть индекс по двум столбцам «a» и «b». Мы можем его использовать для поиска по любому из предикатов, которые применимы для индексов по одному столбцу. Кроме того, можно использовать это индекс и для поиска со следующими дополнительными предикатами:

a = 3.14   and b = 'pi'   
a = 'xyzzy' and b <= 0

Для следующих ниже примеров, мы можем использовать индекс для удовлетворения условий предиката для столбца «a», но не для столбца «b». В этих случаях потребуется остаточный предикат:

a > 100 and   b > 100
a like 'abc%' and b = 2

И, наконец, невозможно использовать индекс для поиска со следующим ниже набором предикатов, поскольку поиск невозможен даже по столбцу «a». В таких случаях, оптимизатор вынужден использовать другой индекс (например, такой индекс, у которого столбец «b» указан первым), или он будет использовать просмотр с остаточным предикатом.

b = 0
a + 1 = 9   and b between   1 and   9
a like '%abc' and b in (1, 3,   5)

Добавим в пример немного конкретики.

Рассмотрим следующую схему:

create table   person (id int, last_name varchar(30),   first_name varchar(30))
create unique   clustered index   person_id on person (id)
create index   person_name on person (last_name, first_name)

Ниже представлены три запроса с соответствующими им текстовыми планами исполнения. Первый запрос осуществляет поиск по обоим столбцам индекса person_name. Второй запрос ищет только по первому столбцу и использует остаточный предикат, для оценки first_name. Третий запрос не может использовать поиск и использует просмотр с остаточным предикатом.

select id from   person where last_name = 'Doe' and   first_name = 'John'
  |--Index   Seek(OBJECT:([person].[person_name]), SEEK:([person].[last_name]='Doe' AND   [person].[first_name]='John'))
select id from   person where last_name > 'Doe' and   first_name = 'John'
  |--Index   Seek(OBJECT:([person].
  [person_name]), SEEK:([person].[last_name] >   'Doe'),  WHERE:([person].[first_name]='John'))
select id from   person where last_name like '%oe' and first_name = 'John'
  |--Index   Scan(OBJECT:([person].[person_name]),    WHERE:([person].[first_name]='John' AND [person].[last_name] like '%oe'))

Внимание: Если Вы пробуете воспроизвести эти планы для этих и некоторых следующих примеров, учтите, что я использовал подсказки индексов (которые не указаны), позволяющие гарантировать получение необходимого плана запроса, поскольку я хотел проиллюстрировать эти примеры без необходимости вставки данных в таблицу.

Дополнение о ключах индекса

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

Поиск по этим неявным ключам осуществляется точно так же, как если бы они были определены явно.

Например, рассмотрим такую схему:

create table   T_heap (a int, b int,   c int, d int,   e int, f int)
create index   T_heap_a on T_heap (a)
create index   T_heap_bc on T_heap (b, c)
create index   T_heap_d on T_heap (d) include (e)
create unique   index T_heap_f on   T_heap (f)
create table   T_clu (a int, b int,   c int, d int,   e int, f int)
create unique   clustered index   T_clu_a on T_clu (a)
create index   T_clu_b on T_clu (b)
create index   T_clu_ac on T_clu (a, c)
create index   T_clu_d on T_clu (d) include (e)
create unique   index T_clu_f on   T_clu (f)

Столбцы ключей и покрываемые столбцы для каждого из индексов:

Индекс	Столбцы ключа	Покрываемые столбцы
T_heap_a	a	a
T_heap_bc	b, c	b, c
T_heap_d	d	d, e
T_heap_f	f	f
T_clu_a	a	a, b, c, d, e
T_clu_b	b, a	a, b
T_clu_ac	a, c	a, c
T_clu_d	d, a	a, d, e
T_clu_f	f	a, f

Обратите внимание, что каждый некластеризованный индекс таблицы T_clu включает ключевой столбец кластеризованного индекса, за исключением уникального индекса T_clu_f.

Описанное в этой статье поведение оптимизатора не изменилось и в SQL Server 2005. Уникальные некластеризованные индексы не включают ключи кластеризованного индекса в свой набор ключевых столбцов, но делают их покрываемыми столбцами. То есть Вы не можете искать по кластеризованному ключу, но Вы можете выбирать значения по индексу. Это происходит потому, что ключ кластеризованного индекса хранится только на страницах листового уровня сбалансированного дерева; он не хранится на страницах не листового уровня. Ключ кластеризованного индекса необходим для поиска закладок. Нет нужды искать по ключу кластеризованного индекса, поскольку поиск по ключу уникального индекса обычно возвращает одну строку. В SQL Server 2000, фактически может использоваться поиск по ключу кластеризованного индекса, но это относится к внутренней реализации в виде остаточного предиката, а не в виде поиска.

Операторы SQL: AND и OR.

Операторы SQL: AND и OR. — it-black.ru Перейти к содержимому

Операторы AND и OR — предикаты языка SQL, служащие для создания логических выражений. В SQL предикатами называются операторы, возвращающие значения TRUE или FALSE. Предикат AND — эквивалент логического умножения (конъюнкции), предикат OR — эквивалент логического сложения (дизъюнкции).

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

first_expression	last_expression	AND	OR
TRUE	TRUE	TRUE	TRUE
TRUE	FALSE	FALSE	TRUE
FALSE	TRUE	FALSE	TRUE
FALSE	FALSE	FALSE	FALSE

Для выполнения условия предиката AND должны быть выполнены оба условия. Для выполнения предиката OR должно быть выполнено хотя бы одно условие.

Предикат AND имеет следующий синтаксис:

boolean_expression AND boolean_expression

Предикат OR имеет следующий синтаксис:

boolean_expression OR boolean_expression

Примеры оператора SQL AND & OR. Имеется следующая таблица Planets:

ID	PlanetName	Radius	SunSeason	OpeningYear	HavingRings	Opener
1	Mars	3396	687	1659	No	Christiaan Huygens
2	Saturn	60268	10759.22	—	Yes	—
3	Neptune	24764	60190	1846	Yes	John Couch Adams
4	Mercury	2439	115.88	1631	No	Nicolaus Copernicus
5	Venus	6051	243	1610	No	Galileo Galilei

Пример 1. Используя операторы AND и OR вывести записи планет, у которых радиус планеты меньше 10000 и открытых (OpeningYear) после 1620:

SELECT * FROM Planets WHERE Radius  1620;

Пример 2. Используя операторы AND и OR вывести записи планет, названия которых начинаются с буквы «N» или заканчиваются на букву «s» и не имеющие колец:

SELECT *
FROM Planets
WHERE (PlanetName LIKE 'N%'
OR PlanetName LIKE '%s')
AND HavingRings = 'No';

Самостоятельно создайте таблицу Planets и выполните каждый пример. В комментариях можете писать новые примеры к данной таблице и не только.

Facebook

Twitter

• Виктор Черемных
• 29 июня, 2018
• One Comment

Группа в VK

Обнаружили опечатку?

Сообщите нам об этом, выделите текст с ошибкой и нажмите Ctrl+Enter, будем очень признательны!

Свежие статьи

Облако меток

Instagram Vk Youtube Telegram Odnoklassniki

Полезно знать

Рубрики

Авторы

Предикаты в SQL

Предикаты в SQL

3 мая 2022 г. by Robert Gravelle

На этой неделе мы ненадолго нажмем кнопку «Пауза» из серии «Некоторые основные ошибки SQL-запросов», чтобы поговорить о предикатах в SQL. Причина в том, что предикаты будут учитываться в части 3 серии «Основные ошибки SQL-запросов».

Предикат — это просто выражение, результатом которого является ИСТИНА, ЛОЖЬ или НЕИЗВЕСТНО. Предикаты обычно используются в условиях поиска предложений WHERE и HAVING, условиях соединения предложений FROM, а также в любой другой части запроса, где требуется логическое значение.

Существует множество типов предикатов, в том числе:

• Сравнение
• КАК
• МЕЖДУ
• В
• СУЩЕСТВУЕТ
• IS NULL (/INTEGER/DECIMAL/FLOAT…)

В оставшейся части этой статьи мы рассмотрим несколько примеров вышеупомянутых типов предикатов.

Каждый раз, когда мы используем оператор сравнения в выражении, таком как WHERE employee_salary > 100000 , мы создаем предикат, который оценивается как ИСТИНА, ЛОЖЬ или НЕИЗВЕСТНО. Операторы сравнения включают:

• = равно
• > Больше
• < Меньше
• >= Больше или равно
• <= Меньше или равно
• <> Не равно

Следовательно, предикат сравнения принимает вид:

выражение_1 оператор сравнения выражение_2
 

В предикате сравнения выражение2 также может быть подзапросом. Если подзапрос не возвращает ни одной строки, предикат сравнения оценивается как FALSE.

В SQL предикатом сопоставления с шаблоном номер один является оператор LIKE, поскольку он сравнивает значения столбца с указанным шаблоном. Like работает с любым символьным или датовым типом данных. Вот пример:

Оператор BETWEEN задает диапазон, который определяет нижнюю и верхнюю границы определяемых значений. Например, в Предикате доход МЕЖДУ 5000 И 20000 выбранные данные представляют собой диапазон больше или равный 5000 и меньше или равный 20000. Оператор Между может использоваться с числовыми, текстовыми типами данных и датами. Вот пример:

Оператор IN позволяет использовать два или более выражений для поиска запроса. Результатом условия является TRUE, если значение соответствующего столбца равно одному из выражений, заданных предикатом IN:

Предикат EXISTS принимает подзапрос в качестве аргумента. Он возвращает TRUE, если подзапрос возвращает одну или несколько строк, и возвращает FALSE, если он возвращает ноль строк.

Вот пример:

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

Предикат IS NULL принимает следующую форму:

ЯВЛЯЕТСЯ [НЕ]НУЛЬНЫМ
 

Например, выражение x IS NULL равно TRUE, если x равно NULL.

IS UNKNOWN является синонимом IS NULL, когда выражение имеет тип BOOLEAN.

Вот запрос, который использует предикат IS NOT NULL для получения всех участников, чья фамилия не равна NULL:

В этом блоге мы прервали регулярно запланированный блог, чтобы показать вам этот важный урок о предикатах SQL. Обычно используемые в условиях поиска предложений WHERE и HAVING, условиях соединения предложений FROM, предикаты — это выражения, которые оцениваются как TRUE, FALSE или UNKNOWN. В следующие недели мы снова увидим предикаты в продолжении серии «Основные ошибки SQL-запросов».

Фильтр-предикаты представляют собой основной риск для производительности Oracle SQL

База данных Oracle использует три различных метода для применения , где предложений (предикатов):

Предикат доступа («доступ»)

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

Предикат индексного фильтра («фильтр» для индексных операций)

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

Предикат фильтра на уровне таблицы («фильтр» для табличных операций)

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

Примечание

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

Планы выполнения, созданные с помощью Утилита DBMS_XPLAN (см. « Получение плана выполнения »), покажите использование индекса в разделе «Информация о предикатах» под табличным планом выполнения:

 --------------- ---------------------------------------
| Идентификатор | Операция | Имя | Ряды | Стоимость |
-------------------------------------------------- ----
| 0 | ВЫБЕРИТЕ ЗАЯВЛЕНИЕ | | 1 | 1445 |
| 1 | СОРТИРОВАТЬ АГРЕГАТ | | 1 | |
|  * 2  | ИНДЕКС ДИАПАЗОН СКАН | SCALE_SLOW | 4485 | 1445 |
-------------------------------------------------- ----
  Информация о предикате (определяется идентификатором операции): 
     2  - доступ ("РАЗДЕЛ" =: A И  "ID2" =: B )
         фильтр("ID2"=:B) 
 

Нумерация информации предиката относится к столбцу «Id» плана выполнения. Там база данных также показывает звездочку, чтобы отметить операции, которые имеют предикатную информацию.

От своего имени

Я зарабатываю на жизнь обучением SQL, настройкой и консультированием SQL, а также своей книгой «Объяснение производительности SQL». Узнайте больше на https://winand.at/.

Этот пример, взятый из главы «Производительность и масштабируемость», показывает INDEX RANGE SCAN с предикатами доступа и фильтрации. Особенность базы данных Oracle заключается в том, что в качестве предикатов доступа также отображаются некоторые предикаты фильтра, например, ID2=:B в приведенном выше плане выполнения.

Важно

Если условие отображается как предикат фильтра, это предикат фильтра — не имеет значения, отображается ли оно также как предикат доступа.

Это означает, что INDEX RANGE SCAN сканирует весь диапазон на наличие условия "РАЗДЕЛ"=:A и применяет фильтр "ID2"=:B к каждой строке.