Re: Join performance on different data types
Date: Wed, 14 Oct 2009 06:23:46 -0700 (PDT)
Message-ID: <5e200970-e183-4002-8d95-0feefebb9e9d_at_a31g2000yqn.googlegroups.com>
On Oct 13, 12:10 pm, Pratap <pratap.deshm..._at_gmail.com> wrote:
> Hi,
>
> Database is Oracle 10.2.0.4.0
> I have two identical test tables, each with a varchar2 and number
> column (vsize of both is 3). When I join the table on varchar2, the
> performance is very slightly lower than the join on the number column.
> Theorotically, in Oracle is there any reason why join on number will
> be faster than a join on varchar2 column?
>
> Thanks.
I have seen test results posted on Oracle support by an Oracle support person that showed there is very little difference in the performance of a join on a single column indexed key where the only difference in the test was the data type of the key column, that is, varchar2 verse number.
The number data type had a very small advantage but you needed 100,000 rows in the test for it to show.
You should pick your table keys based on the data and the data relationships. The database data type should be whatever the data happens to be.
If the value will be used in math operations or represents a numeric value like cost, quantity, and so on then it should be stored as a number. If you store such a value as character then you force data type conversion before match can be performed. That is extra work. On the other hand if no math is ever performed on the column and the value is not used as a number then it should be stored as character.
Once you pick a data type for an attribute then you should use that same data type for that attribute in all tables in which the column is defined. Otherwise you may end up with an implicit conversion which often leads to posts where the poster asks why one version of a query doing a join on two columns where one was defined as character and the other numeric runs while the other version of the same query errors off with a numeric or value error.
HTH -- Mark D Powell -- Received on Wed Oct 14 2009 - 08:23:46 CDT