Re: A Sort Problem ( ADABAS fatser than Oracle )

Is it necessary to create the COMPOSITE index rather than three separate indices:

create index i_land on orte (land);
create index i_postleitzahl on orte (postleitzahl); and
create index i_ort on orte (ort);

When you use a composite index and not all elements of the composite are present in your where clause, the entire index is scanned.

If you use three separate indexes, selects with one or more of the composite elements will still haul.

Andreas Walter wrote in message <6g074c$kj8$1_at_news.nacamar.de>...
>Hi,
>we have Oracle Version 8.0.4.0.0 on an NT. I have the follwing table
>
>Create Table Orte (
> Land varchar(3) not null,
> Postleitzahl varchar(10) not null,
> Ort varchar (25 ) not null,
> Ortsteil varchar(30) )
>
>and an Index
>
>create Index I_Land
> on Orte ( Land , Postleitzahl, Ort ).
>
>When I made the follwing SQL Statment on a client " Select * from orte "
>I have very quick an answer. But when I made the follwing Statment
>" Select * from orte order by land, postleitzahl, ort"
>I must wait some seconds.
>
>In SQL-Plus I saw that the last Statment cuased an sort on disk. But I
>don't
> unterstand why, because I have an perfect fit Index. Have anybody an idea
>why?
>And Have anybody an idea to tune the server that I don't need an index.
>
>I try the same things in an ADABAS 6 enviroment. And the answer was
 quicker.
>I find it a little bit strange because anybody told me that Oracle is so
>much better than ADABAS.
>
>thanks in advance
>
>Andreas
>
>
>
>
>
>
>
>
>
>
>
>
>
>
Received on Fri Apr 03 1998 - 00:00:00 CST