Как лучше сделать запрос?

ils
Дата: 28.09.2004 17:40:04
Здравствуйте. У меня такой вопрос
В таблице присутствуют следующие поля
idsession (number) - id сеанса
idnumber_a (number) - номер абонента
seancedate (date) - время начала разговора
timetalk (number) - длительность разговора (в сек.)

Вообщем, что-то вроде

select idsession, idnumber_a, seancedate, timetalk from seance where idsession=245;

 IDSESSION IDNUMBER_A          SEANCEDATE         TIMETALK
---------- ----------      ----------            ----------
       245    1111             20.05.2003 08:39:54      39,65
       245    2222             20.05.2003 08:40:58      39,53
       245    3333             20.05.2003 08:40:25       ,767
       245    4444             20.05.2003 08:40:37      64,79
       245    5555             20.05.2003 08:41:29
       245    6666             20.05.2003 08:41:09      45,59
       245    7777             20.05.2003 08:41:54      11,77
       245    8888             20.05.2003 08:41:49
       245    9999             20.05.2003 08:41:41      24,53
       

Задача:
Определить номера в конкретной сессии, для которых было зафикировано 2 и более одновременных вызова (нужно макс. кол-во одновременных вызовов с каждого номера), т.е. те номера с которых в какой-то момент времени происходило более одного разговора - проще говоря, многоканальные телефоны

Мой запрос

select to_char(c.num_a) num_a, max(c.count) count_out
    from
   (
     select a.idnumber_a num_a,
              ( select count(*) from seance b where
                b.idnumber_a=a.idnumber_a and
                b.timetalk > to_number(a.seancedate-b.seancedate)*24*60*60 and
                to_number(a.seancedate-b.seancedate)>=0 and
                b.idsession=256 ) count
     from seance a where a.idsession=256
   ) c where count > 1
 group by c.num_a;

План выполнения

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (GROUP BY)
   2    1     VIEW
   3    2       FILTER
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'SEANCE'
   5    4           INDEX (RANGE SCAN) OF 'IND_IDSESSION' (NON-UNIQUE)
   6    3         SORT (AGGREGATE)
   7    6           TABLE ACCESS (BY INDEX ROWID) OF 'SEANCE'
   8    7             AND-EQUAL
   9    8               INDEX (RANGE SCAN) OF 'IND_IDNUMBERA' (NON-UNIQUE)
  10    8               INDEX (RANGE SCAN) OF 'IND_IDSESSION' (NON-UNIQUE)

Запрос нормально работает, если сессия небольшая (сотни - единицы тысяч записей)
Если записей с данным idsession - 40 тыс., то запрос работает больше 2-х минут.
Запустил на 180 тыс. - выполняется уже 3 часа и неизвестно когда будет хеппи енд.
Я так понимаю, все дело в сортировке большого объема данных.
Может кто подскажет, как можно обойти такую сортировку.

Oracle 8.1.7 (win2k)

И еще на всяк случай

SQL> show parameter sort_area;

NAME                                            TYPE    VALUE
------------------------------------ ------- ------------------------------
sort_area_retained_size                 integer         50000000
sort_area_size                           integer       100000000

compute sum of bytes on pool;
break on pool skip 1;
select pool,name,bytes from v$sgastat where pool is null
order by pool, name;

POOL        NAME                            BYTES
----------- -------------------------- ----------
                      db_block_buffers            119488512
                      fixed_sga                       75804
                      log_buffer                      66560
***********                            ----------
sum                                     119630876

Просто увеличил sort_area и буферный кэш - думал поможет - но, видимо, дело не в этом.
Если есть варианты - поделитесь, пожалуйста. Зараннее спасибо.
Black Dragon
Дата: 28.09.2004 18:14:37
Может попробовать с конструкцией having?

Например:

Select Count(*) cnt, idnumber_a
from seance
where idsession=245 and
<Ваши доп. условия>
group by idnumber_a
having Count(*) > 2
z
Дата: 28.09.2004 18:57:42
select to_char(c.num_a) num_a, max(c.count) count_out
    from
   (
     select a.idnumber_a num_a,
              ( select count(*) from seance b where
                b.idnumber_a=a.idnumber_a and
                b.timetalk > to_number(a.seancedate-b.seancedate)*24*60*60 and
                to_number(a.seancedate-b.seancedate)>=0 and
                b.idsession=256 ) count
     from seance a where a.idsession=256
   ) c where count > 1
 group by c.num_a;

попробуй так
select to_char(b.idnumber_a) num_a, count(rowid) num_count from seance a, seance b where
    b.timetalk > to_number(a.seancedate-b.seancedate)*24*60*60 and
    to_number(a.seancedate-b.seancedate)>=0 and
    a.idnumber_a = b.idnumber_a and 
    b.idsession=256 and a.idsession=256 
group by b.idnumber_a
having num_count>1
z
Дата: 28.09.2004 19:09:47
select to_char(b.idnumber_a) num_a, count(*) num_count from seance a, seance b where
    b.timetalk > to_number(a.seancedate-b.seancedate)*24*60*60 and
    to_number(a.seancedate-b.seancedate)>=0 and
    a.idnumber_a = b.idnumber_a and 
    b.idsession=256 and a.idsession=256 
group by b.idnumber_a
having num_count>1

ошибочка =)
ils
Дата: 28.09.2004 19:41:11
2 Z

select to_char(b.idnumber_a) num_a, count(*) num_count from seance a, seance b where
    b.timetalk > to_number(a.seancedate-b.seancedate)*24*60*60 and
    to_number(a.seancedate-b.seancedate)>=0 and
    a.idnumber_a = b.idnumber_a and 
    b.idsession=256 and a.idsession=256 
group by b.idnumber_a
having num_count>1

То же самое получается. Я понял, что мне здесь здесь нужно как-то избежать запроса
select a.idnumber_a, .... from seance a where idsession=... 
- не делать выборку всех idnumber_a (в моем случае - это 180 тыс. записей), а только тех, которые встречаются по-крайней мере два раза - ведь если номер встречается только один раз, то одновременных вызовов с него точно не было.

Нужно что-то вроде
select a.idnumber_a, 
.... 

from
(select idnumber_a, idsession, count(*) from seance where
   idsession=245
  group by idnumber_a, idsession
  having count(*)>1) a

....

Вместо 180 тыс. записей таким образом возвращается меньше 10 тыс.

Только мне еще необходимо как-то получить поле seancedate - я же не могу по нему группировать
igor2222
Дата: 28.09.2004 19:54:50
С ходу может неправильно, но главное мысль :-)
SELECT * FROM seance a
WHERE EXISTS
(SELECT FIELD FROM seance b
WHERE a.idnumber_a=ib.dnumber_a and a.idsession=b.dsession and
idsession=245 and a.rowid != b.rowid) and idsession=245
ils
Дата: 29.09.2004 11:09:21
igor2222
С ходу может неправильно, но главное мысль :-)
SELECT * FROM seance a
WHERE EXISTS
(SELECT FIELD FROM seance b
WHERE a.idnumber_a=ib.dnumber_a and a.idsession=b.dsession and
idsession=245 and a.rowid != b.rowid) and idsession=245


Для сеанса с 180 тыс. записями:

select count(*) from
(
SELECT * FROM seance a 
WHERE EXISTS 
(SELECT 1 FROM seance b 
WHERE a.idnumber_a=b.idnumber_a and a.idsession=b.idsession and
idsession=260 and a.rowid != b.rowid) and idsession=260 
);


COUNT(*)
----------
    171167

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

Номеров же в сеансе, которые встречались более одного раза вот сколько

select count(*) from
(
 select idsession, idnumber_a, count(idnumber_a) from seance
   where idsession=260 
 group by idsession, idnumber_a
 having count(idnumber_a)>1
);


COUNT(*)
----------
      9734

Может для моего запроса нужен вообще другой подход?
igor2222
Дата: 29.09.2004 11:33:07
Возможно я чего то не понял, но
автор
Только мне еще необходимо как-то получить поле seancedate - я же не могу по нему группировать

Вот и выбирай не select *, а distinct того что нужно или group by.
Если сгруппировать
автор
Такой запрос отсекает номера, которые встречались только один раз за сеанс, а таких не так уж и много.
, то насколько я понимаю получается
автор
Номеров же в сеансе, которые встречались более одного раза вот сколько

Или я таки чего то не понял?
Sir
Дата: 29.09.2004 12:02:52
Хотелось бы отказаться от коррелированных запросов.
Может как-нибудь так попытаться:
SELECT idnumber_a, MAX(cnt) 
  FROM (
    SELECT idnumber_a, ch_date, 
               SUM(cnt) OVER(PARTITION BY idnumber_a ORDER BY ch_date ROWS UNBOUNDED PRECEDING) AS cnt 
     FROM(
        SELECT idnumber_a, seancedate AS ch_date, 1 AS cnt 
           FROM seance WHERE idsession = 256
        UNION ALL
        SELECT idnumber_a, seancedate + timetalk/(24*60*60), -1 
           FROM seance WHERE idsession = 256
     )
  )
  GROUP BY idnumber_a
ils
Дата: 29.09.2004 12:20:21
igor2222
Возможно я чего то не понял, но
автор
Только мне еще необходимо как-то получить поле seancedate - я же не могу по нему группировать

Вот и выбирай не select *, а distinct того что нужно или group by.


Я хочу избежать distinct и group by для 171 тыс. записей - мне сначала нужно вернуть меньшее количество, а потом уже сортировать

2 Sir

А Ваш вариант мне надо будет обдумать и переварить :-)