variable vs fixed width fields for performance
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: variable vs fixed width fields for performance

  1. #1
    Join Date
    Jul 2000
    Location
    India
    Posts
    60
    hi,

    can anyone tell me if a fixed width field will give me better performance than a variable field in case on querying and joins ? what are the pros & cons of having numbers/char/varchar as primary/joining keys ? i would be grateful if u can direct me to any previous thread where this discussion has taken place.

    thanks
    satish

  2. #2
    Join Date
    Feb 2002
    Location
    http://db-migration.co.in
    Posts
    50
    Hi,
    For P Keys, if u can give a numeric, it will be better if he No. of rows u expect for the table is not very large. Since,eg field Number(5) is shorter then varchar2(5), performance wise former will be better. Also, u can autogenerate using Oracle Sequence, the Primary key and the primary key generated will be unique even if it is a distributed database systems with replications.
    However, if the cardinality of the table is going to be very high and the Pkey to be generated can be of Var size, so better to have a varchar field for the pkey.
    Similarly, if u can join diff tables, or if it is a self join, then always if the join field is a numeric one, performance will be faster.
    In all the cases, if the data is alphanumeric and of fixed length, then better to have a char field rather then varchar!
    Hope this wud answer ur query.

  3. #3
    Join Date
    Jul 2000
    Location
    India
    Posts
    60
    hi,

    my schema is typically a snowflake schema where i have on fact table and multiple hierarchy of dimension tables. So u can be sure that there can be lots of joins. my fact table has around 2 million rows and dimension can have anywhere between 2 to 5000 records.

    i can decide what i want as the pk. there is an article which says that it is better to have numeric pk and fields on which joins table place as fixed width and have these at the begining of the table because otherwise oracle has to scan through each field to find out its length and reach the necessary column. It is said that it speeds up a lot of things.

    here is the url
    http://www.jlcomp.demon.co.uk/faq/index_speed.html

    any suggestions/comments ?

    thanks
    satish

Posting Permissions

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


Click Here to Expand Forum to Full Width