Oracle学习笔记（六）

drop table t purge;
create table t as select * from dba_objects;
create index idx_object_id on t(object_id);
set linesize 1000
set autotrace traceonly 

--方法1（全表扫描）
select /*+full(t)*/ * from t where object_id=2;

--方法2（索引扫描）
select * from t where object_id=2;


--方法3（rowid扫描）
set autotrace off 
select rowid from t where object_id=2;

set autotrace traceonly 
select * from t where object_id=2 and rowid=‘AAAYiZAALAAAADLAAw‘;


SQL> select /*+full(t)*/ * from t where object_id=2;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   207 |   291   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |   207 |   291   (1)| 00:00:04 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1044  consistent gets

          

SQL> select * from t where object_id=2;
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T             |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
       


SQL> select * from t where object_id=2 and rowid=‘AAAYiZAALAAAADLAAw‘;
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |   219 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY USER ROWID| T    |     1 |   219 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets

---启动大小为2K的块新建表空间(WINDOWS下只能使用2K,4K，8K和16K　　
alter system set db_2k_cache_size=100M;
drop tablespace tbs_ljb_2k including contents and datafiles;
create tablespace TBS_LJB_2k 
blocksize 2K
datafile  ‘D:\ORACLE\ORADATA\TEST11G\TBS_LJB_2K_01.DBF‘ size 100M  
autoextend on  
extent management local 
segment space management auto;
create table t_2k tablespace tbs_ljb_2k as select * from dba_objects;

---启动大小为4K的块新建表空间
alter system set db_4k_cache_size=100M;
drop tablespace tbs_ljb_4k including contents and datafiles;
create tablespace TBS_LJB_4k 
blocksize 4K
datafile  ‘D:\ORACLE\ORADATA\TEST11G\TBS_LJB_4K_01.DBF‘ size 100M  
autoextend on  
extent management local 
segment space management auto;
create table t_4k tablespace tbs_ljb_4k as select * from dba_objects;


---启动大小为8K的块新建表空间(默认就是8K)

drop table t_8k purge;
create table t_8k as select * from dba_objects;

---启动大小为16K的块新建表空间
alter system set db_16k_cache_size=100M;
drop tablespace tbs_ljb_16k including contents and datafiles;
create tablespace TBS_LJB_16k 
blocksize 16K
datafile  ‘D:\ORACLE\ORADATA\TEST11G\TBS_LJB_16K_01.DBF‘ size 100M  
autoextend on  
extent management local 
segment space management auto;
create table t_16k tablespace tbs_ljb_16k as select * from dba_objects;


-----------------------------------------------------------------------------------------
--开始试验，发现代价和逻辑读都是以此变少！

SET autotrace traceonly

select count(*) from t_2k;
/

select count(*) from t_4k;
/

select count(*) from t_8k;
/

select count(*) from t_16k;
/

--但是也不是块越大越好，要注意热点块竞争。

SQL> select count(*) from t_2k;
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   891   (1)| 00:00:11 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T_2K | 83292 |   891   (1)| 00:00:11 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4511  consistent gets
        

SQL> select count(*) from t_4k;
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   480   (1)| 00:00:06 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T_4K | 63139 |   480   (1)| 00:00:06 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2137  consistent gets        

SQL> select count(*) from t_8k;
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   291   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T_8K | 62320 |   291   (1)| 00:00:04 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1043  consistent gets
             
SQL> select count(*) from t_16k;
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |   200   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T_16K | 80144 |   200   (1)| 00:00:03 |
--------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        517  consistent gets