Re: HELP: How to rewrite sorted records back to a table?

jeff davidson wrote:
>
> A. Jorge Florindo wrote:
> >
> > Bill Smith wrote:
> > >
> > > Hello All,
> > > What I am trying to do is to sort an Oracle
> > > table and then write the records back to that table
> > > in the sorted order. I know that this does not
> > > make much sense, but what I have is an existing
> > > program (no source code) that just does a select
> > > with no order by clause. To make the selected
> > > records display in order I would like to sort the
> > > table and write it back. Since you cannot use the
> > > order by clause when doing a create table or create
> > > view this may be impossible to do using SQL. If
> > > anyone has any ideas please email me at
> > > smithb_at_baymont.com as I cannot keep up this these
> > > postings. I know that I could dump the table in
> > > the sorted order and then reload it using
> > > SQLLOADER but I would rather use SQL if possible.
> > >
> > > Thanks in advance,
> > > Bill Smith
> >
> rename your table and create a view that reads something like:
> create view emp as select * from emp_old order by deptno.

You can't (in 7.1) put an order by in a view But what you can do is to create an index on the order by columns.

so

- rename the table
- create a suitable index
- create a view with a where clause WHERE COLUMN > 'minimum value'.

make sure that the column does not contain any nulls otherewise these will be skipped.

The where clause will cause the index to be used and return rows in index sequence

Rgds Paul Tame. Received on Mon Sep 23 1996 - 00:00:00 CEST