Not sure why the DROP PARTITION behaved the way it did. What was the definition of partition p6? The CHALLOC log records indicate that data was being moved around. At the very least, lots of index nodes might have to be rewritten and probably, because the index isn't partitioned, the entire index may have been recreated.
One suggestion is to DETACH the partition next time then drop it afterwards.
I would also note that this expression based partition scheme would work better and be more efficient as a RANGE partition and might even take advantage of rolling windows to automatically add and drop partitions. If nothing else, the overhead of finding the proper partition for a new row is far less for a RANGE partition than an expression partition plan especially when the partition definition for the newest data is the last expression.
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com------------------------------
Original Message:
Sent: Tue January 11, 2022 06:37 AM
From: ZhiWei Cui
Subject: alter fragment on fragment table bring longtx
I found that a long transaction occurred in the database because of the alter table。
create table "informix".t1
(
id serial not null ,
name varchar(20),
tx_date char(8)
)
fragment by expression
partition p1 ((tx_date >= '20210101' ) AND (tx_date < '20210201'
) ) in datadbs1,
partition p2 ((tx_date >= '20210201' ) AND (tx_date < '20210301'
) ) in datadbs1,
partition p3 ((tx_date >= '20210301' ) AND (tx_date < '20210401'
) ) in datadbs1,
partition p4 ((tx_date >= '20210401' ) AND (tx_date < '20210501'
) ) in datadbs1,
partition p5 ((tx_date >= '20210501' ) AND (tx_date < '20210601'
) ) in datadbs1
extent size 16 next size 16 lock mode row;
revoke all on "informix".t1 from "public" as "informix";
create index "informix".idx_t1 on "informix".t1 (tx_date,id) using btree ;
dbaccess ttdb -
> alter fragment on table t1 drop partition p6;
onlog -n 10 -t 7340114
DDL.PNG
In the production environment, I found that the contents of the logical log are as follows：
in logical log，there is more and more CHALLOC，and this is a longTRX
DML.PNG
Why?
Shouldn't the alter statement be a DDL statement?
------------------------------
ZhiWei Cui
GBASE
------------------------------
#Informix