DB2 index performance 
Author Message
 DB2 index performance
Can u help me on IBM DB2 v6 for OS/390 index performance? I have a
table with 10M records, with over 50 columns and I want to define
indexes for query improvement. Can anyone tell me if a composite index
on A, B and C is good for a WHERE A=x AND C=y query? How many columns
are reasonable to use in a composite index? How many indexes can be
defined without loosing too much insert performance, because I have no
updates. Any answers, comments, links or books are welcome! Thank you.


Sun, 31 Jul 2005 03:55:29 GMT
 DB2 index performance

Hi Andr

Q:Is a composite index on A, B and C is good for a WHERE A=x AND C=y query?
A: Better than no indexes, but depends on filterfactor. What about making
the index A, C, B - that would be better.

Q:How many columns are reasonable to use in a composite index?
A: There is no answer to that question. You have to make indexes to match
about 80% of you queries

Q:How many indexes can be defined without loosing too much insert
performance, because I have no updates ?
A: There is no answer to that question. Every time you add an index, you
degrade your insert performance (2-4 I/O per index).

/Kim


Quote:
> Can u help me on IBM DB2 v6 for OS/390 index performance? I have a
> table with 10M records, with over 50 columns and I want to define
> indexes for query improvement. Can anyone tell me if a composite index
> on A, B and C is good for a WHERE A=x AND C=y query? How many columns
> are reasonable to use in a composite index? How many indexes can be
> defined without loosing too much insert performance, because I have no
> updates. Any answers, comments, links or books are welcome! Thank you.



Sun, 31 Jul 2005 05:09:48 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. db2 for os390 data sharing/sysplex check index dsnu715i multiple index entries

2. One-field INDEX performance VS multiple fields index

3. Performance Comparison Between String Indexes and Integer Indexes

4. Distributed DB2 - Oracle/Oracle - Oracle/DB2 - DB2/DB2

5. Differences between SQL on DB2-MVS and DB2-OS/2 or DB2-NT

6. DB2 to SDL7 Bandwidth/Performance

7. ODBC Performance with DB2/2

8. Performance Problems on DB2/BDE !

9. Migrating from Delphi 1 to 3, DB2/2 Performance

10. Performance issue for JDBC for DB2/NT

11. MD-Hyattsville-260455--DBA Skills-Performance Tuning-Client/Server-DB2-DBMS-ORAC

12. SC-Greenville-113553--RDB-DBA Skills-ORACLE-Performance Tuning-DB2-PERFMODL-Oracle DBA


 
Powered by phpBB® Forum Software