Partitioning allows a very large table (or index) to be stored in multiple partitions, which being smaller will be easier to manage.
SQL queries and DML statements do not need to be modified in order to access partitioned tables. You do not need to rewrite application code to take advantage of partitioning.
However, once partitions are defined, SQL statements (including DDL) can access and manipulate an individual partition rather than an entire table.
Partitioning enables data loads, index creation and rebuilding, and backup/recovery to be done at the partition level, rather than on the entire table. This results in significant time savings.
Each row in a partitioned table is assigned to a single partition. The partition key is a set of one or more columns that determine the partition for each row.
Oracle provide 4 partitioning methods: Range, List , Hash and Composite Partitioning (Range+Hash).
List puts a list of discrete values in each partition,
PARTITION sales_east VALUES IN ('New York', 'Virginia', 'Florida')Range puts a range of values in a partition:
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')Hash uses a hash function to place rows into different partitions
PARTITION BY HASH(salesman_id)Composite partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning.
e.g.
PARTITION sales_dec2012 VALUES LESS THAN(TO_DATE('2012-12-31','YYYY-MM-DD'))
( SUBPARTITION sales_dec2012_1 TABLESPACE data1,
SUBPARTITION sales_dec2012_2 TABLESPACE data2,…
Tables on which you want to perform parallel DML operations must be partitioned.
Tables greater than 2GB should always be considered for partitioning.
Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read-only.
When creating an index for a partitioned table, there are 3 choices, you can create:
A traditional (global) index,
A global partitioned index - where the degree of partitioning (and the partition key) are independent from the table's partitioning method.
A LOCAL index - where the degree of partitioning (and the partition key) are the same as the table's partitioning method.
CREATE INDEX emp_local_idx ON emp (empno) LOCAL
Tablespaces can be specified at either index or index subpartition levels. Further examples of partitioned index creation are in the Oracle concepts manual.
You can create temporary tables to hold data that exists only for the duration of a transaction or session.
The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table.
For transaction-specific temporary tables, data exists for the duration of the transaction. For session-specific temporary tables, data exists for the duration of the session.
Data in a temporary table is private to the session. Each session can only see and modify its own data.
A TRUNCATE statement issued on a session-specific temporary table truncates data in its own session. It does not truncate the data of other sessions using the same table.Data from the temporary table is automatically dropped in the case of session termination or an instance crash.
You can create temporary indexes for temporary tables using the CREATE INDEX statement.
You can create views that access both temporary and permanent tables. You can also create triggers on temporary tables. Locks are not acquired on temporary tables because each session has its own private data.The Export utility will not read data from a temporary table.
Data for an index-organised table (IOT) is stored in a B-tree index structure (sorted by primary key).
Besides storing the primary key values of each row, each index entry in the B-tree stores the nonkey column values as well.
Applications manipulate the IOT just like an ordinary table, using SQL statements. However, the database system performs all operations by manipulating the corresponding B-tree index.IOT's Cannot be stored in a cluster or contain columns of datatype LONG.
Storage in an IOT avoids the duplication where a primary key column is stored in both the table and index.Oracle 9.0.1, supports bitmap indexes on an IOT. A mapping table is required, which is a heap-organized table that stores logical rowids of the index-organized table.
You can range or hash partition an index-organized table.
External tables provide a way to read data from external sources as if reading a table.
External tables are read-only and are mainly used for loading data into a "real" database table.SQL> create or replace directory testdir as 'd:\temp';
SQL> select * from all_directoriesCopying data from an external table can be done through standard SQL:
CREATE TABLE AS SELECT … FROM my_external_table
or
INSERT INTO … AS SELECT … FROM my_external_table
Replication is the process of copying and maintaining data across multiple databases (sites) that make up a distributed database system.
In a Distributed database, data is distributed across multiple locations, but a particular table will reside at only one location.
"The whole is greater than the part” ~ Euclid's fifth Common Notion
See also
Full list of Datatypes