SQL Execution

From: Roman Gelfand <rgelfand_at_masmid.com>
Date: Thu, 5 Nov 1998 23:30:29 -0500
Message-ID: <71ttt9$du6$1@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 Thu Nov 05 1998 - 22:30:29 CST