Home » RDBMS Server » Performance Tuning » when do I need to create an indexes.
when do I need to create an indexes. [message #151507] Thu, 15 December 2005 03:54 Go to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
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 . Smile
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
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 Go to previous messageGo to next message
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).
Re: when do I need to create an indexes. [message #151607 is a reply to message #151507] Thu, 15 December 2005 19:20 Go to previous message
cuisia
Messages: 21
Registered: July 2005
Junior Member
Thank you... I will read more books regarding Oracle. More power to orafaq.....
Previous Topic: query optimization
Next Topic: full table scan
Goto Forum:
  


Current Time: Tue Dec 06 20:32:45 CST 2022