Home » RDBMS Server » Performance Tuning » Indexes
Indexes [message #65892] Mon, 24 January 2005 06:17 Go to next message
Natasha
Messages: 1
Registered: January 2005
Junior Member
Is there a particular entity size that is "needed" before an index actually becomes useful?
Re: Indexes [message #65893 is a reply to message #65892] Mon, 24 January 2005 06:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It depends on the number of rows processed and available statistics.
Let the CBO decide to use the index or not.
Frank gives a good example.
This may give some idea about the same....
http://www.orafaq.com/forum/t/23478/0/

[Updated on: Fri, 18 February 2005 23:32]

Report message to a moderator

Re: Indexes [message #65894 is a reply to message #65892] Mon, 24 January 2005 07:12 Go to previous message
Scott Swank
Messages: 24
Registered: January 2005
Junior Member
Look at the number of rows and the number of blocks that this table occupies:
SELECT table_name, num_rows, blocks
  FRom all_tables
 where owner = 'SCOTT'
   AND table_name = 'EMP';

The database reads an entire block of data at a time and buffers the entire block. An index is only useful if it will give the database a better option than a full table scan. This means that an index on sex (Male/Female) will never be useful (significantly skewed data aside for now) because every block will likely contain at least one row of each sex. Lets say that your table contains 20,000 rows and occupies 100 blocks. Then you have an average of 200 rows/block. Very, very broadly speaking an index must be more selective than this to be useful.

Scott
Previous Topic: join conditions for smaller /larger tables
Next Topic: Perfomance difference
Goto Forum:
  


Current Time: Thu Mar 28 10:59:30 CDT 2024