Re: PK field - number of char
Brian, Your figures for the number of bytest are incorrect. A 38 digit number (max Oracle can handle) takes approximately 20 bytes, a 38 character varchar2 takes 39 bytes. To answer the original question: The only difference would be in converting the external format of the data (e.g. a double or int in the number case) to the internal format and in the space used; as soon as it is under the hood of Oracle both numbers and varchar2s are simply a variable length string of bytes, which needs to be saved in the index and compared. If your data really is numerical, you save space (and hence potentially some time inside the kernel) if it is stored as number but you pay with a potentailly somewhat slower conversion between internal and external format. In practical terms, it probably doesn't matter enough to care, so your decision should be based on what you really have, i.e. numerical data should be stored in number, text data in varchar2. Thanks, Bjørn. On Thursday 18 April 2002 00:53, Brian Haas wrote: 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) 10 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 number datatype: Elapsed: 00:00:00.51 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4) 10 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. -- Bjørn Engsig, Miracle A/S http://MiracleAS.dk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Bj=F8rn=20Engsig?= INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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) 10 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 number datatype: Elapsed: 00:00:00.51 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4) 10 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
Re: PK field - number of char
Tom, If you are generating keys as you should be, they will be numeric. Jared On Wednesday 17 April 2002 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. Just curious if anyone has a reference someplace pointing this out. Thanks Tom Mercadante Oracle Certified (Stupified today) Professional -Original Message- Sent: Wednesday, April 17, 2002 2:21 PM To: Multiple recipients of list ORACLE-L If you go with the first option, you will likely be able to get out of joining your STATE table to the referencing tables in a bunch of cases (since the 2-letter abbreviation is interpretable on its own). But if you'll wind up having to do the join anyway (e.g., to display the STATE_DESC) then those joins will likely be faster on a numeric... HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, April 17, 2002 10:19 AM To: Multiple recipients of list ORACLE-L To simplify my question, if I am creating a STATE table to hold all the states of the US, should I create it like this... Name Null?Type - STATE_CODENOT NULL CHAR(2) -- PK STATE_DESCNOT NULL VARCHAR2(50) or like this... Name Null?Type - STATE_ID NOT NULL NUMBER -- PK STATE_CODENOT NULL CHAR(2) STATE_DESCNOT NULL VARCHAR2(50) I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID, when doing a PK lookup, dealing with FKs, etc. Many TIA!!! Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: PK field - number of char
Tom, If you're keeping up on the other threads, you will see that we are in fact in complete agreement. As for the char vs. number , which is faster in an index debate that arises from time to time, I personally think it's a silly waste of time. No disrespect intended, maybe it's the first time you've seriously considered it. Folks that worry about the the nanoseconds they may be wasting by using the 'wrong' one are being penny wise and pound foolish. Much greater gains are to be made elsewhere in every application. Just my shillings worth, Jared On Thursday 18 April 2002 07:37, Mercadante, Thomas F wrote: Jared, I disagree. In some cases, I would support and use natural values for Primary keys. In the case of State Codes, County Codes, Yes/No codes and other that are too obvious, I really do not see the value of using an sequence number for the PK. I have a YES/NO table in my database. The Web developers use a drop-down field to allow the users to select the value they want (YES or NO). If the developers were required to to support the sequence number, it makes the coding a tiny bit more complicated (obviously, you and I can think of dozens of ways to make it insignificant). I guess I'm thinking that this is one of those personal preference things. My original question was looking for a good reason why I should NOT use chars in an index (thus forcing me to always use a sequence as the PK). So far, I see no reason not to . See ya. Tom Mercadante Oracle Certified Professional -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 18, 2002 10:27 AM To: [EMAIL PROTECTED]; Mercadante, Thomas F Subject: Re: PK field - number of char Tom, If you are generating keys as you should be, they will be numeric. Jared On Wednesday 17 April 2002 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. Just curious if anyone has a reference someplace pointing this out. Thanks Tom Mercadante Oracle Certified (Stupified today) Professional -Original Message- Sent: Wednesday, April 17, 2002 2:21 PM To: Multiple recipients of list ORACLE-L If you go with the first option, you will likely be able to get out of joining your STATE table to the referencing tables in a bunch of cases (since the 2-letter abbreviation is interpretable on its own). But if you'll wind up having to do the join anyway (e.g., to display the STATE_DESC) then those joins will likely be faster on a numeric... HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, April 17, 2002 10:19 AM To: Multiple recipients of list ORACLE-L To simplify my question, if I am creating a STATE table to hold all the states of the US, should I create it like this... Name Null?Type - STATE_CODENOT NULL CHAR(2) -- PK STATE_DESCNOT NULL VARCHAR2(50) or like this... Name Null?Type - STATE_ID NOT NULL NUMBER -- PK STATE_CODENOT NULL CHAR(2) STATE_DESCNOT NULL VARCHAR2(50) I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID, when doing a PK lookup, dealing with FKs, etc. Many TIA!!! Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: PK field - number of char
Jared, I disagree. In some cases, I would support and use natural values for Primary keys. In the case of State Codes, County Codes, Yes/No codes and other that are too obvious, I really do not see the value of using an sequence number for the PK. I have a YES/NO table in my database. The Web developers use a drop-down field to allow the users to select the value they want (YES or NO). If the developers were required to to support the sequence number, it makes the coding a tiny bit more complicated (obviously, you and I can think of dozens of ways to make it insignificant). I guess I'm thinking that this is one of those personal preference things. My original question was looking for a good reason why I should NOT use chars in an index (thus forcing me to always use a sequence as the PK). So far, I see no reason not to . See ya. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, April 18, 2002 10:27 AM To: [EMAIL PROTECTED]; Mercadante, Thomas F Tom, If you are generating keys as you should be, they will be numeric. Jared On Wednesday 17 April 2002 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. Just curious if anyone has a reference someplace pointing this out. Thanks Tom Mercadante Oracle Certified (Stupified today) Professional -Original Message- Sent: Wednesday, April 17, 2002 2:21 PM To: Multiple recipients of list ORACLE-L If you go with the first option, you will likely be able to get out of joining your STATE table to the referencing tables in a bunch of cases (since the 2-letter abbreviation is interpretable on its own). But if you'll wind up having to do the join anyway (e.g., to display the STATE_DESC) then those joins will likely be faster on a numeric... HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, April 17, 2002 10:19 AM To: Multiple recipients of list ORACLE-L To simplify my question, if I am creating a STATE table to hold all the states of the US, should I create it like this... Name Null?Type - STATE_CODENOT NULL CHAR(2) -- PK STATE_DESCNOT NULL VARCHAR2(50) or like this... Name Null?Type - STATE_ID NOT NULL NUMBER -- PK STATE_CODENOT NULL CHAR(2) STATE_DESCNOT NULL VARCHAR2(50) I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID, when doing a PK lookup, dealing with FKs, etc. Many TIA!!! Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: PK field - number of char
Jared, you said: As for the char vs. number , which is faster in an index debate that arises from time to time, I personally think it's a silly waste of time. I totally agree. As I said, I saw some recent positings stating that numbers were faster than chars and asked if anyone had any metrics stating such (in case I missed something someplace). My intuition was that there was no difference. However, things change and I was just checking to keep up. thanks for replying. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, April 18, 2002 10:45 AM To: Mercadante, Thomas F; [EMAIL PROTECTED] Tom, If you're keeping up on the other threads, you will see that we are in fact in complete agreement. As for the char vs. number , which is faster in an index debate that arises from time to time, I personally think it's a silly waste of time. No disrespect intended, maybe it's the first time you've seriously considered it. Folks that worry about the the nanoseconds they may be wasting by using the 'wrong' one are being penny wise and pound foolish. Much greater gains are to be made elsewhere in every application. Just my shillings worth, Jared On Thursday 18 April 2002 07:37, Mercadante, Thomas F wrote: Jared, I disagree. In some cases, I would support and use natural values for Primary keys. In the case of State Codes, County Codes, Yes/No codes and other that are too obvious, I really do not see the value of using an sequence number for the PK. I have a YES/NO table in my database. The Web developers use a drop-down field to allow the users to select the value they want (YES or NO). If the developers were required to to support the sequence number, it makes the coding a tiny bit more complicated (obviously, you and I can think of dozens of ways to make it insignificant). I guess I'm thinking that this is one of those personal preference things. My original question was looking for a good reason why I should NOT use chars in an index (thus forcing me to always use a sequence as the PK). So far, I see no reason not to . See ya. Tom Mercadante Oracle Certified Professional -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 18, 2002 10:27 AM To: [EMAIL PROTECTED]; Mercadante, Thomas F Subject: Re: PK field - number of char Tom, If you are generating keys as you should be, they will be numeric. Jared On Wednesday 17 April 2002 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. Just curious if anyone has a reference someplace pointing this out. Thanks Tom Mercadante Oracle Certified (Stupified today) Professional -Original Message- Sent: Wednesday, April 17, 2002 2:21 PM To: Multiple recipients of list ORACLE-L If you go with the first option, you will likely be able to get out of joining your STATE table to the referencing tables in a bunch of cases (since the 2-letter abbreviation is interpretable on its own). But if you'll wind up having to do the join anyway (e.g., to display the STATE_DESC) then those joins will likely be faster on a numeric... HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, April 17, 2002 10:19 AM To: Multiple recipients of list ORACLE-L To simplify my question, if I am creating a STATE table to hold all the states of the US, should I create it like this... Name Null?Type - STATE_CODENOT NULL CHAR(2) -- PK STATE_DESCNOT NULL VARCHAR2(50) or like this... Name Null?Type - STATE_ID NOT NULL NUMBER -- PK STATE_CODENOT NULL CHAR(2) STATE_DESCNOT NULL VARCHAR2(50) I'm trying to figure out which is more efficient, STATE_CODE or STATE_ID, when doing a PK lookup, dealing with FKs, etc. Many TIA!!! Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858
Re: PK field - number of char
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) 10 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 number datatype: Elapsed: 00:00:00.51 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4) 10 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
