CODEWITHSUNDEEP
sqloracle-databaseindexingdatabase-partitioning
Aldo aldo
Aldo aldo

Reputation: 412

What is the difference between partitioning and indexing in DB ? (Performance-Wise)

I am new to SQL and have been trying to optimize the query performances of my microservices to my DB (Oracle SQL).

Based on my research, I checked that you can do indexing and partitioning to improve query performance, I seem to have known each of the concept and how to do it, but I'm not sure about the difference between both?

For example, suppose I have table Orders, with 100 million entries with Columns:

In essence, both methods "subdivides" the orders table so that a DB query wont need to scan through all 100 million entries in DB, right?

Lets say I want to find orders on "2020-01-30", I can create an index on createdTime to improve the performance. But I can also create a partition based on createdTime to improve the performance. (the partition is per day)

Are there any difference to both methods in this case? Is one better than the other ?

Upvotes: 2

Views: 4304

Answers (1)

Andrew Sayer
Andrew Sayer

Reputation: 2336

There are several ways to partition - by range, by list, by hash, and by reference (although that tends to be uncommon).

If you were to partition by a date column, it would usually be using a range, so one month/week/day uses one partition, another uses another etc. If you want to filter rows where this date is equal to a value then you can do a partition full table scan to read all of the partition that houses this data with a full scan. This can end up being quite efficient if most of the data in the partition would match your filter - apply the same thinking about whether a full table scan in general is a good idea but when the data in the table is already filtered down. If you wanted to look for an hour long date range and you’re partitioning by range with monthly intervals then you’re going to be reading about 730 times more data than necessary. Local indexes are useful in that scenario.

Smaller partitions also help this out, but you can end up with a case where you have thousands of partitions. If you have selective queries that don’t know which partition needs to be read - you could want global indexes. These add a lot of effort into all partition maintenance operations.

If you index the date column instead then you can quickly establish the location of the rows in your table that meet your filter. This is easy in an index because it’s just a sorted list - you find the first key in the index that matches the filter and read until it no longer matches. You then have to lookup these rows using single block reads. Usual efficiency rules of an index apply - the less data you need to read with your filters the more useful the index will be.

Usually, queries include more than just a date filter. These additional filters might be more appropriate for your partitioning scheme. You could also just include the columns in your index (remembering the Golden Rule of Indexing would tell you if you’re using a column with equality filters it should go before columns that you use range filters on in an index).

You can generally get all the performance you need with just indexing. Partitioning really comes into play when you have important queries that need to read huge chunks of data (generally reporting queries) or when you need to do things like purge data older than X months.

Upvotes: 3

Related Questions