Q
Problem solve Get help with specific problems with your technologies, process and projects.

Better performance with varchar2(1) or number(1)?

Since Oracle table structures do not support a boolean datatype, I am faced with a choice (make it a varchar2(1) or number(1)), with contents of Y,N or 1,0. Which provides better performance?

Since Oracle table structures do not support a boolean datatype, I am faced with a choice (make it a varchar2(1) or number(1)), with contents of Y,N or 1,0. The questions I have are:
  1. From a comparison perspective, which provides better performance? Comparison of a number or string?
  2. Which solution takes up less storage? Number(1) or varchar2(1)?
  3. The 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(boolstring, 'Y', 1, 'N', 0)?
Many thanks in advance for your thoughts!

For almost every circumstance, comparing numbers or strings will make very little difference to the performance of your application. You will most likely find that using one datatype or the other will not impact your application in any noticable way. There are many other aspects of application performance (such as disk contention or poorly written SQL) that can have a very noticable impact on your application's performance.

The VARCHAR2(1) datatype specification will take up to one byte of storage space. The NUMBER(1) datatype will take at least two bytes since numbers have to store both the exponent and the mantissa of the number's representation. That being said, it is my opinion that worrying about storage requirements at this level takes more effort than it is worth. You will not realize much from your work.

I am not 100% sure how J2EE will be impacted by NUMBER to BOOLEAN or using DECODE as you have suggested. But it should be very simple for you to create a test case. In your J2EE application, query the data from one table that stores the value as a NUMBER and then do the same operation with VARCHAR2(1). Time the operations using Java's timing routines. Which works faster? Do not forget to run the test case multiple times so as to obtain an average and remove a bad run's anomolies. I will bet you will find that there is very little difference in the overall runtimes.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close