RE: Index question

From: <J.Velikanovs_at_alise.lv>
Date: Tue, 10 Aug 2004 22:44:17 +0300
Message-ID: <OF37A02D22.1DCA881E-ONC2256EEC.006C5612-C2256EEC.006C6EAC@alise.lv>

Comparing to the overhead of maintaining indexes (see test below), 2,5% is nothing.
Rick, have you considered using index compression? On the complex indexes this feature can give the significant improvement and can even compensate complex index overhead. Try:
alter index <index name> rebuild compress 1; .
.
>I wouldn't think that a 2.5% decrease in the number of index blocks
>read would justify another index. The 2.5% difference translates to
Index maintenance, insert speed:

0 index   = 7.18 sec
1 index   = 17.16 sec
3 indexes = 35.78 sec

~10 sec (~120%) overhead for each index.

Do you wont to create additional index?
Unless you are not in WH or have almost R/O table.

Juris.

SYS:XXX01> create table testidx (n1 number, n2 number, n3 number) tablespace users;
Table created.

SYS:XXX01> set timing on
SYS:XXX01> begin for f in 1..100000 loop
SYS:XXX01> insert into testidx values (f,f,f);
SYS:XXX01> end loop; commit; end;
SYS:XXX01> /

PL/SQL procedure successfully completed. Elapsed: 00:00:07.18

SYS:XXX01> truncate table testidx;
SYS:XXX01> create index testidx_n1 on testidx (n1) tablespace users;

SYS:XXX01> begin for f in 1..100000 loop
SYS:XXX01> insert into testidx values (f,f,f);
SYS:XXX01> end loop; commit; end;
SYS:XXX01> /

PL/SQL procedure successfully completed. Elapsed: 00:00:17.63

SYS:XXX01> truncate table testidx;
SYS:XXX01> create index testidx_n2 on testidx (n2) tablespace users;
SYS:XXX01> create index testidx_n3 on testidx (n3) tablespace users;

SYS:XXX01> begin for f in 1..100000 loop
SYS:XXX01> insert into testidx values (f,f,f);

SYS:XXX01> end loop; commit; end;
SYS:XXX01> / PL/SQL procedure successfully completed.

Elapsed: 00:00:35.78

On 10.08.2004 21:29:45 oracle-l-bounce wrote:

>> Thanks for your help on this. The additional column is a varchar2(32)
>data
>> type. I ran some tests too see the effect on the number of blocks
>retrieved
>> to satisfy a query. The single column required 360 blocks, whereas the
>> concatenated column required 369 blocks. Not much difference, but I
>guess
>> it all depends on the performance requirement of the application.
>>
>> Thanks,
>>
>> Rick Stephenson
>>
>I wouldn't think that a 2.5% decrease in the number of index blocks
>read would justify another index. The 2.5% difference translates to
>a smaller overall change in response time, particurlary if the query
>must also read table blocks. ie. the index alone cannot satisfy
>the query.
>
>Jared
>
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Aug 10 2004 - 14:45:49 CDT