Re: SQL Tuning

Hi Roman,

    Have you run ANALYZE TABLE table_site_part ESTIMATE STATISTICS yet?

    It won't take long and may solve your problem since the Cost Based Optimizer needs statistical data to make good decisions.

regards

Jerry

Roman Gelfand wrote in message <71qpa6$gik$1_at_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 Thu Nov 05 1998 - 13:36:05 CST