SQL Tuning

From: Roman Gelfand <rgelfand_at_masmid.com>
Date: Wed, 4 Nov 1998 18:53:44 -0500
Message-ID: <71qpa6$gik$1@news.monmouth.com>

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.

Consider the following select statement as it is querying against a view below. The view consists of 8 table join.

The table sizes in rows are:

table_vendor  -  800
table_site_part - 412,000
table_mod_level - 800
table_part_num - 2000
table_site - 3000
table_address - 1000
table_productbin  - 40,000

The problem is that I am getting a full table scan on a table_site_part.

The number of distinct column in each of the fields in the where clause:

table_part_num.objid - unique index was created
table_mod_level.part_info2part_num -   bitmap index was created
table_mod_level.objid - unique index was created
table_site_part.site_part2part_info - bitmap index was created
table_site.objid - unique index was created
table_site_part.all_site_part2site - non-unique index was created
table_address.objid - unique index was created
table_site.cust_primaddr2address - non-unique index was created
table_productbin.objid - unique index was created
table_site_part.site_part2productbin - non-unique index was created
table_del_vendor.objid -unique index was created
table_part_num.del_vendor2vendor - non-unique index was created
table_bill_vendor.objid -unique index was created
table_part_num.bill_vendor2vendor - non-unique index was created
table_site_part.level_to_bin - > (bitmap index was created)

select objid
from table_addrsitepmhpml
where ( S_x_name LIKE 'BWILLIAMS%' ) order by S_site_id asc

CREATE OR REPLACE VIEW TABLE_ADDRSITEPMHPML ( OBJID, PMLOBJID, NAME, S_NAME, FAMILY,
LINE, S_LINE, PART_TYPE, S_PART_TYPE,
PRD_INST_NAME, ACTIVE, PMH_OBJID, SERIAL_NO, S_SERIAL_NO, INVOICE_NO, PART_NUMBER, S_PART_NUMBER, MOD_LEVEL, S_MOD_LEVEL, SITE_NAME, S_SITE_NAME, SN_TRACK, MODEL_NUM, S_MODEL_NUM, SITE_OBJID, SITE_ID, S_SITE_ID, CITY, S_CITY,

STATE, S_STATE, SITE_PART_LEVEL_TO_BIN, INST_OBJID,
X_EQUIPMENT_ID, X_NAME, S_X_NAME, X_ML_PRODUCT_ALIAS,
X_COST_CENTER_REC, X_BILL_VENDOR_CODE, X_DEL_VENDOR_CODE ) AS select
table_site_part.objid,
table_mod_level.objid pmlobjid,
table_part_num.description name,
table_part_num.S_description s_name,
table_part_num.family ,
table_part_num.line,
table_part_num.S_line,
table_part_num.domain part_type,
table_part_num.S_domain s_part_type,
table_site_part.instance_name prd_inst_name,
table_mod_level.active,
table_part_num.objid pmh_objid,
table_site_part.serial_no,
table_site_part.S_serial_no,
table_site_part.invoice_no,
table_part_num.part_number,
table_part_num.S_part_number,
table_mod_level.mod_level,
table_mod_level.S_mod_level,
table_site.name site_name,
table_site.S_name s_site_name,
table_part_num.sn_track,
table_part_num.model_num,
table_part_num.S_model_num,
table_site.objid site_objid,
table_site.site_id,
table_site.S_site_id,
table_address.city,
table_address.S_city,
table_address.state,
table_address.S_state,
table_site_part.level_to_bin site_part_level_to_bin,
table_site_part.inst_objid inst_objid,
table_site_part.x_equipment_id,
table_productbin.name x_name,
table_productbin.S_name s_x_name,
table_part_num.x_ml_product_alias,
table_site_part.x_cost_center_rec,

table_bill_vendor.code x_bill_vendor_code, table_del_vendor.code x_del_vendor_code FROM table_vendor table_del_vendor, table_vendor table_bill_vendor, table_site_part, table_mod_level, table_part_num, table_site, table_address, table_productbin WHERE table_part_num.objid = table_mod_level.part_info2part_num

AND table_mod_level.objid = table_site_part.site_part2part_info
AND table_site.objid = table_site_part.all_site_part2site
AND table_address.objid = table_site.cust_primaddr2address
AND table_productbin.objid (+) = table_site_part.site_part2productbin
AND table_del_vendor.objid (+) = table_part_num.del_vendor2vendor
AND table_bill_vendor.objid (+) = table_part_num.bill_vendor2vendor
AND table_site_part.level_to_bin != 999

Thanks, Received on Wed Nov 04 1998 - 17:53:44 CST