Индекс в оракуле для подстановочной карты

Если подстановочный знак находится в середине или в конце, а начальные символы перед подстановочным знаком соответствуют относительно небольшому количеству записей, вам следует использовать оператор LIKE. Замените «x» на _:

WHERE column LIKE 'AAA___CCC'

Если ваш пользователь не может использовать _ и настаивает на использовании подстановочного знака x, замените его на _ в вашем SQL:

WHERE column LIKE REPLACE('AAAxxxCCC','x','_')

Это мой основной ответ. Однако это не будет быстро, если у вас есть тонна строк, начинающихся с AAA. Если вы столкнетесь с этой проблемой, то есть некоторые возможности в зависимости от того, какие шаблоны распространены. Если вы всегда смотрите на первые три символа и последние три, вы можете SUBSTR вывести их и проиндексировать эту функцию:

CREATE INDEX i_first3 on mytable(SUBSTR(mycolumn,1,3));
CREATE INDEX i_last3 on mytable(SUBSTR(mycolumn,-3,3));

SELECT * 
  FROM mytable 
 WHERE SUBSTR(mycolumn,1,3) = 'AAA'
   AND SUBSTR(mycolumn,-3,3) = 'CCC'

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

CREATE INDEX i_last1 on mytable(SUBSTR(mycolumn,-1),mycolumn);
CREATE INDEX i_last2 on mytable(SUBSTR(mycolumn,-2),mycolumn);
CREATE INDEX i_last3 on mytable(SUBSTR(mycolumn,-3),mycolumn);
CREATE INDEX i_last4 on mytable(SUBSTR(mycolumn,-4),mycolumn);
CREATE INDEX i_last5 on mytable(SUBSTR(mycolumn,-5),mycolumn);
CREATE INDEX i_last6 on mytable(SUBSTR(mycolumn,-6),mycolumn);
CREATE INDEX i_last7 on mytable(SUBSTR(mycolumn,-7),mycolumn);
...etc..

SELECT * 
  FROM mytable 
 WHERE mycolumn LIKE REPLACE('AxABBBCCC','x','_')
   AND SUBSTR(mycolumn,-7) = SUBSTR('AxABBBCCC',-7)

Это будет использовать i_last7, выполняющий поиск (двоичный поиск) по последним 7 цифрам, а затем сканировать конечные узлы, применяя оператор LIKE ко всему столбцу mycolumn, который является вторым столбцом в индексе. Это могло бы работать намного лучше, чем LIKE только с одним символом перед подстановочным знаком.

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

 CREATE INDEX i_reverse on mytable(REVERSE(mycolumn));

SELECT * 
  FROM mytable 
 WHERE REVERSE(mycolumn) LIKE REVERSE(REPLACE('xxABBBCCC','x','_'))

против.

SELECT * 
  FROM mytable 
 WHERE mycolumn LIKE REPLACE('AAABBBCxx','x','_')

В любом случае, вы поняли - ваши варианты зависят от ваших шаблонов запросов и ваших данных. Надеемся, что простой LIKE без какого-либо функционального индекса удовлетворит ваши потребности, и вам не понадобятся все эти дополнительные вещи. Вы не узнаете, пока не попробуете и не увидите, как это работает.

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

Как уже было сказано, распределение данных может быть неравномерным в зависимости от позиции в столбце данных, и доступ к индексу может вообще не иметь смысла.

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

-- Create Table with random data
create table my_table (idx number, pattern varchar2(4));
insert into my_table select level, dbms_random.string('U', 4) from dual connect by level <= 1000000;

-- Column 1 is unevenly distributed, 50% = A
update my_table set pattern= substr(pattern,1,1)||'A'||substr(pattern,3,2) where rownum <= 500000;

-- Column 2 is unevenly distributed, almost completely = B
update my_table set pattern= substr(pattern,2,1)||'B'||substr(pattern,3,2) where rownum <= 999000;

-- Build indexes
create index s1 on my_table (substr(pattern, 1, 1));
create index s2 on my_table (substr(pattern, 2, 1));
create index s3 on my_table (substr(pattern, 3, 1));
create index s4 on my_table (substr(pattern, 4, 1));

В зависимости от частоты планы выполнения бывают:

select max(idx)from my_table where substr(pattern, 1, 1) = 'A' and substr(pattern, 3, 1) = 'C';
----------------------------------------------------------------------------------------------
| Id  | Operation                              | Name     | Rows  | Bytes  | Cost | Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |          |     1 |      9 |  263 | 00:00:01 |
|   1 |   SORT AGGREGATE                       |          |     1 |      9 |      |          |
|   2 |    TABLE ACCESS BY INDEX ROWID BATCHED | MY_TABLE | 20326 | 182934 |  263 | 00:00:01 |
|   3 |     BITMAP CONVERSION TO ROWIDS        |          |       |        |      |          |
|   4 |      BITMAP AND                        |          |       |        |      |          |
|   5 |       BITMAP CONVERSION FROM ROWIDS    |          |       |        |      |          |
| * 6 |        INDEX RANGE SCAN                | S3       | 37358 |        |   14 | 00:00:01 |
|   7 |       BITMAP CONVERSION FROM ROWIDS    |          |       |        |      |          |
| * 8 |        INDEX RANGE SCAN                | S1       | 37358 |        |  203 | 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 6 - access(SUBSTR("PATTERN",3,1)='C')
* 8 - access(SUBSTR("PATTERN",1,1)='A')


select max(idx) from my_table where substr(pattern, 2, 1) = 'B' and substr(pattern, 3, 1) = 'C';
----------------------------------------------------------------------------------------------
| Id  | Operation                              | Name     | Rows  | Bytes  | Cost | Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |          |     1 |      9 |  415 | 00:00:01 |
|   1 |   SORT AGGREGATE                       |          |     1 |      9 |      |          |
| * 2 |    TABLE ACCESS BY INDEX ROWID BATCHED | MY_TABLE | 37358 | 336222 |  415 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN                   | S3       | 37358 |        |   14 | 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter(SUBSTR("PATTERN",2,1)='B')
* 3 - access(SUBSTR("PATTERN",3,1)='C')