Given a table with 100 list partitions. Each partition size of 10GB, 30 million lines.

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 );

It is necessary to provide an opportunity for the application to read the lines with a filter in one of the fields. Only 3 partitions are needed. An hour later, this opportunity can be closed.

Request:

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

In the request, the value of the city condition changes every hour, as well as the FIELD of the second condition. That is seichas:

 where city ='NEWYORK' and aaa_id=12345

in an hour:

 where city ='NEWYORK' and aaa_id=12345;

, In 2 hours:

 where city ='NEWYORK' and aaa_id=12345;

etc..

The number of requests is about 1 million per hour. The number of possible fields for the filter is 30.

Building a local index on the entire table takes a lot of time. Keeping indexes on every possible field is too much space and slows down the insertion very much.

Question

Is it possible to build a local index only for partitions?

Oracle 12.1 Enterprise Edition.

  • Sorry, could you explain, what difficulties do you have to create indexes for a part of partitions with indexing on | off? "In the request, the value of the condition city is changed every hour ..." - but now, and after an hour, and after two, you specified the same filter. "It is necessary to provide the ability to read the lines of the application" - as you understand it, initially, unless your app can not make a request for data from some partitions? - 0xdb 3:31 pm

2 answers 2

Yes, it is possible to index only part of the partition expression INDEXING ON | OFF .

Non-indexed partitions will be marked as 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

You can change:

 alter table mydata modify partition paris indexing on;

    You can try this:

     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 );
    • How will the query on the AAA_ID field work? There is no index there. - Michael
    • You can add it. - Yaroslav