Using Indexes. 1

[Diogo]

Hello.

I'm trying to create two indexes for two tables.
But when i do the sql explain, with the indexes or without the indexes the estimated cost is the same.

The index:
1. CREATE INDEX TRICS200_IDX1 ON TRICS200 (T_ORNO ASC, T_PONO ASC )
2. CREATE INDEX TRTOC850_IDX1 ON TRTOC850 (T_ORNO ASC, T_PONO ASC )

The sql:
SELECT TRICS200.T_TTYP, TRTOC850.T_AUTO
FROM DB2ADMIN.TRICS200, DB2ADMIN.TRTOC850
WHERE ((
DB2ADMIN.TRICS200.T_ORNO = DB2ADMIN.TRTOC850.T_ORNO AND
DB2ADMIN.TRICS200.T_PONO = DB2ADMIN.TRTOC850.T_PONO))

What i have to do?

Thanks any way... and sorry my English...

DR

 

[BrianTyler]

You will need to use RUNSTATS to allow the optimiser to use the index.

RUNSTATS ON TABLE table-name [{FOR index-clause | [column-stats-clause]
[AND index-clause]}] [ALLOW {WRITE | READ} ACCESS]

index-clause:
[[SAMPLED] DETAILED] {INDEXES | INDEX} {ALL | index-name [{,index-name}...]}

columns-stats-clause:
[ON {{ALL | KEY} COLUMNS [AND COLUMNS (column-option [{,column-option}...])] |
COLUMNS (column-option [{,column-option}...])}] [distribution-clause]

column-option:
{column name [LIKE STATISTICS] | (column-name [{,column-name}...])}

distribution-clause:
WITH DISTRIBUTION [ON {{ALL | KEY} COLUMNS [AND COLUMNS (dist-column
[{,dist-column}...])] | COLUMNS (dist-column [{,dist-column}...]) }]
[DEFAULT [NUM_FREQVALUES number] [NUM_QUANTILES number]]

dist-column:
{column name | (column-name [{,column-name}...])} [NUM_FREQVALUES number]
[NUM_QUANTILES number]


Brian

 

[Diogo]

I have just tried that, and the reorg.

Thanks any way...

DR

 

[gregsimpson]

Diogo,

I assume from your reply it didn't make any difference? This is often especially true for small testing type tables with only a few rows, where given the amount of records db2 recognises it can tablescan quicker than doing index and table reads. If this is the case you may well wish to manually update your table stats to production type values.

Let us know.

Cheers
Greg

 

[Diogo]

It's true, that doesn't do any diferrence.

The table trics200 have 107639 rows, and the trtoc850 212991 rows.

 

[gregsimpson]

Diogo,

A high level guess here would be that as you've got no physical placements into the data in your where clause (for example; where T_ORNO = 23) the optimiser has decided that it is quicker to tablespace scan both tables rather than read the indexes for physical placement and then the tables for the 2 items you require.

It often doesn't make sense to read each index page if each and every (or many) table pages are going to be read as well.

Have you tried db2advis to see what indexes db2 recommends?

Cheers
Greg

 

[Diogo]

I tried the db2advis and it gives me "no indexes are recommended for this workload".

I didn't know db2advis.
This is the indexes that i have created, or in case of necessary he goes to recommend me a new?

Thanks any way.

DR

 

[gregsimpson]

Diogo,

I believe from the info you have posted and the output of db2adis, that the optimiser has decided table scans are the most efficient way of resolving your query.

Cheers
Greg

 

[Diogo]

Thanks.

DR

 

[martynh]

Looking at the query and the tables, I would say that a tablespace scan is the only sensible choice....

Your query returns non-indexed data from both tables - so index-only access is not available. Your query has no predicates that would reduce the number of rows required from either table, so no initial filtering is available on either table before the tables are joined. Thus, DB2 will simply scan both tables and either sort and merge scan the data or perform a hash join. Either way a tablespace scan on both tables would be employed.

To see your indexes being used, try a version of the query with a simple predicate (t1.column = somevalue). Here you should see 't1' being accessed first and 't2' being accessed via an index.