索引_物理读_逻辑读_实验

最新推荐文章于 2021-05-05 18:26:17 发布

cnk45946

最新推荐文章于 2021-05-05 18:26:17 发布

阅读量231

点赞数

文章标签：数据库

1 描述

从已学的知识点分析，索引可以降低物理读。有人说索引可以降低逻辑读，不大明白索引为什么可以降低逻辑读。所以做了下面的实验分析分析索引可不可以降低逻辑读。

2 实验操作环境

2.1 OS info

[root@racnode3 ~]# lsb_release -a

LSB Version: :core-4.0-ia32:core-4.0-noarch:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-ia32:printing-4.0-noarch

Distributor ID: EnterpriseEnterpriseServer

Description: Enterprise Linux Enterprise Linux Server release 5.6 (Carthage)

Release: 5.6

Codename: Carthage

2.2 DB info

[oracle@racnode3 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@racnode3 ~]$ sqlplus / as sysdba <

> set lines 150

> COL PRODUCT FORMAT A55

> COL VERSION FORMAT A15

> COL STATUS FORMAT A15

> SELECT * FROM PRODUCT_COMPONENT_VERSION;

> col value for a50

> set pages 50

> select * from nls_database_parameters;

> archive log list;

> EOF

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 29 15:18:59 2013

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> SQL> SQL> SQL> SQL>

PRODUCT VERSION STATUS

------------------------------------------------------- --------------- ---------------

NLSRTL 10.2.0.1.0 Production

Oracle Database 10g Enterprise Edition 10.2.0.1.0 Prod

PL/SQL 10.2.0.1.0 Production

TNS for Linux: 10.2.0.1.0 Production

SQL> SQL> SQL>

PARAMETER VALUE

------------------------------ --------------------------------------------------

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_CHARACTERSET ZHS16GBK

NLS_CALENDAR GREGORIAN

NLS_DATE_FORMAT DD-MON-RR

NLS_DATE_LANGUAGE AMERICAN

NLS_SORT BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY $

NLS_COMP BINARY

NLS_LENGTH_SEMANTICS BYTE

NLS_NCHAR_CONV_EXCP FALSE

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_RDBMS_VERSION 10.2.0.1.0

20 rows selected.

SQL> Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 101

Next log sequence to archive 103

Current log sequence 103

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options

[oracle@racnode3 ~]$

3 实验过程总概

1 使用scott用户创建测试表

2 统计测试表信息

3 进行表的查询，查看执行计划与执行的状态信息

4 创建索引

5 再次执行表的查询，查看执行计划与执行的状态信息

6 得出结论

4 详细步骤操作

4.1 创建scott用户测试表test_index

SQL> conn / as sysdba

Connected.

SQL> create table scott.test_index as select * from dba_objects;

Table created.

SQL>

4.2 对scott用户测试表test_index进行统计信息收集

SQL> exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'TEST_INDEX',estimate_percent => 10,method_opt=> 'for all indexed columns') ;

PL/SQL procedure successfully completed.

SQL> select owner,table_name,blocks from dba_tables where table_name='TEST_INDEX';

OWNER TABLE_NAME BLOCKS

------------------------------ ------------------------------ ----------

SCOTT TEST_INDEX 721

SQL>

收集完统计信息后，查询scott的test_index表，发现test_index表占用721个数据块。

4.3 无索引状态查看表的执行计划与执行统计信息

SQL> conn scott/tiger

Connected.

SQL> set autotrace traceonly;

SQL> select object_id from test_index where object_id > 4000;

47084 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2713238154

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2608 | 33904 | 162 (2)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| TEST_INDEX | 2608 | 33904 | 162 (2)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID">4000)

Statistics

----------------------------------------------------------

240 recursive calls

0 db block gets

3824 consistent gets

703 physical reads

0 redo size

684824 bytes sent via SQL*Net to client

34903 bytes received via SQL*Net from client

3140 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

47084 rows processed

SQL>

注意此时返回47084行，对test_index表的物理读为 703 个数据块。与第二步分析表所占的数据块空间 721很接近，至于为什么不是721不知道原因。可知此时对test_index表走的是全表扫。清空buffer cache中数据，再接着做实验看看取出更少的行，数据库对表的块的扫描情况。

SQL> conn / as sysdba

Connected.

SQL> alter session set events 'immediate trace name flush_cache';

Session altered.

SQL> conn scott/tiger

Connected.

SQL> set autotrace traceonly;

SQL> select object_id from test_index where object_id > 44000;

8005 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2713238154

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2608 | 33904 | 162 (2)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| TEST_INDEX | 2608 | 33904 | 162 (2)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID">44000)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

1234 consistent gets

702 physical reads

0 redo size

117735 bytes sent via SQL*Net to client

6248 bytes received via SQL*Net from client

535 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

8005 rows processed

SQL>

更改限制条件后，返回结果8005行，对表的扫描结果为物理读取702个数据块。与第二步分析表所占的数据块空间 721很接近。下面不清空buffer cache执行相同的语句，这次的结果会没有物理读，而全部是逻辑读。

SQL> select object_id from test_index where object_id > 44000;

8005 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2713238154

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2608 | 33904 | 162 (2)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| TEST_INDEX | 2608 | 33904 | 162 (2)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID">44000)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

1234 consistent gets

0 physical reads

0 redo size

117735 bytes sent via SQL*Net to client

6248 bytes received via SQL*Net from client

535 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

8005 rows processed

SQL>

至此，test_index表中的数据块已经全部加载到了内存中去。分析逻辑读与上一次的差别，将表的702个数据块去掉后，此步的逻辑读本身的消耗由1234个块变成了1234-702=532个块。

4.4 对scott用户的表test_index创建object_id列的索引

SQL> create index test_index_object_id on test_index(object_id);

Index created.

SQL>

4.5 执行查询操作，查看执行计划与执行统计信息

SQL> select object_id from test_index where object_id > 44000;

8005 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3080705149

--------------------------------------------------------------------------------

---------

ime |

--------------------------------------------------------------------------------

---------

| 0 | SELECT STATEMENT | | 2608 | 33904 | 3 (0)| 0

0:00:01 |

|* 1 | INDEX RANGE SCAN| TEST_INDEX_OBJECT_ID | 2608 | 33904 | 3 (0)| 0

0:00:01 |

--------------------------------------------------------------------------------

---------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("OBJECT_ID">44000)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

554 consistent gets

23 physical reads

0 redo size

117735 bytes sent via SQL*Net to client

6248 bytes received via SQL*Net from client

535 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

8005 rows processed

SQL>

创建索引之后，使用相同的select语句再次执行查询时，虽然buffer cache中已经缓存了全部的表数据块，但是执行时的统计信息依然会进行物理读，此时从磁盘读取了23个数据块，而语句本身的逻辑读554与上一步的532差别不大。推测此时的物理读是直接读取新创建的索引中的块数据，语句本身的分析占用的内存依然使用上一步的逻辑读分析结果。清空buffer cache中的数据，再改变判断条件，增加返回的行数结果，看看物理读块数是否增加。

SQL> set lines 120

SQL> conn / as sysdba

Connected.

SQL> alter session set events 'immediate trace name flush_cache';

Session altered.

SQL> conn scott/tiger

Connected.

SQL> set autotrace traceonly;

SQL> select object_id from test_index where object_id > 4000;

47084 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3080705149

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2608 | 33904 | 3 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| TEST_INDEX_OBJECT_ID | 2608 | 33904 | 3 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("OBJECT_ID">4000)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3239 consistent gets

124 physical reads

0 redo size

684824 bytes sent via SQL*Net to client

34903 bytes received via SQL*Net from client

3140 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

47084 rows processed

SQL>

与前一步无索引时的不同判断条件都返回相同的物理块数扫描结果不同，当创建索引后，使用不同的判断条件，语句对表的数据块的扫描数不一样。返回结果多，扫描的块多，返回结果少，扫描的块少。

5 个人总结

实验的关键结果在 4.3与4.5的结果对比。在4.3步执行完后，无索引的test_index表中的全部数据块已加载进内存，4.5步时已创建好索引的test_index表再次执行与4.3步相同的查询语句，此时的与表中的数据块加载的逻辑读并未减少，证明创建索引后，索引并不会直接影响逻辑读。索引创建后所影响的是数据加载的物理读。

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

转载于:http://blog.itpub.net/11780477/viewspace-1061682/