Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: denormalize using function based indexes

Re: denormalize using function based indexes

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/09
Message-ID: <8hru66$ob0$1@nnrp1.deja.com>#1/1

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;

  5 end;
  6 /

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'

  6 /

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US