Den 2022-02-15 kl. 16:10, skrev Vlad Khorsun:
15.02.2022 15:20, Dmitry Yemanov wrote:
...
I can think of two ways to proceed:
1) Keep rebuilding the index every time the type is changed. Cast all
column values into the new format before storing keys into the index.
Forget about being scale-independent, pick the scale from the latest
table format (and store BIGINTs or all exact numerics as integers
without scale, as you suggest).
2) Prohibit decreasing the scale. Avoid rebuilding the indices unless
really necessary (idx_itype is changed). Consider it a good thing (tm).
Maybe there may be other options, I didn't think deeply about this.
2+) Rebuild index only if scale was decreased. Add optional clause
at SQL syntax level
to explicitly [dis]allow this. If scale was not decreased - leave
index unchanged.
With respect, since I'm not in the dev team, I fail to see it as an
important feature to avoid index rebuild when changing between integer
and numeric column types:
1. Changing between such types must be pretty rare, at least on
production databases. During app development, yes, sure, but at that
stage the data is probably small or non existent, so rebuild won't
matter anyway.
2. It already doesn't work and nobody has really complained, correct?
3. Changing data type of an indexed column is an operation where you do
expect a non-negligible impact, i.e. you expect the DB engine to have to
do "some work".
So, I'd vote for a solution that drops the ambition to avoid index
rebuilds, and instead focuses only on index size and index performance.
Just m.h.o.
I do have a production database with a couple of tables containing a few
hundred million records using bigint primary keys, and all tables use
bigint keys all over the place, so I think I would benefit from
improvements there. :-)
Regards,
Kjell
begin:vcard
fn:Kjell Rilbe
n:Rilbe;Kjell
org:Marknadsinformation i Sverige AB;Utveckling & databaser
adr;quoted-printable:;;Ulvsundav=C3=A4gen 106C;Bromma;Stockholm;16867;Sverige
email;internet:kjell.ri...@marknadsinformation.se
title:Utvecklings- & databasansvarig
tel;cell:0733-442464
x-mozilla-html:TRUE
url:http://www.marknadsinformation.se
version:2.1
end:vcard
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
