#sql #oracle #between
#sql #Oracle #между
Вопрос:
Я использую Oracle SQL Developer, и после выполнения этой команды отображаются только имена, начинающиеся с K и L . Почему имена, начинающиеся с M , не отображаются?
SELECT DISTINCT(names) FROM STUDENTS WHERE names BETWEEN 'K%' AND 'M%' ORDER BY 1 DESC;
И когда я выполняю:
SELECT DISTINCT(names) FROM STUDENTS WHERE names BETWEEN 'K%' AND 'N%' ORDER BY 1 DESC;
K , L , M появляются, но имена, начинающиеся с N , не отображаются. BETWEEN включено, так в чем проблема?
Это работает отлично:
SELECT * FROM STUDENTS WHERE year BETWEEN 1 AND 3;
Ответ №1:
Предикат between не подразумевает like , поэтому вы просто сравниваете строки. Напишите это вместо
-- Names with starting letters K, L, M, N
names >= 'K' AND names < 'O'
В качестве альтернативы используйте регулярные выражения
-- Names with starting letters K, L, M
REGEXP_LIKE (names, '^[K-M]')
Комментарии:
1. @MarmiteBomber: я ответил на вопрос «K, L, M отображаются, но имена, начинающиеся с N, не отображаются. МЕЖДУ включено, так в чем проблема? »
2. Извините, теперь я перечитал поясняющие комментарии и понял!
Ответ №2:
МЕЖДУ включено, так в чем проблема?
Подстановочный знак «%» не работает, как LIKE здесь, и применяется лексикографический порядок
SELECT CASE WHEN 'Mango' BETWEEN 'K%' AND 'M%' THEN 'true' ELSE 'false' END AS result
FROM dual
-- false
SELECT CASE WHEN 'Mango' >= 'K%' AND 'Mango' <= 'M%' THEN 'true' ELSE 'false' END
AS result
FROM dual
--false
SELECT CASE WHEN 'Mango' >= 'K%' THEN 'true' ELSE 'false' END AS result
FROM dual
-- true
SELECT CASE WHEN 'Mango' <= 'M%' THEN 'true' ELSE 'false' END AS result
FROM dual
-- false
Это работает отлично:
SELECT * FROM STUDENTS WHERE year BETWEEN 1 AND 3;
Переписывание аналогичным образом (хотя это невозможно исправить):
SELECT DISTINCT(names)
FROM STUDENTS
WHERE SUBSTR(names,1,1) BETWEEN 'K' AND 'M'
ORDER BY 1 DESC;
Ответ №3:
Лукас объяснил проблему — путаница LIKE шаблонов со строками. Еще одно решение:
WHERE SUBSTR(names, 1, 1) BETWEEN 'K' AND 'M'
Обратите внимание, что из упомянутых решений только прямое сравнение:
WHERE names >= 'K' AND names < 'O'
будет использовать индекс. Однако при таком широком диапазоне значений индекс может быть бесполезен для запроса в любом случае.
Комментарии:
1. Если есть индекс on
names, он обязательно будет использоваться, независимо от избирательности, а также независимо от того, какой предикат используется. Поскольку запрос выбирает только имена, все будет считано непосредственно из индекса; к таблице не будет доступа, потому что в этом нет необходимости. Что может отличаться, так это то, как используется предикат — полное сканирование индекса или сканирование диапазона индексов.2. Вы тестировали доступ к индексу @mathguy? По сути, вы утверждаете, что Oracle может использовать доступ к индексу (здесь
index_ffs), даже если в имени индексированного столбца есть функция (здесьSUBSTR(names,1,1)). Я знаю, что теоретически это может быть возможноindex_ffs, но мои тесты на 19c показывают полное сканирование таблицы .3. @MarmiteBomber — я ничего не сказал о access (что означает: сначала просканируйте индекс, примените некоторые фильтры, затем получите доступ к строкам из базовой таблицы). Я сказал, что сканирование индекса — считывает все данные из индекса; доступ к базовой таблице вообще не требуется. С помощью функции, подобной SUBSTR, Oracle должен знать, что столбец (
names) НЕ равен НУЛЮ; Я предполагаю, что это было бы верно в таблице, подобнойstudents. Кроме того, я предполагаю, что в таблице есть дополнительные данные (больше столбцов); в противном случае нет никакой пользы для чтения данных из индекса (small) по сравнению с полной таблицей (big).4. @MarmiteBomber — С учетом этих дополнительных допущений я только что протестировал, и действительно, запрос будет выполнен через «быстрое полное сканирование индекса». Я использую Oracle 12.2, но я предполагаю, что вы найдете то же самое в Oracle 19, если вы правильно настроили тест (как объяснено выше).