In DataWarehouses and OLTP systems we use partitions for keeping data separately. In this way when we run a query database engine doesn’t need to scan all the table , it just accesses the partition that needs.
Oracle defines its partitioning like this ;
Partitioning is powerful functionality that allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Oracle provides a comprehensive range of partitioning schemes to address every business requirement. Moreover, since it is entirely transparent in SQL statements, partitioning can be used with any application, from packaged OLTP applications to data warehouses.
Sometimes we need create new partitions for loading data to a table. When we do this we use this stored procedure ;
CREATE PROCEDURE sp_add_partition (p_table_owner varchar2,p_table_name varchar2,p_start_date number, p_end_date number,p_partition_name VARCHAR2 default NULL) IS v_high_value number; v_YEAR_MONTH varchar2(6); v_table_name varchar(100); -- CURSOR c1 IS select DISTINCT to_char(DAY_ID,'YYYYMM') YEAR_MONTH , to_number(to_char(DAY_ID,'YYYYMM') ||'99') high_value from DIM_CALENDAR_DATES where to_number ( to_char(DAY_ID ,'YYYYMMDD' )) between p_start_date and p_end_date order by 1; BEGIN -- OPEN c1; LOOP FETCH c1 INTO v_YEAR_MONTH , v_high_value; EXIT WHEN c1%NOTFOUND; -- execute immediate ' select max(Table_Name) from dba_tables where owner = UPPER('''||p_table_owner||''') and table_name = UPPER('''||p_table_name||''')' into v_table_name; BEGIN execute immediate 'ALTER TABLE '||p_table_owner||'.'||v_table_name||' ADD PARTITION '||nvl(p_partition_name,v_table_name)||'_'||v_YEAR_MONTH||' VALUES LESS THAN ('||v_high_value||') STORAGE ( INITIAL 64K NEXT 50M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT ) NOLOGGING'; -- END; END LOOP; -- CLOSE c1; -- END ;
For get more information about partitioning you can check links below.