Home » SQL & PL/SQL » SQL & PL/SQL » NUMBER(1) or CHAR(1) for flag? (11g, server 2003)
NUMBER(1) or CHAR(1) for flag? [message #422547] Thu, 17 September 2009 06:37 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi all,
I have some doubt regarding data type of FLAG like column.
I have to put one column in my table and that table is like flag value. It can have values like TRUE/FALSE or say 0/1. Or it may have 2-3 more values.
This column value is not used in any calculation. It is used only for comperision with IF statement.

So out of two options, NUMBER(1) and CHAR(1), which will be better, considering performance and memory requirement?

Please help me in deciding type of that column.

regards,
Delna
Re: NUMBER(1) or CHAR(1) for flag? [message #422549 is a reply to message #422547] Thu, 17 September 2009 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No one is better, it does not matter, use the one you want.

Regards
Michel
Re: NUMBER(1) or CHAR(1) for flag? [message #422550 is a reply to message #422549] Thu, 17 September 2009 06:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
One thing that I have been doing lately in some cases, especially for Flag values that are likely to change from install base to install base of an application is to define one number field for that "diverse" flags, and then have the flag values assigned bit-wise.

For example, define flags like
processed   = 1
in transfer = 2
closed      = 4
assigned    = 8


and then store the ORed / summed value in the field.

That way I can add additional flags without changing the data structure.

Somewhat like this example here in the Oracle Documentation to BITAND
Re: NUMBER(1) or CHAR(1) for flag? [message #422552 is a reply to message #422550] Thu, 17 September 2009 06:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think this kind of datatype (bit flag used with BITAND) is useful when several flags are used and not exclusive.
When the values are exclusive it is better to use VARCHAR2 or raw INTEGER with check constraint.
There is no need to have a bit for each value if each value cannot happen at the same time. It is faster to have FLAG=ASSIGNED than BITAND(FLAG,ASSIGNED)=ASSIGNED.

Regards
Michel
Re: NUMBER(1) or CHAR(1) for flag? [message #422555 is a reply to message #422547] Thu, 17 September 2009 07:08 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Ok.
Thanks all.

regards,
Delna
Re: NUMBER(1) or CHAR(1) for flag? [message #422556 is a reply to message #422547] Thu, 17 September 2009 07:09 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Boolean values and variables are very useful in PL/SQL. Because a Boolean variable can only be TRUE, FALSE, or NULL, you can use that variable to explain what is happening in your code. With Booleans you can write code that is easily readable because it is more English-like. You can replace a complicated Boolean expression involving many different variables and tests with a single Boolean variable that directly expresses the intention and meaning of the text.

Here is an example of an IF statement with a single Boolean variable (or functionyou really can't tell the difference just by looking at this short bit of code):
    IF report_requested
    THEN
       print_report (report_id);
    END IF;



The beauty of this technique is that it not only makes your code a bit more self-documenting, it also has the potential to insulate your code from future change. For example, consider the human interface that needs to precede the previous code fragment. How do we know that a report was requested? Perhaps we ask the user to answer a question with a Y or an N, or perhaps the user must place a check in a checkbox or select an option from a drop-down list. The point is that it doesn't matter. We can freely change the human interface of our code, and, as long as that interface properly sets the report_requested Boolean variable, the actual reporting functionality will continue to work correctly.

Quote:
Or it may have 2-3 more values.


then in that case go ahead with your approach
Re: NUMBER(1) or CHAR(1) for flag? [message #422557 is a reply to message #422552] Thu, 17 September 2009 07:10 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes. If selection fastness is required, that is an additional consideration.

There can always be a function based index for special cases, tough. We had one application where there used to be a char field with a status to decide whether or not the row still needed further processing, with two states. Even with an index on that column, it still wasn't as fast as we liked it to be. (about 500 million rows in the table, with usually a few thousand that needed processing), and the index was quite big.

We then switched to a function based index, where the function returned 1 when the row needed processing and NULL if not, which of course resulted in a lightning-fast select and a tiny index.

Re: NUMBER(1) or CHAR(1) for flag? [message #422561 is a reply to message #422556] Thu, 17 September 2009 07:34 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Yes Ayush, I am aware of that. But as I said, I have to store flag value in the column of the table. Which does not support BOOLEAN type.

regards,
Delna
Re: NUMBER(1) or CHAR(1) for flag? [message #422562 is a reply to message #422561] Thu, 17 September 2009 07:39 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
ohhh I missed again Sad

Quote:
We then switched to a function based index, where the function returned 1 when the row needed processing and NULL if not, which of course resulted in a lightning-fast select and a tiny index.


Thomas why did you consider the possibility of adding bitmap indexes?
Re: NUMBER(1) or CHAR(1) for flag? [message #422565 is a reply to message #422547] Thu, 17 September 2009 07:48 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Just for curiosity, which one, out of these two, should I select, if I want to use that column value in calculation?

regards,
Delna
Re: NUMBER(1) or CHAR(1) for flag? [message #422566 is a reply to message #422562] Thu, 17 September 2009 07:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
ayush_anand wrote on Thu, 17 September 2009 14:39

Thomas why did you consider the possibility of adding bitmap indexes?


Did you mean "didn't" instead of "did"? Very Happy

In fact I did not only consider it, I added bitmap index on the test system to try it.

The test then were somewhat like 10 Minutes run-time with a normal index, 8 minutes with the bitmap one, and 20 seconds with the function based one.
Re: NUMBER(1) or CHAR(1) for flag? [message #422567 is a reply to message #422565] Thu, 17 September 2009 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delna.sexy wrote on Thu, 17 September 2009 14:48
Just for curiosity, which one, out of these two, should I select, if I want to use that column value in calculation?

regards,
Delna

Which calculation? In the first question it was just a flag.

Regards
Michel

Re: NUMBER(1) or CHAR(1) for flag? [message #422568 is a reply to message #422547] Thu, 17 September 2009 07:58 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
delna.sexy wrote on Thu, 17 September 2009 07:37

So out of two options, NUMBER(1) and CHAR(1), which will be better, considering performance and memory requirement?


Well...taking a non-technical look at this, if you use number, you can save on 2 extra characters in each IF/CASE statement as you won't have to wrap the value in single quotes. Just thought I'd throw that in there too.
Re: NUMBER(1) or CHAR(1) for flag? [message #422572 is a reply to message #422567] Thu, 17 September 2009 08:04 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Michel sir, As I said in that post,

Quote:
Just for curiosity...


It was just a question.
And can't I ask second question?

regards,
Delna
Re: NUMBER(1) or CHAR(1) for flag? [message #422574 is a reply to message #422572] Thu, 17 September 2009 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you can, and the question remains "which (king of) calculations"? Of course, the type and implementation depends on this.

Regards
Michel
Re: NUMBER(1) or CHAR(1) for flag? [message #422623 is a reply to message #422568] Thu, 17 September 2009 23:36 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
And looking into technically, the space occupied by number(1) and char(1) is as under

XE@SQL> create table test(x number(1));

Table created.

XE@SQL> insert into test select rownum from dual connect by level <=9;

9 rows created.

XE@SQL> commit;

Commit complete.


XE@SQL> select x,vsize(x) from test;

         X   VSIZE(X)
---------- ----------
         1          2
         2          2
         3          2
         4          2
         5          2
         6          2
         7          2
         8          2
         9          2

9 rows selected.

XE@SQL> delete from test commit;

9 rows deleted.

XE@SQL> insert into test select -rownum from dual connect by level <=9;

9 rows created.

XE@SQL> commit;

Commit complete.

XE@SQL> select x,vsize(x) from test;

         X   VSIZE(X)
---------- ----------
        -1          3
        -2          3
        -3          3
        -4          3
        -5          3
        -6          3
        -7          3
        -8          3
        -9          3

9 rows selected.

XE@SQL> drop table test purge;

Table dropped.

XE@SQL> create table test(x varchar2(1));

Table created.

XE@SQL> insert into test select rownum from dual connect by level <=9;

9 rows created.

XE@SQL> commit;

Commit complete.

XE@SQL> select x,vsize(x) from test;

X   VSIZE(X)
- ----------
1          1
2          1
3          1
4          1
5          1
6          1
7          1
8          1
9          1

9 rows selected.

XE@SQL> drop table test purge;

Table dropped.



This does not mean you have to always use char(1) instead of number(1)
but you should use one that fits your requirements correctly.

[Updated on: Thu, 17 September 2009 23:54]

Report message to a moderator

Re: NUMBER(1) or CHAR(1) for flag? [message #422625 is a reply to message #422547] Thu, 17 September 2009 23:54 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Oracle's minimum unit of space allocation is 1 block.
As long as any field does not "extend" a record into another block, such discussion are comparable to discussing how many angles can dance on the head of a pin.

Plus, given the low, low, low price of disk storage today,
salary spent in such discussions could outweigh any potential savings.

Re: NUMBER(1) or CHAR(1) for flag? [message #422628 is a reply to message #422625] Fri, 18 September 2009 00:12 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
Oracle's minimum unit of space allocation is 1 block.


No it must be at least 2 blocks in size as an absolute minimum, source Richard Foote's blog
Re: NUMBER(1) or CHAR(1) for flag? [message #422630 is a reply to message #422628] Fri, 18 September 2009 00:23 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
bonker wrote on Thu, 17 September 2009 22:12
Quote:
Oracle's minimum unit of space allocation is 1 block.


No it must be at least 2 blocks in size as an absolute minimum, source Richard Foote's blog


My point is/was it is "slightly" larger than the size of any single "small" field.


Re: NUMBER(1) or CHAR(1) for flag? [message #422633 is a reply to message #422623] Fri, 18 September 2009 00:30 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Oh thanks bonker sir for unvailing all those information I see ever first time.

That is surprizing that Oracle provides one more byte for negative values with same magnitude.
Is there any particular reason or logic behind that?
In other programming languages, it is not like that.

The other thing, I am shocked about it is
SQL>desc tb;
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- ------
 COL                                                                                          NUMBER(38)

SQL>select col, vsize(col) from tb;

       COL VSIZE(COL)
---------- ----------
         1          2
        -1          3
        99          2
       -99          3
       999          3
      -999          4
      9999          3
     -9999          4
9999999999          6
-1.000E+10          7

10 rows selected.


different number of bytes required to store different sized values in same column.
Or is it VSIZE function that shows only required number of bytes to store that value instead of showing actual number of bytes used to store that value?

Please reply.

Thanks for providing this unknown information.

regards,
Delna
Re: NUMBER(1) or CHAR(1) for flag? [message #422634 is a reply to message #422633] Fri, 18 September 2009 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use DUMP instead of VSIZE to answer your question.

Regards
Michel
Re: NUMBER(1) or CHAR(1) for flag? [message #422636 is a reply to message #422547] Fri, 18 September 2009 00:42 Go to previous messageGo to next message
amit_nanote
Messages: 56
Registered: July 2005
Location: Indore
Member

Hi,

You can use Char(1). After considering memory and performance requirements and no calculations are required.

Because Zero and negative infinity each require 1 byte; positive infinity requires 2 bytes. In Oracle's internal number format, 0 takes 1 byte and 1 takes 2 bytes after the length byte (so it's more efficient to store it as CHAR).
Also it is very easy to convert a Char to number whenever required.
Thanks.
Re: NUMBER(1) or CHAR(1) for flag? [message #422637 is a reply to message #422636] Fri, 18 September 2009 00:44 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
Also it is very easy to convert a Char to number whenever required.


This can be disasterous sometimes in purview of performance.
Re: NUMBER(1) or CHAR(1) for flag? [message #422638 is a reply to message #422633] Fri, 18 September 2009 00:49 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Answer in Asktom
Re: NUMBER(1) or CHAR(1) for flag? [message #422642 is a reply to message #422637] Fri, 18 September 2009 01:09 Go to previous messageGo to next message
amit_nanote
Messages: 56
Registered: July 2005
Location: Indore
Member

Yes I agree with your view, but there I mean to say that in select queries we can get the desired number type.

Thanks.
Amit
Re: NUMBER(1) or CHAR(1) for flag? [message #422649 is a reply to message #422547] Fri, 18 September 2009 01:50 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thanks all for their comment.

Michel sir,
Quote:
Use DUMP instead of VSIZE to answer your question.

DUMP is ok, but it gives CHAR representation of the number. I am asking for NUMBER type and is not supported by DUMP.
If any other explaintion, please tell me.

bonker sir,
Special thanks for that link.
Re: NUMBER(1) or CHAR(1) for flag? [message #422650 is a reply to message #422566] Fri, 18 September 2009 02:10 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
ThomasG
Did you mean "didn't" instead of "did"?

In fact I did not only consider it, I added bitmap index on the test system to try it.

The test then were somewhat like 10 Minutes run-time with a normal index, 8 minutes with the bitmap one, and 20 seconds with the function based one.


Thomas does that mean you were applying a function in your where clause to that column?? Or what sort of function based index you are talking ..as this performance increase makes me curious Wink
If you can show what function index you wrote in this case that can be really helpful

[Updated on: Fri, 18 September 2009 02:11]

Report message to a moderator

Re: NUMBER(1) or CHAR(1) for flag? [message #422653 is a reply to message #422633] Fri, 18 September 2009 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See Number, Internal storage section.

Regards
Michel
Re: NUMBER(1) or CHAR(1) for flag? [message #422654 is a reply to message #422649] Fri, 18 September 2009 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
DUMP is ok, but it gives CHAR representation of the number. I am asking for NUMBER type and is not supported by DUMP.
If any other explaintion, please tell me.

What does this mean? DUMP gives the value of each bytes of NUMBER representation, is this not what you asked?

Regards
Michel

[Updated on: Fri, 18 September 2009 02:40]

Report message to a moderator

Re: NUMBER(1) or CHAR(1) for flag? [message #422659 is a reply to message #422547] Fri, 18 September 2009 03:11 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thanks Michel sir.
Now I got it, after reading example on the link you provided.

But I have now new question from Tom Kyte's link and Oracle documentation.
In data dictionary, USER_TAB_COLUMNS contains one column, with name DATA_LENGTH which gives column length in bytes.
SQL>select column_name, data_length from user_tab_columns
  2  where table_name like 'TEMP';

COLUMN_NAME                    DATA_LENGTH
------------------------------ -----------
NUM_1                                   22
NUM_2                                   22
NUM_10                                  22
NUM_38                                  22
CHR_1                                    1
CHR_10                                  10
VCHR_1                                   1
VCHR_10                                 10
DAT                                      7
TSTMP_6                                 11
TSTMP_9                                 11

11 rows selected.


Now, if column NUM_1 has NUMBER(1) as its data type,
NUM_2 has NUMBER(2), and so on;
then also why is it showing length of all NUMBER type columns 22 bytes?
Or does it blindly allocate 22 bytes for all NUMBER type column no matter with precision?

Please answer.

regards,
Delna
Re: NUMBER(1) or CHAR(1) for flag? [message #422663 is a reply to message #422659] Fri, 18 September 2009 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a standard value for NUMBER. It is the maximum size for any number.
You have to ask Oracle why they do not try to put a more accurate value.

Regards
Michel
Re: NUMBER(1) or CHAR(1) for flag? [message #422671 is a reply to message #422547] Fri, 18 September 2009 05:00 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thanks sir.
But it is not necessary to be that much harsh.
It you are tired or get bored, then go and take rest.
Not me, and even you can ask Oracle to do so.

I raised that question and I asked here.
That's all.

regards,
Delna
Re: NUMBER(1) or CHAR(1) for flag? [message #422672 is a reply to message #422547] Fri, 18 September 2009 05:08 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
By the way, one more question from bonker sir's post.

XE@SQL> delete from test commit;

9 rows deleted.

I haven't seen that syntax ever before.

Although it works without any error, following behaviour is strange.
SQL>commit;

Commit complete.

SQL>select count(1) from tb;

  COUNT(1)
----------
        15

1 row selected.

SQL>delete from tb commit;

15 rows deleted.

SQL>select count(1) from tb;

  COUNT(1)
----------
         0

1 row selected.

SQL>rollback;

Rollback complete.

SQL>select count(1) from tb;

  COUNT(1)
----------
        15

1 row selected.

So how this COMMIT with DELETE works?
can anyone explain?

regards,
Delna
Re: NUMBER(1) or CHAR(1) for flag? [message #422673 is a reply to message #422672] Fri, 18 September 2009 05:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The fact thatyou can do a rollback is usually a big indicator that no actual commit has taken place.

In this case the word 'commit' is being used as the table alias for the table tb.
Re: NUMBER(1) or CHAR(1) for flag? [message #422674 is a reply to message #422547] Fri, 18 September 2009 05:23 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Ok........
That is table alias.

So following is the consequence of this thread

1. Each NUMBER value requires from 1 to 22 bytes.

2. The Oracle NUMBER type is similar to a varying length character string.

3. Every 2 digits added another 1 byte of storage, starting from 2 digit number.

4. Negative values require one more byte to store it than the positive value having same magnitude.

5. VSIZE gives actual number of bytes allocated by oracle to store that number in column of the table.

reagrds,
Delna
Re: NUMBER(1) or CHAR(1) for flag? [message #422675 is a reply to message #422672] Fri, 18 September 2009 05:40 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
That was my sloppy work of cut/copy paste from sqlplus session.
Re: NUMBER(1) or CHAR(1) for flag? [message #422676 is a reply to message #422547] Fri, 18 September 2009 05:43 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
No problem at all bonker sir.
I learned so much from that post of yours.
Thanks a lot.
Thanks all.

regards,
Delna
Re: NUMBER(1) or CHAR(1) for flag? [message #422685 is a reply to message #422671] Fri, 18 September 2009 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delna.sexy wrote on Fri, 18 September 2009 12:00
Thanks sir.
But it is not necessary to be that much harsh.
It you are tired or get bored, then go and take rest.
Not me, and even you can ask Oracle to do so.

I raised that question and I asked here.
That's all.

regards,
Delna

To whom are you answering? Use the Reply button on the post you want to answer not the one on your original post.
If it is me, I'm not harshed at all, I'm serious, you can ask Oracle to do so, I often asked Oracle to change or add some behaviour and from time to time it happens it is implemented.
If no one asks for an evolution, there will likely be no more evolution.

Regards
Michel

Re: NUMBER(1) or CHAR(1) for flag? [message #422693 is a reply to message #422674] Fri, 18 September 2009 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delna.sexy wrote on Fri, 18 September 2009 12:23
Ok........
That is table alias.

So following is the consequence of this thread

1. Each NUMBER value requires from 1 to 22 bytes.

2. The Oracle NUMBER type is similar to a varying length character string.

3. Every 2 digits added another 1 byte of storage, starting from 2 digit number.

4. Negative values require one more byte to store it than the positive value having same magnitude.

5. VSIZE gives actual number of bytes allocated by oracle to store that number in column of the table.

reagrds,
Delna


1. 2 to 22 bytes.
2. More or less, you can see the characters in dump of varchar, you cannot see the number in the dump of number
3. Yes.
4. Yes because Oracle adds a tag at the end of negative number, whatever is the value
5. Not exactly the size of the data part that is without the length byte.

Regards
Michel
Re: NUMBER(1) or CHAR(1) for flag? [message #422700 is a reply to message #422693] Fri, 18 September 2009 07:34 Go to previous messageGo to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
1.
NARESH@gold12>select col, vsize(col) from tb
  2  where col = 0 or col = 1;

       COL VSIZE(COL)
---------- ----------
         1          2
         0          1

2 rows selected.


2. I meant, Oracle allocates only required number of bytes.

3. Ok

4. Ok

5. Didn't get, what you want to say.

regards,
Delna
Previous Topic: select ... for update lock type is RX not RS?
Next Topic: output lines for a select query
Goto Forum:
  


Current Time: Sun Dec 04 22:45:37 CST 2016

Total time taken to generate the page: 0.11261 seconds