SQL Execution
Date: Thu, 5 Nov 1998 23:31:19 -0500
Message-ID: <71ttuq$dv2$1_at_news.monmouth.com>
Can someone tell me how Oracle evaluates (order of precedence) the following where clause identifying what causes recursion, etc... It seems that recursion causes unnecessary reads killing the buffer cache.
It appears that order in the where clause is irrelevant.
If someone could describe programatically what Oracle is doing with select below I would greatly appreciate it.
This is the access path.
Optimiser mode: ANALYZED
Optimiser mode: CHOOSE
SELECT STATEMENT
SORT ORDER BY
NESTED LOOPS NESTED LOOPS OUTER NESTED LOOPS OUTER MERGE JOIN SORT JOIN FILTER MERGE JOIN OUTER SORT JOIN NESTED LOOPS MERGE JOIN SORT JOIN TABLE ACCESS FULL TABLE_MOD_LEVEL SORT JOIN TABLE ACCESS FULL TABLE_PART_NUM TABLE ACCESS BY ROWID TABLE_SITE_PART BITMAP CONVERSION TO ROWIDS BITMAP MINUS BITMAP INDEX SINGLE VALUE IND_SITE_PART2PART_INFO BITMAP INDEX SINGLE VALUE IND_LEVEL_TO_BIN SORT JOIN TABLE ACCESS FULL TABLE_PRODUCTBIN SORT JOIN TABLE ACCESS FULL TABLE_SITE INDEX UNIQUE SCAN VENDOR_OBJINDEX INDEX UNIQUE SCAN VENDOR_OBJINDEX INDEX UNIQUE SCAN ADDRESS_OBJINDEX
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 Fri Nov 06 1998 - 05:31:19 CET