Re: SQL Execution

Roman Gelfand wrote:

> 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.
>
> 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,
> ....
> 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,

  According to Oracle SQL High-Performance Tuning, by Guy Harrison, Prentice Hall Books, pg 118, :
"Oracle will not employ an index if the NOT EQUALS operator (!=) is employed. This is generally sensible, because when retrieving all rows except for those matching a single value, a full table scan is usually the fatest way of retrieving the data."

He suggests rephrasing the query. For example, instead of saying != 999, you could say

AND ( table_site_part.level_to_bin = 12 OR table_site_part.level_to_bin = 21 OR table_site_part.level_to_bin = 34 .....)

 I assume that it won't be too much typing if the cardinality is low enough for a bit-map. Received on Tue Nov 10 1998 - 21:14:36 CST