Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Замена DISTINCT / 25 сообщений из 38, страница 1 из 2
  1  все
24.09.2021, 15:39
    #40099782
verter
verter
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена DISTINCT
Вопрос знатокам оптимизации производительности.

Есть большая таблица 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
| Ответить | Цитировать | Написать  
24.09.2021, 15:48
    #40099785
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена DISTINCT
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
| Ответить | Цитировать | Написать  
24.09.2021, 15:57
    #40099793
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена DISTINCT
Stax

сравните с
SELECT count(DISTINCT to_char(nvl(CODE,-1),9999999)|| NAME) cc
Должно быть быстрее?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.09.2021, 16:11
    #40099809
verter
verter
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена DISTINCT
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
| Ответить | Цитировать | Написать  
24.09.2021, 16:40
    #40099833
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена DISTINCT
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
| Ответить | Цитировать | Написать  
24.09.2021, 16:54
    #40099845
flexgen
flexgen
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена DISTINCT
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
| Ответить | Цитировать | Написать  
24.09.2021, 17:11
    #40099857
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена DISTINCT
flexgen

А если сделать в два этапа
А почему не в три? Count и distinct-ирование вполне можно разбить на 2 этапа!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.09.2021, 17:24
    #40099860
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена 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.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
| Ответить | Цитировать | Написать  
24.09.2021, 17:27
    #40099862
verter
verter
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена DISTINCT
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
| Ответить | Цитировать | Написать  
24.09.2021, 17:31
    #40099865
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена DISTINCT
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
| Ответить | Цитировать | Написать  
24.09.2021, 17:33
    #40099869
flexgen
flexgen
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена DISTINCT
AmKad
А почему не в три?


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

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


Так то оно так, но если нужны точные значения, то APPROX_COUNT_DISTINCT не подойдет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.09.2021, 17:41
    #40099872
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена DISTINCT
 
Еще один пример на 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
| Ответить | Цитировать | Написать  
24.09.2021, 17:44
    #40099875
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена DISTINCT
flexgen
SQL*Plus

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


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

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

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

А ещё проблема может решаться (а может и не решаться) построением дополнительных индексов. Чтобы не гадать на кофейной гуще, лучше сразу бы приложили план с +ALLSTATS или репорт SQL Monitor.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.09.2021, 17:52
    #40099882
verter
verter
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена DISTINCT
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
| Ответить | Цитировать | Написать  
24.09.2021, 17:59
    #40099892
verter
verter
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена DISTINCT
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
| Ответить | Цитировать | Написать  
24.09.2021, 18:07
    #40099897
flexgen
flexgen
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Замена DISTINCT
SQL*Plus
Приведите примеры, когда нужны точные значения для "Количества разных значений показателя".


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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример не убедительный.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Замена DISTINCT / 25 сообщений из 38, страница 1 из 2
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 303ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1879844]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 174ms
get topic data:
 14ms
get forum data:
 3ms
get page messages:
 63ms
get tp. blocked users:
 2ms
others: 15ms
total:  303ms
x
x
Закрыть


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