| Indexes [message #65892] |
Mon, 24 January 2005 06:17  |
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 #65894 is a reply to message #65892] |
Mon, 24 January 2005 07:12  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
