2

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.

Improve this question
2
  • 2
    This is a question about an important part of the Oracle software an I think it is 100% on topic here. Commented Jun 1, 2017 at 20:04
  • 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? Commented Jun 2, 2017 at 15:16

1 Answer 1

Reset to default
2

Manuals:

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:

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

Improve this answer
2
  • 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! Commented Jun 1, 2017 at 20:35
  • 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. Commented Jun 2, 2017 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.