Also before any index rebuild, set FILLFACTOR during the build to something that reflects how the index will be used.
BTW, a parallel index build after a data load will usually take less time than the time saved by loading the table without that index in place. It usually saves time to drop the index before the build, load the data, then build the index with PDQPRIORITY or PSORT_NPROCS set with a high fill factor (like 99 or 100) so that the index is most efficient to query. An index build by a data load will have, on average, nearly all of its nodes half filled making index searches use twice the number of IOs. So, besides the load-time savings, there will be processing performance improvements from building the index after the data load.
If you are using a unique index to filter duplicate data out of the inputs, then do that filtering BEFORE the load instead. One simple way is by sorting the input data by the unique key, then map the input file to an external table and insert into the staging table by SELECT DISTINCT from the external table. Or just let 'sort -u' remove the duplicates from the file.
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com------------------------------
Original Message:
Sent: Fri June 04, 2021 09:13 AM
From: TOM GIRSCH
Subject: Compression with fragmented tables
Also, I was suggesting just doing the index rebuild once, after implementing compression. All of the on-the-fly rewrites to update the page locations may have made the indices more inefficient and therefore larger. Post rebuild, just make sure you've got your btscanners set fairly aggressively.
Original Message:
Sent: 6/4/2021 9:08:00 AM
From: Benoit Chamberland
Subject: RE: Compression with fragmented tables
Thanks Tom,
This is what I thought.
In our case rebuilding the indexes every time we're doing our weekly automatic fragment rotation on a, lets say, 320G of data table is out of question.
It would be great though if this functionality was available, especially where we need compression the most.
Benoît
Benoît Chamberland
Administrateur de bases de données | Database Administrator
T : 514-870-2440 M : 514-207-7015
Original Message:
Sent: 6/4/2021 8:55:00 AM
From: TOM GIRSCH
Subject: RE: Compression with fragmented tables
Unfortunately, to use "rolling window" purging, the indices must be attached. Still, you might try dropping and recreating an index that isn't needed for referential integrity to see if it builds back a bit more compactly.
I think it would be a pretty heavy lift to allow for index compression, since it would make the search logic more complicated.
Original Message:
Sent: 6/3/2021 11:20:00 AM
From: Benoit Chamberland
Subject: RE: Compression with fragmented tables
Thanks Art and Jean-François!
I observe a reduction of 30-40% in the number of data pages after activating the compression.
Often, the indexes are taking as much space than the data but by fragmenting the table, these btree indices being attached are not compressible.
Are there any strategies possible to compress attached indexes? If not, what if I fragmented these indexes according to a matching table fragmentation pattern but in their own set of dbspaces? I suspect this could interfere with the data rotation/cleanup mechanism?
Benoît
------------------------------
Benoit Chamberland
Bell Canada
Original Message:
Sent: Wed June 02, 2021 07:13 PM
From: Art Kagel
Subject: Compression with fragmented tables
FYI, Look into using RANGE/INTERVAL fragmentation to automate adding and detaching or dropping partitions!
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Wed June 02, 2021 03:14 PM
From: Benoit Chamberland
Subject: Compression with fragmented tables
Hi everyone, we're using Informix 14.10.FC5. I need to fragment a table in let's say one 30G dbspace a week (7 millions records/ week) for easier data rotation (detach full fragment, attach empty fragment strategy). Btree indexes are of course attached.
In order so save some space, I was asked if compression could be used either at the table or the dbspace level.
I understand that If I create the table with the 'COMPRESSED' option, auto-compress should take care of compressing data in the newly attached fragments after they reach enough (2000) entries to build a compression dictionary. I will run some test in a few days to see...
My other concern is about the attached btree indices: I read that they will not compress. Am I right? Is there a better approach I could take?
Thanks,
Benoît Chamberland
------------------------------
Benoit Chamberland
------------------------------
#Informix