Re: how oracle treats rows..? help

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sun, 16 Oct 2005 16:04:09 +0200
Message-ID: <43525dd7$0$4298$626a54ce@news.free.fr>

"laloo" <sreelalpp_at_gmail.com> a écrit dans le message de news: 1129364372.956050.133430_at_f14g2000cwb.googlegroups.com...
|
| hi all,
|
| can any body tell me,
| in which order oracle scans the rows in a table in normal select
| quries?
| ie, from top to bottom or bottom to top?
|
| regs,
| lal
|

SQL> desc test
 Name Null? Type

• -------- -------------- ID NUMBER VAL CHAR(2000)

No index or constraint on this table.

SQL> begin
  2 for i in reverse 0..25 loop
  3 insert into test values (i, chr(ascii('A')+i));   4 end loop;
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select extent_id, file_id, block_id, blocks from dba_extents   2 where owner=user and segment_name='TEST';  EXTENT_ID FILE_ID BLOCK_ID BLOCKS ---------- ---------- ---------- ----------

         0          7          7          5
         1          7         12          5
         2          6          2          5
         3          6         12          5
         4          7          2          5
         5          6          7          5

6 rows selected.

SQL> select id,

  2         dbms_rowid.rowid_relative_fno(rowid) file#,
  3         dbms_rowid.rowid_block_number(rowid) block#,
  4         dbms_rowid.rowid_row_number(rowid) row#

  5 from test
  6 /

        ID FILE# BLOCK# ROW# ---------- ---------- ---------- ----------

        16          6          2          0          -- Extent 2
        15          6          3          0          ----
        14          6          4          0          ----
        13          6          5          0          ----
        12          6          6          0          ----
         1          6          7          0          -- Extent 5
         0          6          8          0          ----
        11          6         12          0          -- Extent 3
        10          6         13          0          ----
         9          6         14          0          ----
         8          6         15          0          ----
         6          7          2          0          -- Extent 4
         5          7          3          0          ----
         4          7          4          0          ----
         3          7          5          0          ----
         2          7          6          0          ----
        25          7          8          0          -- Extent 0
        24          7          9          0          ----
        23          7         10          0          ----
        22          7         11          0          ----
        21          7         12          0          -- Extent 1
        20          7         13          0          ----
        19          7         14          0          ----
        18          7         15          0          ----
        17          7         16          0          ----
         7          6         16          0          -- Extent 3

26 rows selected.

Regards
Michel Cadot Received on Sun Oct 16 2005 - 09:04:09 CDT