Возможно ли построить локальный индекс только лишь на часть 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
);