OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » select order

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

select order [message #258352]

Sat, 11 August 2007 05:04

alantany
Messages: 115
Registered: July 2007

Senior Member

Hi! all:
Could someone tell me in what order of the rows when I　do a full table scan like:
select * from t;
I guess it is by rowid order,am I correct?
Regards.
Alan

[Updated on: Sat, 11 August 2007 05:05]

Report message to a moderator

Re: select order [message #258356 is a reply to message #258352]

Sat, 11 August 2007 05:18

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

unless you add an order by clause there is no order

Report message to a moderator

Re: select order [message #258359 is a reply to message #258352]

Sat, 11 August 2007 05:29

Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India

Senior Member

I think, Data would be scaned in the order in which they are stored physically in datafile in case of full table scan..

Let us know if you reach to the confirmed final answer..

ragards..
dipali

Report message to a moderator

Re: select order [message #258365 is a reply to message #258359]

Sat, 11 August 2007 05:58

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No, this is not that.
There is no order, neither logical nor physical.
Moreover, you can issue several times the same query and get several different orders.

Regards
Michel

Report message to a moderator

Re: select order [message #258369 is a reply to message #258352]

Sat, 11 August 2007 06:04

Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India

Senior Member

hello michel..
but what does internally done during full tablescan?

as per i think,
the physical link of first datablock is stored within database which again contain the address of next data block..
and the data would be physically accessed in this order..

plz, correct me..

Report message to a moderator

Re: select order [message #258372 is a reply to message #258369]

Sat, 11 August 2007 06:16

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The only thing you can say is that all blocks (below the HWM) are scanned.
The algorithm depends on many things like Oracle version tablespace type, segment management type, parallelism degree, partitioning, table type (heap, iot)...

Btw, blocks are not linked.

Regards
Michel

Report message to a moderator