Joined: 02 Dec 2002 Posts: 618 Topics: 172 Location: Stockholm, Sweden
Posted: Wed Oct 27, 2021 8:18 am Post subject: Different indexes for different partitions
My impression (after googling) is that the answer is no, but .....
Can different partitions in a table have different indexes. For example, partition 1 might have an index on part_no, but partition 2 on sales_date.
I'm been tasked with looking at the idea of the most efficient way of logging transactions in the bank where I'm working. Note that I am not a trained DBA (so why have I been tasked with the job? Next question).
We're talking 4-5 million records per day, with , at a guess, 7-8 million around Xmas.
If it's at all relevant, the idea would be to load the partition(s) based on a 90 day schedule and then drop the oldest dates on a daily basis. _________________ Michael
Joined: 26 Nov 2002 Posts: 12367 Topics: 75 Location: San Jose
Posted: Wed Oct 27, 2021 10:33 am Post subject: Re: Different indexes for different partitions
misi01 wrote:
My impression (after googling) is that the answer is no, but .....
Can different partitions in a table have different indexes. For example, partition 1 might have an index on part_no, but partition 2 on sales_date.
Misi01,
It is quite unclear as to what you want to do. A partition is simply putting the keys in a different segment based on the range.
So assuming you have defined the table on partition on PART_NO, then each and every partition will have the SALES_DATE on it. You can have an index on it, but it applies to all partitions. Not sure as to how you can avoid sales_date in particular partition.
Here is a sample table definition.
Code:
CREATE TABLE some_tbl (Part_no interger NOT NULL,
sales_date date NOT NULL,
some_info varchar)
in your.tablespc
partition by (Part_no)
( part 01 value (02000000),
part 02 value (04000000),
part 03 value (06000000),
part 04 value (08000000),
part 05 value (10000000));
CREATE INDEX A ON some_tbl(part_no)
...
CREATE INDEX B ON some_tbl(sales_date)
...
_________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Joined: 17 Dec 2002 Posts: 17 Topics: 2 Location: NJ, USA
Posted: Sun Oct 31, 2021 5:51 pm Post subject:
Hello Michael
I think what you want to accomplish can be done by defining the table using range partitioning. For example, each partition could house rows for a particular sales_date week. At the end of a week, you would remove the partition containing the rows for the oldest week and create a partition for the upcoming week.
Here's a link to the IBM DB2 documentation on range partitioning.
Here's a link to a site that has articles on range partitioning and partition detaching in the context of something similar to what you may want to consider.
Joined: 10 Oct 2003 Posts: 315 Topics: 49 Location: Germany
Posted: Mon Nov 15, 2021 11:16 am Post subject:
Spolacek,
your links, remarks according "detach partition" seems to be for DB2 LUW, right?
This does not work for DB2 zOS, my impression.
For deleting all records in one partition I know only the DB2 zOS utility function REORG DATA with the DISCARD option and an meaningful WHERE clause for this purpose.
Are there any other options to delete the content of one partition (without rotation and redefining the limiting keys of the last partition)????
I'm talking about partition by range table spaces.
Joined: 17 Dec 2002 Posts: 17 Topics: 2 Location: NJ, USA
Posted: Tue Nov 16, 2021 8:03 pm Post subject:
Hello Bauer
I'm no longer working so I can't confirm whether the detach partition command pertains DB2 on zOS. I recall DBAs talking about partition ranging and detaching partitions, but I'm not certain now whether they were referencing zOS.
If your information shows that it does not pertain to zOS, then I withdraw my suggestion to Mis01 and I stand corrected.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
