Re: Performance effect of adding a new index

On Apr 10, 10:37 pm, "Sam" <sasan.raisd...@xxxxxxxxxxx> wrote:
Hi there,
I have a table with about 10-20 insert per minutes and it can carry up to
500,000 records, except the primary key this table doesn't have any index,
I need to make some reports based on a field that's System Date, this report
shows in a web interface and run a few times per day,
I heard that adding index on table with lot's of insert downgrade the insert
performance,
How much do you think adding this index effect the general performance of
this database? What can I do to minimize the performance effect?

Server Configuration:
Oracle 10g
Windows 2003
No Raid

Thank you in advance - Sam

If this is a table in oracle and there's really a primary key defined
then it does have an index.

It doesn't sound though as if you are planning or thinking about
changing that field anyway are you thinking about indexing some other
field?

As opposed to the opinions made by some posters recently in cdos, any
changes in the indexes for a table can potentially affect quite a few
different parts of your existing applications.

You need to think carefully about what SQL statements will be improved
by adding the index, and which statements will be negatively
impacted. Does the index need to potentially be added and dropped
around a batch cycle processing requirement?

Along with the thoughts included above, the first question needs to be
"do you have a test system available and how will you measure/quantify
the pro's and con's of adding this index before thinking about any
changes to a production system". Well maybe the first question should
be "do you have a dba to work with or a lead application developer
that ( one or both ) should get involved".

.

Relevant Pages

  • SQL::Statement questions
    ... I have a file containing several SQL statements mainly ... PRIMARY KEY ... ALTER TABLE Tray ... Is SQL::Statement the appropraite module for this or is there a module ...
    (perl.dbi.users)
  • Re: getting only first error when executing multiple sql statements
    ... > only get the first error out of Errors collection. ... > is the primary key. ... If I would execute the following SQL statements separately I ...
    (microsoft.public.data.ado)
  • Re: Multiple Table Creation in Access
    ... Allen Browne - Microsoft MVP. ... > Could you provide the macro or VBA assuming that the SQL statements are ... > stores in a table with a key to indicate order and a memo. ... >> How about a table, with a primary key to indicate the order, and a memo ...
    (microsoft.public.access.queries)
  • RE: What happened to my PKs????
    ... To answer your first question. ... By default the primary key is coverted to a unique index on the ... Declared Referential Intergrity check box on the Snapshot tab. ...
    (microsoft.public.sqlserver.replication)
  • Re: access newbie
    ... I have amended the 'problems' table so that the primary key is now the ... pr_number, and removed the autoincrement number field, but I'm still having ... but Access doesn't seem to permit this (it keeps changing my SQL statements ...
    (microsoft.public.access.gettingstarted)