Oracle boolean data type questions

pts.
Tags:
Database
IBM DB2
Oracle
Greetings, Since Oracle Table structures do not support a boolean data type, there is a choice (Make it a varchar2(1) or Number(1)), with contents of 0,1 or Y,N The questions I have are: 1) From a comparison perspective, which provides better performance? Comparison of a number (1,0) or string (Y/N) 2) Which method takes up less storage? 3) Results will be mapped into a J2EE data structure supporting a boolean datatype. Which method (from a performance perspective)is better. Number to boolean or DECODE(bool, 'Y', 1', 'N', 0) Many thanks in advance for your thoughts! Randy

Answer Wiki

Thanks. We'll let you know when a new response is added.

> 1) From a comparison perspective, which provides better performance? Comparison
> of a number (1,0) or string (Y/N)

It won’t make a difference.

> 2) Which method takes up less storage?

They both take 1 byte.

> 3) Results will be mapped into a J2EE data structure supporting a boolean datatype.
> Which method (from a performance perspective)is better. Number to boolean or
> DECODE(bool, ‘Y’, 1′, ‘N’, 0)

It won’t make a difference.

Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Setirobert
    I have to agree with Sheldon except on #3 you just answered your question yourself! DECODE(bool, 'Y', 1', 'N', 0) if you want to decode to 0,1 then store 0,1 ;) Cheers
    30 pointsBadges:
    report
  • MrOracle
    The differences are somewhat negligible unless you are talking about LOTS and LOTS of data, but it is easy to demonstrate rather than to just throw out answers. If you look at the test case below, comparing the 1 digit number against the single character, you'll see that the internal length of a 0 is one, but the internal length of a 1 is TWO. Whereas the single character only occupies 1 byte regardless. Another choice that wasn't mentioned, is if you are storing more than ONE boolean value, then you could combine them into a single INTEGER column, and unpack them at read time using the BITAND() function. Oracle does it a lot in the data dictionary. The disadvantage is that single BITS within a number cannot be indexed. 8^) Look for a test run at the bottom after the first case. I hope this is clear, or at least gives you the tools to answer your own question. Happy computing! ((MrO)) ================================== Case 1, 0/1 vs 'Y','N': SQL> create table t(a number(1,0), b char(1)); Table created. Elapsed: 00:00:00.23 SQL> insert into t values (1,'Y'); 1 row created. Elapsed: 00:00:00.07 SQL> insert into t values (0,'N'); 1 row created. Elapsed: 00:00:00.00 SQL> commit; Commit complete. SQL> select a, dump(a), b, dump(b) from t; A DUMP(A) B DUMP(B) ---------- ------------------ - ----------------- 1 Typ=2 Len=2: 193,2 Y Typ=96 Len=1: 89 0 Typ=2 Len=1: 128 N Typ=96 Len=1: 78 2 rows selected. ========================================== Case 2, using BITAND(): SQL> insert into t (a) values (5); 1 row created. Elapsed: 00:00:00.00 SQL> insert into t (a) values (6); 1 row created. Elapsed: 00:00:00.01 SQL> commit; Commit complete. SQL> select a, bitand(a,1), dump(a) from t; A BITAND(A,1) DUMP(A) ---------- ----------- ------------------------------------ 1 1 Typ=2 Len=2: 193,2 0 0 Typ=2 Len=1: 128 5 1 Typ=2 Len=2: 193,6 6 0 Typ=2 Len=2: 193,7 4 rows selected. Elapsed: 00:00:00.00 SQL>
    0 pointsBadges:
    report
  • me570319
    From my perspective the storage is not the issue, if it's not a very large table, but rather the principle how to store a boolean in oracle. My feeling is that 0,1 is the better. Then with the bitand performance probably there are more significant columns with both text and numbers to take in account. Hope it helps
    0 pointsBadges:
    report
  • SrDataArch
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/oracle-boolean-data-type-questions/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following