Re: doubt on table scan

From: AnySQL (d.c.b.a) <anysql_at_gmail.com>
Date: 18 Oct 2005 20:27:50 -0700
Message-ID: <1129692470.956529.196060@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> 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
...... Received on Tue Oct 18 2005 - 22:27:50 CDT