CODEWITHSUNDEEP
sqloracle-database
Prakruti Pathik
Prakruti Pathik

Reputation: 392

Creating index for a query

I have one table Person with two columns Name and Gender and suppose in my application if I have a query which is called frequently :

select * from Person where Gender = 'M'

So is it advisable to create an index on the column Gender?

Upvotes: 0

Views: 109

Answers (2)

Arun Palanisamy
Arun Palanisamy

Reputation: 5469

Below points might give you the idea:

From Documentation

In general, index access paths are more efficient for statements that retrieve a small subset of table rows, whereas full table scans are more efficient when accessing a large portion of a table.

Do not index columns that are modified frequently. UPDATE statements that modify indexed columns and INSERT and DELETE statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes as well as data in tables. They also generate additional undo and redo.

When choosing to index a key, consider whether the performance gain for queries is worth the performance loss for INSERTs, UPDATEs, and DELETEs and the use of the space required to store the index. You might want to experiment by comparing the processing times of the SQL statements with and without indexes. You can measure processing time with the SQL trace facility.

Upvotes: 0

LoztInSpace
LoztInSpace

Reputation: 5697

It's not advisable unless there is loads of one an only a few of the other and your query only looks at the few. A full table scan would give you a much more efficient result than diving through an index. In fact, even if you created the index, it's highly unlikely the optimiser would use it.

Upvotes: 1

Related Questions