Create an index for a partitioned table

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.