Re: Tricky SQL Problem: Top "n" of queried records

larionov_at_yahoo.com wrote:

> try then:
> select distinct object_id, object_name from all_objects
> where ROWNUM < 4
> order by object_id

Got the same result as below.

> I think, Oracle does some sorting when you are using the
> keyword "distinct"

ALL_OBJECTS is a view, so it's difficult to know exactly what the optimizer is doing, since it will combine the query above with the query in the view. In any case, we shouldn't really depend on implicit ordering, because those things tend to change from release to release. For example, selects being returned in order when using an ordered index are not returned in order when the table is partitioned.

> By the way, is there an index on object_id?
>

Yes. OBJ$.OBJ# has an index on it.

> Michael.

Thanks,

Brenda.

>
>
> In article <6f95kq$jhk$1_at_nnrp1.dejanews.com>,
> >
> > In Oracle, this session:
> >
> > SQL> select object_id, object_name from all_objects
> > 2> where ROWNUM < 4
> > 3> order by object_id
> >
> > returns:
> >
> > OBJECT_ID OBJECT_NAME
> > ---------- ---------------
> > 78 ACCESS$
> > 934 ALL_ARGUMENTS
> > 984 ALL_ALL_TABLES
> >
> > very quickly - also subsecond, but it does not represent the correct
 dataset.
> > The first three object_id values should be 3, 4, and 5.
> >
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Wed Mar 25 1998 - 00:00:00 CET