Where can I find the algorithm used in Oracle Range Partitioning

Question

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.

• miracle173: This is a question about an important part of the Oracle software an I think it is 100% on topic here.
• miracle173: It is hard to answer this question. I am not sure what you already know. What is your level of experience? Are you familiar with Oracle specific terms like Block, Extent, Segment, SGA , Shared pool? Why do you want to know this? Do you want to program database software? Are you an experienced programmer?

Answer 1

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:

• Expert Oracle Database Architecture,
  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:

• Partitioning in Oracle Database 11g
• Partitioning with Oracle Database 11g Release 2

---

• 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.

• user: I looked at the online documentations and white papers that you have pointed above. All of them mostly have use case and sample query to create and use the partitioning. I am looking for resource where I can understand the data structure used for example does range partition uses a binary search tree to find which partition a row should be inserted into? I am yet to read up the book you mentioned above. I will update this thread if I find the info. Thanks!
• Raj: In a nutshell, Range partitions usually have a maxvalue defined. When data is to be inserted, you can look at the value of partitioned columns, find the correct partition (if not already specified) by comparing them to maxvalue and select the partition. I am not sure (because never needed to) trace partition pruning for inserts (event 10128 may/maynot work). For hash partition, oracle generates hash values of values of partitioned keys and finds which partition it need to go into. If you are new to Oracle,IMHO, this is probably too detailed to understand how Oracle works to begin with.