12、表的访问方式（索引）_2
表的访问方式
以t1表为例来看表的访问方式
首先创建了一个用户，建立了一张表t1，按照object_id列排序的：
SQL> create user u1 identified by u1; -- 创建用户u1
User created.
SQL> grant connect,resource,dba to u1; -- 给u1授权
Grant succeeded.
SQL> connect u1/u1 -- 连接用户u1
Connected.
SQL> create table t1 as select * from dba_objects order by object_id; -- 创建t1表
Table created.
SQL> exec dbms_stats.gather_table_stats('U1','T1'); -- 收集t1表的统计信息
PL/SQL procedure successfully completed.
SQL>
查询t1表的统计信息：
1、t1表的块数：
select * from dba_tables s where s.table_name='T1';
占1261个块
2、object_id列的唯一值数量：
select * from dba_tab_columns a where a.OWNER='U1' and a.TABLE_NAME='T1'；
object_id列有86315个唯一值
对于一个表，我们收集完统计信息以后，关注的几个东西：
1、表的行数
2、表的块数
3、重点列的唯一值的数量
查询从t1表里取object_id = 150的数据SQL语句的执行计划（无索引，全表扫描）：
SQL> set autotrace trace
SQL> set linesize 150
SQL> set pagesize 0
SQL> select * from t1 where object_id = 150;
Execution Plan -- 语句select * from t1 where object_id = 150;的执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 344 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 98 | 344 (1)| 00:00:05 | --TABLE ACCESS FULL：全表扫描，因为没有索引，所以只能全表扫描
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=150) -- 过滤条件
Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
1244 consistent gets -- 内存读
0 physical reads -- 物理读
0 redo size
1625 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> / -- 再次执行一下
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 344 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 98 | 344 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=150)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1235 consistent gets
1232 physical reads
0 redo size
1625 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
在t1表的object_id列上建一个索引：
SQL> create index i_t1_id on t1(object_id);
Index created.
建完索引之后，再次统计一下t1表的信息：
SQL> exec dbms_stats.gather_table_stats('U1','T1'); -- 再次收集t1表的统计信息
查询t1表关于索引的相关信息：
select * from dba_indexes s where s.table_name='T1' and s.owner='U1';
索引的叶子数量191，唯一值数量86315
查询索引在哪个列上：
select * from dba_ind_columns x where x.table_name='T1' and x.table_owner='U1';
查询t1表索引的集群因子：
select * from dba_indexes s where s.table_name='T1' and s.owner='U1';
集群因子：1233
关于索引需要关注的几个东西：
1、唯一值的数量
2、集群因子
查询有索引时从t1表里取object_id = 150的数据SQL语句的执行计划（走索引）：
SQL> set autotrace trace
SQL> set linesize 150
SQL> set pagesize 0
SQL> select * from t1 where object_id = 150;
Execution Plan
----------------------------------------------------------
Plan hash value: 1111474805
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 98 | 2(0)| 00:00:01 | -- 先访问索引之后，在通过INDEX ROWID访问表
|* 2 | INDEX RANGE SCAN | I_T1_ID | 1 | | 1(0)| 00:00:01 | -- INDEX RANGE SCAN：走索引了
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=150)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets -- 内存读从原来全表扫描的1244降低到4了
0 physical reads
0 redo size
1628 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
查询从t1表里取object_id 1到8000的数据SQL语句的执行计划（走索引）：
SQL> select * from t1 where object_id between 1 and 8000;
7960 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1111474805
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7879 | 754K| 132(0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 7879 | 754K| 132(0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | I_T1_ID | 7879 | | 19(0)| 00:00:01 | -- 走的还是索引
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=8000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1173 consistent gets -- 内存读：1173
0 physical reads
0 redo size
838468 bytes sent via SQL*Net to client
6354 bytes received via SQL*Net from client
532 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7960 rows processed
建另外一张表：t2表，按照object_name排序的
SQL> create table t2 as select * from dba_objects order by object_name;
Table created.
在t2表的object_id列上建一个索引：
SQL> create index i_t2_id on t2(object_id);
Index created.
收集t2表的统计信息：
SQL> exec dbms_stats.gather_table_stats('U1','T2');
PL/SQL procedure successfully completed.
查询t2表索引的集群因子：
select * from dba_indexes s where s.table_name='T2' and s.owner='U1';
集群因子：46382，相对于t1表的1233来说，这个集群因子就很大了，这个索引就不是很好了
查询从t2表里取object_id 1到8000的数据SQL语句的执行计划（全表扫描，有索引，但是没有走索引）：
SQL> select * from t2 where object_id between 1 and 8000;
7960 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7879 | 754K| 344 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T2 | 7879 | 754K| 344 (1)| 00:00:05 | -- 全表扫描
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<=8000 AND "OBJECT_ID">=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1762 consistent gets -- 内存读：1762
0 physical reads
0 redo size
452436 bytes sent via SQL*Net to client
6354 bytes received via SQL*Net from client
532 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7960 rows processed
查询从t2表里取object_id 1到8000的数据SQL语句的执行计划（强制走索引）：
SQL> select /*+ index(t2,i_t2_id) */ * from t2 where object_id between 1 and 8000;
7960 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3661687773
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7879 | 754K| 4254(1)| 00:00:52 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 7879 | 754K| 4254(1)| 00:00:52 |
|* 2 | INDEX RANGE SCAN | I_T2_ID | 7879 | | 19(0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=8000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5750 consistent gets -- 内存读：5750
0 physical reads
0 redo size
838468 bytes sent via SQL*Net to client
6354 bytes received via SQL*Net from client
532 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7960 rows processed
这时候，走索引的效果比全表扫描的效果更差，因为受集群因子的影响
查询从t2表里取object_id = 150的数据SQL语句的执行计划（走了索引）：
SQL> select * from t2 where object_id = 150;
Execution Plan
----------------------------------------------------------
Plan hash value: 3661687773
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 98 | 2(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T2_ID | 1 | | 1(0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=150)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets -- 内存读：4
0 physical reads
0 redo size
1628 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
访问一行，原来是4，现在也是4，访问一行数据时走索引还不错
arraysize参数
每次返回给用户数据行数的限制
查询arraysize大小：
SQL> show arraysize
arraysize 15
一般情况：全表扫描最大的内存读取量不会超过表的总的block的数量，但是由于参数arraysize（每次读取返回给用户数据的行数）的设置，可能一个块要读取多次，比如一个块有100行数据，arraysize的大小设置为了25，一个块就要读取4次了
在程序里面叫做：FetchSize
JAVA程序：（设置fetch size）
public class FetchSize {
static final String driver_class = "oracle.jdbc.driver.OracleDriver";
static final String connectionURL = "jdbc:oracle:thin:@linux1:1521:orcl1";
static final String userID = "scott";
static final String userPassword = "tiger";
public FetchSize() {
}
public void runTest() {
Connection con = null;
Statement stmt = null;
ResultSet rset = null;
String query_string = "SELECT * FROM tables WHERE rownum < 200 ORDER BY owner, table_name";
int newFetchSize = 50;
try {
System.out.println("+-------------------------------+");
System.out.println("| SETUP CONNECTION |");
System.out.println("+-------------------------------+");
System.out.println("Loading JDBC Driver -> " + driver_class);
Class.forName (driver_class).newInstance();
System.out.println("Connecting to -> " + connectionURL);
con = DriverManager.getConnection(connectionURL, userID, userPassword);
System.out.println("Connected as -> " + userID);
System.out.println("Turning Off AutoCommit...");
con.setAutoCommit(false);
/*
** EXECUTE GENERIC QUERY
*/
System.out.println("+-------------------------------+");
System.out.println("| EXECUTE GENERIC QUERY |");
System.out.println("+-------------------------------+");
System.out.println("Executing Generic (SYSDATE) Query...");
System.out.println("Creating Statement...");
stmt = con.createStatement ();
System.out.println("Get Default Fetch Size:" + stmt.getFetchSize());
System.out.println("Manually Set Default Fetch Size to " + newFetchSize);
stmt.setFetchSize(newFetchSize);
System.out.println("Get New Fetch Size:" + stmt.getFetchSize());
System.out.println("Opening ResultsSet...");
rset = stmt.executeQuery (query_string);
while (rset.next ()) {
System.out.println(" RESULTS -> " + rset.getString (2));
}
System.out.println("Closing ResultSet...");
rset.close();
System.out.println("Closing Statement...");
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
if (con != null) {
try {
con.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (con != null) {
try {
System.out.println("+-------------------------------+");
System.out.println("| CLOSE DOWN ALL CONNECTIONS |");
System.out.println("+-------------------------------+");
System.out.println("Closing down all connections...");
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} // FINALLY
}
public static void main(String[] args) {
FetchSize fetchSize = new FetchSize();
fetchSize.runTest();
}
}
