Create an index for a partitioned table

Global Index
================
Create index month_ix on sales (sales_month)

Local index
================
Create index loc_dept_ix on dept (deptno) local;

Global partition Index
================
Create index month_ix on sales (sales_month)
Global partition by range (sales_month)
(Partition pm1_ix values less than (2)
Partition pm2_ix values less than (3)
Partition pm3_ix values less than (4)
Partition pm4_ix values less than (5)
Partition pm5_ix values less than (6)
Partition pm6_ix values less than (7)
Partition pm7_ix values less than (8)
Partition pm8_ix values less than (9)
Partition pm9_ix values less than (10)
Partition pm10_ix values less than (11)
Partition pm11_ix values less than (12)
Partition pm12_ix values less than (maxvalue ));

You can create an index for a partitioned table like a common table. You can create a local index and a global index for a partitioned table. Global indexes are used when there are many transactions in a partition and the uniqueness of data records in all partitions must be ensured.
3.1 create a local index partition:
SQL> Create index dinya_idx_t on dinya_test (item_id)
2 Local
3 (
4 partition idx_1 tablespace dinya_space01,
5 partition idx_2 tablespace dinya_space02,
6 partition idx_3 tablespace dinya_space03
7 );

Index created.

SQL>

Looking at the query execution plan, we can see from the following execution plan that the system has used the index:
SQL> select * From dinya_test partition (part_01) t where T. item_id = 12;

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 2 card = 1 bytes = 187)
1 0 Table Access (by local index rowid) of 'dinya _ test' (cost =
2 card = 1 bytes = 187)
2 1 index (range scan) of 'dinya _ idx_t '(NON-UNIQUE) (cost = 1
Card = 1)
Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
4 consistent gets
0 physical reads
0 redo size
334 bytes sent via SQL * Net to client
309 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (Disk)
2 rows processed

SQL>

3.2 Create a global index partition.
When a global index is created, the global clause allows you to specify the index range value. The range value is the range value of the index field:
SQL> Create index dinya_idx_t on dinya_test (item_id)
2 global partition by range (item_id)
3 (
4 partition idx_1 valueless than (1000) tablespace dinya_space01,
5 partition idx_2 values less than (10000) tablespace dinya_space02,
6 partition idx_3 values less than (maxvalue) tablespace dinya_space03
7 );

Index created.

SQL>

In this example, create an index partition for the item_id field of the table. You can also create an index for the entire table without specifying the index partition name. For example:
SQL> Create index dinya_idx_t on dinya_test (item_id );

Index created.

SQL>

Similarly, according to the execution plan, the global index can be used:
SQL> select * From dinya_test t where T. item_id = 12;

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 2 card = 3 bytes = 561)
1 0 Table Access (by global index rowid) of 'dinya _ test' (Cost
= 2 card = 3 bytes = 561)

2 1 index (range scan) of 'dinya _ idx_t '(NON-UNIQUE) (cost = 1
Card = 3)
Statistics
----------------------------------------------------------
5 recursive cballs
0 dB block gets
10 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL * Net to client
309 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
3 sorts (memory)
0 sorts (Disk)
5 rows processed