本帖最后由 yongzhi2008 于 2012-12-28 13:57 编辑

本来做一下直方图测试， 遇到了一个想不明白的问题， 求助大家。

--直方图测试， 建立一个数据分布严重倾斜的表，在id，name 两个字段上分别建了索引。SQL> desc t_histogram;

Name Type         Nullable Default Comments

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

ID   INTEGER      Y

NAME VARCHAR2(40) Y

SQL> select name, count(*) from t_histogram group by name order by 1;

NAME                                       COUNT(*)

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

haohua                                            1

zhang                                         87692

SQL> select id, count(*) from t_histogram group by id order by 1;

ID   COUNT(*)

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

1          1

100      87692

--执行计划

SQL> show parameter optimizer_index_cost_adj;

NAME                                 TYPE        VALUE

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

optimizer_index_cost_adj             integer     100

SQL> analyze table T_HISTOGRAM compute statistics for table for all indexes for all indexed columns;

Table analyzed

SQL> select * from  t_histogram where id =1;

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

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                |     1 |     8 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_HISTOGRAM    |     1 |     8 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T_HISTOGRAM_ID |     1 |       |     1   (0)| 00:00:01 |

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

1  recursive calls

0  db block gets

4  consistent gets

0  physical reads

0  redo size

593  bytes sent via SQL*Net to client

519  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> set arraysize 5000;

SQL> select * from  t_histogram where id =100;

87692 rows selected.

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

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                | 87692 |   685K|   462   (6)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_HISTOGRAM    | 87692 |   685K|   462   (6)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T_HISTOGRAM_ID | 87692 |       |   280   (5)| 00:00:01 |

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

1  recursive calls

0  db block gets

474  consistent gets

0  physical reads

0  redo size

442213  bytes sent via SQL*Net to client

706  bytes received via SQL*Net from client

19  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

87692  rows processed

SQL> select /*+ full(t_histogram) */ * from t_histogram where id = 100;

87692 rows selected.

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

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |             | 87692 |   685K|   530   (3)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T_HISTOGRAM | 87692 |   685K|   530   (3)| 00:00:01 |

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

Statistics

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

1  recursive calls

0  db block gets

208  consistent gets

0  physical reads

0  redo size

442213  bytes sent via SQL*Net to client

706  bytes received via SQL*Net from client

19  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

87692  rows processed

数据库情况：

SQL> show parameter db_file_multiblock_read_count;

NAME                                 TYPE        VALUE

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

db_file_multiblock_read_count        integer     128

SQL> show parameter db_block_size;

NAME                                 TYPE        VALUE

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

db_block_size                        integer     8192

SQL> select * from v$version where rownum = 1;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production