Home » SQL & PL/SQL » SQL & PL/SQL » What is the criteria for creating an index on a column
What is the criteria for creating an index on a column [message #194311] Thu, 21 September 2006 08:04 Go to next message
Messages: 14
Registered: June 2005
Location: Al-Khobar
Junior Member

Hello all,

Recently in an interview they asked me this question and I have tried to find out the answer for this without any luck.

What is the criteria or what conditions should a column satisfy for creating an index on a column?

Re: What is the criteria for creating an index on a column [message #194313 is a reply to message #194311] Thu, 21 September 2006 08:11 Go to previous messageGo to next message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What a poorly worded question.

Did they mean 'What criteria would mean that an index would be useful' or 'What critera prevent an index from being created'

Re: What is the criteria for creating an index on a column [message #194325 is a reply to message #194311] Thu, 21 September 2006 09:13 Go to previous message
Messages: 18
Registered: September 2006
Junior Member
I suggest you to read a loads of documents before appearing for an interview.

Check link of FAQ Orafaq it's really useful for preparing oneself to face any oracle interviews.


from oracle docs
Indexes are optional structures associated with tables. You can create them to improve query performance. Just as the index in this book helps you to quickly locate specific information, an Oracle index provides a quick access path to table data. You can create indexes on one or more columns of a table. After an index is created, it is automatically maintained and used by Oracle. Changes to a table's data or structure, such as adding new rows, updating rows, or deleting rows, are automatically incorporated into all relevant indexes with complete transparency to the user.Some indexes are created implicitly through constraints that are placed on a table. For example, a column with the constraint that its values be unique causes Oracle to create a unique key index.When you click the Indexes link in the Database Objects section of the Administration page, an Indexes page appears. This page is similar in content and functionality to the Tables page shown in Figure 8–3.

Indexes and Performance

Before you add additional indexes, you should examine the performance of your database. You can then compare performance after the new indexes are added.Indexes are generally of value to queries and to SQL statements that need to operate on a single, existing row or a small number of existing rows. However, too many indexes can increase the processing overhead for statements that add, modify, or delete rows. To determine if your application can improve performance with more indexes, you can run the SQL Access Advisor in Enterprise Manager. See Using the SQL Access Advisor in Chapter 10, "Monitoring and Tuning the Database".
Previous Topic: email alert after export
Next Topic: Dynamic SQL Question
Goto Forum:

Current Time: Thu Oct 27 14:09:25 CDT 2016

Total time taken to generate the page: 0.08584 seconds