0

Can anyone tell me will there be any impact on changing the datatype of a column from char to varchar2.

Because the issue i am facing is when i fire a select query i.e

select * from table_name where column_name in ('X','Y','Z');

The above query is returning only few rows. And recently the column_name data type was changed from char to varchar. The rows returned are the rows after the data type was changed.

herry
  • 1,708
  • 3
  • 17
  • 30
akshatha shetty
  • 39
  • 2
  • 7
  • that depends on your database system, the amount of data, the type of queries and the type of index you are using. – phoet Oct 15 '13 at 11:17

1 Answers1

0

A varchar2 datatype, when stored in a database , uses only the space allocated to it. If you have a varchar2(100) and put 50 bytes in the table, it will use 52 bytes (leading length byte).

A char datatype, when stored in a database table, always uses the maximum length and is blank padded. If you have char(100) and put 50 bytes into it, it will consume 102 bytes.

So in your case probably its only giving the rows from the space allocated to varchar and hence only few rows are returned i believe.

Refered from : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1542606219593

Magic
  • 505
  • 2
  • 6
  • 19
  • Thank you so much, found the same reason at http://www.oratable.com/char-varchar-difference/ as well. Could you please tell me how to select all the rows tried doing rpad(value,numberofbytes) but it's returning only the rows which were inserted when the datatype of the column was char. – akshatha shetty Oct 15 '13 at 11:44