Re: doubt on table scan

"AnySQL (d.c.b.a)" <anysql_at_gmail.com> wrote in message news:1129692470.956529.196060_at_g14g2000cwa.googlegroups.com...
> See my test, before query, I offline and then online the tablespace.
>
> SQL> create tablespace SCANTEST
> 2 datafile '/oracle/data02/alex9/scantest01.dbf' size 10m,
> 3 '/oracle/data02/alex9/scantest02.dbf' size 10m
> 4 extent management local uniform size 64k
> 5 /
>
> Tablespace created.
>
> SQL> select name,file# from v$datafile;
>
> ......
> /oracle/data02/alex9/scantest01.dbf
> 13
> /oracle/data02/alex9/scantest02.dbf
> 14
>
> 14 rows selected.
>
> SQL> CREATE TABLE SKIP_TABLE1 (COL1 NUMBER, COL2 CHAR(2000)) tablespace
> scantest;
>
> Table created.
>
> SQL> CREATE TABLE SKIP_TABLE2 (COL1 NUMBER, COL2 CHAR(2000)) tablespace
> scantest;
>
> Table created.
>
> SQL> CREATE TABLE SKIP_TABLE3 (COL1 NUMBER, COL2 CHAR(2000)) tablespace
> scantest;
>
> Table created.
>
> SQL> CREATE TABLE SCANTEST (COL1 NUMBER, COL2 CHAR(2000)) tablespace
> scantest;
>
> Table created.
>
> SQL> select EXTENT_ID,FILE_ID,BLOCK_ID FROM DBA_EXTENTS WHERE
> SEGMENT_NAME='SCANTEST' ORDER BY EXTENT_ID;
>
> EXTENT_ID FILE_ID BLOCK_ID
> ---------- ---------- ----------
> 0 14 33
>
> SQL> DROP TABLE SKIP_TABLE1;
>
> Table dropped.
>
> SQL> ALTER TABLE SCANTEST ALLOCATE EXTENT;
>
> Table altered.
>
> SQL> ALTER TABLE SCANTEST ALLOCATE EXTENT;
>
> Table altered.
>
> SQL> select EXTENT_ID,FILE_ID,BLOCK_ID FROM DBA_EXTENTS WHERE
> SEGMENT_NAME='SCANTEST' ORDER BY EXTENT_ID;
>
> EXTENT_ID FILE_ID BLOCK_ID
> ---------- ---------- ----------
> 0 14 33
> 1 13 9
> 2 14 9
>
> SQL> ALTER TABLE SCANTEST ALLOCATE EXTENT;
>
> Table altered.
>
> SQL> select EXTENT_ID,FILE_ID,BLOCK_ID FROM DBA_EXTENTS WHERE
> SEGMENT_NAME='SCANTEST' ORDER BY EXTENT_ID;
>
> EXTENT_ID FILE_ID BLOCK_ID
> ---------- ---------- ----------
> 0 14 33
> 1 13 9
> 2 14 9
> 3 13 17
>
> SQL> INSERT INTO SCANTEST SELECT ROWNUM,'SCAN TEST' FROM DBA_OBJECTs
> where rownum < 201;
>
> 200 rows created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> ALTER TABLESPACE SCANTEST OFFLINE;
>
> Tablespace altered.
>
> SQL> alter tablespace scantest online;
>
> Tablespace altered.
>
> SQL> alter session set events = '10046 trace name context forever,
> level 12';
>
> Session altered.
>
> SQL> select col1 from scantest;
>
> ...
>
> SQL> alter session set events = '10046 trace name context off';
>
> Session altered.
>
> SQL Trace's result:
>
> WAIT #1: nam='db file sequential read' ela= 67 p1=14 p2=33 p3=1 //
> Segment Header
> WAIT #1: nam='db file scattered read' ela= 300 p1=14 p2=34 p3=7 //
> First Extent
> WAIT #1: nam='db file scattered read' ela= 162 p1=13 p2=9 p3=8 //
> Second Extent
> WAIT #1: nam='db file scattered read' ela= 197 p1=14 p2=9 p3=8 //
> Third Extent
> WAIT #1: nam='db file scattered read' ela= 193 p1=13 p2=17 p3=8 //
> Forth Extent
> ......
>

Don't rely on this it won't always work. Inserted rows are not always inserted into the table in block order (and hence extent order). For example if your rows are of different size (highly likely) then the rows could be inserted differently. For example: Lets say the block size is 8K and you have 5 extents allocated without any data. Let us further assume that you insert rows like this insert row 7K
insert row 7K
insert row 7K
insert row 30 bytes
insert row 7K
insert row 30 bytes.

What will probably happen is that 1st row will go like: row block

1        1
2        2
3        3
4         1
5        4
6        1

Also extents are not always allocated 1st in one file then in the net etc. In LMT Auto extent allocation this is true but in LMT uniform extents in 9i at least it wasn't. (it would fill up one file before using the 2nd one)

Now, if we add deletions to the mix then you are changing the freelist (which blocks are on the free list and which are not)

So it is a real bad idea to try and predict the scan order. Jim Received on Tue Oct 18 2005 - 23:36:54 CDT