| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: PK field - number or char
Brian & Bjorn,
Thanks for the replies.
Sql*Net traffic, in my mind, is insignificant. If we are trying to determine query speed, most of the tests should be done with more than one table in the query, with a join against the table in question. It is not very often that we will be querying one table (really talking about "code" tables - or static lookup tables - state codes, country codes etc.) and if we are, the speed doesn't matter in my mind. It is the constant join against the table that is more common and would have the larger impact if the index was significantly slower for a char field rather than a number field that I was thinking about. And these types of joins (passing the sql query across the network) would have the same network cost - the sql string is not any larger.
I just had the feeling that if this ws an issue, Oracle would have warned us a *long* time ago that char index values are slower than number index values (or someone on this list would have figued it out long ago).
thanks
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Wednesday, April 17, 2002 6:53 PM
To: Multiple recipients of list ORACLE-L
Tom,
Well you got me sort of. I ran a few quick tests on a table with 500K rows. The return times were almost always identical. The main difference between a Pk with a number and a char/varchar is storage. a 40 digit number takes 4bytes of space. A 40 character string takes 10 bytes.
This translated into the character datatype moving 30 more bytes per
query over sql*net than the number datatype. On a heavily used
application hitting a backend Oracle DB via sql*net, those extra bytes
could make a difference in response time. but I guess that is more of a
network bottleneck than a database one.
here are my results:
char(40) Pk:
Elapsed: 00:00:00.52
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=10) 1 0 INDEX (UNIQUE SCAN) OF 'PKTEST_CHAR_PK' (UNIQUE) (Cost=2 C
ard=1 Bytes=10)
Statistics
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
239 bytes sent via SQL*Net to client
253 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4) 1 0 INDEX (UNIQUE SCAN) OF 'PKTEST_PK' (UNIQUE) (Cost=2 Card=1
Bytes=4)
Statistics
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
218 bytes sent via SQL*Net to client
246 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-Brian
On Wed, 2002-04-17 at 11:52, Mercadante, Thomas F wrote:
> All,
>
> Does anyone have any specific metrics demonstrating that a PK that is
based
> on a number field is faster than a PK based on a character field?
>
> I've seen it mentioned a couple of times today under the "Design Question"
> topic.
>
> It doesn't make any sense to me that one or the other would be faster.
> After all, we are talking about comparison searches within the B-Tree
index
> structure. Why searching down the tree for a number is any faster than a
> char is lost on me.
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brian Haas INET: bhaas_at_musiciansfriend.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.us Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Apr 18 2002 - 08:03:22 CDT
 |
 |