Oracle Tables Partitioning : Database performance using table partitioning
Kidane, Daniel (2015)
Metropolia Ammattikorkeakoulu
2015
Creative Commons Attribution 1.0 Finland
Julkaisun pysyvä osoite on
https://urn.fi/URN:NBN:fi:amk-2015112818318
https://urn.fi/URN:NBN:fi:amk-2015112818318
Tiivistelmä
The purpose of this thesis is to evaluate how Oracle table partitioning affects the performance of a database. In this thesis, the partitioning techniques are applied to a table varying in size from 30,000 to 18 million rows.
Different techniques and applications are used to support this thesis. On the client side a personal computer with an Oracle database version 12c is installed. At the back end, a free version of Oracle database is used to send data back to the client side whenever requested. In order to access the database, the application used is Toad, which stands for Tools for Oracle Application Development. Toad is used for application development, database development, measuring database performance and other purposes.
As a result, the tests conducted in this thesis show that there is not any performance difference when the partitioning techniques are applied or not on tables with less than 300,000 (22.9 MB) records. However, a clear gain in performance is observed when the number of rows increases to 3 million (323 MB) worth of data and beyond for partitioned tables compared to non-partitioned ones.
In conclusion, it is not necessary to partition a table if its size is below 22.9 MB. But, based on the findings in this thesis, if the size of the table grows up to 323 MB and beyond, it is recommended to partition the table for manageability, availability and performance purposes.
Different techniques and applications are used to support this thesis. On the client side a personal computer with an Oracle database version 12c is installed. At the back end, a free version of Oracle database is used to send data back to the client side whenever requested. In order to access the database, the application used is Toad, which stands for Tools for Oracle Application Development. Toad is used for application development, database development, measuring database performance and other purposes.
As a result, the tests conducted in this thesis show that there is not any performance difference when the partitioning techniques are applied or not on tables with less than 300,000 (22.9 MB) records. However, a clear gain in performance is observed when the number of rows increases to 3 million (323 MB) worth of data and beyond for partitioned tables compared to non-partitioned ones.
In conclusion, it is not necessary to partition a table if its size is below 22.9 MB. But, based on the findings in this thesis, if the size of the table grows up to 323 MB and beyond, it is recommended to partition the table for manageability, availability and performance purposes.