Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2009
    Posts
    68

    Unanswered: Primary key format

    Hi everyone...

    I have to create a primary key on a large table (some millions rows).
    I can define the column as
    - a Number(10) and fill it with the raw result of a sequence,
    or
    - a VARCHAR2(15) and fill it with a prefix and a number, say 'abc-12345687'

    Now my question: what about the index associated with this column ? Is there a difference beetween the two solution (size, efficiency, performance...) ?
    Thanks in advance,

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there a difference beetween the two solution (size, efficiency, performance...) ?
    No, bits are bits.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    does a primary key already exist within the table or are you just creating a sequnce to fill in as an alternate?
    Dave

  4. #4
    Join Date
    Aug 2009
    Posts
    68
    No, it's not an alternate.
    Actually, I shall import data from another database (not SQL) and I'm thinking about improving the data model.
    Thanks,
    LS

  5. #5
    Join Date
    Jun 2009
    Posts
    11

    hi

    Quote Originally Posted by Pyrophorus
    Hi everyone...

    I have to create a primary key on a large table (some millions rows).
    I can define the column as
    - a Number(10) and fill it with the raw result of a sequence,
    or
    - a VARCHAR2(15) and fill it with a prefix and a number, say 'abc-12345687'

    Now my question: what about the index associated with this column ? Is there a difference beetween the two solution (size, efficiency, performance...) ?
    Thanks in advance,

    hi Pyrophorus,
    u asked about the data(raw sequence (or) 'abc-4349934830'),
    but when u specify col as varchar2(), it just occupy the space only to length of the data.
    e.g., ----aa varchar2(20)---
    if u give data as
    'aaa',
    'bbbb'

    it just occupy according to length of the data. i.e., aaa=3(||ly varchar(3), bbbb=4(||ly varchar(4) )
    so varchar2() itself increase performance.

    case 2:
    When u create primary key , the index created is unique index, not ordinary index, that performes the unique key action,
    -----primary key=notnull+unique------

    As u asked, the performance may be increased when we specify varchar2() rather than num,char. But when we consider about the restrictions between number and character , specify number() is better than varchar()/char().

    relation b/w primary key and index:-
    --> as u said primary key also one of the reason to increase the
    performance. because when we create a pk , unique index also created
    for that col. so that index is reason for speed performance.

    char() vs varchar()
    --> if we specify the char() , the length of datatype is occupied at beginning itself. eg., char(20) occupies the space upto the lengh 20.
    --> if we specify the varchar(), the length of datatype is occupied during insertion. As red spoted letters. That's the advantage of varchar() compare with char().

    u can check the query performance by elapsed time..
    > set timing on;
    >select * from tb_name;
    .
    .
    .
    .
    elapsed:00:00:00:04
    >
    Last edited by sujitha; 09-07-09 at 14:56.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I normally use numeric values for primary keys myself. That allows you to use a sequence to fill it and never have to worry about unique numbers.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Aug 2009
    Posts
    68
    Yes,
    I do the same... adding a prefix to the number produced by a sequence.
    My question was if this practice could be weird on large tables.
    Regards

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I don't know what you consider large, but I have done it on tables with hundreds of millions of rows. why would you make a prefix to the sequence. Index on the prefix as a separate column if you want, but why make it your primary key?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    ...my guess, because Access does it.

    It adds no value (information) and more than doubles the size required to store that value.

    Code:
    SELECT DUMP(12345687) AS num, DUMP('abc-12345687') AS chr FROM DUAL
    Shows that the numeric value will use 5 bytes to store while the char value will require 12 bytes. And seeing as how this is the primary key and will occur in possibly many indexes, it makes sense to use the value that requires less space.

  10. #10
    Join Date
    Aug 2009
    Posts
    68
    Quote Originally Posted by artacus72
    ...my guess, because Access does it.
    Bad guess...
    Actually, this makes no sense considering the database alone. It's interesting for object oriented applications.

    Quote Originally Posted by artacus72
    It adds no value (information) and more than doubles the size required to store that value.
    Thanks, this is something to consider.

  11. #11
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Bad guess...
    Thank goodness

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •