ITPub博客

首页 > 数据库 > Oracle > oracle访问数据的方法

oracle访问数据的方法

原创 Oracle 作者:stilllovekk 时间:2016-07-05 14:21:10 0 删除 编辑
访问数据的方法:
访问表:
      全表扫描:从表所在的第一个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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-01-30

  • 博文量
    20
  • 访问量
    29420