Suggest your own partitioning
Gh.., February 19, 2017 - 12:08 pm UTC
Good analysis for giving partitioned table. But what if have to do the best partitioning / indexing? How do you proceed giving the same definitions for cols Id and Code?
Eg what about partitioning by hash (Id, code ) what would be the indexes?
Or what about partitioning by rang interval id and subpartition by hash code? What indexes?
And in which case of partitioning choice you would use a bitmap index on code? ..
So what do you choose as partitioning + indexing?
Please suppose it's you tabke and job. So what is you technical choice?
What if table is not partitioned?
Sam, February 21, 2017 - 7:53 pm UTC
Thanks Connor. I did not think about "queries just on CODE are taken care of because of Partitioned by CODE". Sometimes answer is simple but we chase the wrong end. :)
Sometimes we do not partition table (less than 100 million rows). In this scenario what would be better choice -
We defined Primary Key as (ID, CODE) where ID has lowest cardinality and ID itself is sufficient for PrimaryKey but CODE is part of PrimaryKey to support possible partitioning scheme.
Question -
1. Primary key and its index (ID, CODE) is a better choice or (CODE, ID)?
2. Primary key (ID) and a separate index on (CODE) would be a better choice?
Thanks a lot.
-Sam
February 22, 2017 - 1:27 am UTC
Running under the same assumption:
"Most of the queries we perform on this table includes ID and CODE column in WHERE clause. There are very few queries which have only CODE column in WHERE clause. "
I would index by (CODE,ID) because it satisfies both operations with a single index, and I can also take advantage of leading column compression, ie
create index IX on T ( code, id ) compress 1;
to keep the index compact.
i didnt get a feedback on my post signed Gh..
A reader, February 23, 2017 - 9:51 am UTC
tkx for any suggestion on my above question
February 25, 2017 - 12:46 am UTC
How do you proceed giving the same definitions for cols Id and Code?
Partitioning is often driven by a combination of business requirements, not just what is "technically best", eg, do I want to archive by code ? compress by code ? What is the speed of my storage ? (which might determine granularity)