| NUMBER(1) or CHAR(1) for flag? [message #422547] |
Thu, 17 September 2009 06:37  |
 |
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 #422550 is a reply to message #422549] |
Thu, 17 September 2009 06:50  |
ThomasG
Messages: 3212
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 #422556 is a reply to message #422547] |
Thu, 17 September 2009 07:09 |
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 |
ThomasG
Messages: 3212
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 #422568 is a reply to message #422547] |
Thu, 17 September 2009 07:58 |
joy_division
Messages: 4963
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 #422623 is a reply to message #422568] |
Thu, 17 September 2009 23:36 |
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 #422636 is a reply to message #422547] |
Fri, 18 September 2009 00:42 |
|
|
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 #422650 is a reply to message #422566] |
Fri, 18 September 2009 02:10 |
ayush_anand
Messages: 417
Registered: November 2008
|
Senior Member |
|
|
ThomasGDid 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 
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 #422654 is a reply to message #422649] |
Fri, 18 September 2009 02:39 |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
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 #422673 is a reply to message #422672] |
Fri, 18 September 2009 05:13 |
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 #422685 is a reply to message #422671] |
Fri, 18 September 2009 06:23 |
|
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
delna.sexy wrote on Fri, 18 September 2009 12:00Thanks 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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
