Re: Do indexes matter on db in memory

the answer to the question: "will adding an index improve performance?" is "it depends". that is, tuning a database essentially involves tradeoffs, and balancing a finite number of resources (cpu, memory, disk, bandwidth.)

with that said:

for some databases (espcially large ones), the biggest performance bottleneck can be disk i/o... and indexes can drastically improve performance of some queries (selects, inserts, updates, deletes) by substantially reducing the total number of i/o's that the database needs to perform to complete a given task.

the tradeoff associated with an index is the additional disk (space and i/o), memory and cpu that the database uses to create and maintain the index. in some cases, an additional index negatively impacts the overall performance of the database. (the more indexes there are on a table, the more work there is to be done to insert and delete rows from the table, and if those indexes are not utilized in the execution of any database task, it's simply extra overhead.)

if you are experiencing what you consider to be "slow" performance from your database, the dba should be able to figure out if there is a problem, where the problem is, and if anything can be done to improve the situation. in a lot of cases, it's a matter of making a few small changes to some SQL statements. sometimes, its a problem with the application design. sometimes, redistributing i/o by moving database objects can help. sometimes its the network that is the bottleneck. sometimes modifying the characteristics of a database object (extent sizes, initrans, pctfree, pctused, etc.) can improve performance. in some cases, modifying one or more of the parameters for the instance can improve performance.

so, in short, there is no hard and fast rule that determines what impact on performance an additional index will have on overall performance of an application or database.

sorry if this wasn't the answer you wanted to hear.

HTH "( " <new_user_at_email.msn.com> wrote in message news:Och5aw$p$GA.225_at_cpmsnbbsa03...
> We have a heavily used oracle database that is 100MB in size,
 the amount of
> memory
> on the server is 1G. The dba says that since the major
 queries get run so
> often that the data is all in memory and further indexes won't
 help the
> performance.
>
> Is the true? Can anyone tell me reasons why this might not be
 the optimal
> way to run a heavily used database.
>
> Thanks.
>
>
Received on Sun Apr 16 2000 - 00:00:00 CDT