Home » SQL & PL/SQL » SQL & PL/SQL » Problem r'ing place of inserting
Problem r'ing place of inserting [message #196986] Mon, 09 October 2006 08:11 Go to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi,

I have just a doubt i.e when we insert data into the table then the new record goes as last record but some times new record gets somewhere between the records ...

bye
ashu
Re: Problem r'ing place of inserting [message #196991 is a reply to message #196986] Mon, 09 October 2006 08:21 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
There is no 'first', 'last' nor 'in between' when we talk about tables. A table is an unsorted collection of records. It is you who provides the order by.

So, no doubt: there is no order and no guarantee where Oracle will physically write records.

What is "r'ing"?

MHE
Re: Problem r'ing place of inserting [message #196992 is a reply to message #196986] Mon, 09 October 2006 08:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid you have a fundamental misunderstanding about relational databases.

Basically, there is no such thing as a 'last row' in SQL.

When you are inserting a record, Oracle checks to see whereabouts there is space to put that row. If some rows were deleted near the start of the datafile, then it may decide to put the new row there, or it may not. You have no control over this process.

The only time you get an order to data retrieved by a query is when you add an ORDER BY statement to the end of the query. If you don't have an Order By, then there is no guarantee what order the data will come back in. For simple queries on small tables, it may well look like they come back in the order that they were inserted, but this is not to be relied on.
Re: Problem r'ing place of inserting [message #197010 is a reply to message #196986] Mon, 09 October 2006 09:40 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The data in an IOT is stored in sorted order, but that is based on a primary key and NOT so much when the data is inserted. As the others have indicated, normal heap tables are stored unsorted.
Re: Problem r'ing place of inserting [message #197013 is a reply to message #197010] Mon, 09 October 2006 09:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Fair point about IOTs.
External tables seem to have a default sort order as well (the order that the rows are in the file).
Re: Problem r'ing place of inserting [message #197050 is a reply to message #197013] Mon, 09 October 2006 13:48 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member


thanks to all of you people. You people helped to clear my point.
Re: Problem r'ing place of inserting [message #197078 is a reply to message #197013] Mon, 09 October 2006 21:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
JRowbottom wrote on Tue, 10 October 2006 00:50

Fair point about IOTs.
External tables seem to have a default sort order as well (the order that the rows are in the file).


And if we're being complete, lets not forget sorted hash clusters.
Re: Problem r'ing place of inserting [message #197115 is a reply to message #197078] Tue, 10 October 2006 02:03 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So really, in a couple of releases, it'll be a minority of tables that don't have a sort order.

I did a conversion from SQL Server recently, and it looked to me like their tables have default sort orders build into the table definition, which struck me as very odd.
Previous Topic: question on getting first date of a month
Next Topic: Question on sum up of 2 columns that created by "decode" function
Goto Forum:
  


Current Time: Wed Dec 07 14:41:05 CST 2016

Total time taken to generate the page: 0.43220 seconds