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

Рассмотрим важность наличия гистограмм по колонкам с высокой степень неравномерности распределения данных в колонке.
Возьмем достаточно большую таблицу 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'

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

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'

Как видим, имеется 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 так как знает, что объединяемые данные невелики. Что, в итоге, благотворно сказывается на производительности запроса даже при предварительно очищенных пулах.

Автор: Brass_nn