Важность гистограмм при высокой степени неравномерности распределения данных в колонке

Рассмотрим важность наличия гистограмм по колонкам с высокой степень неравномерности распределения данных в колонке.
Возьмем достаточно большую таблицу STG.TEST. Имеется неуникальный индекс TEST_I по полю FIELD_ID.

select count(*) from stg.test ----------- 43756707  SQL> desc stg.test; Name        Type          Nullable Default Comments  ----------- ------------- -------- ------- --------  NAME CHAR(2)                                  DAT   DATE                                     ID     NUMBER(12)    Y                          FIELD_ID    INTEGER                                  FIELD_VALUE VARCHAR2(100) Y 

Создадим неоднородность распределения данных в колонке FIELD_ID — проапдейтим колонку FIELD_ID, выставив значение=100 и несколько значений руками выставим=103, 1000, 1002, 1003 (для примера)

Соберем статистику по таблице, но удалим гистограмму:

begin   dbms_stats.gather_table_stats(ownname => 'stg',tabname => 'test',degree => 16,cascade => true,estimate_percent => 50);   dbms_stats.delete_column_stats(ownname => 'stg',tabname => 'test',colname => 'field_id'); end;   select table_name,num_rows,blocks,avg_row_len from dba_tables where table_name='TEST' and owner='STG' 

TABLE_NAME	NUM_ROWS	BLOCKS	AVG_ROW_LEN
TEST	43756707	302080	44

Посмотрим на план и время выполнения нехитрового тестового запроса:

explain plan for select * from stg.test  t where subs_id in (select subs_id from stg.test b where b.field_id=103 and b.rowid=t.rowid)  Plan hash value: 1839037834 ----------------------------------------------------------------------------------------------- | Id  | Operation                    | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |        |     1 |    75 |       |   209K  (1)| 00:41:53 | |*  1 |  HASH JOIN                   |        |     1 |    75 |    17M|   209K  (1)| 00:41:53 | |   2 |   TABLE ACCESS BY INDEX ROWID| TEST   |   437K|    12M|       |  9408   (1)| 00:01:53 | |*  3 |    INDEX RANGE SCAN          | TEST_I |   175K|       |       |  7851   (1)| 00:01:35 | |   4 |   TABLE ACCESS FULL          | TEST   |    43M|  1836M|       | 82685   (2)| 00:16:33 | -----------------------------------------------------------------------------------------------   SQL> select count(*) from stg.test  t   2  where subs_id in   3  (select subs_id from stg.test b where b.field_id=103 and b.rowid=t.rowid);        COUNT(*) ----------          4   Executed in 11,513 seconds 

Как видим, оптимизатор выбирает HASH JOIN закладываясь на объединение двух больших объемов данных, хотя в запросе мы указали ему значение, встречающееся в таблице 1-2 раза. Приведено значение выполнения после 3-4 раза, дабы учесть разогревание буферного кеша.
Пересоберем статистику не удаляя гистограмму

 begin   dbms_stats.gather_table_stats(ownname => 'stg',tabname => 'test',degree => 16,cascade => true, estimate_percent => 50); end;    

Проверим её наличие:

select column_name,endpoint_number,endpoint_value from dba_tab_histograms where owner='STG' and table_name='TEST' and column_name='FIELD_ID' 

COLUMN_NAME	ENDPOINT_NUMBER	ENDPOINT_VALUE
FIELD_ID	1	1
FIELD_ID	21875192	100
FIELD_ID	21875195	103
FIELD_ID	21875196	1000
FIELD_ID	21875198	1003

Как видим, имеется FREQUENCY гистограмма (что логично при небольшом числе уникальных значений) в которой основная масса приходится на значение=100.
Взглянем теперь на время и план выполнения:

--------------------------------------------------------------------------------------- | Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |        |     1 |    66 |    10   (0)| 00:00:01 | |   1 |  NESTED LOOPS                |        |     1 |    66 |    10   (0)| 00:00:01 | |   2 |   TABLE ACCESS BY INDEX ROWID| TEST   |     6 |   132 |     4   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN          | TEST_I |     6 |       |     3   (0)| 00:00:01 | |*  4 |   TABLE ACCESS BY USER ROWID | TEST   |     1 |    44 |     1   (0)| 00:00:01 | ---------------------------------------------------------------------------------------   SQL> alter system flush shared_pool;   System altered   Executed in 0,046 seconds   SQL> alter system flush buffer_cache;   System altered   Executed in 0,39 seconds  SQL> select count(*) from stg.test  t   2  where subs_id in   3  (select subs_id from stg.test b where b.field_id=103 and b.rowid=t.rowid);     COUNT(*) ----------          4   Executed in 0,047 seconds 

На этот раз оптимизатор уже выбирает более логичный в данном случае NESTED_LOOPS так как знает, что объединяемые данные невелики. Что, в итоге, благотворно сказывается на производительности запроса даже при предварительно очищенных пулах.