Быстрое получение количества строк записей, удовлетворяющих условию(cardinality) с погрешностью с помощью оптимизатора


Сегодня пришлось задуматься о получении количества записей, удовлетворяющих условию легким и быстрым способом.
 К сожалению, в случаях когда условия не совсем примитивные не поможет ни num_rows из dba_tables, num_distinct из всяких user_tab_col_statistics, а ускорить запрос нужно и допустима небольшая погрешность, то могут подойти два способа:
1. Кляуза sample(сколько_процентов) позволяющая не походиться по всей таблице.
 Пример: select * from table sample(30)
2. Заставить оптимизатор потрудиться за нас - взять значение из explain plan.

Оба способа, конечно, очень и очень часто не будут подходить: в первом случае на удачу выбранные блоки могут категорически не подходить для репрезентативной выборки, а во втором даже комментарии излишни - ну кто не мучался с оптимизатором :) хотя у Льюиса в основах стоимостной оптимизации по многим вопросам можно проконсультироваться.

Тем не менее представляю свое решение вторым способом(мне сегодня помогла :) ) - функция get_cardinality:

create or replace function get_cardinality(pQuery in varchar2)
 return integer is
 pragma autonomous_transaction;
 ret integer;
 rnd varchar2(10);
begin
 rnd:=dbms_random.string('x',10);
 execute immediate 'explain plan
                     set statement_id='
''||rnd||'''
                     for '
||pQuery;
        
 select cardinality
         into ret
 from (
      select pt.cardinality
             ,row_number()over(order by pt.timestamp) rn
      from plan_table pt
      where pt.statement_id=rnd
             and pt.id=0
      )
 where rn=1;
 rollback;
 return ret;
exception
 when others then
    rollback;
    return sqlcode;
end;



* This source code was highlighted with Source Code Highlighter.
Выполнять, например, так: select get_cardinality('select * from tab1,tab2 where tab1.a=tab2.b and tab2.c>30')

Comments

Отправить комментарий