Local Index vs. Global Index em tabela particionada
31/03/2010
A maioria das pessoas tem essas perguntas sobre Index Partition:
- O que é um Local Index?
- O que é um Global Index?
- Quando você forçaria a criação de um Global Index em uma partition table?
- Quando você recomendaria criar um Global Index em vez de um Local Index?
Para responder a essas perguntas….
1. O que é um Local Index?
Local Indexes particionados são mais fáceis de gerenciar, cada partição do Local Index está associado a uma partição. Eles também oferecem maior disponibilidade e são comuns em ambientes de DSS. Quando tomamos qualquer ação (MERGE, SPLIT,EXCHANGE etc) em um Local Index, isso impacta apenas aquela partição e as outras estarão disponíveis. Nós não podemos adicionar explicitamente um Local Index para uma nova partição. O Local Index será adicionado implicitamente a nova partição, quando for criada uma nova partição na tabela. Da mesma forma, não podemos dropar o índice local em uma partição específica. Ele pode ser dropado automaticamente quando nós dropamos a partição da tabela subjacente. Local Indexes podem ser UNIQUE quando a chave da partição é parte do índice composto. Unique Local Indexes são úteis para o ambiente OLTP. Podemos também criar bitmap indexes em tabelas, com a restrição de que os índices de bitmap deve ser local para a tabela particionada. Eles não podem ser Global Indexes.
SQL> CREATE TABLE employees 2 (employee_id NUMBER(4) NOT NULL, 3 last_name VARCHAR2(10), 4 department_id NUMBER(2)) 5 PARTITION BY RANGE (department_id) 6 (PARTITION employees_part1 VALUES LESS THAN (10) TABLESPACE ODS_STAGE_DATA, 7 PARTITION employees_part2 VALUES LESS THAN (20) TABLESPACE ODS_STAGE_DATA, 8 PARTITION employees_part3 VALUES LESS THAN (30) TABLESPACE ODS_STAGE_DATA); Table created. SQL> declare 2 v_no number :=1; 3 begin 4 delete employees; 5 for i in 1..10 loop 6 insert into employees values(v_no,'name...',v_no); 7 v_no := v_no+1; 8 end loop; 9 end; 10 / PL/SQL procedure successfully completed. SQL> SQL> create index idx_local on employees(last_name) local; Index created. SQL>
2. O que é Global Index?
Global Indexe são usado em ambientes OLTP e oferecem um acesso eficiente a qualquer registro individual. Temos dois tipos de Global Index: Eles são global Non-partitioned index e Global partitioned index. Global Non-partitioned index se comportam como um índice não particionado.
A chave de partição de um Global partitioned index é independente da chave de partição da tabela. A maior partição de um Global Index deve ter um limite de partição, cujos todos valores são MAXVALUE. Se você quiser adicionar uma nova partição, sempre, é preciso dividir a partição MAX. Se um Global Index Partition está vazio, você pode dropá-lo explicitamente com um ALTER INDEX DROP PARTITION. Se um Global Index Partition contém dados, ao dropar a partição faz com que a partição mais próxima seja marcada como inutilizável. Não é possível dropar a maior partição de um índice global.
Exemplo de Global Non-partitioned index:
SQL> CREATE INDEX employees_global_idx ON employees(employee_id); Index created. SQL>
Exemplo de Global Partitioned index:
SQL> CREATE INDEX employees_global_part_idx ON employees(employee_id) 2 GLOBAL PARTITION BY RANGE(employee_id) 3 (PARTITION p1 VALUES LESS THAN(3), 4 PARTITION p2 VALUES LESS THAN(6), 5 PARTITION p3 VALUES LESS THAN(9)); PARTITION p3 VALUES LESS THAN(9)) * ERROR at line 5: ORA-14021: MAXVALUE must be specified for all columns SQL> CREATE INDEX employees_global_part_idx ON employees(employee_id) 2 GLOBAL PARTITION BY RANGE(employee_id) 3 (PARTITION p1 VALUES LESS THAN(3), 4 PARTITION p2 VALUES LESS THAN(6), 5 PARTITION p3 VALUES LESS THAN(11), 6 PARTITION p4 VALUES LESS THAN(20), 7 PARTITION p5 VALUES LESS THAN(MAXVALUE)); Index created.
Agora a partição p4 está vazia. Vamos dropar a partição vazia e ver o status:
SQL> select count(*) from employees where 2 employee_id between 12 and 20; COUNT(*) ---------- 0 SQL> ALTER index employees_global_part_idx drop partition p4; Index altered. SQL> SELECT partition_name,status from user_ind_partitions where 2 index_name='EMPLOYEES_GLOBAL_PART_IDX'; PARTITION_NAME STATUS ------------------------------ -------- P1 USABLE P2 USABLE P3 USABLE P5 USABLE
Agora vamos dropar a partição P3 e ver o estado. Ao dropar essa partição, isso deve invalidar a partição mais próxima. Aqui a partição P5 é a próxima maior partição.
SQL> alter index employees_global_part_idx drop partition p3; Index altered. SQL> SELECT partition_name,status from user_ind_partitions where 2 index_name='EMPLOYEES_GLOBAL_PART_IDX'; PARTITION_NAME STATUS ------------------------------ -------- P1 USABLE P2 USABLE P5 UNUSABLE SQL> alter index employees_global_part_idx rebuild; alter index employees_global_part_idx rebuild * ERROR at line 1: ORA-14086: a partitioned index may not be rebuilt as a whole SQL> alter index employees_global_part_idx rebuild partition p5; Index altered. SQL> SELECT partition_name,status from user_ind_partitions where 2 index_name='EMPLOYEES_GLOBAL_PART_IDX'; PARTITION_NAME STATUS ------------------------------ -------- P1 USABLE P2 USABLE P5 USABLE
Partition Index podem ser mantidos usando UPDATE GLOBAL INDEXES. Os índice estarão disponíveis durante a manutenção e está disponível online. Nós não precisamos de rebuild o índice após a manutenção do índice.
Por exemplo,
SQL> alter table employees drop partition employees_part3 2 update global indexes; Table altered. SQL> SELECT partition_name,status from user_ind_partitions where 2 index_name='EMPLOYEES_GLOBAL_PART_IDX'; PARTITION_NAME STATUS ------------------------------ -------- P1 USABLE P2 USABLE P5 USABLE SQL>
3. Quando você forçaria a criação de Global index em uma Partition table?
Quando você cria uma chave primária, você será forçado a criar um Global Index. Quando você cria Unique Index, você é forçado a criar o Global Index. Manter a coluna única é a razão mais comum para os índices globais. Se você tentar criar um Local Index em chave única, você receberá o erro abaixo.
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
Assim, podemos criar Local Index em chave única, quando adicionamos a chave de partição como parte da chave composta no índice. Vamos demonstrar isto:
No exemplo abaixo, o Unique Index falhou já que a chave da partição não é parte da chave composta do índice.
SQL> create unique index idx_emp_id on employees(employee_id) local; create unique index idx_emp_id on employees(employee_id) local * ERROR at line 1: ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
No caso abaixo, ele permite criar um Global Index:
SQL> create unique index idx_emp_id on employees(employee_id); Index created. SQL> drop index idx_emp_id; Index dropped.
O caso abaixo, o Unique Index é criado com sucesso, pois a chave DEPARTMENT_ID é parte da chave composta.
SQL> create unique index idx_emp_id on employees(employee_id,department_id) local; Index created. SQL>
4. Quando você recomendaria criar Global Index vs. Local Index?
Podemos usar Global Index se uma consulta retorna um número pequeno de linhas a partir de um número potencialmente grande de partições.
Texto baseado no blog do Govind
Tags: Index, Oracle 10g, Oracle 11g, Partition, Tuning
