Re: char(1) vs Number(1)

From: Noons <wizofoz2k_at_gmail.com>
Date: Mon, 30 Nov 2009 18:57:08 -0800 (PST)
Message-ID: <d5fa2f31-5e0e-4dc4-8d4d-106824bb130c_at_15g2000prz.googlegroups.com>



On Dec 1, 4:59 am, CrazyKarma <ska..._at_gmail.com> wrote:
> Quick check on whether char(1) is more efficient than number (1).
> Basically storing a bit value of either 0 or 1. since this is a
> numeric maybe its better to store this as number(1).
>
> Did a simple test for 1million rows, the IO cost is the same but the
> number of bytes is double with number(1).
> This makes me think that char(1) is more efficient in storage.
>
> any thoughts?

char(1) will be more efficient spacewise, but be absolutely sure you won't use it as "0"/"1".
Use instead "N"/"Y".

The last thing you want is Oracle doing implicit conversions of a char (1) to number when comparing it to "1". In simple terms, don't use this: WHERE BOOL_COL = 1.
Use instead WHERE BOOL_COL = 'Y'.
The reason is very simple: the first form will cause an implicit CAST of BOOL_COL to NUMBER data type to compare against 1.

That will cause any indexes including BOOL_COL in its native format to not be used. So: either use the second form of the WHERE or make sure any indexes using the column will be FBI. Received on Mon Nov 30 2009 - 20:57:08 CST

Original text of this message