Adding Index to Table may reduce performance of Some Select Statements ?

srinik

Guy’s,

Adding Index to Table may reduce performance of Some Select?

Thanks in advance,
Srini

Jul 19 '05 #1

Subscribe Post Reply

3003

sybrandb

sr********@yahoo.com (srinik) wrote in message news:<95**************************@posting.google. com>...

Guy’s,

Adding Index to Table may reduce performance of Some Select?

Thanks in advance,
Srini

Vague response for a vague question without any sensible clue:
'it depends'

Sybrand Bakker
Senior Oracle DBA

Jul 19 '05 #2

Daniel Roy

To receive any useful information, you will certainly need to provide
additional details on your setup, or find someone more familiar with
your Oracle to do it for you. The cases where I saw that an additional
index slowed some select statements was generally when:
- rule-based optimizing is used and the new index has low cardinality
columns (few distinct values)
- cost-based optimizing is used, and the stats are not kept up-to-date
- cost-based optimizing is used, and the indexed columns suffer from
skewness, and no histograms are used.

Those are 3 examples, and is certainly not a complete list.

Daniel

sr********@yahoo.com (srinik) wrote in message news:<95**************************@posting.google. com>...

Guy’s,

Adding Index to Table may reduce performance of Some Select?

Thanks in advance,
Srini

Jul 19 '05 #3

Similar topics

Performance with different index types

by: Johann Uhrmann | last post by:

Hello, are there any experiences about the performance of indices with different data types. How do the performance of an index that consists of - an integer field - a varchar() field - a...

PostgreSQL Database

Why does adding SUM and GROUP BY destroy performance?

by: David Link | last post by:

Hi, Why does adding SUM and GROUP BY destroy performance? details follow. Thanks, David Link s1.sql: SELECT t.tid, t.title, COALESCE(s0c100r100.units, 0) as w0c100r100units,

PostgreSQL Database

Adding fields on the fly

by: Gail Zacharias | last post by:

I am investigating the possibility of using pgsql as the database in an application. I have some unusual requirements that I'd like to ask you all about. I apologize in advance if my terminology is...

PostgreSQL Database

Better index access = worse performance??

by: Sean C. | last post by:

Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...

DB2 Database

When adding lots of records, do you still use INSERT INTO?

by: Mike Hnatt | last post by:

My goal is to get data from an XML file into a couple of tables in an Access database. The XML file is a little complex so I need control over what I do (I can't just read it into a dataset). ...

ASP.NET

Q: Adding a column to a dataset

by: Geoff Jones | last post by:

Hi Can anybody help me with the following, hopefully simple, question? I have a table which I've connected to a dataset. I wish to add a new column to the beginning of the table and to fill...

Visual Basic .NET

type-2 index causing performance hit

by: bernhard.willems | last post by:

We are using DB2 UDB version 8.1 fp7 & fp9 on AIX 5.2. On our databases we do massive inserts and deletes (millions of rows). The performance is dramatically reduced because of pseudo deleted keys....

DB2 Database

many "or" operation make system choose incorrect index

by: lsllcm | last post by:

Hi All, I have one question about many "or" operation make system choose incorrect index There is one table TT ( C1 VARCHAR(15) NOT NULL, C2 VARCHAR(15) NOT NULL, C3 VARCHAR(15) NOT NULL,...