Re: SQL Tuning

Roman Gelfand <rgelfand_at_masmid.com> wrote:
>I am trying to tune an sql statement by manipulating initialization
>parameters, indexes... etc. These SQL statements are vendor supplied and I
>am not at liberty to change them....

Hi Roman,

combining the information provided in your postings ( the explain plan, view definition, and index definitions ), this is how I think the optimiser is processing your query ...

(in the following, FTS = "Full table scan" and TSP = "TABLE_SITE_PART" )

1. FTS of TABLE_MOD_LEVEL and sort by PART_INFO2PART_NUM
2. FTS OF TABLE_PART_NUM and sort by OBJID
3. Merge join of result sets of previous two operations using condition: TABLE_PART_NUM.OBJID = TABLE_MOD_LEVEL.PART_INFO2PART_NUM
4. For each row in result set from step (3) ...
5. Combine the bitmapped indexes on TSP.SITE_PART2PART_INFO and TSP.LEVEL_TO_BIN to obtain rows from TSP where TSP.SITE_PART2PART_INFO = TABLE_MOD_LEVEL.OBJID of current row and TSP.LEVEL_TO_BIN != 999
6. Take result set from step (4/5) and sort by TSP.SITE_PART2PRODUCTBIN
7. FTS of TABLE_PRODUCTBIN, selecting only those rows where S_X_NAME like 'BWILIAMS%' and sort by OBJID.
8. Outer merge the result sets of steps (6) and (7) using join condition: TABLE_PRODUCTBIN.OBJID = TSP.SITE_PART2PRODUCTBIN.
9. Sort result set of step (8) by TSP.ALL_SITE_PART2SITE
10. FTS of TABLE_SITE and sort by OBJID
11. Merge result sets of steps (9) and (10) using join conditions TABLE_SITE.OBJID = TSP.ALL_SITE.PART2SITE
12. For each row in result set from step (11)
13. Use unique indexes on TABLE_VENDOR and TABLE_ADDRESS to obtain the vendor codes and customer address for the current row.
14. Sort the result set of step (12/13) by TABLE_SITE.S_SITE_ID.

You claim that your problem is a FTS on TABLE_SITE_PART. This isn't true: your explain plan doesn't show a FTS on TABLE_SITE_PART ( perhaps you misread the FTS on TABLE_SITE ).

Your real problem is that the optimiser has "optimised" the query in an almost diametrically opposite way to that required to produce an efficient result. Note that your condition S_X_NAME like BWILLIAMS% isn't taken into account until step (7), so all the prior steps are acting on all rows in the tables concerned.

An obvious observation is that there is no index on TABLE_PRODUCTBIN.S_X_NAME. Creating one may help the optimiser to invert the query plan to produce an efficient result. Your aim (probably) is to get the condition S_X_NAME LIKE 'BWILLIAMS%' evaluated early on in the execution plan so that it drives the query. Be aware though that adding indexes may cause the optimiser to optimise other queries in a different way, possibly resulting in them going slower.

HTH, Dave.
--
Remove the no-spam bit from my email address to reply. Received on Sat Nov 07 1998 - 15:59:31 CST