Re: q:Subqueries

In article <boatwright.41.05661330_at_logica.com>, boatwright_at_logica.com (Antoine Boatwright) writes:
> I am trying to do an insert using a subquery i.e.
>
> INSERT INTO table1 (field1, field2)
> SELECT field3, field 4
> FROM table2
>
> The problem is as follows. I would like to ORDER BY field3 but one cannot
> use the ORDER BY clause in a subquery according to the manual.

If you know what the smallest value of 'field3' is then you can put an index on the field and then in your select subquery you have

          WHERE field3 >= value <--- numeric

Use EXPLAIN PLAN to check that the SELECT (not the INSERT) will use your index.

Oracle should walk the index and you will get the results in the sorted order of the index.

-- 

Bruce...        pihlab_at_cbr.hhcs.gov.au

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, ACT, Australia                        (W) 06-289-7056 *
*=================================================================*
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************

"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras