Home » SQL & PL/SQL » SQL & PL/SQL » which Index (Oracle 9i)
which Index [message #444574] Mon, 22 February 2010 11:30 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Hi All,

Gender

M
F
M
F
F

Like this the Gender column contains millions of records.
In this case which index is better to create on that column and also please give me the reason.
Thanks in Advance.
Re: which Index [message #444575 is a reply to message #444574] Mon, 22 February 2010 11:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
None. Whatever you do, you will get back half of the records present. FTS will beat indexes if you search with gender as the only argument.
Re: which Index [message #444577 is a reply to message #444574] Mon, 22 February 2010 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 63809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Application Developer's Guide - Fundamentals
Chapter 5 Using Indexes in Application Development
Section Guidelines for Application-Specific Indexes
Especially subsection Index the Correct Tables and Columns

Now you know which index and why, I hope you will no more have to post this question.
And if you want to know the answers to your future questions, read the whole book.

Regards
Michel

[Updated on: Mon, 22 February 2010 11:46]

Report message to a moderator

Re: which Index [message #444580 is a reply to message #444574] Mon, 22 February 2010 12:45 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
I follow a basic set of rules:

Outside of a true Kimball Data Warehouse, and various special situations not withstanding, I do the following and require it of systems I support:

Index all of the following:

1) PRIMARY KEYS
2) UNIQUE KEYS
3) FOREIGN KEYS
4) Any other access pattern that you can show is worth while for your application

Consider also, the following:

1) Use of data columns at the end of indexes in order to avoid TABLE ACCESS BY ROWID
2) Use of function based indexes in rare situations where they make a significant difference in performance
3) Removal of subset indexes where the key columns of one index are at the front of some other longer index
4) making your PRIMARY/UNIQUE key indexes non-unique and letting the constraint define the uniqueness of the key


As to your specific question, the only place I can think of where an index on an M/F like column might have any use would be as a bitmap index in a data warehouse and then it will only be valuable when used in conjuction with other bitmap indexes. To understand why, you need to research how bitmap indexes work, and why they were created as an access method to begin with.

Start here if you are intersted in bitmap indexes.

Good luck, Kevin

[Updated on: Mon, 22 February 2010 12:48]

Report message to a moderator

Previous Topic: Bulk inserts get EXTREMELY slow after 10,000 queries
Next Topic: GLOBAL TEMPORARY TABLE
Goto Forum:
  


Current Time: Thu Sep 29 02:11:37 CDT 2016

Total time taken to generate the page: 0.18752 seconds