访问数据的方法:
访问表:
全表扫描:从表所在的第一个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
访问表:
全表扫描:从表所在的第一个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/