Re: Help with Optimizer!

Hi Brian,

did you try optimizer hints ?

something like

select /*+ index(<table_name> <index_name>) */ loc_id from ...

or

alter session set optimizer_goal = rule;

HTH, Toni

In article <322E463D.6B7F_at_spacestar.com>, Brian Motzer <bmotzer_at_spacestar.com> wrote:

> Help with optimizer problem!
>
> Can anyone shed some light on the following scenario?
>
> 1) We created two tables:
>
> TABLE1 TABLE2
> LOC_ID SKU_ID
> SKU_ID LOC_ID
>
> 2) We then created a view that says:
>
> Create view X
> (loc_id,sku_id) AS
> (Select LOC_ID,SKU_ID from TABLE1
> UNION ALL
> Select LOC_ID,SKU_ID from TABLE2)
>
> 3) We then created an index on LOC_ID on table1 and table2.
>
> When we select from view X where loc_id in (5,8,10,12) we get full
> table scans.
>
> When we recreate TABLE2 the same as TABLE1 and run the above query we
> get index range scans.
>
> Why are we not getting index range scans when the column order is not
> the same?
>
> This scenario is taking place on Oracle 7.2.3 on a Sun Solaris Sparc
> 2000.
>
> We know that recreating the tables in the same column order would
> resolve this
> but this is not possible because of size limitations.
>
> Thanks, please post all replies to
> tsmjbw_at_bestbuy.com
 

-- 
A. Dischner, SGI&AIX sysadmin, Oracle DBA       | Don't let friends
Institut fuer Klinische Chemie                  | use WinDose
Klinikum Grosshadern                            | Just say NO. 
Ludwig Maximilians Universitaet Muenchen,  GER  |            
Marchioninistr.15 81366 Muenchen 49-89-70953202 |