hi,
I want to ask a question about SQL/MP add partition. I have 'payment' table and it is nearly %97 full of record.
I want to add partition using 'date' column, is it possible?
Because I know that if you use 'FIRST KEY' command, the name of primary key first column has to be same.
Do you think this below command works? Otherwise, I will copy data, purge table, create table primary key 'date' firstly and then copy datas.
alter table payment
add partition payment2
catalog xxxxx
extent (xx, xx) maxextents xxx
FIRST KEY (20201231); (YYYYMMDD-31 Dec 2020)
create table paymentHi,
(
card_number
, date
, branch_id
, sequence_number
, reserve_field
, PRIMARY KEY
(
card_number
, date
, branch_id
, sequence_number
)
)
Thank for your reply.Yes, load would be the preferred way. If you change the sequence of the columns you have to use the MOVEBYNAME option.
I have already done "fup reolad payment, slack 0" also increased maxextents to max. value before.
Adding partition with card_number didn't make me think. So I want to change order of primary key for new table.
May be new big table again full of record 2-3 years later and this time I will be adding partition for date column.
IS IT POSSIBLE use LOAD command for copy datas from payment to paymentNEW? They will have same column only primary key order change. OR another way instead of write this. (insert into paymentNEW select * from payment;)
create table paymentNEW
(
card_number
, date
, branch_id
, sequence_number
, reserve_field
, PRIMARY KEY
(
date
, card_number
, branch_id
, sequence_number
)
Memmedaga Memmedov schrieb am Donnerstag, 3. Dezember 2020 um 12:33:23 UTC+1:
Thank for your reply.
I have already done "fup reolad payment, slack 0" also increased maxextents to max. value before.
Adding partition with card_number didn't make me think. So I want to change order of primary key for new table.
May be new big table again full of record 2-3 years later and this time I will be adding partition for date column.
IS IT POSSIBLE use LOAD command for copy datas from payment to paymentNEW? They will have same column only primary key order change. OR another way instead of write this. (insert into paymentNEW select * from payment;)
create table paymentNEWYes, load would be the preferred way. If you change the sequence of the columns you have to use the MOVEBYNAME option.
(
card_number
, date
, branch_id
, sequence_number
, reserve_field
, PRIMARY KEY
(
date
, card_number
, branch_id
, sequence_number
)
But you should be careful to change the primary key, maybe the application needs the actual sequence of fields. In addition doing partitioning by date usually isn't a good idea.
We have always used things like the cardnumber or the account number as partitioning criteria.
Thanks for replies.
I'll only change the order of primary key statement. Create table statement will be same. So, I think that there is no need to edit programs using select or insert. I created new table and start load command, I will see what will happen when finished.
On Thu, 3 Dec 2020 14:43:07 -0800 (PST), Memmedaga Memmedov <mkel...@gmail.com> wrote:Just one additional comment:
Thanks for replies.I want to point out what you may already know.
I'll only change the order of primary key statement. Create table statement will be same. So, I think that there is no need to edit programs using select or insert. I created new table and start load command, I will see what will happen when finished.
You should create and load new tables with an alternatate filename, then find all programs that access the program, make duplicates of them to a work location.
Then you should SQLCOMP the duplicate programs, with EXPLAIN PLAN. You should review that and compare to the current production files.
Changing the primary keys (or indexes) can negatively impact the executaion plan for statements, since you are changing an access path.
If you find that this is the case, you can add an Index on the alternate table that is the same as the original primary key and recompile.
The resulting plan will still have slightly higher costs, both Insert statements (to insert the new index row), and on access statements to read the index row.
Alternatively, you can create the alternate table with larger extent sizes so it can be much bigger, but that's really a limited-time fix (unless older rows are
occasionally deleted from the tables).
Good luck with these, Memmedaga!
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 296 |
Nodes: | 16 (2 / 14) |
Uptime: | 67:29:04 |
Calls: | 6,654 |
Files: | 12,200 |
Messages: | 5,331,958 |