Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Primary key type selection

Re: Primary key type selection

From: Jared Still <jkstill_at_bcbso.com>
Date: Wed, 17 May 2000 09:02:20 -0700 (PDT)
Message-Id: <10500.105860@fatcity.com> 





Here's a quick and dirty test on this.  The actual number
of rows in the test table was 960,000.




First is the create script, then the test script, then the
results.  The differences in time are not worth worrying about.



Jared






drop table pk_test;


drop sequence pk_test_seq;



create table pk_test (

	uid1 number(9,0) not null,
	uid2 varchar2(9) not null,
	data date  default sysdate not null




)


storage ( initial 1m next 1m pctincrease 0 )
/



create sequence pk_test_seq start with 1000000;



create or replace trigger pk_test_bit


before insert on pk_test


for each row


declare


        newPk integer;	


begin

	select pk_test_seq.nextval into newPK
	from dual;

	:new.uid1 := newPk;
	:new.uid2 := to_char(newPk);





end;


/




declare

	v_commit_point integer := 10000;
	v_commit integer := 0;
begin
	for x in 1 .. 1000000
	loop
		insert into pk_test(data) values( sysdate );
		v_commit := v_commit + 1;
		if v_commit >= v_commit_point then
			commit;
			v_commit := 0;
		end if;
	end loop;
	commit;




end;


/





alter table pk_test add constraint uid1 unique ( uid1 )
using index


tablespace indx


storage ( initial 1m next 1m pctincrease 0 )
/



alter table pk_test add constraint uid2 unique ( uid2 )
using index


tablespace indx


storage ( initial 1m next 1m pctincrease 0 )
/








set autotrace on


set timing on



select /*+ index(pk_test,uid1) */ count(uid1) from pk_test;
select /*+ index(pk_test,uid2) */ count(uid2) from pk_test;



set timing off


set autotrace off







Segments for jkstill


COUNT(UID1)





     960000



1 row selected.



Elapsed: 00:00:02.40



Execution Plan




          0


SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=296401 Bytes=3853213)



          1                  0




  SORT (AGGREGATE)




          2                  1




    INDEX (FULL SCAN) OF 'UID1' (UNIQUE) (Cost=26 Card=296401 Bytes=3853213)








Statistics



          8  recursive calls
          0  db block gets
       4388  consistent gets
       4382  physical reads
          0  redo size
        248  bytes sent via SQL*Net to client
        307  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed






Segments for jkstill


COUNT(UID2)





     960000



1 row selected.



Elapsed: 00:00:02.51



Execution Plan




          0


SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=296401 Bytes=1778406)



          1                  0




  SORT (AGGREGATE)




          2                  1




    INDEX (FULL SCAN) OF 'UID2' (UNIQUE) (Cost=26 Card=296401 Bytes=1778406)








Statistics



          8  recursive calls
          0  db block gets
       4936  consistent gets
       4926  physical reads
          0  redo size
        248  bytes sent via SQL*Net to client
        307  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



---------------------------------------












On Tue, 16 May 2000, Joseph S. Testa wrote:



> Winnie, u've tested that theory that a number scan as a PK will be faster than

> a character scan(varchar2)?, i'd like to see the results :)

> 

> joe

> 

> 

> Winnie Liu wrote:

> 

> > Colin,

> >

> > That's right. The number field occupied varies as varchar2. But still, with

> > the same width, it is a lot faster to scan and match a primary key of

> > numbers than a primary key with characters.

> >

> > Winnie :)

> >

> > -----Original Message-----

> > Colin.Shaw_at_phs.com

> > Sent: Monday, May 15, 2000 9:28 PM

> > To: Multiple recipients of list ORACLE-L

> >

> > >A number field takes less no. of bytes than a varchar2 field.

> >

> > Not necessarily.  A number field occupies a variable number of bytes,

> > depending on its magnitude and number of digits.

> >

> > Colin.

> >

> > -----Original Message-----

> > Sent: Sunday, May 14, 2000 9:14 PM

> > To: Multiple recipients of list ORACLE-L

> >

> > Hi,

> >

> >   The first advantage is the space saving. A number field takes less no.

> >   of bytes than a varchar2 field. This decrease in storage may

> >   positively impact performance...

> >

> > Regards

> > Rajagopal Venkataramany

> >

> > ----Original Message Follows----

> > Reply-To: ORACLE-L_at_fatcity.com

> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

> > Date: Sun, 14 May 2000 10:54:12 -0800

> >

> > Is there any advantages and disadvantages (performance? , ...) between

> > NUMBER and VARCHAR2 for primary key column?

> >

> > Alex Hillman

> >

> > ________________________________________________________________________

> > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

> >

> > --

> > Author: Rajagopal Venkataramany

> >   INET: rajagopalvr_at_hotmail.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).

> >

> > --

> > Author:

> >   INET: Colin.Shaw_at_phs.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).

> >

> > --

> > Author: Winnie Liu

> >   INET: hkitty_at_earthlink.net

> >

> > 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).

> 

> -- 

> Author: Joseph S. Testa

>   INET: teci_at_oracle-dba.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

Received on Wed May 17 2000 - 11:02:20 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US