Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Отказаться от сортировки  [new]
Valergrad
Member

Откуда:
Сообщений: 703
Есть вьюха, которая, возвращает два набора полей, назовем их id и value. Время работы вьюхи - 0.4 сек, что приемлемо. Для каждого id в этой вьюхе, есть 2-3, в худшем случае 4 записи.
Нужно выбрать из них одно лучшую, по какому-то известному мне принципу.
Очевидный вариант такой:

select id, first_value(value) over ( partition by id order by ... ) from my_view 

Он работает 3 секунды. Понятно, почему - WINDOW_SORT. Менее очевидный и кривой использует grop by и вложенный запрос, и работает еще дольше. Понятно, почему - GROUP BY SORT.
Но на самом деле ведь тут ничего сортировать не надо, надо пройтись по всем записям и сравнить текущую запись с лучшей для данного набора id, примерно как это делают агрегатные функции.
Как бы написать такой запрос, подскажите идею?
23 июл 10, 15:31    [9150939]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
AlexFF__|
Member

Откуда:
Сообщений: 2852
Valergrad

... order by .... 


Valergrad

Но на самом деле ведь тут ничего сортировать не надо
23 июл 10, 15:37    [9151000]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
JaRo
Member

Откуда:
Сообщений: 1659
Valergrad
Менее очевидный и кривой использует grop by и вложенный запрос, и работает еще дольше
Вернуть то Вы чего хотите? ИМХО Вам как раз группировка(которую Вы называете неочевидной) и нужна, а не приведенный Вами запрос. Сколько Вам строк в результате надо?

Что касается GROUP BY SORT - то это один из способов выполнения группировки (с помощью пересортировки). Не единственный.

+ совершенно некорректно сравнивать время получения первых нескольких записей из вью и полный перебор всех.
23 июл 10, 15:43    [9151053]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
Valergrad
Member

Откуда:
Сообщений: 703
AlexFF__|, поясню
Как найти максимум в массиве? Можно отсортировать и взять первый элемент, а можно пройтись в цикле, каждый раз сравнивая с текущим максимумом, и выбирая больший из двух. Очевидно, второй подход эффективней. Но синтаксис аналитических функций позволяет применить только первый подход ( если я не ошибаюсь). Поэтому там clause order by.
Если же использовать вместо аналитических функций group by, то он тоже сортирует, но уже не внутри окошка, а для того, чтобы вычленить группы.

Я знаю, что сортировка в данной задаче не нужна. Как написать запрос, чтобы в плане выполнения не было сортировки - это я и спрашиваю.
23 июл 10, 15:47    [9151099]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
Valergrad
Member

Откуда:
Сообщений: 703
Jaro, да, фактически группировка мне и нужна.
Но работает она слишком медленно - в 7 раз медленней чем без нее.
Из-за сортировки в плане запроса, которая не нужна.
Как от нее отказаться?
23 июл 10, 15:49    [9151115]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
JaRo
Member

Откуда:
Сообщений: 1659
Valergrad
Как от нее отказаться?
Для начала ответить на вопрос - что Вам нужно получить?
23 июл 10, 15:54    [9151152]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
Valergrad
Member

Откуда:
Сообщений: 703
Для каждого набора полей id, оставить одну запись, у которой некоторая целевая функция от полей value принимает максимум.
23 июл 10, 15:59    [9151199]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
Andrey.L
Member

Откуда: Харьков
Сообщений: 1546
Valergrad

Нужно выбрать из них одно лучшую, по какому-то известному мне принципу.

Как бы написать такой запрос, подскажите идею?
Колись уже ...
23 июл 10, 16:00    [9151209]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18337
Valergrad
Есть вьюха, которая, возвращает два набора полей, назовем их id и value. Время работы вьюхи - 0.4 сек, что приемлемо.

Пример "работы вьюхи за .4 сек" в студию.
Имеется ввиду протокол SQL*Plus
23 июл 10, 16:04    [9151240]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
Valergrad
Member

Откуда:
Сообщений: 703
простой пример:

create table T
(
  ID    NUMBER,
  VALUE NUMBER
);

for i in 1..100000 loop
  insert into t(id,value)
  values(i,rdms_random.value(0,10));
  insert into t(id,value)
  values(i,rdms_random.value(0,10));
  insert into t(id,value)
  values(i,rdms_random.value(0,10));
end loop;

Предположим, вот этот запрос возвращает то, что мне нужно:

select t1.id, t1.value from t t1,
( select id, max(3*value-id) value from t
group by t.id ) t2
where (3*t1.value - t1. id) = t2.value
and t1.id = t2.id

Время его выполнения слишком велико.

Вот этот тоже:

select distinct t.id, first_value(value) over ( partition by t.id order by 3*value-id) from t

Выполняется еще дольше.
Можете предложить оптимизацию?
23 июл 10, 16:09    [9151283]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
AmKad
Member

Откуда:
Сообщений: 5222
Valergrad

for i in 1..100000 loop
  insert into t(id,value)
  values(i,rdms_random.value(0,10));
  insert into t(id,value)
  values(i,rdms_random.value(0,10));
  insert into t(id,value)
  values(i,rdms_random.value(0,10));
end loop;
23 июл 10, 16:15    [9151322]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
Valergrad
Member

Откуда:
Сообщений: 703
dbms_random, разумеется.
А еще во втором запросе desc пропустил.
23 июл 10, 16:17    [9151336]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
spp
Guest
SELECT t.id,
       MAX(value) KEEP(dense_rank FIRST ORDER BY 3*value-id)
  FROM t  
 GROUP BY ID 
23 июл 10, 16:17    [9151337]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6694
Valergrad,

Долго выполняется запрос или фетч?
Трассировку покажите и elapsed time в sqlplus для каждого варианта.
23 июл 10, 16:17    [9151341]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
Andrey.L
Member

Откуда: Харьков
Сообщений: 1546
env,

У него вьюха. Все проблемы (имхо) там.
23 июл 10, 16:20    [9151356]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6694
Valergrad,

примерно в таком виде можете показать?

+
SQL> create or replace view killme_v as
  2  select level id, dbms_random.value(0,10) value from dual connect by level<1e5;

View created.

Elapsed: 00:00:00.26
SQL> set autotr on
SQL>  select count(*) from
  2   (
  3    select t1.id, t1.value from killme_v t1,
  4   ( select id, max(3*value-id) value from killme_v
  5     group by id ) t2
  6    where (3*t1.value - t1. id) = t2.value
  7    and t1.id = t2.id
  8    )
  9    ;

  COUNT(*)
----------
         0

Elapsed: 00:00:02.23

Execution Plan
----------------------------------------------------------
Plan hash value: 1729284391

----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |     1 |    52 |     6  (34)| 00:00:01 |
|   1 |  SORT AGGREGATE                   |          |     1 |    52 |            |          |
|*  2 |   HASH JOIN                       |          |     1 |    52 |     6  (34)| 00:00:01 |
|   3 |    VIEW                           | KILLME_V |     1 |    26 |     2   (0)| 00:00:01 |
|*  4 |     CONNECT BY WITHOUT FILTERING  |          |       |       |            |          |
|   5 |      FAST DUAL                    |          |     1 |       |     2   (0)| 00:00:01 |
|   6 |    VIEW                           |          |     1 |    26 |     3  (34)| 00:00:01 |
|   7 |     HASH GROUP BY                 |          |     1 |    26 |     3  (34)| 00:00:01 |
|   8 |      VIEW                         | KILLME_V |     1 |    26 |     2   (0)| 00:00:01 |
|*  9 |       CONNECT BY WITHOUT FILTERING|          |       |       |            |          |
|  10 |        FAST DUAL                  |          |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T2"."VALUE"=3*"T1"."VALUE"-"T1"."ID" AND "T1"."ID"="T2"."ID")
   4 - filter(LEVEL<1e5)
   9 - filter(LEVEL<1e5)


Statistics
----------------------------------------------------------
         18  recursive calls
          0  db block gets
          4  consistent gets
        285  physical reads
          0  redo size
        341  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*) from (select distinct t.id, first_value(value) over ( partition by t.id order by 3*value-id) from k
illme_v t);

  COUNT(*)
----------
     99999

Elapsed: 00:00:01.43

Execution Plan
----------------------------------------------------------
Plan hash value: 1659143424

----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |     1 |       |     4  (50)| 00:00:01 |
|   1 |  SORT AGGREGATE                   |          |     1 |       |            |          |
|   2 |   VIEW                            |          |     1 |       |     4  (50)| 00:00:01 |
|   3 |    HASH UNIQUE                    |          |     1 |    26 |     4  (50)| 00:00:01 |
|   4 |     WINDOW SORT                   |          |     1 |    26 |     4  (50)| 00:00:01 |
|   5 |      VIEW                         | KILLME_V |     1 |    26 |     2   (0)| 00:00:01 |
|*  6 |       CONNECT BY WITHOUT FILTERING|          |       |       |            |          |
|   7 |        FAST DUAL                  |          |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter(LEVEL<1e5)


Statistics
----------------------------------------------------------
         10  recursive calls
          0  db block gets
          2  consistent gets
        240  physical reads
          0  redo size
        344  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed
23 июл 10, 16:29    [9151427]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Valergrad,

материализуйте результат запроса перед тем, как прикладывать свой
(секретный,видимо) агрегатив. если про селективность не врёте - поможет
23 июл 10, 16:29    [9151433]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
Valergrad
Member

Откуда:
Сообщений: 703
Немного не понял, что вы имели ввиду под словами "материализуйте"?
23 июл 10, 17:05    [9151721]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Valergrad
Как найти максимум в массиве? Можно отсортировать и взять первый элемент, а можно пройтись в цикле, каждый раз сравнивая с текущим максимумом, и выбирая больший из двух. Очевидно, второй подход эффективней. Но синтаксис аналитических функций позволяет применить только первый подход ( если я не ошибаюсь). Поэтому там clause order by.
Если же использовать вместо аналитических функций group by, то он тоже сортирует, но уже не внутри окошка, а для того, чтобы вычленить группы.

Я знаю, что сортировка в данной задаче не нужна. Как написать запрос, чтобы в плане выполнения не было сортировки - это я и спрашиваю.
Мне кажется, что тут хромает логика. Предполагается, что есть "очевидный" быстрый алгоритм выполняющий разделение на группы, сохранение групп в памяти без сортировки, быстрый поиск в этом неупорядоченном списке и быстрый поиск нужного значения внутри этой группы. А, например, алгоритм который вставляет данные в упорядоченное дерево, - это уже медленно, только лишь на основании того, что присутствует слово "sort". Хотя в общем случае оба алгоритма читают данные, выполняют ряд сравнений и формируют структуру в памяти/на диске.
Также надо учитывать, что под "sort" в плане в Oracle могут быть разные алгоритмы. К примеру,
SELECT  MAX(value) FROM t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |    94  (76)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| T    |   265K|  3365K|    94  (76)| 00:00:01 |
---------------------------------------------------------------------------
Но это ведь не значит, что таблица будет сортироваться и в конце выберется максимальное значение. По факту сортировки не будет.
Для примера, через trace 10032 сравним сортировки самого быстрого варианта, предложенного spp
SELECT t.id, MAX(value) KEEP(dense_rank FIRST ORDER BY 3*value-id)  FROM t GROUP BY ID 

---- Sort Parameters ------------------------------
sort_area_size                    9723904
sort_area_retained_size           9723904
sort_multiblock_read_count        1
max intermediate merge width      592
*** 2010-07-26 16:34:21.882
---- Sort Statistics ------------------------------
Input records                             300000
Output records                            100000
Total number of comparisons performed     300047
  Comparisons performed by in-memory sort 300047
Total amount of memory used               9723904
Uses version 1 sort
Does not use asynchronous IO
---- End of Sort Statistics -----------------------

С вариантом через аналитику:
select distinct t.id, first_value(value) over ( partition by t.id order by 3*value-id) from t

---- Sort Parameters ------------------------------
sort_area_size                    20497408
sort_area_retained_size           20497408
sort_multiblock_read_count        1
max intermediate merge width      1250
*** 2010-07-26 16:35:58.699
---- Sort Statistics ------------------------------
Input records                             300000
Output records                            300000
Total number of comparisons performed     2349635
  Comparisons performed by in-memory sort 2349635
Total amount of memory used               20497408
Uses version 2 sort
Does not use asynchronous IO
---- End of Sort Statistics -----------------------
10.2.0.4. разница в Total number of comparisons performed и Total amount of memory used бросается в глаза. И я сомневаюсь что супербыстрый абстрактный вариант без сортировки сможет ограничиться кол-вом сравнений равным кол-ву строк в выборке и соответственно будет быстрей.
26 июл 10, 10:06    [9156554]     Ответить | Цитировать Сообщить модератору
 Re: Отказаться от сортировки  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Valergrad
Немного не понял, что вы имели ввиду под словами "материализуйте"?

имел ввиду заставить оптимизатор выполнить с вашими данными в нужном вам месте шаг temp table transformation, для чего подойдет (например) соответствующий хинт в with кляузе
26 июл 10, 14:01    [9158585]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить