Возможно ли построить локальный индекс только лишь на часть partitions?

#sql #oracle #индексация #oracle12c

Дана таблица с 100 list partitions. Каждая partition размером в 10ГБ, по 30 милионов
строк.

create table mydata
(
city varchar2(100) not null,
purchase_id number not null,
reference_id number not null,
aaa_id number not null,
bbb_id number not null,
some_data1  varchar2(100) not null,
some_data2  varchar2(100) not null,
some_data3  varchar2(100) not null,
)
partition by list(city)
(partition newyork values ('NEWYORK'),
partition paris values ('PARIS'),
... 98 more partitions
);


Надо предоставить возможность приложению читать строки с фильтром по одному из полей.
Нужны только 3 partitions. Спустя час эту возможность можно закрыть.

Запрос:

select * from mydata
where city ='NEWYORK' and aaa_id=12345;


В запросе каждый час меняется значение условия city , а также ПОЛЕ второго условия.
То есть сеичас :

where city ='NEWYORK' and aaa_id=12345


, через час: 

where city ='NEWYORK' and aaa_id=12345;


, через 2 часа:

where city ='NEWYORK' and aaa_id=12345;


и т.д..

Количество запросов - около 1 миллиона в час. Количество возможных полей для фильтра
- 30.

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

Вопрос

Возможно ли  построить локальный индекс только лишь на часть partitions?

Oracle 12.1 Enterprise Edition.
    

Ответы

Ответ 1

Да, возможно проиндексировать лишь часть партиций выражением INDEXING ON|OFF.

Не индексированные партиции бдут маркированы как UNUSABLE 

create table mydata (
    city varchar2(100) not null,
    id number not null,
    data  varchar2(100) not null
)
partition by list (city) (
    partition newyork values ('NEWYORK'),
    partition paris values ('PARIS') indexing off
);

create index idx_mydata_partloc on mydata (id) local indexing partial;

select index_name, partition_name, status 
from user_ind_partitions p
where lower (index_name) like '%mydata%'
;


INDEX_NAME           PARTITION_NAME       STATUS 
-------------------- -------------------- --------
IDX_MYDATA_PARTLOC   NEWYORK              USABLE  
IDX_MYDATA_PARTLOC   PARIS                UNUSABLE


Изменить можно:

alter table mydata modify partition paris indexing on;


Ответ 2

можете попробывать так:

CREATE TABLE MYDATA (
  MYDATA_ID    NUMBER        NOT NULL,
  CITY         VARCHAR2(100) NOT NULL,
  PURCHASE_ID  NUMBER        NOT NULL,
  REFERENCE_ID NUMBER        NOT NULL,
  AAA_ID       NUMBER        NOT NULL,
  BBB_ID       NUMBER        NOT NULL,
  SOME_DATA1   VARCHAR2(100) NOT NULL,
  SOME_DATA2   VARCHAR2(100) NOT NULL,
  SOME_DATA3   VARCHAR2(100) NOT NULL,
  PRIMARY KEY (CITY, MYDATA_ID)
)
ORGANIZATION INDEX INCLUDING MYDATA_ID
OVERFLOW
TABLESPACE mydata_overflow
PARTITION BY LIST (CITY)
(
  PARTITION newyork VALUES ('NEWYORK')
  OVERFLOW TABLESPACE newyork_overflow,
  PARTITION paris VALUES ('PARIS')
  OVERFLOW TABLESPACE paris_overflow
);

Построение индекса в oracle

#oracle #индексация #индексы

Пусть создан индекс по строковой колонке (не важно какой). Как известно, он будет
представлять собой дерево, где каждый лист - подстрока. Каким образом устанавливается,
какой длины подстрока в листе?
    

Ответы

Ответ 1

Как известно, он будет представлять собой дерево, где каждый лист - подстрока.


Нет, это не так. В оф. документации говорится:


  The leaf blocks contain every indexed data value and a corresponding rowid used
to locate the actual row.


Каждая запись (leaf entry) в листке (leaf node) содержит пару key+value, соостоящая
из полного значения индексированой колонки и адреса-указателя на соответствующую строку
в таблице данных.

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

create table tab1 as
    select dbms_random.string('u', 80) itemNo, rpad ('A', 80, 'A') itemDs 
    from xmltable('1 to 100');

create unique index idx_tab1_uniq on tab1 (itemNo);
create index idx_tab1_same on tab1 (itemDs);

exec dbms_stats.gather_table_stats(user, upper('tab1'));

select index_name, blevel, leaf_blocks, num_rows, clustering_factor 
from user_indexes where table_name=upper('tab1') 
order by 2,3;

INDEX_NAME           BLEVEL LEAF_BLOCKS   NUM_ROWS CLUSTERING_FACTOR
---------------- ---------- ----------- ---------- -----------------
IDX_TAB1_SAME             1           2        100                 3
IDX_TAB1_UNIQ             1           2        100                63


Получили уровень ветвления 1 по 2 блока листьев .

Проведём анализ индексов:

analyze index idx_tab1_uniq validate structure offline;
analyze index idx_tab1_same validate structure offline;
-- выполнить после анализа каждого индекса
select name, height, lf_blks, lf_rows, lf_rows_len, br_blks, br_rows, br_rows_len
from index_stats t
;

NAME                 HEIGHT    LF_BLKS    LF_ROWS LF_ROWS_LEN
---------------- ---------- ---------- ---------- -----------
IDX_TAB1_UNIQ             2          2        100        9100
IDX_TAB1_SAME             2          2        100        9200


Оба индекса не намного отличаются по длине записей:
lf_rows_len/lf_rows=avgLength 91 и 92 байта средняя длина записи.

В дампе блоков с листьями (здесь только выдержка двух первых записей в первом листе
каждого индекса) обратите внимание на длину записей:

Leaf block dump
===============
header address 139975326998628=0x7f4e8ba4a064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 78
kdxcofbo 192=0xc0
kdxcofeo 1012=0x3f4
kdxcoavs 820
kdxlespl 0
kdxlende 0
kdxlenxt 50335373=0x3000e8d
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[7942] flag: -------, lock: 0, len=90
col 0; len 80; (80):
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41
col 1; len 6; (6):  03 00 0e 7b 00 00
row#1[7852] flag: -------, lock: 0, len=90
col 0; len 80; (80):
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41
col 1; len 6; (6):  03 00 0e 7b 00 01

Leaf block dump
===============
header address 139975326998628=0x7f4e8ba4a064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 78
kdxcofbo 192=0xc0
kdxcofeo 1090=0x442
kdxcoavs 898
kdxlespl 0
kdxlende 0
kdxlenxt 50335365=0x3000e85
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8032
row#0[7943] flag: -------, lock: 0, len=89, data:(6):  03 00 0e 7c 00 0a
col 0; len 80; (80):
 41 43 45 48 54 42 41 58 4f 52 45 5a 46 56 46 47 45 59 4a 51 53 49 4e 55 5a
 56 54 4c 52 42 4e 42 42 48 54 4f 52 48 4a 52 4a 5a 55 43 42 44 43 51 49 43
 43 48 47 42 56 41 58 58 54 4c 43 4e 59 50 59 59 4e 57 43 50 58 4f 59 44 53
 55 4d 51 4b 48
row#1[7854] flag: -------, lock: 0, len=89, data:(6):  03 00 0e 7c 00 11
col 0; len 80; (80):
 41 43 49 51 41 57 4b 4b 4d 4b 46 44 4d 57 59 54 54 42 43 56 49 4a 58 4f 44
 4e 51 41 56 49 53 54 55 47 42 49 4c 51 48 4a 4f 45 52 42 58 53 47 41 4a 56
 54 51 59 43 4f 44 50 59 51 56 45 43 44 47 51 48 4c 59 4e 46 44 48 47 55 58
 47 42 45 50 4d

Поиск текста с учетом звучания на русском и английском языках

#elasticsearch #индексация #полнотекстовый_поиск #lucene

Какими средствами лучше всего организовать индексирование и поиск русских слов, которые
являются записью английской транскрипции? Например, слово vision в русском варианте
будет записано как вижн или вижен, но значение сути не меняется. Поиск, соответственно,
должен быть произведен как по vision так и по вижн без потери результатов на выходе. 
Soundex и Metaphone, на сколько я понимаю, работают строго в одном языке без скрещиваний.
    

Ответы

Ответ 1

Мне кажется, в большинстве случаев, будет достаточно функционала синонимов. База
данных, скорее всего, имеет определенную тематическую направленность, например медицина
или электротехника. И чтобы настроить поиск с учетом транслита/звучания слов на разных
языках достаточно заполнить 500-1000 синонимов. Но использовать анализатор с фильтром
типа synonym не для индексации, а для анализа поискового запроса.

Синонимы лучше вынести в отдельный файл, а не хранить в настройках.

{
    "index" : {
        "analysis" : {
            "analyzer" : {
                "synonym" : {
                    "tokenizer" : "whitespace",
                "filter" : ["synonym"]
            }
        },
        "filter" : {
                "synonym" : {
                    "type" : "synonym",
                "synonyms_path" : "analysis/synonym.txt"
            }
        }
    }
}




Пример содержимого файла synonym.txt: 

vision, вижн, вижен



Если этого недостаточно, то получаем очень сложную задачу. 

Если дело ограничивается транслитом, то смотреть в сторону плагина ICU Transform
Token Filter
Если нет, и нужно vision, вижн, вижен для всех возможных слов, то смотреть в сторону
машинного обучения. Это решение Rosette for Elasticsearch, видимо платное.

Полезные ссылки.


Обсуждение данной темы
Rosette name translator - есть
поддержка русского языка


Soundex и Metaphone - это фонетические алгоритмы, работают, как вы написали, в одном
языке. Это не то, что вам нужно.

Поиск текста с учетом звучания на русском и английском языках

Какими средствами лучше всего организовать индексирование и поиск русских слов, которые являются записью английской транскрипции? Например, слово vision в русском варианте будет записано как вижн или вижен, но значение сути не меняется. Поиск, соответственно, должен быть произведен как по vision так и по вижн без потери результатов на выходе. Soundex и Metaphone, на сколько я понимаю, работают строго в одном языке без скрещиваний.

Ответ

Мне кажется, в большинстве случаев, будет достаточно функционала синонимов. База данных, скорее всего, имеет определенную тематическую направленность, например медицина или электротехника. И чтобы настроить поиск с учетом транслита/звучания слов на разных языках достаточно заполнить 500-1000 синонимов. Но использовать анализатор с фильтром типа synonym не для индексации, а для анализа поискового запроса.
Синонимы лучше вынести в отдельный файл, а не хранить в настройках.
{ "index" : { "analysis" : { "analyzer" : { "synonym" : { "tokenizer" : "whitespace", "filter" : ["synonym"] } }, "filter" : { "synonym" : { "type" : "synonym", "synonyms_path" : "analysis/synonym.txt" } } } }

Пример содержимого файла synonym.txt:
vision, вижн, вижен

Если этого недостаточно, то получаем очень сложную задачу.
Если дело ограничивается транслитом, то смотреть в сторону плагина ICU Transform Token Filter Если нет, и нужно vision, вижн, вижен для всех возможных слов, то смотреть в сторону машинного обучения. Это решение Rosette for Elasticsearch, видимо платное.
Полезные ссылки.
Обсуждение данной темы Rosette name translator - есть поддержка русского языка
Soundex и Metaphone - это фонетические алгоритмы, работают, как вы написали, в одном языке. Это не то, что вам нужно.

Построение индекса в oracle

Пусть создан индекс по строковой колонке (не важно какой). Как известно, он будет представлять собой дерево, где каждый лист - подстрока. Каким образом устанавливается, какой длины подстрока в листе?

Ответ

Как известно, он будет представлять собой дерево, где каждый лист - подстрока.
Нет, это не так. В оф. документации говорится:
The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row.
Каждая запись (leaf entry) в листке (leaf node) содержит пару key+value, соостоящая из полного значения индексированой колонки и адреса-указателя на соответствующую строку в таблице данных.
Например, построим два индекса с ключами одной и той же длины, один уникальный со случайными символами, и другой безсмысленный, с одним и тем же символом заполнителем.
create table tab1 as select dbms_random.string('u', 80) itemNo, rpad ('A', 80, 'A') itemDs from xmltable('1 to 100');
create unique index idx_tab1_uniq on tab1 (itemNo); create index idx_tab1_same on tab1 (itemDs);
exec dbms_stats.gather_table_stats(user, upper('tab1'));
select index_name, blevel, leaf_blocks, num_rows, clustering_factor from user_indexes where table_name=upper('tab1') order by 2,3;
INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR ---------------- ---------- ----------- ---------- ----------------- IDX_TAB1_SAME 1 2 100 3 IDX_TAB1_UNIQ 1 2 100 63
Получили уровень ветвления 1 по 2 блока листьев .
Проведём анализ индексов:
analyze index idx_tab1_uniq validate structure offline; analyze index idx_tab1_same validate structure offline; -- выполнить после анализа каждого индекса select name, height, lf_blks, lf_rows, lf_rows_len, br_blks, br_rows, br_rows_len from index_stats t ;
NAME HEIGHT LF_BLKS LF_ROWS LF_ROWS_LEN ---------------- ---------- ---------- ---------- ----------- IDX_TAB1_UNIQ 2 2 100 9100 IDX_TAB1_SAME 2 2 100 9200
Оба индекса не намного отличаются по длине записей: lf_rows_len/lf_rows=avgLength 91 и 92 байта средняя длина записи.
Дамп блоков с листьями выглядит так (здесь только выдержка двух первых записей в первом листе каждого индекса):
Leaf block dump =============== header address 139975326998628=0x7f4e8ba4a064 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 78 kdxcofbo 192=0xc0 kdxcofeo 1012=0x3f4 kdxcoavs 820 kdxlespl 0 kdxlende 0 kdxlenxt 50335373=0x3000e8d kdxleprv 0=0x0 kdxledsz 0 kdxlebksz 8032 row#0[7942] flag: -------, lock: 0, len=90 col 0; len 80; (80): 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 col 1; len 6; (6): 03 00 0e 7b 00 00 row#1[7852] flag: -------, lock: 0, len=90 col 0; len 80; (80): 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 col 1; len 6; (6): 03 00 0e 7b 00 01
Leaf block dump =============== header address 139975326998628=0x7f4e8ba4a064 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 0 kdxconro 78 kdxcofbo 192=0xc0 kdxcofeo 1090=0x442 kdxcoavs 898 kdxlespl 0 kdxlende 0 kdxlenxt 50335365=0x3000e85 kdxleprv 0=0x0 kdxledsz 6 kdxlebksz 8032 row#0[7943] flag: -------, lock: 0, len=89, data:(6): 03 00 0e 7c 00 0a col 0; len 80; (80): 41 43 45 48 54 42 41 58 4f 52 45 5a 46 56 46 47 45 59 4a 51 53 49 4e 55 5a 56 54 4c 52 42 4e 42 42 48 54 4f 52 48 4a 52 4a 5a 55 43 42 44 43 51 49 43 43 48 47 42 56 41 58 58 54 4c 43 4e 59 50 59 59 4e 57 43 50 58 4f 59 44 53 55 4d 51 4b 48 row#1[7854] flag: -------, lock: 0, len=89, data:(6): 03 00 0e 7c 00 11 col 0; len 80; (80): 41 43 49 51 41 57 4b 4b 4d 4b 46 44 4d 57 59 54 54 42 43 56 49 4a 58 4f 44 4e 51 41 56 49 53 54 55 47 42 49 4c 51 48 4a 4f 45 52 42 58 53 47 41 4a 56 54 51 59 43 4f 44 50 59 51 56 45 43 44 47 51 48 4c 59 4e 46 44 48 47 55 58 47 42 45 50 4d