- From a comparison perspective, which provides better performance? Comparison of a number or string?
- Which solution takes up less storage? Number(1) or varchar2(1)?
- 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)?
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.