Re: 2 Oracle doubts

Karsten,

Try what happens when you add an unique index on your lookup tables. The index should be a concatenated one with the column you actually want to read. Or are you using the rownum < 2 approach?

I always index often accessed lookup tables!

Check following example:

SQL> create table t as select rownum as id, name from sys.obj$ where rownum <= 50;  

Table created.  

SQL> set serverout on size 10000

SQL> 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 = mod(i, 49)+1;

 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]: 2007  

PL/SQL procedure successfully completed.  

10000 full table random scans on 50 rows take 20 secs.

In above case, the whole block is scanned, whether we have already found a match or not. Let's fix it a bit:  

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 = mod(i, 49)+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> exec p

Time [cs]: 1433  

PL/SQL procedure successfully completed.  

Same scan with searching only for first row takes over 25% less  

Let’s make a composite index now  

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

Index created.  

SQL> exec p

Time [cs]: 534  

PL/SQL procedure successfully completed.  

Huge savings about 75% with only 50 rows!

Let see what CBO thinks about tiny tables and indexes:  

SQL> analyze table t compute statistics;  

Table analyzed.  

SQL> select last_analyzed from user_indexes where index_name = 'I';  

LAST_ANALY

25.07.2003  

(Index is analyzed when computing table statistics in 9i...)  

SQL> exec p

Time [cs]: 531  

PL/SQL procedure successfully completed.  

CBO goes for index as well. Otherwise hinting or statistics faking would be possible. Or using IOT’s.  

SQL> drop table t;  

Table dropped.  

SQL> create table t (id primary key, name) organization index as select rownum as id, name from sys.obj$ where rownum <=

 50;  

Table created.  

SQL> exec p

Time [cs]: 542  

PL/SQL procedure successfully completed.  

IOT’s are as fast as indexes on tables.  

Sorry, I’m too lazy to make an example with single table hash clusters, but those were the fastest when I tested few months ago..  

Why such difference? One reason is that with full table scan segment header block (or more in case of big segment) has to be visited, to find out where the extents of segment are, but for indexes no such operation is done. You can verify it with 10046 trace, with index access you see one sequential read, with table you see one sequential and one scattered read...

Other issue with unique index might be that when first match is found, no more looking has to be done.  

Imagine which benefit you could gain with 1000 or 10000 record lookup tables..

So, happy indexing! ;)

Tanel.

"Karsten Farrell" <kfarrell_at_belgariad.com> wrote in message news:MPG.198b01e758561e9f989807_at_news.la.sbcglobal.net...
> Hi delavega, thanks for writing this:
> > Hi. I´m a MySql programmer, but i need make a application with Oracle
> > 8.1 Server. I have 2 doubts (for the moment)
> >
> > 1- Can i have a Oracle table without primary Key?
> > 2- In a sql, the date fields are with quotas ("")? Exists the
> > direct comparation between dates (<, >)?
> >
> > Thanks!
> >
>
> Several posts have asked why you want a table without a primary key. In a
> pure "data design" sense, that's a good question. However, in implementing
> a physical representation of your logical design, it's sometimes prudent
> to have a table without a primary key.
>
> For example, we have a table that contains the two-character state
> abbreviation and its spelled-out (long) name. Unless something very
> unusual happens in the USA, there are only 52 rows in this table. Of
> course, I could use the state abbreviation as the PK (since it will be
> unique), or even use a sequence, but doing so would cause Oracle to
> generate an index for it. Now I know that all 52 states will easily fit in
> an Oracle block, so the optimizer will never use the index ... which means
> I've wasted space (not a lot of space, mind you).
>
> We have other lookup tables that have things like status codes and
> descriptions. They also don't have a PK. A full table scan (in memory) on
> these small tables is not a serious performance hit. So, in conclusion,
> the answer is "it depends."
>
> --
> [:%s/Karsten Farrell/Oracle DBA/g]
Received on Fri Jul 25 2003 - 14:06:29 CDT