oracle访问数据的方法

访问数据的方法：
访问表：
      全表扫描：从表所在的第一个extend的第一个block开始，扫描到高水位。全表扫描的产生的等待事件是数据库多块读、直接路径读等（这就不多说了），想说的是，
              全表扫描是一次扫描多个数据块，而具体是一次扫描多少块，是由参数db_file_multiblock_read_count控制的。全表扫描并不一定是问题，但是全表扫描对
              资源的消耗和时间是不可控制的，他会随着表数据量的增加而增加，同时，全表扫描还会受到高水位的影响，比如：一个表经常delete，但是delete不会降低高水位，
              再有新的数据insert的时候，很多块就用不上了，也就是说，存在很多数据库都没有数据，但是全表扫描会扫高水位以下的所有块。就酱。解决高水位的办法呢，
              有这么几种。
              1，使用truncate，这是 清空全表的数据，使用这个的时候需要先确认是不是存在依赖。truncate不写日志，常规的方法无法恢复（odu可以），他其实也并不是
              降低了高水位，而是改变了段的指针，把段只想了一个新的高水位为0的段的开头。
              2，move，move的原理就是新建一张表，把原来的数据写到这张表里，然后再删除原来的表。这就要求相应的表空间，至少要有不小于原表大小的空间可用。
              后面不加表空间，默认是当前表空间。
              scott@ORA> alter table t1 move;


              Table altered.
              scott@ORA> select tablespace_name from dba_tablespaces;


TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
DBADATA

7 rows selected.

scott@ORA> alter table t1 move tablespace dbadata;

Table altered.
move完之后要重建索引
3，shrink,这个需要先打开一个参数。
scott@ORA> alter table t1 enable row movement;


Table altered.

scott@ORA> alter table t1 shrink space;

Table altered.
move和shrink的区别在于：
move：之后，rowid都会改变，move是以block为单位，进行移动，block的位置会变，所以需要重建索引，move产生的日志也较少。
shrink：是移动其中的一部分数据，所以一部分rowid会变，但是过程中，shrink会维护所以。索引不需要重建。

另外，在插入数据的时候呢，有的时候会加一个hint   /*+append*/，这个提示呢，是告诉oracle，你就从我高水位之后开始插入把，别从前面去找可用空间了。
我个人理解，ctas表的时候用这个比较好。

      rowid扫描（首先，rowid分为物理，逻辑，这不多说）
              我们都知道一个数据库中rowid是唯一的，那他为什么是唯一的呢？
              rowid由四部分组成：object_id,datafile_id,block_id,在block中的行id。
              rowid扫描，就是在查询的时候以rowid为过滤条件;或者索引访问的时候回表
              scott@ORA> select empno,rowid from emp where rowid='AAAaagAAEAAAACUAAA';


    EMPNO ROWID
---------- ------------------
     7369 AAAaagAAEAAAACUAAA


Execution Plan
----------------------------------------------------------
Plan hash value: 1116584662

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |    25 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY USER ROWID| EMP  |     1 |    25 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------


      
访问索引（不一定需要回表）再回表
在这说的是用的最多的b-tree索引，：这个索引包括，索引分支块和索引叶子块。索引扫描是单块读（快速索引全扫例外）
分支块：存的两种指针，一种是lcm，一种是行记录指针，通过lmc找到对应叶子块，通过行记录指针，找到对应的记录。但是并不一定分支块包含了指针的全部，比如一个索引特别的大
       这时候呢，可能一层的分支块存的只是一部分，通过这部分找到他的下一层分支，然后才会找到叶子块，这也就是blevel。
叶子块；存的是rowid和列值（唯一索引不存rowid），以及键值，也就是说，叶子块，才是真正存放着列数据的部分，而叶子块到跟节点的距离基本是一致的，所以访问叶子块的每一个
       键值的时间几乎是相同的。同时呢，叶子块，是有序的，两边是互相关联的。
   通常情况下，通过索引进行访问，成本和时间是可控的，就是说，通过索引访问的效率不会随表数据量的增加而增加（这说的不包括索引全扫，快速索引全扫，在这不说选择性，
   这是相对全表扫描来说的，如果非得扯什么数据倾斜，返回结果集多之类的情况，那就没意思了）
      索引唯一扫：这个针对的是唯一性索引来说的。嗯，反正这种扫描非常好。
      索引范围扫：索引范围扫适用于类型的b-tree索引，但是即使你每个值都是唯一的，索引却不是唯一索引。那么，范围扫的代价至少也会比唯一扫要大1的
      索引全扫：索引全扫，需要说的是，并不是扫描索引所有的块，他是需要扫描所有的叶子块，但分支块不一定，只需要找到索引开始的那个叶子块的第一条数据，然后通过叶子块
              的互相联系就可以扫描所有的叶子块了。同时索引全扫返回的结果集是有序的。              
      索引快速全扫：索引快速全扫看似只多了一个快速，区别如下：
               1，索引全扫是单块读，快速的是多块
               2，索引全扫不需要扫描所有分支块；快速全扫是从段头开始，扫描所有的根节点，所有的分支块，所有的的叶子块
               3，索引全扫是有序的；快速全扫是无序的。
      索引跳扫：索引跳扫是针对复合索引来说的，当不使用复合索引的前导列时，就有可能发生索引跳扫，那为什么会发生索引跳扫呢？因为oralce会把你的前导列全部过滤一遍。
              测试如下：
    scott@ORA> BEGIN
 2    FOR i IN 1 .. 99999
 3    LOOP
 4      INSERT
 5      INTO test VALUES
 6        (
 7          CASE
 8            WHEN i<=30000
 9            THEN 'a'
10            WHEN i>30000
11            AND i<=60000
12            THEN 'b'
13            ELSE 'c'
14          END,
15          i
16        );
17    END LOOP;
18  END; 
19  /

PL/SQL procedure successfully completed.

scott@ORA> commit;

Commit complete.



scott@ORA> create index idx_test_s_n on test(s,n);

Index created.

scott@ORA> exec dbms_stats.gather_table_stats('scott','test');

PL/SQL procedure successfully completed.  

scott@ORA> select * from test where n=9999;


S          N
- ----------
a       9999


Execution Plan
----------------------------------------------------------
Plan hash value: 3325193863

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |     7 |     4   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | IDX_TEST_S_N |     1 |     7 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - access("N"=9999)
      filter("N"=9999)


Statistics
----------------------------------------------------------
         0  recursive calls
         0  db block gets
         8  consistent gets
         0  physical reads
         0  redo size
       585  bytes sent via SQL*Net to client
       523  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed
这个sql也就类似与如下，可以发现，逻辑读并不完全一样，差了1，所以这里说可以这么理解，但是实际上的处理方式并不完全这样的。
scott@ORA>  select * from test where n=9999 and s='a' union all
 2   select * from test where n=9999 and s='b' union all
 3   select * from test where n=9999 and s='c' 
 4  ;

S          N
- ----------
a       9999


Execution Plan
----------------------------------------------------------
Plan hash value: 2708086881

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     3 |    21 |     3   (0)| 00:00:01 |
|   1 |  UNION-ALL        |              |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_TEST_S_N |     1 |     7 |     1   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN| IDX_TEST_S_N |     1 |     7 |     1   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN| IDX_TEST_S_N |     1 |     7 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("S"='a' AND "N"=9999)
  3 - access("S"='b' AND "N"=9999)
  4 - access("S"='c' AND "N"=9999)


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         7  consistent gets
         0  physical reads
         0  redo size
       585  bytes sent via SQL*Net to client
       523  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed   
     
     
        

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30123160/viewspace-2121537/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30123160/viewspace-2121537/