Is anybody knows what type of field preferable to use as index and why?
Thanks :-)
Printable View
Is anybody knows what type of field preferable to use as index and why?
Thanks :-)
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
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.
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
Quote:
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.
Hi, 14th May 2001 16:10 hrs *******
Nothing wrong as per your Application requirement if you index a varchar column.
Cheers
Padmam.
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.
Tell the other dba to get a clue ;).Quote:
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.
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
Thank you very much!
Best wishes!
Dmitri