OraFAQ Forum: Performance Tuning » Does Increasing Field Length Decrease Perf?

Home » RDBMS Server » Performance Tuning » Does Increasing Field Length Decrease Perf?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Does Increasing Field Length Decrease Perf? [message #65914]

Mon, 31 January 2005 09:53

Steve F
Messages: 4
Registered: January 2005

Junior Member

If i do an alter table, and increase a field's length, does that mean that a portion of the data held in that field will now be written to a 'new' separate area of the disk, thus degrading performance when querying on that field?

We have a pretty tenuous performance situation here at work so I need to be careful about any changes to our db.

Thank you, -Steve

Report message to a moderator

Re: Does Increasing Field Length Decrease Perf? [message #65915 is a reply to message #65914]

Mon, 31 January 2005 11:11

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

after increasing the column length, if you update the concerned column, You may endup with a chained row.
But , you know..databases always have some chained rows...which can be fixed / be controlled .. update the statistics of tables and indexes.

this URL might help...
http://www.orafaq.com/forum/t/23515/0/

[Updated on: Fri, 18 February 2005 23:32]

Report message to a moderator

Re: Does Increasing Field Length Decrease Perf? [message #109308 is a reply to message #65915]

Wed, 23 February 2005 12:47

Mack Sundar
Messages: 20
Registered: July 2002

Junior Member

When the size of a particular column within a record is increased, the database will most likely store that in a different logical data block (assuming that there is no more space available within that data block). This could be in a different physical disk location.

Couple of ways to resolve this:
1. CTAS: Create table <new_table_name> as (select * from <old_table>). Read up on CTAS.
2. Export/Import the table
3. ALTER TABLE...COALESCE

Report message to a moderator

Re: Does Increasing Field Length Decrease Perf? [message #109902 is a reply to message #109308]

Tue, 01 March 2005 15:38

andrew again
Messages: 2577
Registered: March 2000

Senior Member

simply analyze your tables (in a non-prod environment) and check the chained row count. If updates are causing a high (> a few %) number of chained rows, you may need to increase PCTFREE for the table.

analyze table abc estimate/compute statistics;

select table_name, pct_free, pct_used, num_rows, chain_cnt, last_analyzed
from user_tables where table_name = 'ABC';

As far as I know, dbms_stats doesn't gather chained row counts - but give it a try - analyze table is an oldish command.

begin
dbms_stats.gather_table_stats('SCOTT', 'ABC');
end;

Report message to a moderator

Previous Topic:	Help: chained rows
Next Topic:	Why Performance and Tuning is important

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 01 01:05:35 CDT 2025