Re: CHAR vs VARCHAR2 and BITMAP efficiency

Hi Sheepish,

I'm always a little concerned when blocks are deliberately made less efficient (in that they store less data than they could) for the sake of improved efficiencies with bitmap indexes. Using CHAR to achieve this would in my opinion be counter productive in that more blocks would potentially be required to store the same amount of data. This decreases full table scan performance (which is still a very valuable execution path in may cases). Using record_per_block to *artificially* limit the records per block (rather than letting Oracle know that a table actually only has a maximum of x records_per_block) is also potentially counter productive.

Cheers

Richard
"sheepish" <rjc4687_at_hotmail.com> wrote in message news:1c0a0a83.0207300729.63a34ea6_at_posting.google.com...
> Sort of related to the earlier CHAR vs VARCHAR2 thread.
>
> Having probably never used CHAR I had this thought.
>
> When a bitamp index is being created Oracle needs to calculate the
> maximum number of slots per block for a given table so it knows how
> many values there are for the bitmap for the index on that block.
>
> I know marking columns as NOT NULL would let Oracle know there would
> be at least
> 1 byte in that column and so possibly decrease the maximum number of
> slots calculated. If I had a column that would always be 10 characters
> long would CHAR(10) be better than VARCHAR2(10) for getting smaller
> bitmaps created given that a definitely longer row means fewer slots
> per block?
>
> May all be hypothetical as far as performance goes but I'm interested
> in the internals. I know I could just try and if it works I should see
> a smaller created index, but basically I'm too lazy, unless no one
> knows in which case curiosity may get the better of me.
>
> Rob Cowell
> DBA
> Cardiff, UK
Received on Tue Jul 30 2002 - 20:06:23 CDT