@@@@ PARTITIONS @@@@
It makes manging large tables & indexes management easy. Its done through a new type of storage structure called PARTIOTION.
A partition is an independent object; it may also have its independent storage characteristics.
However, from an application's perspective, there is just only one schema. No change is required for the DML statements.
IOTs can also be partitioned like a normal table.
BENEFITS - Increased availability, increased query performance for data warehouses, decreased contention for a single resource in OLTP systems, easy administration of schema objects(DDL)
Each partition should have the same logical attributes like column names, constraints; but they can have different logical attributes like tablespace to which it belongs.
PARTITION KEY - one or more columns which determine the partition to which a row of data will go. Ex- In a range partition, the date is the parameter on the basis of which we can partiition a table data.
PARTITIONING STRETEGIES - it defines the conditions based on which the data is partitioned. There are 3 available - RANGE, LIST and HASH.
RANGE - most common type, PARTITION BY RANGE(colname)
**database creates interval partitions for data beyond that transition point. Interval partitions extend range partitioning by instructing the database to create partitions of the specified range or interval automatically when data inserted into the table exceeds all of the range partitions. the SALES_2001 partition contains rows with partitioning key time_id values greater than or equal to 01-JAN-2001.
LIST - partitioning key is based on a set of values and based on those values , the data is partitioned. PARTITION colname VALUES (set of values)
HASH - hash algo is applied on the key to select the partition to which the data will be added. Useful for OLTP systems where high update contention is there. Increases availability.
PARTITION BY HASH(colname)
you cannot specify to which partition what data should go, and oracle distributes the data evenly across all partitions.
PARTITIONED TABLES -
partitioned table is different from a non-partitioned table. The normal table has its data allocated to table segment where as in case of partitioned tables, the data is allocated to partition segment. Each partition segment has a portion of data allocated to it.
PARTITIONED INDEXES -
Like a partitioned table, the partitioned index is decomposed into smaller pieces.
Global(B-Tree index) indexes are partitioned independent of their table where as local partitioned indexes are automatically linked to its table partition.
It makes manging large tables & indexes management easy. Its done through a new type of storage structure called PARTIOTION.
A partition is an independent object; it may also have its independent storage characteristics.
However, from an application's perspective, there is just only one schema. No change is required for the DML statements.
IOTs can also be partitioned like a normal table.
BENEFITS - Increased availability, increased query performance for data warehouses, decreased contention for a single resource in OLTP systems, easy administration of schema objects(DDL)
Each partition should have the same logical attributes like column names, constraints; but they can have different logical attributes like tablespace to which it belongs.
PARTITION KEY - one or more columns which determine the partition to which a row of data will go. Ex- In a range partition, the date is the parameter on the basis of which we can partiition a table data.
PARTITIONING STRETEGIES - it defines the conditions based on which the data is partitioned. There are 3 available - RANGE, LIST and HASH.
RANGE - most common type, PARTITION BY RANGE(colname)
**database creates interval partitions for data beyond that transition point. Interval partitions extend range partitioning by instructing the database to create partitions of the specified range or interval automatically when data inserted into the table exceeds all of the range partitions. the SALES_2001 partition contains rows with partitioning key time_id values greater than or equal to 01-JAN-2001.
LIST - partitioning key is based on a set of values and based on those values , the data is partitioned. PARTITION colname VALUES (set of values)
HASH - hash algo is applied on the key to select the partition to which the data will be added. Useful for OLTP systems where high update contention is there. Increases availability.
PARTITION BY HASH(colname)
you cannot specify to which partition what data should go, and oracle distributes the data evenly across all partitions.
PARTITIONED TABLES -
partitioned table is different from a non-partitioned table. The normal table has its data allocated to table segment where as in case of partitioned tables, the data is allocated to partition segment. Each partition segment has a portion of data allocated to it.
PARTITIONED INDEXES -
Like a partitioned table, the partitioned index is decomposed into smaller pieces.
Global(B-Tree index) indexes are partitioned independent of their table where as local partitioned indexes are automatically linked to its table partition.
