powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Замена DISTINCT
38 сообщений из 38, показаны все 2 страниц
Замена DISTINCT
    #40099782
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Вопрос знатокам оптимизации производительности.

Есть большая таблица TBL (CODE, NAME, CREATE_DATE, ...) с сотнями миллионов строк.
Таблица парционирована по дате CREATE_DATE

Код: plsql
1.
2.
3.
4.
5.
6.
SELECT COUNT(*)
FROM (
SELECT DISTINCT CODE, NAME
FROM TBL
WHERE CREATE_DATE = to_date('2021.09.24','YYYY.MM.DD')
)


Нужно подсчитать кол-во уникальных строк по перечисленным после дистинкта полям.

Дистинкт делается очень долго. Без него кол-во считается быстро.

Как бы обойтись без него? И подсчитать кол-во уникальных строк другим способом?
Может посчитать всё, а потом дубли выкинуть?
Если без дистинкта, то получается 20 млн строк.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099785
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
verter,

сравните с
SELECT count(DISTINCT to_char(nvl(CODE,-1),9999999)|| NAME) cc
FROM TBL
WHERE CREATE_DATE = to_date('2021.09.24','YYYY.MM.DD')

.....
stax
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099793
Фотография AmKad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Stax

сравните с
SELECT count(DISTINCT to_char(nvl(CODE,-1),9999999)|| NAME) cc
Должно быть быстрее?
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099809
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Stax
verter,

сравните с
SELECT count(DISTINCT to_char(nvl(CODE,-1),9999999)|| NAME) cc
FROM TBL
WHERE CREATE_DATE = to_date('2021.09.24','YYYY.MM.DD')

.....
stax


к сожалению стало даже дольше.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099833
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
verter,

Используйте функцию APPROX_COUNT_DISTINCT

Код: plsql
1.
2.
SELECT APPROX_COUNT_DISTINCT(CODE || '~' || NAME)
FROM ...



APPROX_COUNT_DISTINCT APPROX_COUNT_DISTINCT returns the approximate number of rows that contain a distinct value for expr.

This function provides an alternative to the COUNT (DISTINCT expr) function, which
returns the exact number of rows that contain distinct values of expr.

APPROX_COUNT_DISTINCT processes large amounts of data significantly faster than COUNT,
with negligible deviation from the exact result .

For expr, you can specify a column of any scalar data type other than BFILE, BLOB, CLOB, LONG, LONG RAW, or NCLOB.
APPROX_COUNT_DISTINCT ignores rows that contain a null value for expr.
This function returns a NUMBER.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099845
flexgen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
verter
Stax
verter,

сравните с
SELECT count(DISTINCT to_char(nvl(CODE,-1),9999999)|| NAME) cc
FROM TBL
WHERE CREATE_DATE = to_date('2021.09.24','YYYY.MM.DD')

.....
stax


к сожалению стало даже дольше.


А если сделать в два этапа:
Код: plsql
1.
2.
3.
4.
5.
6.
CREATE GLOBAL TEMPORARY TABLE tmp1 ON COMMIT PRESERVE ROWS AS
SELECT CODE, NAME
FROM TBL
WHERE CREATE_DATE = to_date('2021.09.24','YYYY.MM.DD');

select count(*) from (select distinct CODE, NAME from tmp1) a;
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099857
Фотография AmKad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
flexgen

А если сделать в два этапа
А почему не в три? Count и distinct-ирование вполне можно разбить на 2 этапа!
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099860
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
SQL> SELECT COUNT(*) FROM ssb.lineorder;

  COUNT(*)
----------
5999989709

Elapsed: 00:01:30.539
SQL> 
SQL> SELECT COUNT(DISTINCT lo_suppkey) FROM ssb.lineorder;

COUNT(DISTINCTLO_SUPPKEY)
-------------------------
                  2000000

Elapsed: 00:18:05.433 (1085 seconds)

SQL> SELECT APPROX_COUNT_DISTINCT(lo_suppkey) FROM ssb.lineorder;

APPROX_COUNT_DISTINCT(LO_SUPPKEY)
---------------------------------
                          1939328

Elapsed: 00:11:29.062 (689 seconds)


Таблица большая, параллелизма не было.

APPROX_COUNT_DISTINCT сработала в 1.6 раза быстрее, чем COUNT(DISTINCT ...
Результат c APPROX_COUNT_DISTINCT отличается на 3% от результата COUNT(DISTINCT ...
Для большинства практических применений точность достаточная.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099862
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
SQL*Plus,

да, проблема решается только включением параллельности, т.е. вот так:

SELECT /*+PARALLEL(16)*/ COUNT(*)
FROM (
SELECT DISTINCT CODE, NAME
FROM TBL
WHERE CREATE_DATE = to_date('2021.09.24','YYYY.MM.DD')
)
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099865
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
verter
SQL*Plus,

да, проблема решается только включением параллельности, т.е. вот так:

SELECT /*+PARALLEL(16)*/ COUNT(*)
FROM (
SELECT DISTINCT CODE, NAME
FROM TBL
WHERE CREATE_DATE = to_date('2021.09.24','YYYY.MM.DD')
)

Включение параллельности увеличивает потребление ресурсов.
В вашем случае раз в 18-20.
За счет этого запрос выполняется быстрее.
APPROX_COUNT_DISTINCT решает "проблему" за счет эффективности алгоритмов.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099869
flexgen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
AmKad
А почему не в три?


Да хоть в десять. Ни в чем себе не отказывай.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099871
flexgen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
SQL*Plus

APPROX_COUNT_DISTINCT решает "проблему" за счет эффективности алгоритмов.


Так то оно так, но если нужны точные значения, то APPROX_COUNT_DISTINCT не подойдет.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099872
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Еще один пример на APPROX_COUNT_DISTINCT
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
SQL> SELECT COUNT(*) FROM ssb.customer;

  COUNT(*)
----------
  30000000

Elapsed: 00:00:00.472
SQL> 
SQL> SELECT COUNT(DISTINCT c_phone) FROM ssb.customer;

COUNT(DISTINCTC_PHONE)
----------------------
              29988794

Elapsed: 00:00:16.755 (17 c)
SQL> 
SQL> SELECT APPROX_COUNT_DISTINCT(c_phone) FROM ssb.customer;

APPROX_COUNT_DISTINCT(C_PHONE)
------------------------------
                      29762366

Elapsed: 00:00:03.774 (4 c)



C APPROX_COUNT_DISTINCT получил:
Ускорение в 4+ раза
Погрешность 0,8%
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099875
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
flexgen
SQL*Plus

APPROX_COUNT_DISTINCT решает "проблему" за счет эффективности алгоритмов.


Так то оно так, но если нужны точные значения, то APPROX_COUNT_DISTINCT не подойдет.

Приведите примеры, когда нужны точные значения для "Количества разных значений показателя".

Для суммы по показателю - да нужны точные значения.
Для количества значений показателя - да нужны точные значения, но уже не всегда.
Для среднего арифметического значения показателя - да нужны точные значения, но уже не всегда - еще реже.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099878
PuM256
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
verter
SQL*Plus,
да, проблема решается только включением параллельности, т.е. вот так:

А ещё проблема может решаться (а может и не решаться) построением дополнительных индексов. Чтобы не гадать на кофейной гуще, лучше сразу бы приложили план с +ALLSTATS или репорт SQL Monitor.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099882
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
PuM256,

индексы тут навряд ли помогут. Таблица парционирована по дате CREATE_DATE, поэтому такой запрос (без дистинкта) работает почти мгновенно:

Код: plsql
1.
2.
3.
4.
5.
6.
SELECT COUNT(*)
FROM (
SELECT CODE, NAME
FROM TBL
WHERE CREATE_DATE = to_date('2021.09.24','YYYY.MM.DD')
)



дело именно в дистинкте.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099892
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
SQL*Plus

Включение параллельности увеличивает потребление ресурсов.
В вашем случае раз в 18-20.
За счет этого запрос выполняется быстрее.
APPROX_COUNT_DISTINCT решает "проблему" за счет эффективности алгоритмов.


Да, вы совершенно правы!
В моём случае APPROX_COUNT_DISTINCT отработал практически за такое же время что и с параллельностью.

Код: plsql
1.
2.
3.
SELECT APPROX_COUNT_DISTINCT(CODE||NAME)
FROM TBL
WHERE CREATE_DATE = to_date('2021.09.24','YYYY.MM.DD')


Время работы уменьшилось в 14 раз!
потеря точности = 0.6%

Жаль, что мне нужны точные результаты.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099897
flexgen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
SQL*Plus
Приведите примеры, когда нужны точные значения для "Количества разных значений показателя".


Да пожалуйста:
verter
Жаль, что мне нужны точные результаты.


Я работаю в финансовой сфере, задачи, которые нам ставят всегда требуют точных данных. Всякие там усреднения, округления и тому подобное - для этого есть BI.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099898
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
verter

Время работы уменьшилось в 14 раз!
потеря точности = 0.6%

Жаль, что мне нужны точные результаты .

Зачем?
Как вы их используете?
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099901
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
flexgenзадачи, которые нам ставят всегда требуют точных данных

И какие же задачи в финансовой сфере требуют "количество уникальных строк по
перечисленным полям"?..
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099902
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
flexgen
SQL*Plus
Приведите примеры, когда нужны точные значения для "Количества разных значений показателя".

Да пожалуйста:
verter
Жаль, что мне нужны точные результаты.

Я работаю в финансовой сфере, задачи, которые нам ставят всегда требуют точных данных. Всякие там усреднения, округления и тому подобное - для этого есть BI.

Это не пример.
Это голословное эмоциональное утверждение.

Приведите пример из вашей финансовой сферы,
когда требуется использовать COUNT DISTINCT вообще
и когда его нельзя заменить приблизительное значение.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099905
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Как часто требуется выполнять такую операцию?
Как часто её требуется выполнять относительно других операция по указанной таблице?
Чем можно пожертвовать ради решения задачи (дисковое пространство, CPU, замедление других операций)?
Требуется ли абсолютная точность в online или это подсчет постфактум?
...в конце концов, достаточных ли размеров sort area и нельзя ли её увеличить?
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099907
flexgen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
SQL*Plus,

Ну хорошо, вот пример - приходит некий аудитор и говорит, хочу знать количество транзакций, произведенных в определенном магазине, в определенный отрезок времени, определёнными кредитными картами. Мало того, хочу знать точное количество уникальных кредитных карт по организации, выдавшей эти карты, подпадающих под заданные параметры. И хочу знать количество транзакций, получивших разрешение кредитной компании, и количество транзакций, не получивших разрешение, а так же причины отказа. А у нас в день более 500 миллионов транзакций, плюс-минус 0.5% здесь не прокатит, ему точные данные подавай. Так что все зависит от постановки задачи, если устраивает приблизительное значение - то мы его и дадим. Но если от нас требуют точных данных - мы даем точные данные.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099909
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Аудитор пару часов спокойно подождет точных данных, если они ему реально нужны.
Но, полагаю, даже аудитор для оценки положения дел примет цифру с точностью +-1%.
С третьей стороны, если Вы заранее знаете типовые вопросы аудитора, то ничто не мешает считать цифирь по закрытию банковского дня и откладывать в сторону.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099912
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
flexgen
SQL*Plus,

Ну хорошо, вот пример - приходит некий аудитор и говорит, хочу знать количество транзакций, произведенных в определенном магазине, в определенный отрезок времени, определёнными кредитными картами. Мало того, хочу знать точное количество уникальных кредитных карт по организации, выдавшей эти карты, подпадающих под заданные параметры. И хочу знать количество транзакций, получивших разрешение кредитной компании, и количество транзакций, не получивших разрешение, а так же причины отказа. А у нас в день более 500 миллионов транзакций, плюс-минус 0.5% здесь не прокатит, ему точные данные подавай. Так что все зависит от постановки задачи, если устраивает приблизительное значение - то мы его и дадим. Но если от нас требуют точных данных - мы даем точные данные.

Дадите ему APPROX_COUNT_DISTINCT.
Этого будет достаточно.

"А то, понимаешь, ходят тут всякие и захотелками своими работать мешают".

2574256 и 2525562 это для практического применения одно и то же.

Пример не убедительный.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099913
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
andrey_anonymous
Аудитор пару часов спокойно подождет точных данных, если они ему реально нужны.
Но, полагаю, даже аудитор для оценки положения дел примет цифру с точностью +-1%.
С третьей стороны, если Вы заранее знаете типовые вопросы аудитора, то ничто не мешает считать цифирь по закрытию банковского дня и откладывать в сторону.

+1
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099929
Фотография Egoр
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
PuM256
verter
SQL*Plus,
да, проблема решается только включением параллельности, т.е. вот так:

А ещё проблема может решаться (а может и не решаться) построением дополнительных индексов. Чтобы не гадать на кофейной гуще, лучше сразу бы приложили план с +ALLSTATS или репорт SQL Monitor.

+1
еще distinct может портить план запроса, выключать, например, single partition.
с индексами у Кайта есть красивый иерархический пример построения distinct.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099941
flexgen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
SQL*Plus
Пример не убедительный.

Я не собираюсь кого-либо в чем-то убеждать, как говорится - "по вопросам веры идите в церковь". Реальная жизнь такова что иногда приходится делать то, что должно, а не то, что хочется.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40099961
Правильный Вася
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
А как аудитор узнает, что ему дали данные, на .5% отличающиеся от факта?
Сам он их считать не будет на миллионах транзакций. Какие дадут, на те и обопрётся.
Для него это просто цифры, причем не сильно отличающиеся.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40100015
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
verter

к сожалению стало даже дольше.


а так?
select count(count(*)) cc from tbl group by CODE, NAME

ps
+PARALLEL(xx)

.....
stax
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40100084
lav314
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Впервые вижу, что кто-то так активно продвигает функцию приближенного вычисления. Во-первых, в общем случае аналитик, решающий задачу поиска distinct-значения, бесконечно далек от финальной цели поиска ответа и не может оценить требуемую точность. Более того, человек, ставящий ему задачу тоже может не понимать этого, особенно если речь идет о финансовых и бухгалтерских расчетах.

Во-вторых, сама приближенная функция дает не какую-то заранее известную ошибку, она тупо не гарантирует никакой заранее известной точности! Это не научный подход. На такую функцию можно положиться, когда ты для своих целей что-то делаешь и в любой момент можешь переделать решение на 100% точное. А не в случае, когда кто-то зачем-то попросил тебя посчитать число и уже никогда не вернется к тебе, думая, что получил единственно верный, абсолютно точный ответ, а по факту не доплатит налоги в размере 3% от месячной выручки Мегафона или, если перевести в рубли, примерно 3 года тюрьмы.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40100087
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Кстати, заметил такую вещь.

Если делать выборку с параллелизмом, т.е.

Код: plsql
1.
2.
SELECT /*+ PARALLEL(16) */
FROM TBL ...


то ничего кроме хинта PARALLEL оптимизатору не нужно, параллельность включается.

А вот если хочется включить параллельность на вставку в таблицу, т.е.

Код: plsql
1.
2.
3.
INSERT /*+ PARALLEL(16) */
INTO TBL(...)
SELECT ...


то одного хинта не достаточно, нужно сначала перед вставкой выполнить:

Код: plsql
1.
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';


а потом после вставки выполнить соответственно:

Код: plsql
1.
EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL DML';


Это почему так?
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40100092
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
verter

Это почему так?


Потому что SELECT не есть DML и ему не надо заморачиваться с трансакционностью, "stable set of rows", и.т.д.

SY.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40100093
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
lav314
приближенная функция дает не какую-то заранее известную ошибку, она тупо не гарантирует никакой заранее известной точности ! Это не научный подход.

Не научный подход - это ляпнуть на весь лес авторитетное мнение, не изучив матчасть.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40100156
PuM256
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
SY
verter

Это почему так?


Потому что SELECT не есть DML и ему не надо заморачиваться с трансакционностью, "stable set of rows", и.т.д.

SY.

А более конкретно, от старта parallel DML и до commit/rollback не будут работать ни другие DML, ни даже простые селекты. Так что с ними надо быть очень осторожным.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40100327
SQL*Plus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
verter
Кстати, заметил такую вещь.

Если делать выборку с параллелизмом, т.е.

Код: plsql
1.
2.
SELECT /*+ PARALLEL(16) */
FROM TBL ...


то ничего кроме хинта PARALLEL оптимизатору не нужно, параллельность включается.

А вот если хочется включить параллельность на вставку в таблицу, т.е.

Код: plsql
1.
2.
3.
INSERT /*+ PARALLEL(16) */
INTO TBL(...)
SELECT ...


то одного хинта не достаточно, нужно сначала перед вставкой выполнить:

Код: plsql
1.
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';


а потом после вставки выполнить соответственно:

Код: plsql
1.
EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL DML';


Это почему так?
Потому что обычно в базах данных по умолчанию отключено параллельное выполнение операций DML.

Но, например, автономной базе данных конфигурации Data Warehouse параллельное выполнение DML включено по умолчанию.
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40102519
igaraev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
verter,

А вы пробовали DISTINCT заменить на group by ?
Я не знаю почему, но иногда это работает намного быстрее.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
SELECT COUNT(*)
FROM (
SELECT CODE, NAME
FROM TBL
WHERE CREATE_DATE = to_date('2021.09.24','YYYY.MM.DD')
GROUP BY CODE, NAME
)
...
Рейтинг: 0 / 0
Замена DISTINCT
    #40102536
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
igaraev,

22375764

.....
stax
...
Рейтинг: 0 / 0
38 сообщений из 38, показаны все 2 страниц
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Замена DISTINCT
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]