| when do I need to create an indexes. [message #151507] |
Thu, 15 December 2005 03:54  |
cuisia
Messages: 21
Registered: July 2005
|
Junior Member |
|
|
Hi Everybody,
I would like to know when will be the right time to create an indexes for the tables ? What will be the rules to create an indexes ? Do I need to create an indexes right away during designing a tables or it will be more ok if I create the indexes when the table rows are going big ... How many records do I need think that the tables need an indexes.
thanks in advance ....
Mike Cuisia
Oracle DBA
|
|
|
|
| Re: when do I need to create an indexes. [message #151529 is a reply to message #151507] |
Thu, 15 December 2005 06:46  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Buy Tom Kytes new book.
Buy Tom Kytes new book.
Buy Tom Kytes new book.
Read it. If you have further questions, go and bug him at asktom.oracle.com . 
He would more happy to answer these questions ( about his book).
Probably it is written again and again over asktom and here/OTN forums.
>> when will be the right time to create an indexes for the tables ?
Design, development and Testing and sometimes in production ( NOT ALWAYS).
When database/application matures over time, your requirement may vary. Priorities vary.
So should your database design.
>>What will be the rules to create an indexes
Apply rationale.
Write good code, test, analyze, measure and see whether creation of index may be useful.
Presence of index **may** help during select statements.
But will be painful during loading the data ( as in batch environments).
>> Do I need to create an indexes right away during designing a tables
Of course!.
Else be prepared for surprises.
90% of all the so-called 'Tuning issues/ Tuning tips or Rule-of-thumb ' could have been fixed during the design stage.
Understand your Application.
Understand your Database design.
Pick the right choice of tables/indexes ( we have more than one kind of table and index. Pick the right ones)
Simulate and test.
>>How many records do I need think that the tables need an indexes.
There are many theories here.
Folks say ,if you query less than 1-3% of data, you need an index.
But again, nothing can be generalized.
In most cases ( from 9i) CBO does the right job. So let us just help it!.
[Updated on: Thu, 15 December 2005 16:31] Report message to a moderator |
|
|
|
| Re: when do I need to create an indexes. [message #151536 is a reply to message #151529] |
Thu, 15 December 2005 07:12 |
|
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Also look into virtual indexes.
It is very handy in production / stage areas.
quoting Amar Kumar Padhi from http://www.databasejournal.com/features/oracle/article.php/3413961
| Quote: |
As per Oracle, this functionality is not intended for standalone usage. It is part of the Oracle Enterprise Manger Tuning Pack (Virtual Index Wizard). The virtual index wizard functionality allows the user to test a potential new index prior to actually building the new index in the database. It allows the CBO to evaluate the potential new index for a selected SQL statement by building an explain plan that is aware of the potential new index. This allows the user to determine if the optimizer would use the index, once implemented.
|
[Updated on: Thu, 15 December 2005 16:31] Report message to a moderator |
|
|
|
| Re: when do I need to create an indexes. [message #151565 is a reply to message #151507] |
Thu, 15 December 2005 09:55 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I think in a way what Mahesh is saying is that there is no simple 1+1=2 answer to your questions, or to oracle in general. There is a lot that needs to be known about both oracle and about your specific system. The only way to get that knowledge is to read read read, then practice, then read, then practice. See the sticky in this forum for more info, and the book Mahesh recommended is probably one of the best starting places out there. It won't happen overnight.
But it is good that you are asking the question as to when it would be appropriate, rather than just a) never using them or b) always using them. You are thinking along the correct lines.
As a minimum rule of thumb, and rules of thumb are, as a rule of thumb, not all that helpful, you would want an index on your primary key column (which will happen for you automatically if you put a PK constraint on it), and on foreign key columns (which you should put a FK constraint on, but will need to build your own index).
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
