oracle单表数据量的大小对索引性能的影响

实验内容：

表数据量与索引性能的关系

特别说明：

生产环境中的row_id为应用程序生成，无明显生成规则，因此使用row_id建立分区表可行度不高；

实验结论：

一、各表查询执行计划路线都相同，但cost、consistent gets、physical reads三个指标逐渐增加，说明数据量的增加对于CPU和物理块还是有影响的；

二、通过tkprof工具查看更详细的变化，可以观察到cr、pr、pw、time、us cost几个参数在逐渐递增；

三、随着数据量增加，索引树的高度也会增加，oracle的建议是索引树高度超过4需要重建索引，但如果因为数据量的累加而导致高度增加，重建不起作用，本人唯一了解到的办法只有物理分表，单表数据量的控制可以有效避免索引的性能恶化；

备注：本人实验中最高只将树高度顶至3,没能产生高度为4的索引，所以不清楚数据量的累加是否会导致索引树高度超过3

以下为实验步骤：

一、创建实验表

create table t_index_test as select rownum ROW_ID,to_char(round(rownum/3,2)) DESC_TEXT from xmltable('1 to 10000');//一万笔数据

create table t_index_test_10w as select rownum ROW_ID,to_char(round(rownum/3,2)) DESC_TEXT from xmltable('1 to 100000');//十万笔数据

create table t_index_test_100w as select rownum ROW_ID,to_char(round(rownum/3,2)) DESC_TEXT from xmltable('1 to 1000000') ;//一百万笔数据

create table t_index_test_1000w as select rownum ROW_ID,to_char(round(rownum/3,2)) DESC_TEXT from xmltable('1 to 10000000') ;//一千万笔数据

create table t_index_test_10000w as select rownum ROW_ID,to_char(round(rownum/3,2)) DESC_TEXT from xmltable('1 to 100000000');//一亿笔数据

二、创建索引

create index idx_1w on t_index_test (row_id); create index idx_10w on t_index_test_10w (row_id); create index idx_100w on t_index_test_100w (row_id); create index idx_1000w on t_index_test_1000w (row_id); create index idx_10000w on t_index_test_10000w (row_id);

三、各表根据row_id查询的执行计划记录

SQL> select * from scott.t_index_test where row_id='1234'; Execution Plan ---------------------------------------------------------- Plan hash value: 2250468099 -------------------------------------------------------------------------------------------- | Id  | Operation    | Name  | Rows  | Bytes | Cost (%CPU)| Time   | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT    |  | 1 |35 | 2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T_INDEX_TEST |1 | 35 |2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN    | IDX_1W  | 1 |  | 1   (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("ROW_ID"=1234) Note -----    - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 13  recursive calls  0  db block gets 33  consistent gets  0  physical reads  0  redo size 607  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> select * from scott.t_index_test_10w where row_id='12345'; Execution Plan ---------------------------------------------------------- Plan hash value: 481348210 ------------------------------------------------------------------------------------------------ | Id  | Operation    | Name      | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT    |      |     1 |    35 |     2(0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T_INDEX_TEST_10W |     1 |    35 |     2(0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN    | IDX_10W      |     1 |       |     1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("ROW_ID"=12345) Note -----    - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 13  recursive calls  0  db block gets 74  consistent gets  1  physical reads  0  redo size 606  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> select * from scott.t_index_test_100w where row_id='123456'; Execution Plan ---------------------------------------------------------- Plan hash value: 1315594877 ------------------------------------------------------------------------------------------------- | Id  | Operation    | Name| Rows | Bytes | Cost (%CPU)| Time| ------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT    ||     1 |    35 |     4   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T_INDEX_TEST_100W |     1 |    35 |     4   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN    | IDX_100W|     1 | |     3   (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("ROW_ID"=123456) Note -----    - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 13  recursive calls  0  db block gets 75  consistent gets  2  physical reads  0  redo size 607  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> select * from scott.t_index_test_1000w where row_id='1234567'; Execution Plan ---------------------------------------------------------- Plan hash value: 2655249834 -------------------------------------------------------------------------------------------------- | Id  | Operation    | Name| Rows  | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT    ||     1 |    35 |     4   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T_INDEX_TEST_1000W |     1 |    35 |     4   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN    | IDX_1000W |     1 | |     3   (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("ROW_ID"=1234567) Note -----    - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 13  recursive calls  0  db block gets 84  consistent gets  2  physical reads  0  redo size 612  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> select * from scott.t_index_test_10000w where row_id='12345678'; Execution Plan ---------------------------------------------------------- Plan hash value: 584708459 --------------------------------------------------------------------------------------------------- | Id  | Operation    | Name | Rows  | Bytes | Cost (%CPU)| Time  | --------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT    | | 1 |    35 |4   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T_INDEX_TEST_10000W |1 |    35 | 4   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN    | IDX_10000W | 1 |  | 3   (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("ROW_ID"=12345678) Note -----    - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 13  recursive calls  0  db block gets 86  consistent gets  3  physical reads  0  redo size 610  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

四、tkprof工具生成结果的记录

******************************************************************************** SQL ID: 3as912cxvm33j Plan Hash: 2250468099 select *  from  scott.t_index_test where row_id=:"SYS_B_0" call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0         13          0           0 Execute      1      0.01       0.05          0         29          0           0 Fetch        2      0.00       0.00          0          4          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4      0.02       0.05          0         46          0           1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          1          1          1  TABLE ACCESS BY INDEX ROWID T_INDEX_TEST (cr=4 pr=0 pw=0 time=171 us cost=2 size=35 card=1)          1          1          1   INDEX RANGE SCAN IDX_1W (cr=3 pr=0 pw=0 time=168 us cost=1 size=0 card=1)(object id 89045) Elapsed times include waiting on following events:   Event waited on                             Times   Max. Wait  Total Waited   ----------------------------------------   Waited  ----------  ------------   SQL*Net message to client                       2        0.00          0.00   SQL*Net message from client                     2       29.27         29.27 ******************************************************************************** SQL ID: 51mvppfk9ajaz Plan Hash: 481348210 select *  from  scott.t_index_test_10w where row_id=:"SYS_B_0" call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          2          0           0 Fetch        2      0.00       0.00          0          4          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4      0.00       0.00          0          6          0           1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          1          1          1  TABLE ACCESS BY INDEX ROWID T_INDEX_TEST_10W (cr=4 pr=0 pw=0 time=925 us cost=2size=35 card=1)          1          1          1   INDEX RANGE SCAN IDX_10W (cr=3 pr=0 pw=0 time=889 us cost=1 size=0 card=1)(object id 89046) Elapsed times include waiting on following events:   Event waited on                             Times   Max. Wait  Total Waited   ----------------------------------------   Waited  ----------  ------------   SQL*Net message to client                       2        0.00          0.00   SQL*Net message from client                     2       24.43         24.43 ******************************************************************************** SQL ID: 8wuk0v6rkjxxw Plan Hash: 1315594877 select *  from  scott.t_index_test_100w where row_id=:"SYS_B_0" call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          2          0           0 Fetch        2      0.00       0.01          1          5          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4      0.00       0.01          1          7          0           1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          1          1          1  TABLE ACCESS BY INDEX ROWID T_INDEX_TEST_100W (cr=5 pr=1 pw=0 time=18161 us cost=4 size=35 card=1)          1          1          1   INDEX RANGE SCAN IDX_100W (cr=4 pr=0 pw=0 time=20 us cost=3 size=0 card=1)(object id 89047) Elapsed times include waiting on following events:   Event waited on                             Times   Max. Wait  Total Waited   ----------------------------------------   Waited  ----------  ------------   SQL*Net message to client                       2        0.00          0.00   db file sequential read                         1        0.01          0.01   SQL*Net message from client                     2       18.90         18.90 ******************************************************************************** SQL ID: 7chhd95b0jrzn Plan Hash: 2655249834 select *  from  scott.t_index_test_1000w where row_id=:"SYS_B_0" call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          2          0           0 Fetch        2      0.00       0.01          1          5          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4      0.00       0.01          1          7          0           1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          1          1          1  TABLE ACCESS BY INDEX ROWID T_INDEX_TEST_1000W (cr=5 pr=1 pw=0 time=17290 us cost=4 size=35 card=1)          1          1          1   INDEX RANGE SCAN IDX_1000W (cr=4 pr=0 pw=0 time=19 us cost=3 size=0 card=1)(object id 89048) Elapsed times include waiting on following events:   Event waited on                             Times   Max. Wait  Total Waited   ----------------------------------------   Waited  ----------  ------------   SQL*Net message to client                       2        0.00          0.00   db file sequential read                         1        0.01          0.01   SQL*Net message from client                     2       18.32         18.32 ******************************************************************************** SQL ID: 9m9uc8ugxgjmn Plan Hash: 584708459 select *  from  scott.t_index_test_10000w where row_id=:"SYS_B_0" call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          2          0           0 Fetch        2      0.00       0.02          1          5          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4      0.00       0.02          1          7          0           1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          1          1          1  TABLE ACCESS BY INDEX ROWID T_INDEX_TEST_10000W (cr=5 pr=1 pw=0 time=22253 us cost=4 size=35 card=1)          1          1          1   INDEX RANGE SCAN IDX_10000W (cr=4 pr=0 pw=0 time=22 us cost=3 size=0 card=1)(object id 89049) Elapsed times include waiting on following events:   Event waited on                             Times   Max. Wait  Total Waited   ----------------------------------------   Waited  ----------  ------------   SQL*Net message to client                       2        0.00          0.00   db file sequential read                         1        0.02          0.02   SQL*Net message from client                     2        6.83          6.83 ********************************************************************************

五、查看索引树高度记录

SQL> select s.table_name,s.index_name,s.blevel from dba_indexes s where s.index_name like 'IDX_1%'   2  ; TABLE_NAME       INDEX_NAME BLEVEL ------------------------------ ------------------------------ ---------- T_INDEX_TEST       IDX_1W      1 T_INDEX_TEST_10W       IDX_10W      1 T_INDEX_TEST_100W       IDX_100W       2 T_INDEX_TEST_1000W       IDX_1000W      2 T_INDEX_TEST_10000W       IDX_10000W      2