Type of field for index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Type of field for index

  1. #1
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684

    Smile

    Is anybody knows what type of field preferable to use as index and why?
    Thanks :-)

  2. #2
    Join Date
    Apr 2001
    Posts
    112
    I think you are asking which column to index in a table. This mainly depends on the types of queries you issue against the tables. It is beneficial to index the columns used mostly in the Where clauses. Also you need to explore if there are several columns used in the where clause, so you need to figure out if you need to index more than one column.
    Primary Key columns are automatically indexed. Its a good idea to index columns used in Joins & foreign keys. Also try to figure out if your queries are running slow & explore the possibility if indexing might help.
    Also before deciding to index the column or not try assessing the kind of data & the frequency of data i.e the cardinality & selectivity of an index.

    Tansdot

  3. #3
    Join Date
    Nov 2000
    Posts
    344
    I am not sure how the question was meant, but here is the answer to how I read it....

    NUMBER field types will produce the fastest indexes, so try to make your indexed fields NUMBER types if you can.

    I agree with that Tansdot said.

  4. #4
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684

    Cool

    Thank you very much, I just had questionable problem with another DBA he is going to use varchar2(25) as primary key in OLTP system and as well in DSS system just for more readable logical scheme.
    Best wishes!
    Dmitri

    Originally posted by jdorlon
    I am not sure how the question was meant, but here is the answer to how I read it....

    NUMBER field types will produce the fastest indexes, so try to make your indexed fields NUMBER types if you can.

    I agree with that Tansdot said.

  5. #5
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Solution

    Hi, 14th May 2001 16:10 hrs *******

    Nothing wrong as per your Application requirement if you index a varchar column.

    Cheers

    Padmam.
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  6. #6
    Join Date
    Jul 2000
    Posts
    243
    Hi

    way does your freind whants to use a varchar as a primary key? i suggest you use a number column as a primary key for the id column and use the varchar2 column as a use id column (the id the user actual see on the form). use unique index on this field + not null, and you have no probelms. better work this way. and as an argument to support your case you can tell that in oracle applications erp suite this is normaly what is done. the varchar is the id for the user and the number system generated id is for the select statment inside the system.

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by kgb
    I just had questionable problem with another DBA he is going to use varchar2(25) as primary key in OLTP system and as well in DSS system just for more readable logical scheme.
    Tell the other dba to get a clue .

    I'm sorry, but he wants to use a varchar2(25) field for an index 'so he can have a more readable *logical* schema'!!!?? That is not his primary job!!! There are 2 goals in database design:

    - The design must meet the requirements of the application

    - The design must meet the requirements of the database

    The second point is the key. This means, primarily, flexibility and performance. He is sacrificing performance so that some *logical* model will be more readable? Incredible. A numeric PK is almost *always* preferred. The only default exception, IMHO, is the State_CD (if you're in the U.S.), because of its nature. IMHO, if the string is *very* well defined, nearly static, and very small, then it can be a PK. But even then, I generally only allow this for code tables. Because, if nothing else, most string values in primary tables come from the user, and I am rather opposed to using user-supplied data in a PK - it's too dangerous and introduces too many issues. However, even if the data is *very* clean, I would still fall back to the efficiency issue. Why would you want to use this large string as a PK? It would make your PK index larger. This field would also then be propogated to all the related tables. This would make those tables larger. This would make the indexes on those FKs larger as well. Not only are the indexes larger, and therefore inherently less efficient, but string comparisons are also inherently less efficient than numeric comparisons.

    Actually, this makes me think of a good example I need to build. I need to take the demo model I have been building and use ERwin's size-estimation feature/add-on. I am willing to bet that IF I were to use the user-supplied AK fields from all the tables as PKs, my total database size would grow by at least 20%. That, together with maintaining multi-part keys - another pet peeve of mine I personally advocate abstracted keys as the default methodology. Anything else has to have a solid argument behind it. But I need to build that model in both fashions and then not only size them but run some timing tests on different queries...

    Hmmmm...

    Thanks for the idea .

    - Chris

  8. #8
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    Thank you very much!
    Best wishes!
    Dmitri

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