Re: doubt on table scan

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 19 Oct 2005 07:20:10 +0200
Message-ID: <4355d78b$0$4717$636a15ce@news.free.fr>

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> a écrit dans le message de news: qMydnSYN1804UMjenZ2dnUVZ_tGdnZ2d_at_comcast.com...
|
|
| So it is a real bad idea to try and predict the scan order.
| Jim
|

Yes i agree, here's another test i made with the same non-indexed table and you can see the 3 successive scans display different order and the extents are displayed in "random" order, even the blocks in the extents are not displayed consecutively.

SQL> desc test
 Name Null? Type

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

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          2          5
         1          7          7          5
         2          6          2          5
         3          6          7          5
         4          7         12          5
         5          6         12          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#     -- ExtNb BlockNb BlocksInExtent
---------- ---------- ---------- ----------        ----- ------- --------------
        16          6          2          0     --   2      0          5
        14          6          4          0     --   2      2          5
        13          6          5          0     --   2      3          5
        22          7          6          0     --   0      4          5
        20          7          8          0     --   1      1          5
        10          6          8          0     --   3      2          5
        19          7          9          0     --   1      2          5
         7          6         11          0     --   3      4          5
         1          6         12          0     --   5      0          5
         0          6         13          0     --   5      1          5
         3          7         15          0     --   4      1          5
        25          7          3          0     --   0      1          5
        24          7          4          0     --   0      2          5
        23          7          5          0     --   0      3          5
        21          7          7          0     --   1      0          5
        18          7         10          0     --   1      3          5
         6          7         12          0     --   4      0          5
         5          7         13          0     --   4      1          5
         4          7         14          0     --   4      2          5
        15          6          3          0     --   2      1          5
        12          6          6          0     --   2      4          5
        11          6          7          0     --   3      0          5
         9          6          9          0     --   3      2          5
         8          6         10          0     --   3      3          5
        17          7         11          0     --   1      4          5
         2          7         16          0     --   4      4          5

26 rows selected.

SQL> /
        ID FILE# BLOCK# ROW# ---------- ---------- ---------- ----------

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

26 rows selected.

SQL> /
        ID FILE# BLOCK# ROW# ---------- ---------- ---------- ----------

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

26 rows selected.

Regards
Michel Cadot Received on Wed Oct 19 2005 - 00:20:10 CDT