PCTFREE=0 and updates
Robert Eden
VARCHAR)) can I sent PCTFREE to 0 to avoid the (default) 10% waste? (no
nulls involved)
The Oracle docs aren't clear on the types of operations that change "row
size".
Robert
Daniel Morgan
CREATE TABLE xyz (
varfield VARCHAR2(10),
charfield CHAR(10));
INSERT INTO xyz
(varfield)
VALUES
('ABC');
COMMIT;
SELECT LENGTH(varfield), LENGTH(charfield)
FROM xyz;
What do you think?
Is hard disk space that precious?
Daniel Morgan
Sybrand Bakker
wrote:
number is not statically sized.
Any operation that would make any column at least one byte longer will
affect the rowsize.
Decreasing PCTFREE to 0 will likely result in chained rows (ie rows in
more than 1 Oracle block)
The effect of chaining on performance is big enough to avoid reducing
PCTFREE to 0. Please reconsider.
Regards
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Mark D Powell
Robert, column of type numeric result in variable size of storage
requirements depending on their value, and even if all the columns
were declared as type char and date (which is always 7 bytes) what
happens if at some point in the future you need to add another column?
I would suggest you set at least a small value for pctfree, say 3 -
5%.
IMHO -- Mark D Powell --
Connor McDonald
As well as some other arguments, you can end up with an artificial
locking scenarion because of insufficient space for ITL's. Maybe go
with 1
hth
connor
--
==============================
Connor McDonald
"Some days you're the pigeon, some days you're the statue..."
Robert Eden
Thanks to all.. I didn't realize that numbers vary in storage requirements.
I'll watch my data and see if it stays in a small range or varies a lot on
updates.
I'm not too concerned with additional columns, these are daily raw data
tables that get created fresh every day. (deletes within a multi-day table
would lead to serious fragmentation... this way I drop a segment at once.)
I could use partitioned tables, but since we're writing the application,
storing the date in the table name isn't a problem.
I'm currently leaning towards a 0 PCTFREE for my daily raw table (only
inserts and update of char(1)) and for my monthly summary table either the
default (10%) or reducing it after a study of the number fields.
Transaction slots aren't an issue for the raw table, only 2 programs access
the raw table, but it's huge ( 2-5 GB each ), so 10% isn't chump change.
Thanks for everyone's input.
Robert
Sean M
>
> Decreasing PCTFREE to 0 will likely result in chained rows (ie rows in
> more than 1 Oracle block)
> The effect of chaining on performance is big enough to avoid reducing
> PCTFREE to 0. Please reconsider.
Just to be clear, you mean migrated rows, right? Not chained? Reducing
PCTFREE will likely result in more *migrated* rows, but will typically
*decrease* the number of chained rows. Migrated rows are those that
grew during an update such that they exceeded the amount of usable free
space in the block and had to be moved entirely out of that block and
placed in another block (or blocks), leaving behind only a pointer to
the new location. A chained row is merely one that doesn't entirely fit
in the usable free space of a block during an insert and is spread
across multiple blocks. Therefore *increasing* PCTFREE (i.e. reserving
more room in the block for updates, and thereby reducing the amount of
usable freespace for inserts) will likely result in chained rows, while
decreasing PCTFREE typically reduces the likelihood that a row will be
chained across blocks but increase the chance it will be migrated during
an update.
If your row is bigger than your blocksize, you simply can't avoid
chaining. But migrated rows hurt worse since you have to waste a read
on the original block only to find the row is no longer there and you
have to go read it's new block (which itself may be migrated, etc.).
Regards,
Sean
Daniel Morgan
Try this:
CREATE TABLE xyz (
varfield VARCHAR2(10),
charfield CHAR(10));
INSERT INTO xyz
(varfield)
VALUES
('ABC');
COMMIT;
SELECT LENGTH(varfield), LENGTH(charfield)
FROM xyz;
What do you think?
Is hard disk space that precious?
Daniel Morgan
Robert Eden wrote:
> If I only update statically sized columns in a table ( NUMBER, CHAR
(not
> VARCHAR)) can I sent PCTFREE to 0 to avoid the (default) 10% waste?
(no
> nulls involved)
>
> The Oracle docs aren't clear on the types of operations that change
"row
> size".
>
> Robert
--
Daniel Morgan - Unregistered User
------------------------------------------------------------------------
View this thread: http://homer-w2k:7802/baanboard/showthread.php?threadid=7606
Robert Eden
If I only update statically sized columns in a table ( NUMBER, CHAR
(not
VARCHAR)) can I sent PCTFREE to 0 to avoid the (default) 10% waste? (no
nulls involved)
The Oracle docs aren't clear on the types of operations that change
"row
size".
Robert
--
Robert Eden - Unregistered User
Mark D Powell
> If I only update statically sized columns in a table ( NUMBER, CHAR
(not
> VARCHAR)) can I sent PCTFREE to 0 to avoid the (default) 10% waste?
(no
> nulls involved)
>
> The Oracle docs aren't clear on the types of operations that change
"row
> size".
>
> Robert
Robert, column of type numeric result in variable size of storage
requirements depending on their value, and even if all the columns
were declared as type char and date (which is always 7 bytes) what
happens if at some point in the future you need to add another column?
I would suggest you set at least a small value for pctfree, say 3 -
5%.
IMHO -- Mark D Powell --
--
Mark D Powell - Unregistered User
Sybrand Bakker
>If I only update statically sized columns in a table ( NUMBER, CHAR
(not
>VARCHAR)) can I sent PCTFREE to 0 to avoid the (default) 10% waste?
(no
>nulls involved)
>
>The Oracle docs aren't clear on the types of operations that change
"row
>size".
>
>Robert
>
number is not statically sized.
Any operation that would make any column at least one byte longer will
affect the rowsize.
Decreasing PCTFREE to 0 will likely result in chained rows (ie rows in
more than 1 Oracle block)
The effect of chaining on performance is big enough to avoid reducing
PCTFREE to 0. Please reconsider.
Regards
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
--
Sybrand Bakker - Unregistered User
Robert Eden
(not
> VARCHAR)) can I sent PCTFREE to 0 to avoid the (default) 10% waste?
(no
> nulls involved)
>
> The Oracle docs aren't clear on the types of operations that change
"row
> size".
Thanks to all.. I didn't realize that numbers vary in storage
requirements.
I'll watch my data and see if it stays in a small range or varies a lot
on
updates.
I'm not too concerned with additional columns, these are daily raw data
tables that get created fresh every day. (deletes within a multi-day
table
would lead to serious fragmentation... this way I drop a segment at
once.)
I could use partitioned tables, but since we're writing the
application,
storing the date in the table name isn't a problem.
I'm currently leaning towards a 0 PCTFREE for my daily raw table (only
inserts and update of char(1)) and for my monthly summary table either
the
default (10%) or reducing it after a study of the number fields.
Transaction slots aren't an issue for the raw table, only 2 programs
access
the raw table, but it's huge ( 2-5 GB each ), so 10% isn't chump
change.
Thanks for everyone's input.
Robert
--
Robert Eden - Unregistered User
Howard J. Rogers
want to avoid row migration.
Don't forget, too, that a column declared as a CHAR, but nullable, is also
inherently variable. So the existence of such columns would also tend to
make row migration a likely problem.
Regards
HJR
--
-----------------------------------------------
Resources for Oracle : http://www.hjrdba.com
===============================
"Robert Eden" <rme...@yahoo.com> wrote in message
news:0WXs8.77499$Bk1.295...@newssvr30.news.prodigy.com...