insert statement using order by
Stephen Pillow
I would like to be able to take data from one table and put the data in
another table sorted. I have attempted to use the "insert into tablename
(select * from othertable order by desc)". However, Oracle 8 and 8i do not
support this(it is a bug). Oracle did support the use of order by in a sub
query in release 7.3.4.
I am looking for a solution that does not involve exporting the table data
to file sorted and then loading it using sql loader.
Thanks for your help,
Stephen
rtpro...@my-deja.com
depend on what order your data is stored in,
so inserting into a table with an order by
clause doesn't make sense...
You would use Order by in your views or
select statements to guarantee the order
of your data...
In fact, I have seen Oracle insert my
individual insert statements into a
different order than I had inserted them, so
one cannot trust the physical storage
to order the data.....
Robert Proffitt
Beckman Coulter
Sent via Deja.com http://www.deja.com/
Before you buy.
Dave
>another table sorted.
Sorry, I don't have an answer to your question. However I am interested as
to *why* you would be concerned about the order in which the data is being
stored.
dave
Mark Malakanov
If you will not insert duplicated rows, you can use <DISTINCT> word which
bring Oracle to sort rows, but duplicates will killed. Or <group by> also.
insert into T select distinct id, name ftom T0;
insert into T select id, name ftom T0 group by id, name;
However Oracle does not guarant phisical order of rows in segment. Oracle
will insert row into first free space is find. You can use suppress
inserting using hint "APPEND". In this case Oracle will ignore any free
space lower highwatermark and will append rows into new blocks created above
highwatermark.
insert /*+APPEND*/ into T select distinct id, name ftom T0;
Note, this approach - phisically ordered tables - is acquittaled in case you
will create index on this table. When you will create index on columns where
all row values presorted you can use <NOSORT> word. Benefit is in that
Oracle will not make sorting.
create index IT1 on T(id) nosort;
create index IT2 on T(id,name) nosort;
This approach have sence for big tables.
If you interested in "ordered" tables, take a look on the index-organized
tables in Oracle8.
This stored whole data of raw in a primary key based on B-tree index. So you
will have no "dual" logical reads (index-table) when select from the
index-organized tables. Only index will being accessed. But you cant have
other indexes on such tables because this have no ROWID.
Sorry, I have not sight your "order by DESC" in question :^).
You can use
insert /*+append*/ into backT
select -di, translate(eman,
'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'ZYXWVUTSRQPONMLKJIHGFEDCBA'),
from (select distinct -id di, translate(name,
'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'ZYXWVUTSRQPONMLKJIHGFEDCBA') eman
from T)
But I'm only wandering for what cause do you need this? May be there is
something wrong in a database design?
Mark Malakanov,
OraDBA
Sapience, Toronto
Stephen Pillow <stephen...@i2.com> wrote in message
news:_WqM3.3825$7K4....@typ11a.deja.bcandid.com...
> Hi all,
>
> I would like to be able to take data from one table and put the data in
jarichter
unlike the previous reply....I don't care why you want to insert in
order...that is your business the easiest way I would see to do it would be
with a PL/SQL block or Procedure such as:
DECLARE
commit_counter PLS_INTEGER (3) :=0;
CURSOR get_em IS
SELECT *
FROM othertable
ORDER BY desc;
BEGIN
FOR ins_rows IN get_em
LOOP
INSERT INTO table_name VALUES (ins_rows.col1, ins_rows.col2);
IF commit_counter > 500 THEN
COMMIT;
commit_counter :=0;
ELSE
commit_counter := commit_counter +1;
END IF;
END LOOP;
END;
/
However, after this you will need to rebuild any indexes on the table
because they will be severly unbalanced and thus slow.
Regards,
Jack
Bob Fazio
The point is no matter if you do or don't insert in a sorted order Oracle
won't guarentee that it will store it that way. So when you select you may
get another order.
It will most often work that if you insert in a certain order, Oracle will
store it that way. But it can't
be counted on. So don't bother expecting that it will work.
jarichter <jaric...@freezone.co.uk> wrote in message
news:380393d0.0@news1...
Stephen Pillow
I am trying to accomplish the following:
1. Sort data in a table
2. Select rows 50 - 100 of the sorted data
3. Return only rows 51-100 to the app server
I want the sorting to take place on the database and I do not want to return
all rows to the app server.
We are using JDBC and oracle 8i.
Here is the path I was heading down when I sent the previous question.
Oracle 8i has temporary tables. The temporary tables can work so that each
process inserting(for this example) data into the temporary table does so in
its own memory space. Once commit() is called, the data that was inserted
is removed from the temporary table.
Example,
User A requests to see rows 51-100 of the data sorted by name.
User B requests to see rows 201-250 of the data sorted by description.
They perform these requests at exactly the same time.
This is the order of events:
1. The data from the physical table is inserted into the temporary table
sorted and a field containing the row number is added.
2. The appropriate range of rows is selected (i.e. 51-100) and returned
to the app server
3. Commit is called and the sorted data is removed.
There are no concurrency issues because each process that performs the sort
and select operates in its own space. This is how User A and User B can
request different sort criteria and range of rows and get the appropriate
response.
However, as you guys have informed me, even if I could insert data into the
temporary table sorted, Oracle does not guarantee to store it that way.
So, any suggestions on how to accomplish this would help. I believe the
answer probably deals with using cursors.
Thanks,
Stephen
Thomas Kyte
(if that email address didn't require changing)
On Wed, 13 Oct 1999 09:49:26 -0500, you wrote:
>This explains why I was trying to insert using order by
>
>I am trying to accomplish the following:
>
>1. Sort data in a table
>2. Select rows 50 - 100 of the sorted data
>3. Return only rows 51-100 to the app server
>
>I want the sorting to take place on the database and I do not want to return
>all rows to the app server.
>
>We are using JDBC and oracle 8i.
>
>Here is the path I was heading down when I sent the previous question.
>
>Oracle 8i has temporary tables. The temporary tables can work so that each
>process inserting(for this example) data into the temporary table does so in
>its own memory space. Once commit() is called, the data that was inserted
>is removed from the temporary table.
>
>Example,
>
>User A requests to see rows 51-100 of the data sorted by name.
>User B requests to see rows 201-250 of the data sorted by description.
>
>They perform these requests at exactly the same time.
>
>This is the order of events:
>1. The data from the physical table is inserted into the temporary table
>sorted and a field containing the row number is added.
>2. The appropriate range of rows is selected (i.e. 51-100) and returned
>to the app server
>3. Commit is called and the sorted data is removed.
>
consider this:
scott@8i> select ename, job, sal from emp order by 1, 2, 3
2 /
ENAME JOB SAL
---------- --------- ----------
ADAMS CLERK 1100
ALLEN SALESMAN 1600
BLAKE MANAGER 2850
CLARK MANAGER 2450
FORD ANALYST 3000
JAMES CLERK 950
JONES MANAGER 2975
KING PRESIDENT 5000
MARTIN SALESMAN 1250
MILLER CLERK 1300
SCOTT ANALYST 3000
SMITH CLERK 800
TURNER SALESMAN 1500
WARD SALESMAN 1250
14 rows selected.
scott@8i> select * from
2 ( select rownum r, a.* from ( select ename, job, sal
3 from emp
4 order by ename, job, sal ) a
5 where rownum <= 10 )
6 where r >= 5
7 /
R ENAME JOB SAL
---------- ---------- --------- ----------
5 FORD ANALYST 3000
6 JAMES CLERK 950
7 JONES MANAGER 2975
8 KING PRESIDENT 5000
9 MARTIN SALESMAN 1250
10 MILLER CLERK 1300
6 rows selected.
That shows how to get rows 5-10 of a sorted result set IN ORACLE8i release 8.1
(this will *not* work in prior releases).
>There are no concurrency issues because each process that performs the sort
>and select operates in its own space. This is how User A and User B can
>request different sort criteria and range of rows and get the appropriate
>response.
>
>However, as you guys have informed me, even if I could insert data into the
>temporary table sorted, Oracle does not guarantee to store it that way.
>
>So, any suggestions on how to accomplish this would help. I believe the
>answer probably deals with using cursors.
>
>Thanks,
>Stephen
>
>
>
>> Hi all,
>>
>> I would like to be able to take data from one table and put the data in
>> another table sorted. I have attempted to use the "insert into tablename
>> (select * from othertable order by desc)". However, Oracle 8 and 8i do
>not
>> support this(it is a bug). Oracle did support the use of order by in a
>sub
>> query in release 7.3.4.
>>
>> I am looking for a solution that does not involve exporting the table data
>> to file sorted and then loading it using sql loader.
>>
>> Thanks for your help,
>> Stephen
>>
>>
>>
>
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tk...@us.oracle.com
Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Jonathan Lewis
Is there any reason why you can't store the counter
in the temporary table, and use it in the JDBC
e.g.
create global temporary glob_temp1 (
id_seq number,
etc.
);
insert into glob_temp1 (
id_seq,
etc.....
)
select rownum, t.*
from (
your select statement with order by
) t
;
This should work in 8.1 which allows order by in
views, hence in-line views.
Then you can do:
select * from glob_temp
where id_seq between{M} and {N}
;
(PS - I've just tested this on 8.1.5, which version of
8.1 are you running ? -- The bug is that it's in 7.3,
it wasnt' supposed to exist until 8.1).
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
Stephen Pillow wrote in message
dougs...@my-deja.com
could see then why you may want to insert in sorted order?
Lars Lerager
Stephen Pillow <stephen...@i2.com> wrote in message
news:_WqM3.3825$7K4....@typ11a.deja.bcandid.com...
> Hi all,
>
> another table sorted. I have attempted to use the "insert into tablename
> (select * from othertable order by desc)". However, Oracle 8 and 8i do
not
> support this(it is a bug).
[CUT]
I tried to do the same thing with my University database (Oracle 8.0.5
running on HP-UX) Didn't work!
Later i tried it at home with Oracle 8i EE (8.1.5 running on Windows NT) -
Worked out fine!
Go figure
Lars
Wise Guy
guarentee that the inserted data will stay in the same order all the time.
Lars Lerager wrote in message ...
rtpro...@my-deja.com
I believe Thomas was on the right track when he described
using In-line tables. The problem is that ORDER BY
cannot be used earlier than Oracale 8i (8 or 8i ???).
I am routinely using rownum in some List-Of-Values
algorithms for Forms, and it works fine...I get around
the Order By problem by using Group By with enough
columns to guarantee single row uniqueness.
As long as you remember that the physical order cannot
be guaranteed, and that all query statements which involve
rownum will need to account for the sort order, then
you'll be fine using In-line tables, I think...
In your example, two users request two different selects
at the same time...Therefore you cannot use a solution
where the table is physically sorted...it must be
a virtual solution, such as: two different kinds of
select statements....
Consider the general case:
Select rownum as MyRow, a,b,c,d
From
(select a,b,c,d
from tbla (...,tblb, tblc..etc)
Where ...etc...
Group by
a,b,x,y...etc. enough to be unique to row
(order by in Oracle 8)
) WrkTbl
Where rownum>= beginning_value
And rownum <= ending_value
Order by d,e,f ...optional rearragement of output
The inner table gathers the data, sorted (in this
case grouped) a particular way...
For user A, it would be
Group by Name, b,c,d... etc to make uniqueness
For user B, it would be
Group by Description, b,c,d...etc.
The outer select adds rownum, which is the
rownum of the sorted inner result thus:
User A's data would be (name, description):
1, Allen ,Roof
2, Billings ,House
3, Smith ,Garage
while
User B's data would be (name, description)
1, Smith, ,Garage
2, Billings ,House
3, Allen ,Roof
Your trick will be how to organize this into
an easy view or query for all users to use...
I don't know if this is faster or slower than
Thomas' method... just written differently..
Good Luck,
Robert Proffitt
Beckman Coulter
Brea California