CODEWITHSUNDEEP
oracle-databaseperformancepartitioning
Madhura Oak
Madhura Oak

Reputation: 13

Should I use oracle partition if i have to query using column not used in partition clause

I have a customer table containing 200 million records. There are three sources of customers (70 million, 80 million and 50 million records).

I have three queries on this table.

Should I use list partition on this table, where I partition by source? The query to fetch customer by mobile number would become slow after partitioning. It takes a long time to insert records without partition.

Following columns are present in customer table: 

customerid number(12), 
source varchar2(100), 
accountid number(12), 
mobile number(10).

Each customer record will have distinct customerid, source and accountid combination.

Upvotes: 0

Views: 118

Answers (2)

APC
APC

Reputation: 146229

Should I use oracle partition if i have to query using column not used in partition clause

Probably not. Partitioning is primarily a management tool, for wrangling large amounts of data and keeping it available. The performance impact of Partitioning can be negative as well as positive, especially for queries which do not filter on the Partitioning key (as is the case with your query on mobile number).

Anyway, I doubt Partitioning on source will make much improvement to the performance of any of your queries. It just isn't selective enough for partition pruning to give a noticeable benefit.

What might be more useful would be compound indexes on (source, customerid) and (source, accountid) with compress 1 in both cases. It's worth compressing the leading column of the index precisely because source is so unselective. Also a single column index on (mobile) (with no compression).

Incidentally, why is source defined as varchar2(100)? That seems insanely long for what is a trivalent identifier. It ought to be a one (or two or three) character code (with a lookup table for the full description if required). I think that might explain why it "takes a long time to insert records without partition". Tackling that should be the focus of your efforts.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142713

From my point of view, no partition + indexes on those columns would be my choice (having information you provided).

Moreover, "partition" means "a lot of money" as you have to have Enterprise Edition (EE), and partitioning is (as far as I can tell) an add-on to already expensive EE. So ... I'm not suggesting that you (or your company) don't have that money, but pointing out that it might become an issue.

Upvotes: 0

Related Questions