System partitioning provides the ability to create a single table that has many physical partitions. System partitions doesn't have partition keys like other partitioning schemes.
REATE TABLE system_partitioned_tab ( id NUMBER, code VARCHAR2(10), description VARCHAR2(50), created_date DATE ) PARTITION BY SYSTEM ( PARTITION part_1, PARTITION part_2 );The partition must be explicitly defined in all insert statements or an error is produced.
TheINSERT INTO system_partitioned_tab VALUES (1, 'ONE', 'One', SYSDATE); * ERROR at line 1: ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method SQL>
Notice that theINSERT INTO system_partitioned_tab PARTITION (part_1) VALUES (1, 'ONE', 'One', SYSDATE); INSERT INTO system_partitioned_tab PARTITION (part_2) VALUES (2, 'TWO', 'Two', SYSDATE); COMMIT; EXEC DBMS_STATS.gather_table_stats(USER, 'SYSTEM_PARTITIONED_TAB'); COLUMN table_name FORMAT A25 COLUMN partition_name FORMAT A20 COLUMN high_value FORMAT A10 SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions ORDER BY table_name, partition_name; TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS ------------------------- -------------------- ---------- ---------- SYSTEM_PARTITIONED_TAB PART_1 1 SYSTEM_PARTITIONED_TAB PART_2 1 2 rows selected. SQL>
TheSQL> DELETE FROM system_partitioned_tab PARTITION (part_2) WHERE id = 1; 0 rows deleted. SQL> UPDATE system_partitioned_tab PARTITION (part_1) SET code = 'TWO' WHERE id = 2; 0 rows deleted. SQL>
System partitioning gives you many of the advantages of partitioning, but leaves the decision of how the data is partitioned to the application layer.SELECT COUNT(*) FROM system_partitioned_tab PARTITION (part_1); COUNT(*) ---------- 1 1 row selected. SQL>
Conditions and restrictions on system partitioning include:
SELECT * FROM dba_part_tables; SELECT * FROM dba_part_indexes;
One can list the partitions of a table with:SELECT * FROM dba_tab_partitions WHERE table_name = '<table_name>';
One can show the partitions of an given index with:SELECT * FROM dba_ind_partitions WHERE index_name = '<index_name>';
You will need 500GB of free space to do the split of a 500GB partition. Behind the scenes we have to build two new segments for the two new partitions; that's the only way for us to physically separate the data belonging to the new target partitions. If you don't have 500GB available, here is an alternative you can do:
This process is going to take awhile to run, depending on how much data is involved. We assume your table is called PRICE. You should first test this on a smaller table.
1. Do a Create Table As Select (CTAS) to preserve the smaller target partition data as separate table.
Create table PRICE_2 NOLOGGING PARALLEL(DEGREE 2) as select /*+ PARALLEL(A, 2) */ * from PRICE PARTITION (PRICE_MAXVALUE) where month_year between 200501 and 200512;
Check table PRICE_2 to ensure all the correct rows are there.
2. Then do a DELETE from the same data from the large original partition.
Delete from PRICE where month_year between 200501 and 200512;
3. Do a split of the original partition. This will be a fast split now since we figure out that there is no data for one of the new target partitions (we just deleted that data).
alter table PRICE split partition PRICE_MAXVALUE at (20090801) into (partition PRICE_2009_07, partition PRICE_MAXVALUE) parallel 2;
4. Do an exchange with the empty partitions and the standalone table created in step 1.
alter table PRICE exchange partition PRICE_2009_07 with table PRICE_2;
5. Repeat steps 1-4 for each additional partition you want to create.
6. You can run this after each exchange. This 'shrink ' clause is valid only for segments in tablespaces with automatic segment management.
alter table PRICE modify partition PRICE_MAXVALUE shrink space;
(New Table Partitioning Methods Introduced in Oracle Database 11g)
There are a few restrictions on interval partitioning that must be taken into consideration before deciding if it is appropriate for the business requirement:
This feature should be used as an enhancement to range partitioning when uniform distribution of range intervals for new partitions is acceptable. If the requirement demands the use of uneven intervals when adding new partitions, then interval partitioning would not be the best solution.
In Oracle/PLSQL, the numtodsinterval function converts a number to an INTERVAL DAY TO SECOND literal.
The syntax for the numtodsinterval function is:
numtodsinterval( number, expression )
number is the number to convert to an interval.
expression is the unit. It must be one of the following values: DAY, HOUR, MINUTE, or SECOND.
Applies To: Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
numtodsinterval(150, 'DAY') would return '+000000150'
numtodsinterval(1500, 'HOUR') would return '+000000062'
numtodsinterval(15000, 'MINUTE') would return '+000000010'
In Oracle/PLSQL, the numtoyminterval function converts a number to an INTERVAL YEAR TO MONTH literal.
The syntax for the numtoyminterval function is:
numtoyminterval( number, expression )
number is the number to convert to an interval.
expression is the unit. It must be one of the following values: YEAR or MONTH.
Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
numtoyminterval(100000000, 'MONTH') would return '+0083'
numtoyminterval(100000, 'YEAR') would return '+0001'
If you ever want to query a partitioned table, and want to reference a specific partition without using the ‘where’ clause (and do not know the partition name), you could use the following syntax:
Select * from dbceo.part_table partition for (to_date(’08-SEP-2010’,’DD-MON-YYYY’)..