A demo of nonprefix & prefix Index

The following is an experiment I have done on partition indexes to deepen my understanding of related concepts:

For related concepts, refer to my blog:

Http://blog.csdn.net/waterxcfg304/article/details/8509734

1, create a partition table

Create Table batch_usr.sales_history
(
Prod_id number not null,
Cust_id number not null,
Time_id date not null,
Channel_id number not null,
Promo_id number not null,
Quantity_sold number (10, 2) not null,
Amount_sold number (10, 2) not null
)
Partition by range (time_id)
(
Partition p1998_1h values less than (to_date ('2017-07-01 ', 'yyyy-mm-dd') tablespace data1,
Partition p1998_2h values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace data2,
Partition p1999_1h values less than (to_date ('2017-07-01 ', 'yyyy-mm-dd') tablespace data3,
Partition p%_2h values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace data4,
Partition p2000_1h values less than (to_date ('2017-07-01 ', 'yyyy-mm-dd') tablespace data5,
Partition p2000_2h values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace data6,
Partition p2001_1h values less than (to_date ('2017-07-01 ', 'yyyy-mm-dd') tablespace data7,
Partition p2001_2h values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace data8
);

2, populate data into sales_history table
Insert into batch_usr.sales_history select * from SH. Sales where to_number (to_char (time_id, 'yyyy') in );

 

3, the followings are different types of indexes that will be created.

Firstly global indexes:
Yes
Create index batch_usr.sales_hist_pid_idx on batch_usr.sales_history (prod_id) Global
Partition by hash (prod_id)
Partitions 8 store in (Batch)
Nologging
Parallel;
Drop index batch_usr.sales_hist_pid_idx;

No (the global index must be prefix)
Create index batch_usr.sales_hist_pid_idx on batch_usr.sales_history (time_id) Global
Partition by hash (prod_id)
Partitions 8 store in (Batch)
Nologging
Parallel;
Error at line 2:
ORA-14038: Global partitioned index must be prefixed

Yes
Create index batch_usr.sales_hist_pid_idx on batch_usr.sales_history (time_id) Global
Partition by hash (time_id)
Partitions 8 store in (Batch)
Nologging
Parallel;
Drop index batch_usr.sales_hist_pid_idx;

No (the global index must be prefix)
Create index batch_usr.sales_hist_pid_idx on batch_usr.sales_history (prod_id) Global
Partition by hash (time_id)
Partitions 8 store in (Batch)
Nologging
Parallel;

Error at line 2:
ORA-14038: Global partitioned index must be prefixed

Secondly local indexes:

Non-Prefix index (index column and table partition column are different)

SQL> Create index batch_usr.sales_hist_pid_idx on batch_usr.sales_history (prod_id) local;

Index created.

SQL> drop index batch_usr.sales_hist_pid_idx;

Index dropped.

 

An index with a prefix (the same as a table partition)

SQL> Create index batch_usr.sales_hist_pid_idx on batch_usr.sales_history (time_id) local;

Index created.

SQL> drop index batch_usr.sales_hist_pid_idx;

Index dropped.