Partitioning
what is System partitioning in Oracle 11G
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> PARTITION clause is used to define which partition the row should be placed in.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> HIGH_VALUE for the partitions is blank.The PARTITION clause is optional for update and delete
statements, but omitting this clause will force all partitions to be
scanned, since there is no way perform automatic partition pruning when
the database has no control over row placement. When the PARTITION clause is used, you must be sure to perform the operation against the correct partition.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> PARTITION clause can also be used in queries to target specific partitions.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:
|
How do I know if an object is partitioned or not?
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>'; |
How to split a very large partition of a Range-partitioned table into multiple partitions ?
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; |
Change existing range partitioned table to Interval to interval partitioned table
Find a specific partition without using the ‘where’ clause - 11g
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’)..
|
1-5 of 5