Использование секционированных индексов с секционированными таблицами

create index address_city_street_idx on address(city, street) compress 1 local;

Я считаю, что индекс идеально подходит для этого запроса, учитывая таблицу, разбитую на списки в TENANT:

select * from address where tenant = 'X' and street = 'Y' and city = 'Z' 

Чтобы ответить на вопросы 1 и 2: поскольку TENANT является ключом раздела, его не должно быть в этом индексе и, вероятно, не должно быть ни в одном индексе. Этот столбец уже используется сокращением раздела для выбора соответствующего сегмента. Эта работа выполняется во время компиляции или синтаксического анализа и практически бесплатна.

Планы выполнения в тестовом примере демонстрируют, что происходит сокращение секций. Операция PARTITION LIST SINGLE и тот факт, что в столбцах Pstart и Pstop указано число 3 вместо такой переменной, как KEY, показывают, что Oracle уже определил раздел до выполнения запроса. Oracle мгновенно отбрасывает ненужные TENANT во время компиляции, поэтому не нужно беспокоиться о дальнейшем сокращении TENANT во время выполнения с помощью индекса.

Мое предложение по индексу зависит от нескольких предположений о данных. Ни ГОРОД, ни УЛИЦА не могут однозначно идентифицировать строку для арендатора. И УЛИЦА звучит намного избирательнее, чем ГОРОД. Если в одном ГОРОДЕ есть несколько УЛИЦ, то их индексация в указанном порядке и использование сжатия индекса могут сэкономить много места.

Если индекс значительно меньше, он может иметь меньше уровней, что означает, что для поиска потребуется немного меньше операций ввода-вывода. И если он меньше, в буферном кеше может поместиться больше, что может еще больше повысить производительность.

Но с такой большой таблицей у меня есть ощущение, что BLEVEL (количество уровней индекса) будет одинаковым для обоих, и оба индекса будут слишком большими для эффективного использования кеша. Это означает, что не может быть никакой разницы в производительности между (CITY,STREET) и (STREET,CITY). Но с (CITY,STREET) и сжатием вы, по крайней мере, сможете сэкономить много места.

Тестовый пример

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

Этот тестовый пример не сильно поддерживает мое предложение. Это всего лишь отправная точка для более тщательного тестирования. Вам потребуется создать его с большим объемом данных и более реалистичным распределением данных.

--Create sample table.
create table address
(
    id number,
    street varchar2(100),
    city varchar2(100),
    state varchar2(100),
    tenant varchar2(100)
) partition by list (tenant)
(
    partition p1 values ('tenant1'),
    partition p2 values ('tenant2'),
    partition p3 values ('tenant3'),
    partition p4 values ('tenant4'),
    partition p5 values ('tenant5')
) nologging;

--Insert 5M rows.
--Note the assumptions about the selectivity of the street and city
--are critical to this issue.  Adjust the MOD as necessary.
begin
    for i in 1 .. 5 loop
        insert /*+ append */ into address
        select
            level,
            'Fake Street '||mod(level, 10000),
            'City '||mod(level, 100),
            'State',
            'tenant'||i
        from dual connect by level <= 1000000;
        commit;
    end loop;
end;
/

--Table uses 282MB.
select sum(bytes)/1024/1024 mb from dba_segments where segment_name = 'ADDRESS' and owner = user;

--Create different indexes.
create index address_city_street_idx on address(city, street) compress 1 local;
create index address_street_city_idx on address(street, city) local;

--Gather statistics.
begin
    dbms_stats.gather_table_stats(user, 'ADDRESS');
end;
/

--Check execution plan.
--Oracle by default picks STREET,CITY over CITY,STREET.
--I'm not sure why.  And the cost difference is only 1, so I think things may be different with realistic data.
explain plan for select * from address where tenant = 'tenant3' and street = 'Fake Street 50' and city = 'City 50';
select * from table(dbms_xplan.display);

/*
Plan hash value: 2845844304

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                         |     1 |    44 |     4   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE                     |                         |     1 |    44 |     4   (0)| 00:00:01 |     3 |     3 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ADDRESS                 |     1 |    44 |     4   (0)| 00:00:01 |     3 |     3 |
|*  3 |    INDEX RANGE SCAN                        | ADDRESS_STREET_CITY_IDX |     1 |       |     3   (0)| 00:00:01 |     3 |     3 |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("STREET"='Fake Street 50' AND "CITY"='City 50')
*/

--Check execution plan of forced CITY,STREET index.
--I don't suggest using a hint in the real query, this is just to compare plans.
explain plan for select /*+ index(address address_city_street_idx) */ * from address where tenant = 'tenant3' and street = 'Fake Street 50' and city = 'City 50';
select * from table(dbms_xplan.display);

/*
Plan hash value: 1084849450

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                         |     1 |    44 |     5   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE                     |                         |     1 |    44 |     5   (0)| 00:00:01 |     3 |     3 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ADDRESS                 |     1 |    44 |     5   (0)| 00:00:01 |     3 |     3 |
|*  3 |    INDEX RANGE SCAN                        | ADDRESS_CITY_STREET_IDX |     1 |       |     3   (0)| 00:00:01 |     3 |     3 |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CITY"='City 50' AND "STREET"='Fake Street 50')
*/

--Both indexes have BLEVEL=2.
select *
from dba_indexes
where index_name in ('ADDRESS_CITY_STREET_IDX', 'ADDRESS_STREET_CITY_IDX');

--CITY,STREET = 160MB, STREET,CITY=200MB.
--You can see the difference already.  It may get larger with different data distribution.
--And it may get larger with more data, as it may compress better with more repetition.
select segment_name, sum(bytes)/1024/1024 mb
from dba_segments
where segment_name in ('ADDRESS_CITY_STREET_IDX', 'ADDRESS_STREET_CITY_IDX')
group by segment_name;

Если индекс уникален, вам нужно включить TENANT, чтобы сделать его локальным. Если он не уникален, не включайте его, так как это не улучшит производительность в случае раздела LIST/RANGE. Вы можете включить его, если это хеш-раздел со многими различными значениями в одном разделе.

UPD: Однако это зависит от того, какое разделение вы используете - «статическое» или «динамическое». «Статический» — это когда все разделы определяются один раз в операторе создания таблицы и остаются неизменными во время работы приложения. «Динамический» — это когда приложение добавляет/изменяет разделы (например, ежедневный процесс добавляет разделы ежедневного списка для всех таблиц и т. д.).

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