database-internalsoraclepartitioning
I am new to database and would like to know where can I find the resource to study and understand about different algorithms used in table partitioning
For example, what algorithm is used between range and hash partition to find the partition that a data to be inserted.
You can't use interval partitioning with reference partitioning, however, It's possible to mix range partitioning and reference partitioning, as shown in the example of reference partitioning in the 11g doc:
The benefit of this extension is that tables with a parent-child relationship can be logically equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns. The logical dependency also automatically cascades partition maintenance operations, thus making application development easier and less error-prone.
The child table doesn't need to contain the partition key (here the log date). It inherits the partition key from the parent table.
Range partitioning involves a bit more maintenance because you have to create the partitions yourself. However, once the partitions are created, range and interval work similarly.
The solution is dbms_redefinition package. Basically redefition will move, online, to a new table (called interim table), then move all dependent objets like indexes and last exchange the interim table with the original one.
What you have to do is:
Use dbms_redefinition in the following way:
-- DETERMINE IF THE ORIGINAL TABLE CAN BE REDEFINED ONLINE
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA','YOURTABLE', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
-- BEGIN THE REDEFINITION
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
UNAME => 'SCHEMA',
ORIG_TABLE => 'YOURTABLE',
INT_TABLE => 'INTERIM_YOURTABLE'
OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
-- IF THE TABLE HAD DEPENDENCIES (INDEXES ... CONSTRAINTS ... TRIGGERS)
-- THIS WOULD BE THE POINT AT WHICH THEY WOULD HAVE BEEN COPIED
DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCHEMA', 'YOURTABLE', 'INTERIM_YOURTABLE',
dbms_redefinition.cons_orig_params, TRUE, TRUE, TRUE, FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/
-- FINISH THE REDEFINITION
exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA','YOURTABLE','INTERIM_YOURTABLE');
If you run redefinition with SYSTEM user there is no problem. If you want to execute it with a less privileged user you have to trick some privileges in order to get it working. Privileges required are:
Tables with the following characteristics cannot be redefined online:
Other restrictions:
Best Answer
Manuals:
Basic description can be found in the Database Concepts
Some information about administering partitioned tables can be found in theDatabase Administrator’s Guide
Detailed description as you requested can be found in the Database VLDB and Partitioning Guide
Books:
by Kyte, Thomas, Kuhn, Darl
contains about 70 pages about partitioning. I did not know it but I assume it contains a very detailed presentation of the topic.
Whitepapers:
Oracle uses a data dictionary to store the structure of the database objects. These data dictionary contains a set of tables that are located in the SYS schema and have a name that ends with '$'. The table SYS.TABPART$ contains a row for each partition. So if before Oracle stores a row in a partitioned table it hast to read SYS.TABPART$ table of the data dictionary to get information about the partitioned table. Oracle is implemented in C, so access to partitiones is implemented in C-code, too. This C-code is proprietary to Oracle and not made publicly available. The data structures that are used in these programs aren't publicly available too and I think Oracle has no interest to make it known to the public. But it is not necessary to know such details if you want to work with Oracle or want to get a deep understanding of how Oracle works.
The partition relevant dictionary objects are created by the $ORACLE_HOME/rdbms/admin/dpart.bsq' script. There are a lot of other bsq-scripts in this directory that create other dictionary objects. These scripts are executed when the database is created. They are referenced by the well known USER_ / ALL_ / DBA_ views that are a more user friendly access to the dictionary data.
You cannot access the Oracle C-code but if your are interested in how a real database is implemented you can study the source code of an open source database. As far as I know MariaDB (a MySQL successor) is an open source database that has implemented partitioning. Also there are a lot of books about database systems and data structures.