Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: denormalize using function based indexes
In article <8hqip8$m85$1_at_nnrp1.deja.com>,
sam <roadrash_at_my-deja.com> wrote:
> Can function based indexes be used to implement
> denormalization?
>
> i have tables
> tab1 (att1 varchar2(100), att2 varchar2(200)
> att3 varchar2(200), value number);
> tab2 (value number , val2 number);
>
> select tab2.val2 from tab1, tab2
> where tab1.att1 = my_var1
> and tab1.att2 = my_var2
> and tab1.att3 = my_var3
> and tab1.value = tab2.value;
>
> Is there any way to bend function-based
> indexes in a way that gets rid of the join?
>
> I cannot denormalize the app I'm working on
> by adding tables but indexes are fair game.
> Ideas, speculations, and example code appreciated,
> --
> thanks for reading
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
well yes and no.
yes, you could, no you might not want to.
This shows you could:
ops$tkyte_at_8i> alter session set QUERY_REWRITE_ENABLED=TRUE; ops$tkyte_at_8i> alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
ops$tkyte_at_8i> create table tab1 (att1 varchar2(100), att2 varchar2(200),
2 att3 varchar2(200), value number);
Table created.
ops$tkyte_at_8i> create table tab2 (value number , val2 number); Table created.
ops$tkyte_at_8i> create or replace function get_val2( p_value in number )
return number
2 DETERMINISTIC
3 as
4 l_val2 number;
5 begin
6 select val2 into l_val2 from tab2 where value = p_value;
7
8 return l_val2;
9 exception
10 when no_data_found then return NULL;
11 end;
12 /
Function created.
ops$tkyte_at_8i> create index my_idx on tab1 ( att1, att2, att3, get_val2
(value) )
2 /
Index created.
ops$tkyte_at_8i> begin
2 for i in 1 .. 10 loop 3 insert into tab2 values ( i, i*10 ); 4 end loop;
PL/SQL procedure successfully completed.
ops$tkyte_at_8i>
ops$tkyte_at_8i> insert into tab1 values ( 'x', 'x', 'x', 1 );
1 row created.
THIS PROVES WE ARE USING THE INDEX VALUE and not calling the function. Since we nuked tab2 before we run the query *we know the data in the query below cannot be coming from tab2*. It'll also be part of the reason you probably do not want to do this....
ops$tkyte_at_8i> delete from tab2;
10 rows deleted.
ops$tkyte_at_8i> set autotrace on explain
ops$tkyte_at_8i> select /*+ INDEX( my_idx tab1 ) */ att1, att2, att3,
get_val2(value) val2
2 from tab1
3 where att1 = 'x' 4 and att2 = 'x' 5 and att3 = 'x'
ATT1 ATT2 ATT3 VAL2
---- ---- ---- -----
x x x 10
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=100) 1 0 INDEX (RANGE SCAN) OF 'MY_IDX' (NON-UNIQUE) (Cost=2 Card=1
Bytes=100)
ops$tkyte_at_8i> set autotrace off
So that used the index alone to answer the question but raises the following issues:
o in order to get any more benefit then creating an index:
my_idx tab2( value, val2 )
you must index all columns you would select (as I did, i had to put att1, att2, att3 into the index to achieve ANY benefit else I would do just as many IOs to get the answer)
o you have to promise that function get_val2 is deterministic -- in my example *I LIED*. It is not deterministic. The results therefore are "random". I *should* have gotten NULL, not 10 as the answer. If you ever update tab2, or delete from tab2 OR insert a row into tab2 AFTER the corresponding row has been inserted into tab1 -- you are going to get weird results.
-- Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://osi.oracle.com/~tkyte/index.html Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle Corp Sent via Deja.com http://www.deja.com/ Before you buy.Received on Fri Jun 09 2000 - 00:00:00 CDT