Re: Performance implications of increasing a varchar2 column width

The most important point in the question is that the varchar2(100) appears in 3 indexes. that makes for a rather long index entry, and not many index entries per leaf block. Consequently the 3 indexes may (if sufficient numbers of rows use whole 100 characters) be deemed bad choices by the cost based optimiser.

You may want to consider have a 'meaningless number' column in addition to the varchar2(100) column to allow for more space-effective indexes.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Ed Prochak wrote in message <7gk9te$ufg$1_at_nnrp1.dejanews.com>...
>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.
>>
>
>The performance difference between a table with VARCHAR2(30) and
VARCHAR2(100)
>is minimal.
>
>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.
Received on Mon May 03 1999 - 10:47:14 CDT