Oracle Partition Table learning Summary

In the book written by Tom, this part is still relatively detailed. But in general, the basic concepts should be clear, so noteProgramWhat employees need to know
1. Range Partition)

Create Table range_example
(Range_key_column date,
Data varchar2 (20)
)
Partition by range (range_key_column)
(Partition part_1 values less
(To_date ('2014/1/123', 'dd/MM/yyyy ')),
Partition part_2 values less
(To_date ('2014/1/123', 'dd/MM/yyyy '))

Partition part_3 values less
(Maxvalue)
)
/

The daily partition by quarter, month, and so on is a good example of range partitioning .. note that the preceding partition 3 means that if other values are used, partition 3 will be used.
2 hash partitioning
It is sorted by hash.
Create Table hash_example
(Hash_key_column date,
Data varchar2 (20)
)
Partition by hash (hash_key_column)
(Partition part_1 tablespace P1,
Partition part_2 tablespace p2
)
/

Note that the hash partition must be built to the Npower of 2. Otherwise, the data is not even. It is very good that the books of Master Tom have detailed tests.

 

3. List partitions
If we find that some values are discrete, such as state, province, and small range values, we can use this.
Create Table list_example
(State_cd varchar2 (2 ),
Data varchar2 (20)
)
Partition by list (state_cd)
(Partition part_1 values ('me', 'nh ', 'vt', 'Ma '),
Partition part_2 values ('ct ', 'ri', 'ny ')

Partition part_3 values (default );
)
/

Note that if a default partition exists, you cannot add other partitions to the list partition. You must delete the default partition first.

4. Combined partitioning
Create Table composite_example
(Range_key_column date,
Hash_key_column int,
Data varchar2 (20)
)
Partition by range (range_key_column)
Subpartition by hash (hash_key_column) subpartitions 2
(
Partition part_1
Values less than (to_date ('2014/1/123', 'dd/MM/yyyy '))
(Subpartition part_1_sub_1,
Subpartition part_1_sub_2
),
Partition part_2
Values less than (to_date ('2014/1/123', 'dd/MM/yyyy '))
(Subpartition part_2_sub_1,
Subpartition part_2_sub_2
)
)
/

In the preceding example, select hash in subpartition first, and then select by range partition.

 

5 References

Continue with fenqu, a good item on itpub.