In the previous blog post, I had demonstrated a Global (Non-Partitioned) Index and a Local (Partitioned) Index. A Global Index itself may be partitioned. A Local Index is actually Equi-Partitioned with the Tablee.
Let me demonstrate a Global Partitioned Index on the same table now.
Given the SALES_DATA table which is partitioned by SALE_DATE, I might want to support queries by CUSTOMER_ID. What if I have millions of customers? My CUSTOMERS table might be partitioned by CUSTOMER_ID. Can I have an Index on SALES_DATA that is also partitioned by CUSTOMER_ID ?
This is a Global Partitioned Index which is prefixed on a left prefix (CUSTOMER_ID) of the Index Column(s). Oracle does not support Global Partitioned Indexes that are non-prefixed. Let me demonstrated the difference between the two.
My first attempt above is valid because the Index is partitioned on CUSTOMER_ID although it is defined on (CUSTOMER_ID, SALE_DATE). Thus, it is partitioned on a left prefix. The second index attempt fails because the Partition Key (CUSTOMER_ID) is not a left prefix (part of) the Index key (SALE_DATE alone).
Remember : The Index I've created is partitioned on CUSTOMER_ID while the Table itself is partitioned on SALE_DATE.
This Index is a Global Index so any Partition Maintenance operation (TRUNCATE, DROP, SPLIT, MERGE) may result in the *entire* Index becoming UNUSABLE unless the UPDATE INDEXES clause is used in the Partition Maintenance DDL statement. This was demonstrated with the Primary Key index being a Global Index in my earlier blog post.
What about Bitmap Indexes ? Can a Bitmap Index on a partitioned Table be either Global or Local ? Oracle does not support Global Bitmap Indexes. A Bitmap Index on a Partitioned Table has to be Local.
As demonstrated earlier, a Local Index automatically inherits the Partition Name and Tablespace Name from the Table Partitions.
To summarise, these are the Indexes on my SALES_DATA table :
Remember that for a Partitioned Index (either Global or Local), the Tablespace Name and Status are attributes at the Partition definition level not at the Index definition.
Let me demonstrate a Global Partitioned Index on the same table now.
Given the SALES_DATA table which is partitioned by SALE_DATE, I might want to support queries by CUSTOMER_ID. What if I have millions of customers? My CUSTOMERS table might be partitioned by CUSTOMER_ID. Can I have an Index on SALES_DATA that is also partitioned by CUSTOMER_ID ?
SQL> l 1 create index sales_data_glbl_part_ndx_2 2 on sales_data (customer_id) 3 global 4 partition by range (customer_id) 5 ( 6 partition p_1 values less than (501000) tablespace new_indexes, 7 partition p_2 values less than (1000001) tablespace new_indexes, 8 partition p_3 values less than (1500001) tablespace new_indexes, 9 partition p_4 values less than (maxvalue) 10* ) SQL> / Index created. SQL>
This is a Global Partitioned Index which is prefixed on a left prefix (CUSTOMER_ID) of the Index Column(s). Oracle does not support Global Partitioned Indexes that are non-prefixed. Let me demonstrated the difference between the two.
SQL> drop index sales_data_glbl_part_ndx_2;
Index dropped.
SQL>
SQL> l
1 create index sales_data_glbl_part_ndx_2
2 on sales_data (customer_id, sale_date)
3 global
4 partition by range (customer_id)
5 (
6 partition p_1 values less than (501000) tablespace new_indexes,
7 partition p_2 values less than (1000001) tablespace new_indexes,
8 partition p_3 values less than (1500001) tablespace new_indexes,
9 partition p_4 values less than (maxvalue)
10* )
SQL> /
Index created.
SQL>
SQL>
SQL> l
1 create index sales_data_glbl_part_ndx_3
2 on sales_data (sale_date)
3 global
4 partition by range (customer_id)
5 (
6 partition p_1 values less than (501000) tablespace new_indexes,
7 partition p_2 values less than (1000001) tablespace new_indexes,
8 partition p_3 values less than (1500001) tablespace new_indexes,
9 partition p_4 values less than (maxvalue)
10* )
SQL> /
partition by range (customer_id)
*
ERROR at line 4:
ORA-14038: GLOBAL partitioned index must be prefixed
SQL>
SQL> !oerr ora 14038
14038, 00000, "GLOBAL partitioned index must be prefixed"
// *Cause: User attempted to create a GLOBAL non-prefixed partitioned index
// which is illegal
// *Action: If the user, indeed, desired to create a non-prefixed
// index, it must be created as LOCAL; otherwise, correct the list
// of key and/or partitioning columns to ensure that the index is
// prefixed
SQL>
My first attempt above is valid because the Index is partitioned on CUSTOMER_ID although it is defined on (CUSTOMER_ID, SALE_DATE). Thus, it is partitioned on a left prefix. The second index attempt fails because the Partition Key (CUSTOMER_ID) is not a left prefix (part of) the Index key (SALE_DATE alone).
Remember : The Index I've created is partitioned on CUSTOMER_ID while the Table itself is partitioned on SALE_DATE.
This Index is a Global Index so any Partition Maintenance operation (TRUNCATE, DROP, SPLIT, MERGE) may result in the *entire* Index becoming UNUSABLE unless the UPDATE INDEXES clause is used in the Partition Maintenance DDL statement. This was demonstrated with the Primary Key index being a Global Index in my earlier blog post.
What about Bitmap Indexes ? Can a Bitmap Index on a partitioned Table be either Global or Local ? Oracle does not support Global Bitmap Indexes. A Bitmap Index on a Partitioned Table has to be Local.
SQL> create bitmap index sales_data_glbl_ndx_4 2 on sales_data(product_id) 3 / on sales_data(product_id) * ERROR at line 2: ORA-25122: Only LOCAL bitmap indexes are permitted on partitioned tables SQL> !oerr ora 25122 25122, 00000, "Only LOCAL bitmap indexes are permitted on partitioned tables" // *Cause: An attempt was made to create a global bitmap index on a partioned // table. // *Action: create a local bitmap index instead. SQL> SQL> create bitmap index sales_data_lcl_ndx_2 2 on sales_data(product_id) local 3 / Index created. SQL> SQL> select partition_name, tablespace_name 2 from user_tab_partitions 3 where table_name = 'SALES_DATA' 4 order by partition_position 5 / PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ P_2018 TBS_YEAR_2018 P_2019 TBS_YEAR_2019 P_2020 TBS_YEAR_2020 P_MAXVALUE USERS SQL> select partition_name, tablespace_name, status 2 from user_ind_partitions 3 where index_name = 'SALES_DATA_LCL_NDX_2' 4 order by partition_position 5 / PARTITION_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ -------- P_2018 TBS_YEAR_2018 USABLE P_2019 TBS_YEAR_2019 USABLE P_2020 TBS_YEAR_2020 USABLE P_MAXVALUE USERS USABLE SQL>
As demonstrated earlier, a Local Index automatically inherits the Partition Name and Tablespace Name from the Table Partitions.
To summarise, these are the Indexes on my SALES_DATA table :
SQL> l
1 select index_name, index_type, partitioned, tablespace_name, status
2 from user_indexes
3* where table_name = 'SALES_DATA'
SQL> /
INDEX_NAME INDEX_TYPE PAR
------------------------------ --------------------------- ---
TABLESPACE_NAME STATUS
------------------------------ --------
SYS_C0017514 NORMAL NO
HEMANT VALID
SALES_DATA_LCL_NDX_1 NORMAL YES
N/A
SALES_DATA_GLBL_PART_NDX_2 NORMAL YES
N/A
SALES_DATA_LCL_NDX_2 BITMAP YES
N/A
SQL>
Remember that for a Partitioned Index (either Global or Local), the Tablespace Name and Status are attributes at the Partition definition level not at the Index definition.
No comments:
Post a Comment