From the course: Oracle Database 12c: Advanced SQL

Unlock the full course today

Join today to access over 22,600 courses taught by industry experts or purchase this course individually.

Table partitioning benefits

Table partitioning benefits - Oracle Database Tutorial

From the course: Oracle Database 12c: Advanced SQL

Start my 1-month free trial

Table partitioning benefits

“

- [Instructor] So, after we understood what table partitions are, and discussed the core concepts, let's understand why should we even partition our database tables. What are the benefits? There are two main benefits for partitioning our tables: first and foremost, there's a big performance benefit. Let's discuss. When you partition your table, the Oracle database optimizer can perform something known as partition pruning. The Oracle database optimizer is the component responsible for optimizing your SQL queries for execution. Say your Sales table is partitioned by the SALE_DATE column, and your application issues a SQL query that need to retrieve all sales records from January 2017. If the table is partitioned via the SALE_DATE column, the same column that the SQL query is filtering on in the WHERE clause, then the Oracle database query optimizer can perform this partition tuning operation and it knows that only one partition in our table contains relevant data from January 2017…

Contents