Home » RDBMS Server » Server Administration » Min number of rows in a table to get the benefit of index
Min number of rows in a table to get the benefit of index [message #56193] Tue, 11 March 2003 23:42 Go to next message
Sukriti
Messages: 1
Registered: March 2003
Junior Member
Indexes are not very useful when created for table with very less number of rows. What should be the minimum number of rows in a table after which it may be expensive to have a full table scan as compared to index scan.

Thanx

Report message to a moderator

Re: Min number of rows in a table to get the benefit of index [message #56209 is a reply to message #56193] Wed, 12 March 2003 16:18 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
The best way to find it out is to test it because it depends of several parameters.

Usually the optimizer knows when it is faster to use a index insead of doing a full table scan. So in most cases it shouldn't slow down your query when you got an index.

Report message to a moderator

Re: Min number of rows in a table to get the benefit of index [message #56215 is a reply to message #56193] Thu, 13 March 2003 00:07 Go to previous message
Bhupinder Mohan Singh Kap
Messages: 5
Registered: March 2003
Junior Member
DEAR SUKRITI,

THERE IS NO MINIMUM NO.OF ROWS PREDIFINED BY ORACLE FOR U TO HAVE A INDEX ON A TABLE...

IDEALLY ORACLE SUGGESTS TAT IF UR SQL STATEMENTS ARE GOING TO RETRIEVE MORE THAN 3 TO 4 % OF TOTAL ROWS IN A TABLE ,ONLY THEN CREATE AN INDEX... BUT AGAIN IF U R HAVING A COST BASED OPTIMISER, IT ENTIRELY DEPENDS ON THE STATISTICS AVAILABLE TO THE OPTIMISER REGARDING THE DISTRIBUTION OF THE RECORDS BASED ON THE KEY COLUMN VALUES, DEPENDING ON WHICH THE INDEX MAY OR MAY NOT BE USED...

I HOPE THIS SERVES UR PURPOSE...

BHUPI

Report message to a moderator

Previous Topic: export error:ORACLE error 6553 encountered
Next Topic: how can I get ORACLE redolog specification?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 09:57:43 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.