• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » Oracle partition index

Oracle partition index

February 26, 2020 by techgoeasy Leave a Comment

In the previous article, we learned about Oracle Partitioned table. In continuation of that series,i will here be presenting the Oracle partition index

Here are the some of facts about Understanding Partitioned Indexes

Table of Contents

  • Local Partition Indexes
  • Global Partitioned Indexes
  • Global Non-partitioned Indexes
  • FAQ

Local Partition Indexes

  • A local index on a partitioned table is created where the index is partitioned in exactly the same manner as the underlying partitioned table. That is, the local index inherits the partitioning method of the table. This is known as equi-partitioning.
  • For local indexes, the index keys within the index will refer only to the rows stored in the single underlying table partition. A local index is created by specifying the LOCAL attribute, and can be created as UNIQUE or NON-UNIQUE.
  • The table and local index are either partitioned in exactly the same manner, or have the same partition key because the local indexes are automatically maintained, can offer higher availability.
  • As the Oracle database ensures that the index partitions are synchronized with their corresponding table partitions, it follows that the database automatically maintains the index partition whenever any maintenance operation is performed on the underlying tables
  • for example, when partitions are added, dropped, or merged.
  • A local index is prefixed if the partition key of the table and the index key are the same; otherwise it is a local non-prefixed index
Prefixed and Non prefixed local partition index
SQL> CREATE TABLE EXP_PART (id number, item_id number, name varchar2(20))
  PARTITION BY RANGE (id, item_id)
  (partition EXP_PART_1 values less than (10, 100),
  partition EXP_PART_2 values less than (20, 200),
  partition EXP_PART_3 values less than (30, 300),
  partition EXP_PART_4 values less than (40, 400));
 Table created.

 SQL> CREATE INDEX test_idx ON EXP_PART(id, item_id)
  LOCAL
  (partition test_idx_1,
  partition test_idx_2,
  partition test_idx_3,
  partition test_idx_4);
 Index created.

SQL> SELECT index_name, partition_name, status
  FROM user_ind_partitions where index_name='TEST_IDX'
  ORDER BY index_name, partition_name;
 INDEX_NAME PARTITION_NAME STATUS
 ---------   -----------    ------
 TEST_IDX IX_TEST_IDX_1 USABLE
 TEST_IDX IX_TEST_IDX_2 USABLE
 TEST_IDX IX_TEST_IDX_3 USABLE
 TEST_IDX IX_TEST_IDX_4 USABLE

Global Partitioned Indexes

  • A global partitioned index is an index on a partitioned or non-partitioned table that is partitioned independently, i.e. using a different partitioning key from the table. Global-partitioned indexes can be range or hash partitioned.Global partitioned indexes are more difficult to maintain than local indexes. However, they do offer a more efficient access method to any individual record.
  • During table or index interaction during partition maintenance, all partitions in a global index will be affected.
  • When the underlying table partition has any SPLIT, MOVE, DROP, or TRUNCATE maintenance operations performed on it, both global indexes and global partitioned indexes will be marked as unusable. It therefore follows that partition independence it is not possible for global indexes.
  • Depending on the type of operation performed on a table partition, the indexes on the table will be affected. When altering a table partition, the UPDATE INDEXES clause can be specified. This automatically maintains the affected global indexes and partitions.
  • The advantages of using this option are that the index will remain online and available throughout the operation, and does not have to be rebuilt once the operation has completed.
Oracle partition index ( Global Partition index)
SQL> CREATE TABLE EXP_PART (id number, item_id number, name varchar2(20))
  PARTITION BY RANGE (id, item_id)
  (partition EXP_PART_1 values less than (10, 100),
  partition EXP_PART_2 values less than (20, 200),
  partition EXP_PART_3 values less than (30, 300),
  partition EXP_PART_4 values less than (40, 400));
 Table created.

 SQL> CREATE UNIQUE INDEX EXP_PART_IDX ON EXP_PART(id, item_id)
  GLOBAL PARTITION BY RANGE (id, item_id)
  (partition EXP_PART_IDX_1 values less than (20, 200),
   partition EXP_PART_IDX_2 values less than (maxvalue, maxvalue));
 Index created.

SQL> SELECT index_name, partition_name, status
  FROM user_ind_partitions where index_name='EXP_PART_IDX'
  ORDER BY index_name, partition_name;
 INDEX_NAME PARTITION_NAME STATUS
 
 EXP_PART_IDX EXP_PART_IDX_1 USABLE
 EXP_PART_IDX EXP_PART_IDX_2 USABLE

Global Non-partitioned Indexes

  • Global non-partitioned indexes offer the same efficient access to any individual record in any partition, and behave just like a non-partitioned index. Since the index structure is not partitioned, the index is available to all partitions.
  • A scenario where this type of index would be useful is with a query that does not include the partition key of the table as a filter, but you still want the oracle optimizer to use an index

FAQ

What is the prefixed and non-prefixed index?

The index is called prefixed if the leading column(s) in the index definition is (are) the partition key column(s), otherwise it is called non-prefixed.

Can we subpartition the global index?

No, you cannot subpartition global index, but you can partition it. The only way to end up with index subpartition is to have a sub-partitioned table with local index

How to Find Whether an Index is Global or Local

By querying the column, LOCALITY, in DBA_PART_INDEXES.

Also Reads

  • Oracle database Administration Tutorials
  • https://docs.oracle.com/en/database/oracle/oracle-database/18/vldbg/index-partitioning.html#GUID-569F94D0-E6E5-45BB-9626-5506DE18FF00
  • change date format in oracle

Filed Under: Oracle, Oracle Database Tagged With: index partition, Partitioned Indexes

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Password Version in oracle
  • How to login as user without changing the password in Oracle database(alter user identified by values)
  • How to check encrypted tablespace in the Database
  • How To Export -Import TDE Master Encryption Key
  • How to Configure Auto Login TDE Wallet

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us