Data Science. SQL hub

📌

Возможности SQLite, которые вы могли пропустить

🔵

Частичные индексы (Partial Indexes)
При построении индекса можно указать условие попадания строки в индекс, к примеру, одна из колонок не пустая, а другая равна заданному значению.

create index idx_partial on tab1(a, b) where a is not null and b = 5;
select * from tab1 where a is not null and b = 5; --> search table tab1 using index

🔵

Индексы на выражение (Indexes On Expressions)
Если в запросах к таблице часто используется выражение, то можно построить индекс по нему. Однако следует иметь в виду, что пока оптимизатор не очень гибок и перестановка столбцов в выражении приведет к отказу от использования индекса.

create index idx_expression on tab1(a + b);
select * from tab1 where a + b > 10; --> search table tab1 using index ...
select * from tab1 where b + a > 10; --> scan table

🔵

Вычисляемые колонки (Generated Columns)
Если данные столбца представляют собой результат вычисления выражения по другим столбцам, то можно создать виртуальный столбец. Есть два вида: VIRTUAL (вычисляется каждый раз при чтении таблицы и не занимает места) и STORED (вычисляется при записи данных в таблицу и место занимает). Разумеется записывать данные в такие столбцы напрямую нельзя.

create table tab1 (
 a integer primary key,
 b int,
 c text,
 d int generated always as (a * abs(b)) virtual,
 e text generated always as (substr(c, b, b + 1)) stored
);

🔵

R-Tree индекс
Индекс предназначен для быстрого поиска в диапазоне значений/вложенности объектов, т.е. задачи типичной для гео-систем, когда объекты-прямоугольники заданы своей позицией и размером и требуется найти все объекты, которые пересекаются с текущим. Данный индекс реализован в виде виртуальной таблицы (см. ниже) и это индекс только по своей сути. Для поддержки R-Tree индекса требуется собрать SQLite с флагом SQLITE_ENABLE_RTREE (по умолчанию не установлен).

create virtual table idx_rtree using rtree (
 id,              -- ключ
 minx, maxx,      -- мин и макc x координаты
 miny, maxy,      -- мин и макc y координаты
 data             -- дополнительные данные  
);  

insert into idx_rtree values (1, -80.7749, -80.7747, 35.3776, 35.3778); 
insert into idx_rtree values (2, -81.0, -79.6, 35.0, 36.2);

select id from idx_rtree 
where minx >= -81.08 and maxx <= -80.58 and miny >= 35.00  and maxy <= 35.44;

Остальные возможности можно тут

@sqlhub

Please open Telegram to view this post

VIEW IN TELEGRAM

👍11🔥4❤2

3.47K views10:03