variable vs fixed width fields for performance

[satishpc]

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

[opchandani]

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.

[satishpc]

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