Performance and temporary tables
The query performance on our system is extremely bad.
In ST03N we see that it is due to the DB time. When running RSRT in Debug mode on one of the worst queries we see in the acces path that the temporary tables /BI0/06* get the following message:
"Explanation of query block number: 3 step: 2
Performance appears to be bad
No index is used. Sequential tablespace scan
. join each row of composite table, with matching rows
. of new table (nested loop Join).
. data pages are read in advance
. pure sequential prefetch is used
. new table:
. table space locked in mode: N
. Accesstype: sequential tablespace scan.
The temporary table does have a primary index, but no secondary.
We're on DB2 and BW 3.1 support package 16. The solution most probably lies in a not yet implemented support-package, however, implementing new support pachkages is not possible at the moment.
Does anybody now what to do to correct the index/statistics on the temporary table?
Thanks a lot,