ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Замена DISTINCT
25 сообщений из 38, страница 1 из 2
  1  все
Замена DISTINCT
    #40099782
verter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
24.09.2021, 15:39
| Ответить | Цитировать | Написать  
Замена DISTINCT
    #40099785
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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, 15:48
| Ответить | Цитировать | Написать  
Замена DISTINCT
    #40099793
Фотография AmKad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AmKad
Участник
Stax

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

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


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

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


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

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


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

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

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

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


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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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