Здравствуйте, MadHuman, Вы писали:

MH>Почему для A or null не используется поиск индексу? Ведь результаты для 1 и 2-го варианта одинаковы, я не вижу смысла использовать менее эффектиный план для 1-го варианта.
MH>Скорее всего есть какой-то нюанс который мне непонятен, но какой?
MH>Как в других базах, такая же фигня?

При использовании в условии хотя бы одного OR всегда происходит TABLE SCAN
Нужно OR заменить на

WHERE coalesce(f1,1)=1

Здравствуйте, Maniacal, Вы писали:

M>При использовании в условии хотя бы одного OR всегда происходит TABLE SCAN

Это неправда.

Здравствуйте, TMU_1, Вы писали:

TMU>От меня ускользает смысл этой конструкции. В части or null.
В некоторых диалектах bool-константу unknown называют null.
Не во всех диалектах можно написать её явно; можно получить её в результате вычисления выражения. Например, вот так:

select count(*) from table1 where f1=1 or 1 = null

Там явно какой-то генератор SQL попытался выполнить оптимизацию, свернув константное выражение в его результат. Это, вообще говоря, разумно, т.к. большинство СУБД не задуряются с микрооптимизациями самих выражений.
Считается, что эту работу надо проводить до того, как дёргать СУБД своими запросами. Вот оптимизации, зависящие от данных — это уже к СУБД (ну там, выкинуть выражение, которое заведомо совпадает с верифицированным check constraint).
Получается, что генератору SQL надо быть ещё умнее, и сворачивать A or null в A

Здравствуйте, Maniacal, Вы писали:

M>Ну, давно я на оракле не оптимизировал запросы. Возможно это когда скан по двум таблицам и между условиями для двух таблиц используется OR. Может, это особенность Oracle. Может, зависит от того, сколько полей в индексе.

Ораклоиду везде Оракл мерещится. У MozgC на картинке MSSQL. А у ТС вообще SQLite.

Кстати, ЕМНИП для Оракла X OR NULL всегда NULL, и запрос ТС будет всегда возвращать 0 строк.

Здравствуйте, MadHuman, Вы писали:

MH>Я сначала думал что есть какой-то нюанс именно с null, но where f1 = 1 or 0 (где 0 — это по сути false, и можно сократить всё выражение до f1=1)

Я как раз хотел порпросить проверить этот случай.

MH>также имеет такую же проблему (план выполнения не использует индекс). Что говорит о том, что видимо всё таки оптимизатор не обучен таким простым оптимизациям.

Похоже на баг. Напиши разработчикам.

Всем привет!
Есть sql c
WHERE f1=1 or null
По f1 есть индекс. При этом план выполнения вместо поиска по индексу становится — TABLE SCAN (на самом деле SCAN TABLE .. USING COVERING INDEX)
Но если
WHERE f1=1
то используется INDEX SEEK.
Ну и в реальности на большой таблице, разница заметна, 1-й случай — секунда (или несколько, в зависимости от были ли в кэше данные), 2-й — 1мс (почти всегда).
База данных — sqlite.


Почему для A or null не используется поиск индексу? Ведь результаты для 1 и 2-го варианта одинаковы, я не вижу смысла использовать менее эффектиный план для 1-го варианта.
Это sqlite не додумывается? хотя верится с трудом тк вроде случай тривиальный и элеменировать несущественную константу не трудно..
Скорее всего есть какой-то нюанс который мне непонятен, но какой?
Как в других базах, такая же фигня?


PS sql генерится доволььно сложным образом и пока хотелось бы понять почему происходит вышеописанная хрень..

Здравствуйте, MadHuman, Вы писали:

MH>Всем привет!
MH>Есть sql c
MH> WHERE f1=1 or null
MH>По f1 есть индекс. При этом план выполнения вместо поиска по индексу становится — TABLE SCAN (на самом деле SCAN TABLE .. USING COVERING INDEX)
MH>Но если
MH> WHERE f1=1
MH>то используется INDEX SEEK.
MH>Ну и в реальности на большой таблице, разница заметна, 1-й случай — секунда (или несколько, в зависимости от были ли в кэше данные), 2-й — 1мс (почти всегда).
MH>База данных — sqlite.


MH>Почему для A or null не используется поиск индексу? Ведь результаты для 1 и 2-го варианта одинаковы, я не вижу смысла использовать менее эффектиный план для 1-го варианта.

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

Здравствуйте, Sharov, Вы писали:

MH>Есть sql c
MH> WHERE f1=1 or null

MH>>Почему для A or null не используется поиск индексу? Ведь результаты для 1 и 2-го варианта одинаковы, я не вижу смысла использовать менее эффектиный план для 1-го варианта.

S>Вероятно, движок думает, что чтобы удовлетиворить условие null индекса не хватит, поэтому сканирует всю таблицу. Т.е. вероятно null записи в индекс не попадают.
это врядли, тк в данном случае в результат попадают только записи с не пустым значением в f1 (даже с конкретным = 1), и то что в индексе нет записей с null, не мешает его использовать для поиска записей с f1=1
то есть для движка не использовать индекс на основании того что в него не включены записи с null в f1, никак не является внятной мотивацией для такого его поведения.

Здравствуйте, MadHuman, Вы писали:

MH>Здравствуйте, Sharov, Вы писали:

MH>>Есть sql c
MH>> WHERE f1=1 or null

MH>>>Почему для A or null не используется поиск индексу? Ведь результаты для 1 и 2-го варианта одинаковы, я не вижу смысла использовать менее эффектиный план для 1-го варианта.

S>>Вероятно, движок думает, что чтобы удовлетиворить условие null индекса не хватит, поэтому сканирует всю таблицу. Т.е. вероятно null записи в индекс не попадают.
MH>это врядли, тк в данном случае в результат попадают только записи с не пустым значением в f1 (даже с конкретным = 1), и то что в индексе нет записей с null, не мешает его использовать для поиска записей с f1=1
MH>то есть для движка не использовать индекс на основании того что в него не включены записи с null в f1, никак не является внятной мотивацией для такого его поведения.

Так смысл использовать индекс, если для null все равно надо сканировать всю таблиуц? Тут боюсь, проблема не в движке, а в запросе, ибо как смысли в ентом or null

Здравствуйте, Sharov, Вы писали:


S>Так смысл использовать индекс, если для null все равно надо сканировать всю таблиуц?
дак null тут по сути константа, для этого не надо ничего сканировать.

S> Тут боюсь, проблема не в движке, а в запросе, ибо как смысли в ентом or null
такой sql генерится автоматически в результате работы определённого нашего генератора, можно конечно логику генератора наворачивать, но пока хочется понять почему движок так себя ведет.
ведь добавка or null никак не меняет сути и результат отбора, и можно использовать индекс для более эффективного плана, но
движок почему-то не использует, вот и хочется понять — почему?


если всё таки копнуть почему у нас так генериться, то в самом начале был критерий типа A or B=Параметр1, где Параметр1 имеет пустое значение.
генератор содержит определённую логику оптимизации условий и B=null заменяет на константу null. поэтому и имеем в финале A or null.
Так лучше, тк константный null уже говорит что нечего из записи брать не надо, что вроде бы должно упрощать задачу оптимизатору движка БД.
заменять B=null на false нельзя, тк это может быть часть другого выражения типа (.... B=null ) is null, тогда результат будет неверный.
можно логику генерации и оптимизации условий наворачивать, но вроде же оптимизатор БД этим тоже и занимается, и должен заниматься лучше...
И вот надо понять, он тут не занимается потому что не научили?
или есть какая-то другая существенная причина?

Здравствуйте, MadHuman, Вы писали:

MH> WHERE f1=1 or null

Точный текст приведи, пожалуйста.

Здравствуйте, wildwind, Вы писали:

W>Здравствуйте, MadHuman, Вы писали:

MH>> WHERE f1=1 or null

W>Точный текст приведи, пожалуйста.

select count(*) from table1 where f1=1 or null

Здравствуйте, MadHuman, Вы писали:

MH> WHERE f1=1 or null

А это точно то, чего ты хочешь? М.б. where f1 = 1 or f1 is null?

Здравствуйте, Ops, Вы писали:

Ops>Здравствуйте, MadHuman, Вы писали:

MH>> WHERE f1=1 or null

Ops>А это точно то, чего ты хочешь?
Да, точно

Ops>М.б. where f1 = 1 or f1 is null?
Нет, именно where f1 = 1 or null
sql генерится автоматически, просто в определённом случае получается такой, и с точки зрения финального результата он валиден.
Но план выполнения, его не очень хорош (индекс не используется), что печально.
Раз sql валиден, то нехотелось бы лезть в логику генерации, полагал что оптимизатор движка БД сделает своё дело.

Я сначала думал что есть какой-то нюанс именно с null, но where f1 = 1 or 0 (где 0 — это по сути false, и можно сократить всё выражение до f1=1)
также имеет такую же проблему (план выполнения не использует индекс). Что говорит о том, что видимо всё таки оптимизатор не обучен таким простым оптимизациям.
Что тоже странно, ведь оптима тривиальная.

Здравствуйте, MadHuman, Вы писали:

Ops>>А это точно то, чего ты хочешь?
MH>Да, точно

Ops>>М.б. where f1 = 1 or f1 is null?
MH>Нет, именно where f1 = 1 or null

Орнул с "OR null".
Эта часть условия всегда FALSE. Наличие OR + константа приводит к тому, что индекс использоваться не будет. Похоже на ошибку.

Здравствуйте, MozgC, Вы писали:

MC>Здравствуйте, Maniacal, Вы писали:

M>>При использовании в условии хотя бы одного OR всегда происходит TABLE SCAN

MC>Это неправда.
MC>Image: index.png
Ну, давно я на оракле не оптимизировал запросы. Возможно это когда скан по двум таблицам и между условиями для двух таблиц используется OR. Может, это особенность Oracle. Может, зависит от того, сколько полей в индексе.

Здравствуйте, MadHuman, Вы писали:
MH>Почему для A or null не используется поиск индексу? Ведь результаты для 1 и 2-го варианта одинаковы, я не вижу смысла использовать менее эффектиный план для 1-го варианта.
Похоже на баг.
https://www.sqlite.org/optoverview.html#or_opt
Там вроде как каждый из термов под OR должен анализироваться, и оптимизируется

 rowid IN (SELECT rowid FROM table1 WHERE f1=1 
            UNION SELECT rowid FROM table1 WHERE null)

MH>Всем привет!
MH>Есть sql c
MH> WHERE f1=1 or null


От меня ускользает смысл этой конструкции. В части or null.

Здравствуйте, Sinclair, Вы писали:


S>Получается, что генератору SQL надо быть ещё умнее, и сворачивать A or null в A
Всё так, но была мысль, что эту работу сделает оптимизатор движка БД, но получается не делает.
Придётся заморочиться, чтоб в генераторе SQL её сделать, хотя уже заморочились и сделали

M>Нужно OR заменить на
M>

M>WHERE coalesce(f1,1)=1
M>

В этом случае индекс по f1 тоже работать не будет, из-за функции.
Нужен будет индекс по coalesce(f1,1), если такое позволяет субд топикстартера.

MH>Почему для A or null не используется поиск индексу? Ведь результаты для 1 и 2-го варианта одинаковы, я не вижу смысла использовать менее эффектиный план для 1-го варианта.
MH>Это sqlite не додумывается? хотя верится с трудом тк вроде случай тривиальный и элеменировать несущественную константу не трудно..
MH>Скорее всего есть какой-то нюанс который мне непонятен, но какой?
MH>Как в других базах, такая же фигня?

10 лет назад (сейчас я не в курсе) — OR в предикатах мешал использовать индекс в Oracle.
Оптимизатор oracle делал "OR Expansion transformation" — преобразовывал при выполнении запрос таким образом для использования индекса:

SELECT ... WHERE f1=1
UNION
SELECT ... WHERE null

Возможно в твоём случае оптимизатор не вычисляет "or null" на стадии оптимизации, а просто не использует индекс потому что "or", или потому что для "or null" явно не нужен индекс для работы.