Составной индекс Oracle для условий запроса диапазона

Question

У меня была таблица Blah (плавание по широте, плавание по долготе, дата create_time, int owner_id, .....)

и мой код делает только один запрос

select * 
from Blah 
where latitude < l1 and latitude > l2   
and longitude < ll1 and longitude > ll2   
and create_time < t1 and create_time > t2 
and owner_id < o1 and owner_id > o2 ;

(конечно, значения l1, l2,.... o1,o2 являются динамическими параметрами, поступающими из программы)

у меня вопрос, какой индекс я должен создать; составной индекс? в случае составного индекса, какой столбец я должен поставить первым? Насколько эффективен индекс?

Я долго думал об этом и не смог найти подробных документов о том, как работает индекс оракула.

Я могу найти документы, которые реализованы с использованием B-дерева, в нашем случае: каждый ключ в B-дереве является 4-кортежем: ( column1, column2, column3, column4), где отношение порядка таких кортежей определено как лексическое порядок.

тогда для вышеупомянутого запроса, предполагая, что наш порядок (owner_id, create_time, широта, долгота), я предполагаю, что оракулу сначала нужно будет выполнить двоичный поиск до точки ( o1, t1, l1,ll1), для этой операции индекс действительно полезно. но затем нам нужно найти конечную точку этого первого интериума: нам нужно найти (o1,t1, l1, ll2), это можно сделать и с помощью бинарного поиска.

далее нам нужно найти следующий раздел, который удовлетворяет условию, поэтому нам нужно найти (o1, t1, lx, ll1), где lx - следующее значение, большее, чем l1, мы также могли бы найти это с помощью бинарного поиска. но в нашем случае весьма вероятно, что для одной и той же широты может быть не более 1 долготы, поэтому здесь бинарный поиск не более эффективен, чем линейное сканирование.

следуя этому духу, кажется, что сначала мы должны поместить столбец с небольшим количеством значений диапазона, в данном случае, create_time, если наши точки создаются всего за несколько дней. также, если мы никогда не выполняем условия диапазона, а только равняемся (=) условиям, то не имеет значения, какой столбец является первым, верно?

Чтобы было понятнее, вот простой пример:

скажем, у меня есть 2 столбца, X и Y

в БД значения для обоих: [1,2,....100], поэтому у нас есть 100x100 строк

мой запрос

select * from mytable where X > 34 and X < 78 and Y > 12 and Y < 15;

скажем, наш индекс включен (X, Y), поэтому правило сравнения между двумя значениями

v1 < v2 <=====>  v1.x < v2.x || v1.x == v2.x && v1.y < v2.y

учитывая приведенное выше правило упорядочения, мы можем видеть, что значения в индексе упорядочены по порядку (значения для x,y):

1,1, 1,2 1,3 .... 1,100     
2,1  2,2 2,3 ......2,100
.....
100,1 100,2 ....... 100,100

Теперь для поиска значений в запросе необходимо найти (78-34-1) интервалов, следовательно, (78-34-1)*2 поиска (1 для начального для конечных местоположений)., а не только 2 поиска.

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

большое спасибо Ян

Answer 1

Если ваша единственная цель состоит в том, чтобы создать индекс для оптимизации этого запроса, вы бы предпочли, чтобы столбцы в составном индексе были упорядочены в первую очередь по наиболее селективному столбцу. Если предикаты на latitude исключить существенно больше строк, чем другие предикаты, будет эффективнее иметь этот столбец первым. Если предикаты на owner_id исключить существенно больше строк, чем другие предикаты, будет эффективнее иметь этот столбец первым.

В действительности, однако, мы редко создаем индексы, единственной целью которых является оптимизация одного запроса. Как правило, чтобы оправдать затраты на обслуживание индексов, мы хотим, чтобы наши индексы были полезны для многих запросов. В случае составного индекса это означает упорядочение столбцов по вероятности того, что запрос будет иметь предикаты для этого столбца. Если у вас есть составной индекс на owner_id, create_time, latitude, longitudeНапример, вы можете использовать это для запросов, которые просто указывают предикаты на owner_id, Но вы бы реально не использовали этот индекс для запросов, которые просто указывают предикаты на longitude,

Answer 2

Во-первых, имейте в виду, что "B" в "B-Tree" не является "двоичным".

Во-вторых, когда дело доходит до индексации в Oracle, у вас также есть выбор индекса растрового изображения, если:

1. У вас есть корпоративная версия лицензии
2. У вас не много сеансов, одновременно изменяющих таблицу
3. Ваши индексированные значения не близки к тому, чтобы быть уникальными (утверждения о том, что растровые индексы могут использоваться только для столбцов с низкой кардинальностью, обычно преувеличены)

Один из типов запросов, в которых превосходят растровые индексы, заключается в эффективном объединении предикатов для нескольких столбцов, особенно в тех случаях, когда набор предикатных столбцов меняется (что, конечно, может не иметь места). Если вы удовлетворяете трем условиям, указанным выше, то стоит проверить эффект наличия четырех отдельных индексов растрового изображения в таблице.

Answer 3

Одним из простых решений для перебора является создание нескольких комбинаций индексов для одной таблицы, выполнение запроса с включенным EXPLAIN PLAN и выбор индекса, который предпочитает использовать ваша СУБД.

Answer 4

Эта таблица используется для OLTP или в качестве DWH? если в этой таблице нет большого количества строк или многопоточных DML-оценок, вы можете использовать растровые индексы. Растровые индексы позволяют вам указывать операторы ROWID AND между несколькими индексами (иначе звездообразное преобразование). чтобы сделать это, создайте растровый индекс в каждом столбце. Как я уже сказал, это решение лучше всего подходит для системы DWH, где у вас есть одна партия вставки.

Answer 5

ИМХО лучше всего обрабатывать запросы многомерного диапазона вне стандартных индексов B-дерева. Несколько статей по общей теме можно найти в веб-поиске по "запросам многомерного диапазона".

Oracle предоставляет продукт под названием Oracle Spatial. Документация для этого продукта включает в главу 4 примеры и объяснения создания пространственных индексов и выполнения запросов. Там нет нового синтаксиса SQL; их пример для создания индекса:

CREATE INDEX territory_idx ON territories (territory_geom)
    INDEXTYPE IS MDSYS.SPATIAL_INDEX;

который создает индекс R-дерева.

Я думаю, что существование R-деревьев, kdb-деревьев и подобных пространственных структур является доказательством того факта, что стандартные B-деревья, вероятно, не очень подходят для такого рода приложений.