Re: 2 Oracle doubts

Tanel Poder wrote:
>
> 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;<?xml:namespace prefix = o ns =
> "urn:schemas-microsoft-com:office:office" />
>
>
>
> 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]

Exactly...

My mantra is 'if the definition of a "small table" is one that is so small that it does not require to be indexed to query a one of its rows, then there is no such thing as a small table"

:-)

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"