Re: 2 Oracle doubts

> If I remember correctly, IOTs are only available in the Enterprise
> Edition. On our development box, we use the Standard Edition (was that way
> before I came on board). We don't want one kind of schema on our
> development and another on our QA and prod. Flimsy excuse, I know, but
> there you have it...

Since lookup tables are relatively small and non-volatile a regular table with composite index does very well. Or single table hash cluster.

Btw, Karsten, although I understand your point about lookup tables having different characteristics than 'normal' tables, I did an additional test:

Let's create a 50 row table:
SQL> create table t as select rownum as id, name from sys.obj$ where rownum <= 50;

Table created.

SQL> create or replace procedure p
  2 is
  3 v char(30);
  4 i number;
  5 t number;
  6 begin
  7 t:=dbms_utility.get_time;
  8 for i in 1..100000 loop
  9 select name into v from t where id = 1 and rownum < 2;  10 end loop;
 11 dbms_output.put_line('Time [cs]: ' || to_char(dbms_utility.get_time - t));  12 end;
 13 /

Procedure created.

SQL>
SQL> exec p
Time [cs]: 521

PL/SQL procedure successfully completed.

First procedure searched for 1st value in block using FTS (Result: 521 csec) Now let's create an index:

SQL> create index i on t(id, name);

Index created.

SQL> create or replace procedure p
  2 is
  3 v char(30);
  4 i number;
  5 t number;
  6 begin
  7 t:=dbms_utility.get_time;
  8 for i in 1..100000 loop
  9 select name into v from t where id = 25 and rownum < 2;  10 end loop;
 11 dbms_output.put_line('Time [cs]: ' || to_char(dbms_utility.get_time - t));  12 end;
 13 /

Procedure created.

SQL> exec p
Time [cs]: 421

This procedure searched for value 25 *with* index, and did it ca 19% faster than FTS.

PL/SQL procedure successfully completed.

SQL> (I actually ran them multiple times to get averages)

I think the issue is that scanning the contents of a block is much cheaper than getting the block (consistent get). Index access produces much less consistent gets than table acess as autotrace showed me (even though automatic segment space management made results a bit obscure)

Tanel. Received on Fri Jul 25 2003 - 17:27:40 CDT