A couple of points to watch out for - again related to a small model for a
big object.
Are you counting leaf blocks in the index to get 4MB or looking at the size
of the segment ?
At a small scale there could be a big difference - a typical "create index"
in an ASSM tablespace might start with a few 64KB extents and end up with a
few 1MB extents, so you could have an index of just over 3MB that has
allocated 4MB of space. (Unless you're on a very old version of Oracle the
"explain plan" calculation will allow for the tablespace the index is going
to be in in exactly the same way:
https://jonathanlewis.wordpress.com/2009/05/22/index-size/ ;).
There is an opposite problem to consider. If your primary key index is
sequence or time based then it could (in principle) run at 100% efficiency,
i.e. leaving no free space in any blocks. In many cases, though, because
rows that are generated with sequence numbers or time stamps are inserted
slightly out of order, you can easily end up with lots of leaf blocks doing
50/50 splits; it also possible (in modern versions of Oracle) to create the
index using scalable sequence numbers which pretty much guarantee that the
index will have most of it's leaf blocks with 50% free space (the same is
true for various manual tricks that people use to avoid contention on the
"highest value" leaf block). There's a further problem that an odd coding
feature of index leaf block splits means you could end up with far more ITL
entries per leaf block that you need - so in the worst case (in older
versions of Oracle) you could be running at 75% free space; in newer
versions the problem hasn't gone away completely but the wastage could
still be in excess of 56%
Bottom line - you need to think a little carefully about how you define the
index to the "explain plan" feature when you use it to estimate the index
size. INITRANS, PCTFREE and TABLESPACE could make a big difference to what
your index is really going to look like.
NB If you read the article you'll note that I didn't need to create a table
with 100M rows to get an estimate of the index on 100M rows.
Regards
Jonathan Lewis
On Mon, 22 Feb 2021 at 08:21, Pap <oracle.developer35@xxxxxxxxx> wrote:
Yes this way through explain plan also the size coming as ~4MB, same as I
have manually created for 100K rows.
Regards
Pap
On Mon, Feb 22, 2021 at 1:38 PM Lothar Flatz <l.flatz@xxxxxxxxxx> wrote:
if you do an explain plan on an "create index " statement you get a size
estimate (Uncompressed).
Regards
Lothar
Am 22.02.2021 um 01:48 schrieb Jack van Zanen:
Or
Simply create the table and indexes in a test environment and add 100K
dummy records, record the size and multiply to scale.
no need for maths and also fills indexes so you will know the sizes for
those as well :-)
Jack van Zanen
-------------------------
This e-mail and any attachments may contain confidential material for the
sole use of the intended recipient. If you are not the intended recipient,
please be aware that any disclosure, copying, distribution or use of this
e-mail or any attachment is prohibited. If you have received this e-mail in
error, please contact the sender and delete all copies.
Thank you for your cooperation
On Mon, Feb 22, 2021 at 9:35 AM Mark W. Farnham <mwf@xxxxxxxx> wrote:
What JL wrote, and you did only ask about the size for the table.
BUT, since you marked a primary key that is almost certainly supported
by an index and you may have additional indexes, so you’ll need to tack
space for indexes on to get total storage requirements.
mwf
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jonathan Lewis
*Sent:* Sunday, February 21, 2021 1:29 PM
*To:* Oracle L
*Subject:* Re: Size estimation
The number(15,0) will take at most 9 bytes
The number(13,0) will take at most 8 bytes each
So your estimate should be 496 - 13 - 14 - 14 = 455
Then you need to add one byte per column to get 471.
Then you have to allow for block size, which means 8,066 bytes available
from an 8KB block size with pctfree 0, initrans 2 (default) and ASSM
Max rows = trunc(8066 / 471) = 17 rows per block,
At 100M rows that's 5,882,353 data blocks.
If you create the table using a large extent size *8MB min) you get 1
bitmap block for every 128 blocks allocated so your block requirement goes
up by 128/127,
so a total of 5,928,671 blocks. Round that up to the nearest 64MB
(assumed extent size) - 5,931,008 blocks = 45.25GB.
So even with several errors on the way you got pretty close to the
"right" answer.
Realistically, though, you're unlikely to fill all those 40 and 50
character columns, and unless you're very carefull with setting pctfree
(and maybe playing around with the Hakan factor) you're probably going to
run into problems with getting too many rows into a block on the initial
insert and running into problems with row migration.
There's also the question of multi-byte character sets - are you
thinking of your varchar2(N) declarations N bytes (the default assumption)
or N characters (which, depending on character set could mean up to 4N
bytes).
Regards
Jonathan Lewis
On Sun, 21 Feb 2021 at 17:03, Pap <oracle.developer35@xxxxxxxxx> wrote:
Hi Listers, It's Oracle RDBMS version 11.2.0.4 exadata. We have a table
with structure as below which is going to be created as part of a new
project. And we want to predict the storage/space requirement for this. It
may not be the exact size but at least we want to estimate the AVG and
MAXIMUM space requirement for the table , if all the columns filled with
not null values with max column length being occupied/filled for each of
the columns.
So to estimate the maximum space requirement , is it correct to Just
add the length of the column as it is in bytes and multiply it with the
projected number of rows. Something as below.
