SQL & PL/SQL

Consider the following test case which I've tried on Oracle 8.1.7 and 9i. I'm attempting to normalize a denormalized table which has several columns of like data (data1,data2, data3,etc) by using a union and then making a view out of the union. I find that the index on the original denormalized table is ignored when I do a join to another table on the indexed field. However explicit expressions to a literal do use the view. The example should be self-explanatory, so allow me to begin:



create table moo (
farm varchar2(8),
cow_1 number,
cow_2 number,
cow_3 number,
cow_4 number,
cow_5 number,
cow_6 number,
cow_7 number,
cow_8 number
)

create index moo_i on moo (farm)
/

insert into moo values ('WISCO',1,2,3,4,5,6,7,8)
/
insert into moo values ('DISCO',1,2,3,4,5,6,7,8)
/
insert into moo values ('CISCO',1,2,3,4,5,6,7,8)
/


create table farm (
farm varchar2(8),
state varchar2(2)
)
/
insert into farm values ('WISCO','WI')
/
insert into farm values ('DISCO','NV')
/
insert into farm values ('CISCO','CA')
/


create or replace view moo_view as (
select farm, cow_1 cow from moo
union all
select farm, cow_2 from moo
union all
select farm, cow_3 from moo
union all
select farm, cow_4 from moo
union all
select farm, cow_5 from moo
union all
select farm, cow_6 from moo
union all
select farm, cow_7 from moo
union all
select farm, cow_8 from moo
)
/
---- Try the join and print the explain plan

select from moo_view a, farm b
where a.farm = b.farm


SELECT STATEMENT, GOAL = CHOOSE Cost=5 Cardinality=656 Bytes=18368
HASH JOIN Cost=5 Cardinality=656 Bytes=18368
TABLE ACCESS FULL Object owner=CRI Object name=FARM Cost=2 Cardinality=82 Bytes=738
VIEW Object owner=CRI Object name=MOO_VIEW Cost=2 Cardinality=656 Bytes=12464
UNION-ALL
TABLE ACCESS FULL Object owner=CRI Object name=MOO Cost=2 Cardinality=82 Bytes=1558
TABLE ACCESS FULL Object owner=CRI Object name=MOO Cost=2 Cardinality=82 Bytes=1558
TABLE ACCESS FULL Object owner=CRI Object name=MOO Cost=2 Cardinality=82 Bytes=1558
TABLE ACCESS FULL Object owner=CRI Object name=MOO Cost=2 Cardinality=82 Bytes=1558
TABLE ACCESS FULL Object owner=CRI Object name=MOO Cost=2 Cardinality=82 Bytes=1558
TABLE ACCESS FULL Object owner=CRI Object name=MOO Cost=2 Cardinality=82 Bytes=1558
TABLE ACCESS FULL Object owner=CRI Object name=MOO Cost=2 Cardinality=82 Bytes=1558
TABLE ACCESS FULL Object owner=CRI Object name=MOO Cost=2 Cardinality=82 Bytes=1558

-- try an INDEX hint ... could not get it to honor this... same result
select /*+index(moo moo_i)*/* from moo_view a, farm b
where a.farm = b.farm


-- try and explicit equality to a literal... poof it uses the index
select * from moo_view a, farm b
where a.farm = 'CISCO'

SELECT STATEMENT, GOAL = CHOOSE
NESTED LOOPS
TABLE ACCESS FULL Object owner=CRI Object name=FARM
VIEW Object owner=CRI Object name=MOO_VIEW
UNION-ALL
TABLE ACCESS BY INDEX ROWID Object owner=CRI Object name=MOO
INDEX RANGE SCAN Object owner=CRI Object name=MOO_I
TABLE ACCESS BY INDEX ROWID Object owner=CRI Object name=MOO
INDEX RANGE SCAN Object owner=CRI Object name=MOO_I
TABLE ACCESS BY INDEX ROWID Object owner=CRI Object name=MOO
INDEX RANGE SCAN Object owner=CRI Object name=MOO_I
TABLE ACCESS BY INDEX ROWID Object owner=CRI Object name=MOO
INDEX RANGE SCAN Object owner=CRI Object name=MOO_I
TABLE ACCESS BY INDEX ROWID Object owner=CRI Object name=MOO
INDEX RANGE SCAN Object owner=CRI Object name=MOO_I
TABLE ACCESS BY INDEX ROWID Object owner=CRI Object name=MOO
INDEX RANGE SCAN Object owner=CRI Object name=MOO_I
TABLE ACCESS BY INDEX ROWID Object owner=CRI Object name=MOO
INDEX RANGE SCAN Object owner=CRI Object name=MOO_I
TABLE ACCESS BY INDEX ROWID Object owner=CRI Object name=MOO
INDEX RANGE SCAN Object owner=CRI Object name=MOO_I


is there any way to fix the join to use the index? oh... and I know having a denormalized table liek that is sort of silly, but you should see what these vendors throw at us sometimes.

Thanks a ton!

Mark Haller
mhaller@austinisd.org
Austin Independent School District