Re: How to do faster DML

On Sun, Feb 11, 2024 at 11:54 AM veem v <veema0000(at)gmail(dot)com> wrote:
[snip]

> When you said *"you would normally prefer those over numeric " *I was
> thinking the opposite. As you mentioned integer is a fixed length data type
> and will occupy 4 bytes whether you store 15 or 99999999.But in case of
> variable length type like Number or numeric , it will resize itself based
> on the actual data, So is there any downside of going with the variable
> length data type like Numeric,
>

Consider a table with a bunch of NUMERIC fields. One of those records has
small values (aka three bytes). It fits neatly in 2KiB.

And then you update all those NUMERIC fields to big numbers that take 15
bytes. Suddenly (or eventually, if you update them at different times),
the record does *not* fit in 2KiB, and so must be moved to its own.page.
That causes extra IO.

> Varchar type always for defining the data elements?
>

Internally, all character-type fields are stored as TEXT. CHAR and
VARCHAR(XX)'s only purposes are SQL-compliance and length-limitation.
And length-limitation is "just" a legacy carried forward from the card
punch days.