 |
|
| Computers Forum Index » Computer - Databases - Oracle (Server) » Join performance on different data types... |
|
Page 1 of 1 |
|
| Author |
Message |
| Pratap... |
 Posted: Tue Oct 13, 2009 4:10 pm |
|
|
|
Guest
|
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. |
|
|
| Back to top |
|
|
|
| John Hurley... |
| Posted: Tue Oct 13, 2009 6:13 pm |
|
|
|
Guest
|
On Oct 13, 12:10 pm, Pratap <pratap.deshm... at (no spam) gmail.com> wrote:
snip
Quote: 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.
Why don't you post you complete test case so we can see exactly what
you are doing.
Wild shot guess in the dark is implicit conversion is getting invoked. |
|
|
| Back to top |
|
|
|
| Mark D Powell... |
| Posted: Wed Oct 14, 2009 1:23 pm |
|
|
|
Guest
|
On Oct 13, 12:10 pm, Pratap <pratap.deshm... at (no spam) gmail.com> wrote:
Quote: 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 -- |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Mon Dec 14, 2009 11:33 pm
|
|