Secondary Index

jane mike

Secondary Index
Hi

I have doubt on secondary index. When its needed...? Does the db2 really
look the secondary index..? if so, when ?

Actually, I checked the CPU time and I/O time using DB2 Estimator, I got the
following result. Only one index that too Partition inedx.

1. created Partion unique index(PART_KEY).
Executed the sql SELECT * from table where part_key > 0 it takes all rows
and resulted CPU Time 00:08:39.712 and IO time 00:01.29.995.

2. Executed sql SELECT * FROM IND > 0 , resulted as
CPU time : 00:07.38.728 and IO time : 00.01.25.536

both columns are Decimal part_key is dec(3,0) ind key is dec(9,0) but the
second query runs faster than first one.

So i decided to create seconday Non-partitioned index on ind key. The
cluster ratio is 50%
now the result for the 2 query is
CPU time : 00:17:26.947 I/O time : 08:42:47.485

now, it degrades CPU time for the second query

Please clarify without secondary index, my query runs faster, after creation
of secondary index its very slow. All values are calculated in the DB2
Estimator.

can any one suggest regarding secondary index..??

thanks
Jane


_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx



Ali OZTURK

Re: Secondary Index
(in response to jane mike)
Hi jane,
In the first sql, db2 access the partition index depends on where
statements. If you use index columns in where condition,
db2 want to access the index first, and then access the data pages. You
are selecting all the row with this sql, therefore
db2 accessing the index page and data page.(part_key > 0 means, I will
select all rows from the table. In this case,you shouldn't use where
condition.)
In second sql, db2 doesn't use any indexes,and access only data page
via tablespace scan. This is more efficent than first one (Because db2
is not accessing any index for the row).
Finaly, If you want to select all the table row, You don't give where
condition on sql statement.

Regards.
Ali Ozturk
Pamukbank
DB2 DBA.



-----Original Message-----
From: jane mike [mailto:[login to unmask email]
Sent: Monday, December 31, 2001 1:57 PM
To: [login to unmask email]
Subject: Secondary Index


Hi

I have doubt on secondary index. When its needed...? Does the db2 really
look the secondary index..? if so, when ?

Actually, I checked the CPU time and I/O time using DB2 Estimator, I got
the
following result. Only one index that too Partition inedx.

1. created Partion unique index(PART_KEY).
Executed the sql SELECT * from table where part_key > 0 it takes all
rows
and resulted CPU Time 00:08:39.712 and IO time 00:01.29.995.

2. Executed sql SELECT * FROM IND > 0 , resulted as
CPU time : 00:07.38.728 and IO time : 00.01.25.536

both columns are Decimal part_key is dec(3,0) ind key is dec(9,0) but
the
second query runs faster than first one.

So i decided to create seconday Non-partitioned index on ind key. The
cluster ratio is 50%
now the result for the 2 query is
CPU time : 00:17:26.947 I/O time : 08:42:47.485

now, it degrades CPU time for the second query

Please clarify without secondary index, my query runs faster, after
creation
of secondary index its very slow. All values are calculated in the DB2
Estimator.

can any one suggest regarding secondary index..??

thanks
Jane


_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx



the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
can