Create a hash (hash) partition!

Source: Internet
Author: User
Tags create index

--Create a hash partition table:

Sql> CREATE TABLE T_partition_hash (ID number,name varchar2) 2 partition by hash (ID) (3 partition T_HASH_P1 T Ablespace tbspart01 4 partition t_hash_p2 tablespace tbspart02, 5 partition t_hash_p3 tablespace tbspart03, 6 PA

Rtition T_HASH_P4 tablespace tbspart04);

Table has been created. Sql> Select Table_name,partitioning_type,partition_count from user_part_tables where table_name = ' T_PARTITION_  

HASH '; table_name Partiti partition_count----------------------------------------------------t_partition_ Hash Hash 4 sql> select Partition_name,high_value,tablespace_name from User_tab_partiti  

ONS WHERE table_name = ' t_partition_hash '; Partition_name high_value tablespace_name--------------------------------------------------------------                                  --------t_hash_p1 TBSPART01 t_hash_p2 TBSPART02 t_hash_p3 TBSPART03 T_hash_P4 TBSPART04 

To achieve the same effect, you can also do this: (There is no direct correspondence between the number of partitions and the number of tablespaces available for use.) The number of partitions is not necessarily equal to the number of tablespaces. )

sql> drop table T_partition_hash;

The table has been deleted.

sql> CREATE TABLE T_partition_hash (ID number,name varchar2 (
  2) partition by  hash (ID) Partitions 3 sto Re in (tbspart01,tbspart02,tbspart03);

Table has been created.

sql> Select Table_name,partitioning_type,partition_count from user_part_tables where table_name = ' T_PARTITION _hash ';

table_name                     partiti partition_count
----------------------------------------------------
t_partition _hash               HASH                  3

sql> Select Partition_name,high_value,tablespace_name from User_tab_partitions where table_name = ' T_partition_hash ';  

Partition_name                 high_value tablespace_name
----------------------------------------------------------- -----------
sys_p24                                   TBSPART01
sys_p25                                   TBSPART02
sys_p26                                   TBSPART03


--Create Global index hash partition:

Sql> CREATE index idx_parti_hash_id on T_partition_hash (ID)
  2  Global partition by hash (ID) Partitions 3 store I N (tbspart01,tbspart02,tbspart03);

The index has been created.

sql> Select Index_name,partitioning_type,partition_count from user_part_indexes where index_name = ' IDX_PARTI_ hash_id ';

Index_name                     partiti partition_count
----------------------------------------------------
idx_parti_ hash_id              HASH                  3

sql> Select Partition_name,high_value,tablespace_name from User_ind_partitions where index_name = ' idx_parti_hash_id '; 

Partition_name                 high_value tablespace_name
----------------------------------------------------------- -----------
sys_p30                                   TBSPART01
sys_p31                                   TBSPART02
sys_p32                                   TBSPART03

Fully:
Ø range and hash partitions are only supported in 10g for global index partitions, so they are not mentioned in subsequent examples.
Ø in the case of a local index partition, its partition form depends entirely on the form of the table in which the index is located, no matter how difficult it is to create the syntax or understand the difficulty, and therefore no further examples are provided.
Ø Note that if you do not explicitly specify global or local when you create an index, global is the default.
Ø Note that if you do not explicitly specify a partition clause when you create a global index, the default is not to partition (nonsense).

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.