Andrew Fraser DBA

Partition an existing oracle table example

I used this SQL to convert an existing non-partitioned table to be partitioned. Table was mostly queried on snapshot_id=’Current’, which had the most up to date data, but had much more historic data that was rarely queried against, all flagged with snapshot_id = ‘YYYYMMDD’ format.

-- 1) Create an empty table with one partition
CREATE TABLE a1 TABLESPACE targtabs_big
PARTITION BY RANGE ( snapshot_id ) (
    PARTITION adm_fact_all VALUES LESS THAN ( MAXVALUE ) TABLESPACE targtabs_big
)
AS SELECT * FROM adm_fact WHERE 1 = 2 ;
-- 2) Swap adm_fact into that empty table with one partition
ALTER TABLE a1 EXCHANGE PARTITION adm_fact_all WITH TABLE adm_fact ;
-- 3) Flip adm_fact to this thing we have just built. (beware grants, indexes, constraints, triggers, etc.)
DROP TABLE adm_fact ;  -- make sure to take a note of grants, indexes, constraints, triggers, etc. before doing this!
RENAME a1 TO adm_fact ;
-- 4) Split the partition to put Current by itself
ALTER TABLE adm_fact SPLIT PARTITION adm_fact_all AT ( '29990000' ) INTO (
     PARTITION adm_fact_old TABLESPACE targtabs_big COMPRESS -- AD 2999 = everything except 'Current'
   , PARTITION adm_fact_current TABLESPACE targtabs_big -- 'Current'
) ;
-- 5) Add the indexes back in - they all disappeared at step 3 above (leave out LOCAL if want global non-partitioned index).
CREATE INDEX i_af_pc on adm_fact ( programme_code , programme_discipline_code )
LOCAL tablespace targindx_med ;
CREATE INDEX i_af_si on adm_fact ( snapshot_id )
LOCAL tablespace targindx_med ;
-- 6) Add grants, constraints, triggers, etc. back in - they all disappeared at step 3 above.
GRANT SELECT ON adm_fact TO role ;
-- 7) Gather optimizer stats
EXEC dbms_stats.gather_database_stats_job_proc
-- Or for 10gR2 or older instead use: EXEC dbms_scheduler.run_job ( 'GATHER_STATS_JOB' )
-- Optionally check things are working ok.
SELECT snapshot_id , COUNT(*) FROM adm_fact GROUP BY snapshot_id ORDER BY 1 ;
SELECT snapshot_id , COUNT(*) FROM adm_fact PARTITION ( adm_fact_old ) GROUP BY snapshot_id ORDER BY 1 ;
SELECT snapshot_id , COUNT(*) FROM adm_fact PARTITION ( adm_fact_current ) GROUP BY snapshot_id ORDER BY 1 ;
-- 9) (Optional) by default updating snapshot_id is not allowed, so e.g.:
-- UPDATE adm_fact SET snapshot_id = 'Current' WHERE snapshot_id != 'Current' AND ROWNUM < 11 ;
-- would fail with error ""ORA-14402: updating partition key column would cause a partition change"
-- You can change that behaviour with:
ALTER TABLE adm_fact ENABLE ROW MOVEMENT ;

Dbms_redefinition could be used instead to reduce the outage time to almost nothing, but the above method is fast, was good enough for my outage timescales.

Indexes or their partitions can be compressed as well as table partitions, but often less benefit from that.