Re: Performance implications of increasing a varchar2 column width

In article <7gd05p$a8e$1_at_nnrp1.dejanews.com>,   jrb_6502_at_my-dejanews.com wrote:
>
>
> Hi,
>
> I'm trying to get a sense of the performance implications of increasing the
> width of a varchar2 column in a frequently accessed table with several hundred
> thousand rows. The column is currently 30 chars wide. I'd like to make it 100
> chars. An entire row is currently 230 chars wide, and would grow to 300. There
> are 3 indexes on the table, all of which include the column I want to grow.
>
> This change is to permit new rows to be added that have wider data in that
> column. No changes will be made to existing data.
>
> The database as a whole already sees some performance problems, though not
> necessarily related to this table. I'd like to get a sense whether this sort
> of change typically impacts performance.
>
> Any concerns here?
>
> Thanks,
>
> Jonah
>

The performance difference between a table with VARCHAR2(30) and VARCHAR2(100) is minimal.

Since you already have data in the table, will that data be updated (modified)? If it is, then you have a much greater chance of chained rows. Chained rows can be a real performance hit. If that is the case then I'd suggest you backup that table, make the modifications, then reload the data.

You need to examine the storage parameters for your tables and indices too, especially PCTFREE and PCTUSED.

And don't forget to ANALYZE your tables regularly. If the Optimizer concludes that the table statistics are stale, then it can choose badly for queries, yielding lower performance.

--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon May 03 1999 - 08:56:00 CDT