Re: SQL Tuning

Roman,

     I am not entirely sure, but wouldn't the left-outer join:

AND table_productbin.objid (+) = table_site_part.site_part2productbin

...near the end of your CREATE VIEW statement cause a full table scan of table_site_part?

Jay!!!

Roman Gelfand 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.
>
> 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:21:44 CST