ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / И снова об использовании оконными ф-циями индексов
25 сообщений из 32, страница 1 из 2
  1  все
И снова об использовании оконными ф-циями индексов
    #38600689
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
hi all

LI-T3.0.0.31003

Код: plaintext
1.
2.
3.
4.
SQL> show table t;
ID                              INTEGER Not Null
F01                             INTEGER Nullable
CONSTRAINT INTEG_1388:
  Primary key (ID)

В таблице 1 млн записей.
Вот объясните мну, плз, почему запрос:

Код: plaintext
SQL> select f01,lead(f01)over(order by id) from t rows 5;

- имеет план, посылающий в сад индекс PK:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
Select Expression
    -> First N Records
        -> Window
            -> Record Buffer (record length: 49)
                ->  Sort  (record length: 52, key length: 8)
                    -> Window
                        -> Record Buffer (record length: 25)
                            -> Table "T" Full Scan

И соотв-щую статистику:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Statement 180:
-----------------------------------------------------
select f01,lead(f01)over(order by id) from t rows 5
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (T NATURAL)
5 records fetched
   2334 ms, 13348 read(s),  2026686 fetch(es) 

Table                             Natural     Index  
*****************************************************
T                                  1000000
...
Рейтинг: 0 / 0
31.03.2014, 12:43:12
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600698
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Таблоид,

с чего ты решил что здесь уместно использовать индекс? Есть примеры в других СУБД, где он используется на том же самом запросе?
...
Рейтинг: 0 / 0
31.03.2014, 12:48:42
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600749
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Симонов Денисс чего ты решил что здесь уместно использовать индекс? Мне трудно понять, почему в этом случае нельзя использовать индекс. Мы же запрашиваем базу: дай строку, которая будет СЛЕДУЮЩЕЙ по возрастанию ID. А этот самый ID, так уж сложилось, есть PK.
Ну так почему нельзя юзать его ?
Симонов ДенисЕсть примеры в других СУБД, где он используется на том же самом запросе?Дык...
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
SH@ao61 12:59:46>create table t(id int primary key, f01 int);
SH@ao61 13:01:17>insert into t select i,dbms_random.value*10000 from(select level-1 i from dual connect by level<=1000000);
SH@ao61 13:01:25>commit;
SH@ao61 13:01:28>select count(*) from t;

  COUNT(*)
----------
   1000000

SH@ao61 13:01:30>set autot on;
SH@ao61 13:01:40>select f01,lead(f01)over(order by id) from t where rownum<=5;

       F01 LEAD(F01)OVER(ORDERBYID)
---------- ------------------------
      3297                     8498
      8498                      340
       340                     7383
      7383                     7770
      7770

Execution Plan
----------------------------------------------------------
Plan hash value: 2366299382

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     5 |   130 |     3   (0)| 00:00:01 |
|   1 |  WINDOW BUFFER                |                |     5 |   130 |     3   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY               |                |       |       |            |          |
 |   3 |    TABLE ACCESS BY INDEX ROWID| T              |  7732K|   191M|     3   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | SYS_C002062478 |     6 |       |     2   (0)| 00:00:01 | 
------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM<=5)

Note
-----
   - dynamic sampling used for this statement (level=2)

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


PS. Проще говоря, я не понимаю, почему оконная функция в вышеприведенном примере не работает так, как:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
execute block returns (id_curr int, f01_curr int, id_next int, f01_next int) as
  declare variable c cursor for (select id, f01 from t  order by id );
  declare variable n int = 5;
  declare variable i int = 0;
begin
  open c;
  while (n>0) do begin
    if (i=0) then begin
      fetch c into id_curr, f01_curr;
       if (row_count = 0) then leave;
    end
    fetch c into id_next, f01_next;
    if (row_count = 0) then leave;
    suspend;
    id_curr=id_next; f01_curr=f01_next;
    n=n-1;
    i=i+1;
  end
  close c;
end

Код: plaintext
1.
2.
3.
4.
5.
6.
PLAN (T ORDER RDB$PRIMARY305)
5 records fetched
      0 ms, 22 fetch(es)

Table                             Natural     Index 
****************************************************
T                                                 6
...
Рейтинг: 0 / 0
31.03.2014, 13:16:21
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600783
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Таблоид,

1. rownum<=5 это нифига не тоже самое что rows 5
2. Для order by где бы он не находился не всегда эффективней использовать индекс. Обычно он там вредит, если необходимо сделать полную выборку, а не только первые строки. Вот когда появляется rows, то навигация по индексу может оказаться эффективней, но это зависит от фактора кластеризации и отношения того что указано в rows к общему количеству строк.

Думаю что конкретно в этом случае индекс всё же можно было задействовать. Сейчас оконные функции всегда буферизируют весь рекорсет и не прерываются когда ... over(order by id) ... rows ... уже удовлетворяют условиям. Грубо говоря сначала всю выборку запихивают в буфер, а потом только выполняют над ней манипуляции в том числе и сортировки. В таком случае использовать индекс не возможно/не эффективно.
...
Рейтинг: 0 / 0
31.03.2014, 13:32:43
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600797
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Симонов Денис1. rownum<=5 это нифига не тоже самое что rows 5да, вот так правильнее:
Код: plaintext
select f01, f01n from  (  select f01,lead(f01)over(order by id) f01n from t  )  where rownum<=5;
Кстати, орацле и в случае полной выборки попрёт по индексу:
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
SH@ao61 13:37:44>select f01, f01n from (select f01,lead(f01)over(order by id) f01n from t);

Execution Plan
----------------------------------------------------------
Plan hash value: 1546102900

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |  7732K|   191M|   827   (1)| 00:00:04 |
|   1 |  VIEW                         |                |  7732K|   191M|   827   (1)| 00:00:04 |
|   2 |   WINDOW BUFFER               |                |  7732K|   191M|   827   (1)| 00:00:04 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T              |  7732K|   191M|   827   (1)| 00:00:04 |
|   4 |     INDEX FULL SCAN           | SYS_C002062478 |  7732K|       |    26   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          1  db block gets
       4311  consistent gets
          0  physical reads
          0  redo size
   14827120  bytes sent via SQL*Net to client
     733686  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    1000000  rows processed


Симонов ДенисСейчас оконные функции всегда буферизируют весь рекорсет и не прерываются когда ... over(order by id) ... rows ... уже удовлетворяют условиям.Угу
...
Рейтинг: 0 / 0
31.03.2014, 13:40:13
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600811
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Таблоидимеет план, посылающий в сад индекс PK:
Потому что при плане не посылающем в сад этот индекс он вернёт совсем другой результат. А
делать Nested Loop оконные функции не умеют.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
31.03.2014, 13:48:02
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600813
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
ТаблоидКстати, орацле и в случае полной выборки попрёт по индексу

это наверное потому, что он покрывающий или кластерный (IOT)
...
Рейтинг: 0 / 0
31.03.2014, 13:48:32
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600822
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Симонов Денискогда появляется rows, то навигация по индексу может оказаться эффективней, но это зависит от фактора кластеризации и отношения того что указано в rows к общему количеству строк.Пока что вижу, что навигация эта ВСЕГДА эффективнее.
Вот вариант полной выборки курсором:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
execute block returns (id_curr int, f01_curr int, id_next int, f01_next int) as
  declare variable c cursor for (select id, f01 from t order by id);
  declare variable i int = 0;
begin
  open c;
  while (1=1) do begin --
    if (i=0) then begin
       fetch c into id_curr, f01_curr;
       if (row_count = 0) then leave;
    end
    fetch c into id_next, f01_next;
    if (row_count = 0) then leave;
    suspend;
    id_curr=id_next; f01_curr=f01_next;
    i=i+1;
  end
  close c;
end

И статистика по нему:
Код: plaintext
1.
2.
3.
4.
5.
6.
PLAN (T ORDER RDB$PRIMARY305)
999999 records fetched
    4130  ms, 14826 read(s), 3001481 fetch(es)

Table                             Natural     Index 
****************************************************
T                                           1000000

А вот вариант выборки через lead()over():
Код: sql
1.
select id id_curr, f01 f01_curr, lead(id)over(order by id) id_next,lead(f01)over(order by id) f01_next from t;

И статистика:
Код: plaintext
1.
2.
3.
4.
5.
6.
PLAN SORT (T NATURAL)
1000000 records fetched
    6022  ms, 11514 read(s), 2026704 fetch(es)

Table                             Natural   
********************************************
T                                 1000000

"Классика жанра": число фетчей в полтора раза больше (в вырбоке курсором), а время - настолько же меньше :-)
...
Рейтинг: 0 / 0
31.03.2014, 13:53:28
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600829
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Dimitry SibiryakovТаблоидимеет план, посылающий в сад индекс PK:Потому что при плане не посылающем в сад этот индекс он вернёт совсем другой результат.Какой именно ? На трёх строках покажи плз...
...
Рейтинг: 0 / 0
31.03.2014, 13:54:40
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600832
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Симонов ДенисТаблоидКстати, орацле и в случае полной выборки попрёт по индексуэто наверное потому, что он покрывающий или кластерный (IOT)Нет, я таблицу создавал как обычную. Никакого кластера или IOT.
Просто он "так" умеет (бегать только по индексу - index full scan, а иногда и index fast full scan) а мы, к сож-ю, - нет.
...
Рейтинг: 0 / 0
31.03.2014, 13:56:33
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600833
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Таблоид,

не сравнивай разное. Выгодней ли навигация надо проверять по другому

вот с чем надо сравнивать когда нет навигации
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
execute block returns (id_curr int, f01_curr int, id_next int, f01_next int) as
  declare variable c cursor for (select id, f01 from t order by id+0);
  declare variable i int = 0;
begin
  open c;
  while (1=1) do begin --
    if (i=0) then begin
       fetch c into id_curr, f01_curr;
       if (row_count = 0) then leave;
    end
    fetch c into id_next, f01_next;
    if (row_count = 0) then leave;
    suspend;
    id_curr=id_next; f01_curr=f01_next;
    i=i+1;
  end
  close c;
end



да и не забывай жмакать fetch all
...
Рейтинг: 0 / 0
31.03.2014, 13:56:49
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600844
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Таблоид,

да и ещё одно. Я думаю что текущая реализация ближе к объявлению двунаправленного курсора.
...
Рейтинг: 0 / 0
31.03.2014, 14:02:30
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600846
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Симонов Денисвот с чем надо сравнивать когда нет навигации
Код: plsql
1.
...declare variable c cursor for (select id, f01 from t order by id+0); ...

Этим ты сравнишь курсор против вышеприведенного селекта, и оба они будут идти натуралом и сортировку делать (PLAN SORT (T NATURAL)). Где тут вообще навигация и как тогда понять, выгоднее ли она ? :-)
Симонов Денисда и не забывай жмакать fetch allОга. Только в isql нет такой кнопки
...
Рейтинг: 0 / 0
31.03.2014, 14:04:23
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600850
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
ТаблоидЭтим ты сравнишь курсор против вышеприведенного селекта, и оба они будут идти натуралом и сортировку делать

я имел в виду сравнить блоки между собой, а не с оконной функцией.
...
Рейтинг: 0 / 0
31.03.2014, 14:06:20
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600854
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
ТаблоидЭтим ты сравнишь курсор против вышеприведенного селекта, и оба они будут идти натуралом и сортировку делать (PLAN SORT (T NATURAL)).И мну не хотелось бы никого расстраивать, но даже в этом случае курсор уделывает lead()over():
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
2014-03-31T14:06:48.7980 (6403:0x7f2f6a865070) EXECUTE_STATEMENT_FINISH
        sqlex30 (ATT_379, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:8001
                (TRA_12515, CONCURRENCY | WAIT | READ_WRITE)

Statement 133:
-------------------------------------------------------------------------------
execute block returns (id_curr int, f01_curr int, id_next int, f01_next int) as
  declare variable c cursor for (select id, f01 from t order by id +0 );
  declare variable i int = 0;
begin
  open c;
  while (1=1) do begin --
    if (i=0) then begin
       fetch c into id_curr, f01_curr;
       if (row_count = 0) then leave;
    end
    fetch c into id_next, f01_next;
    if (row_count = 0) then leave;
    suspend;
    id_curr=id_next; f01_curr=f01_next;
    i=i+1;
  end
  close c;
end
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (T NATURAL)
999999 records fetched
    3268 ms,  11515 read(s), 2026704 fetch(es)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
T                                 1000000                                                                

2014-03-31T14:07:02.4000 (6403:0x7f2f6a865070) EXECUTE_STATEMENT_FINISH
        sqlex30 (ATT_379, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:8001
                (TRA_12515, CONCURRENCY | WAIT | READ_WRITE)

Statement 134:
-------------------------------------------------------------------------------
select id id_curr, f01 f01_curr, lead(id)over(order by id) id_next,lead(f01)over(order by id) f01_next from t
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (T NATURAL)
1000000 records fetched
    6030 ms,  11515 read(s), 2026704 fetch(es)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
T                                 1000000
...
Рейтинг: 0 / 0
31.03.2014, 14:09:21
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600864
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Симонов Денися имел в виду сравнить блоки между собой, а не с оконной функцией.natural оказался быстрее:
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
2014-03-31T14:10:03.3500 (6403:0x7f2f6a865070) EXECUTE_STATEMENT_FINISH
        sqlex30 (ATT_379, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:8001
                (TRA_12515, CONCURRENCY | WAIT | READ_WRITE)

Statement 135:
-------------------------------------------------------------------------------
execute block returns (id_curr int, f01_curr int, id_next int, f01_next int) as
  declare variable c cursor for (select id, f01 from t order by id);
  declare variable i int = 0;
begin
  open c;
  while (1=1) do begin --
    if (i=0) then begin
       fetch c into id_curr, f01_curr;
       if (row_count = 0) then leave;
    end
    fetch c into id_next, f01_next;
    if (row_count = 0) then leave;
    suspend;
    id_curr=id_next; f01_curr=f01_next;
    i=i+1;
  end
  close c;
end
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T ORDER RDB$PRIMARY305)
999999 records fetched
    4075  ms, 14826 read(s), 3001481 fetch(es)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
T                                           1000000                                                      

2014-03-31T14:10:14.9490 (6403:0x7f2f6a865070) EXECUTE_STATEMENT_FINISH
        sqlex30 (ATT_379, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:8001
                (TRA_12515, CONCURRENCY | WAIT | READ_WRITE)

Statement 136:
-------------------------------------------------------------------------------
execute block returns (id_curr int, f01_curr int, id_next int, f01_next int) as
  declare variable c cursor for (select id, f01 from t order by id +0 );
  declare variable i int = 0;
begin
  open c;
  while (1=1) do begin --
    if (i=0) then begin
       fetch c into id_curr, f01_curr;
       if (row_count = 0) then leave;
    end
    fetch c into id_next, f01_next;
    if (row_count = 0) then leave;
    suspend;
    id_curr=id_next; f01_curr=f01_next;
    i=i+1;
  end
  close c;
end
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (T NATURAL)
999999 records fetched
    3248 ms , 11515 read(s), 2026704 fetch(es)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
T                                 1000000
Итого:
1-е место = твой вариант execute block'a, который с натуралом + сортировкой (3.25 сек)
2-е место = вариант с execute block'a с навигацией по индексу (и отстает, кстати, ненамного от 1-го: 4.0 сек)
3-е место = вариант с оконной функцией (6 сек). А на него столько надежд было...
...
Рейтинг: 0 / 0
31.03.2014, 14:15:29
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600885
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Таблоид,

а теперь сделай курсор двунаправленным

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
execute block returns (id_curr int, f01_curr int, id_next int, f01_next int) as
  declare variable c scroll cursor for (select id, f01 from t order by id);
  declare variable flag boolean = false;
begin
  open c;
  fetch first from c into id_curr, f01_curr;
  flag = (row_count <> 0);
  while (flag) do begin
    fetch relative 0 from c into id_curr, f01_curr;
    fetch next from c into id_next, f01_next;
    flag = (row_count <> 0);
    suspend;
  end
  close c;
end
...
Рейтинг: 0 / 0
31.03.2014, 14:29:55
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600896
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Таблоид2-е место = вариант с execute block'a с навигацией по индексу (и отстает, кстати, ненамного от 1-го: 4.0 сек)

небось ID распределены равномерно да и добавлялись по возрастанию, т.е фактор кластеризации хороший
...
Рейтинг: 0 / 0
31.03.2014, 14:41:01
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600907
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Симонов Дениса теперь сделай курсор двунаправленнымСделал. Получил 3.4 сек против прежних 4.1:
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
Statement 378:
-------------------------------------------------------------------------------
execute block returns (id_curr int, f01_curr int, id_next int, f01_next int) as
  declare variable c scroll cursor for (select id, f01 from t  order by id );
  declare variable flag boolean = false;
begin
  open c;
  fetch first from c into id_curr, f01_curr;
  flag = (row_count <> 0);
  while (flag) do begin
    fetch relative 0 from c into id_curr, f01_curr;
    fetch next from c into id_next, f01_next;
    flag = (row_count <> 0);
    suspend;
  end
  close c;
end
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T ORDER RDB$PRIMARY305)
1000000 records fetched
    3399 ms , 14826 read(s), 3001481 fetch(es)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
T                                           1000000
Хм... интересно в женской бане: это что выходит, двунаправленный курсор лучше однонаправленного ?..

Не, я не против, конечно. Только когда настанет время рулёжа оконных функций-то ?..
...
Рейтинг: 0 / 0
31.03.2014, 14:49:04
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600914
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Симонов Дениснебось ID распределены равномерно да и добавлялись по возрастанию, т.е фактор кластеризации хорошийСправочники в продакшенах почти всегда имеют монотонно возрастающие ID. "Таблицы фактов" - не всегда, но в них львиная доля записей тоже подчиняется этой эмпирике. ИМХО.
...
Рейтинг: 0 / 0
31.03.2014, 14:51:12
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600921
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Таблоид,

ну вообще то он не совсем эквивалентен. Да и ещё одно двунаправленный курсор всегда делает сразу фетч всех записей, т.е. буфферизирует их.

ТаблоидНе, я не против, конечно. Только когда настанет время рулёжа оконных функций-то ?..

когда количество order by увеличиться, например, станет разнонаправленными или по разным полям
...
Рейтинг: 0 / 0
31.03.2014, 14:53:29
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600946
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Симонов Дениснебось ID распределены равномерно да и добавлялись по возрастанию, т.е фактор кластеризации хорошийКогда ID добавляются "задом наперёд":
Код: plaintext
1.
2.
3.
delete from t;
insert into t select 1000000-row_number()over(), rand()*10000 from rdb$types,rdb$types,rdb$types rows 1000000; 
select count(*) from t; 
commit;
- ситуация принципиально НЕ меняется.

1. Однонаправленный курсор, с навигацией: 5090 ms
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
execute block returns (id_curr int, f01_curr int, id_next int, f01_next int) as
  declare variable c cursor for (select id, f01 from t order by id);
  declare variable i int = 0;
begin
  open c;
  while (1=1) do begin --
    if (i=0) then begin
       fetch c into id_curr, f01_curr;
       if (row_count = 0) then leave;
    end
    fetch c into id_next, f01_next;
    if (row_count = 0) then leave;
    suspend;
    id_curr=id_next; f01_curr=f01_next;
    i=i+1;
  end
  close c;
end
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T ORDER RDB$PRIMARY305)
999999 records fetched
   5090 ms, 44907 read(s), 6005697 fetch(es)

Table                             Natural     Index    
***************************************************
T                                           1000000

2. Двунаправленный курсор с навигацией: 4279 ms
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
execute block returns (id_curr int, f01_curr int, id_next int, f01_next int) as
  declare variable c scroll cursor for (select id, f01 from t order by id);
  declare variable flag boolean = false;
begin
  open c;
  fetch first from c into id_curr, f01_curr;
  flag = (row_count <> 0);
  while (flag) do begin
    fetch relative 0 from c into id_curr, f01_curr;
    fetch next from c into id_next, f01_next;
    flag = (row_count <> 0);
    suspend;
  end
  close c;
end
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T ORDER RDB$PRIMARY305)
1000000 records fetched
   4279 ms, 44907 read(s), 6005697 fetch(es)

Table                             Natural     Index    Update    Insert    Delete  
***********************************************************************************
T                                           1000000

3. Однонаправленный курсор БЕЗ навигации: 3760 ms
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
execute block returns (id_curr int, f01_curr int, id_next int, f01_next int) as
  declare variable c cursor for (select id, f01 from t order by id+0);
  declare variable i int = 0;
begin
  open c;
  while (1=1) do begin --
    if (i=0) then begin
       fetch c into id_curr, f01_curr;
       if (row_count = 0) then leave;
    end
    fetch c into id_next, f01_next;
    if (row_count = 0) then leave;
    suspend;
    id_curr=id_next; f01_curr=f01_next;
    i=i+1;
  end
  close c;
end
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (T NATURAL)
999999 records fetched
   3760 ms, 24256 read(s), 4052326 fetch(es)

Table                             Natural     Index    Update    Insert    Delete 
**********************************************************************************
T                                 1000000

4. Двунаправленный курсор БЕЗ навигации: 4061 ms
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
execute block returns (id_curr int, f01_curr int, id_next int, f01_next int) as
  declare variable c scroll cursor for (select id, f01 from t order by id+0);
  declare variable flag boolean = false;
begin
  open c;
  fetch first from c into id_curr, f01_curr;
  flag = (row_count <> 0);
  while (flag) do begin
    fetch relative 0 from c into id_curr, f01_curr;
    fetch next from c into id_next, f01_next;
    flag = (row_count <> 0);
    suspend;
  end
  close c;
end
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (T NATURAL)
1000000 records fetched
   4061 ms, 24256 read(s), 4052326 fetch(es)

Table                             Natural     Index    Update    Insert    Delete
*********************************************************************************
T                                 1000000

5. Оконная функция: 6522 ms
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
select id id_curr, f01 f01_curr, lead(id)over(order by id) id_next,lead(f01)over(order by id) f01_next from t
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (T NATURAL)
1000000 records fetched
   6522 ms, 24256 read(s), 4052326 fetch(es)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
T                                 1000000
...
Рейтинг: 0 / 0
31.03.2014, 15:06:12
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600958
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Симонов Денискогда количество order by увеличиться, например, станет разнонаправленными или по разным полямда как-то опять не сильно бросилось в глаза...
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
2014-03-31T15:13:07.2750 (6403:0x7f309ced8b48) EXECUTE_STATEMENT_FINISH
        sqlex30 (ATT_385, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:9135
                (TRA_12574, CONCURRENCY | WAIT | READ_WRITE)

Statement 398:
-------------------------------------------------------------------------------
execute block returns (id_curr int, f01_curr int, id_next int, f01_next int) as
  declare variable c scroll cursor for (select id, f01 from t order by f01 desc, id desc, f01, id );
  declare variable flag boolean = false;
begin
  open c;
  fetch first from c into id_curr, f01_curr;
  flag = (row_count <> 0);
  while (flag) do begin
    fetch relative 0 from c into id_curr, f01_curr;
    fetch next from c into id_next, f01_next;
    flag = (row_count <> 0);
    suspend;
  end
  close c;
end

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (T NATURAL)
1000000 records fetched
   4339 ms, 24256 read(s), 4052326 fetch(es)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
T                                 1000000

2014-03-31T15:13:34.8440 (6403:0x7f309ced8b48) EXECUTE_STATEMENT_FINISH
        sqlex30 (ATT_385, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:9135
                (TRA_12574, CONCURRENCY | WAIT | READ_WRITE)

Statement 399:
-------------------------------------------------------------------------------
select id id_curr, f01 f01_curr, lead(id)over(order by f01 desc, id desc ) id_next,lead(f01)over(order by f01 desc, id desc, f01, id ) f01_next from t
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (SORT (T NATURAL))
1000000 records fetched
   9737 ms, 24256 read(s), 4052326 fetch(es)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
T                                 1000000
...
Рейтинг: 0 / 0
31.03.2014, 15:14:43
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38600977
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Таблоид,

Код: sql
1.
2.
3.
4.
5.
select f01, 
         lead(f01) over(order by id),
         lead(f01) over(order by id desc)  
from t 
rows 5;



я имел ввиду вот такой случай. Его уже за один селект в блоке не сделаешь
...
Рейтинг: 0 / 0
31.03.2014, 15:29:36
| Ответить | Цитировать | Написать  
И снова об использовании оконными ф-циями индексов
    #38601030
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Симонов ДенисЕго уже за один селект в блоке не сделаешьНды ?..
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
2014-03-31T16:02:41.3680 (6403:0x7f309ced8b48) EXECUTE_STATEMENT_FINISH
        sqlex30 (ATT_385, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:9135
                (TRA_12574, CONCURRENCY | WAIT | READ_WRITE)

Statement 461:
-------------------------------------------------------------------------------
execute block returns (id int, f01_curr int, f01_next int, f01_prev int) as
  declare variable c cursor for (select id, f01 from t order by id);
  declare variable i int = 0;
  declare variable x int;
begin
  open c;
  while (1=1) do begin -- (n>0) do begin --
    if (i=0) then begin
       fetch c into id, f01_curr;
       if (row_count = 0) then leave;
       f01_prev = null;
       i=1;
    end
    fetch c into x, f01_next;
    if (row_count = 0) then leave;
    suspend;
    id=x;
    f01_prev=f01_curr;
    f01_curr=f01_next;
  end
  close c;
end
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T ORDER RDB$PRIMARY305)
999999 records fetched
   4753 ms, 44907 read(s), 6005697 fetch(es)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
T                                           1000000

2014-03-31T16:03:11.7400 (6403:0x7f309ced8b48) EXECUTE_STATEMENT_FINISH
        sqlex30 (ATT_385, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
        /opt/fb30trnk/bin/isql:9135
                (TRA_12574, CONCURRENCY | WAIT | READ_WRITE)

Statement 462:
-------------------------------------------------------------------------------
select id, f01 f01_curr,
         lead(f01) over(order by id) f01_next,
         lead(f01) over(order by id desc)  f01_prev
from t
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT (SORT (T NATURAL))
1000000 records fetched
   9100 ms, 24255 read(s), 4052326 fetch(es)

Table                             Natural     Index    Update    Insert    Delete   Backout     Purge   Expunge
***************************************************************************************************************
T                                 1000000
...
Рейтинг: 0 / 0
31.03.2014, 16:05:11
| Ответить | Цитировать | Написать  
25 сообщений из 32, страница 1 из 2
  1  все
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / И снова об использовании оконными ф-циями индексов
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&fpage=100&tid=1563761]:
 0ms
get settings:
 10ms
get forum list:
 18ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 40ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 62ms
get tp. blocked users:
 2ms
others: 236ms
total:  390ms
созд. / загр.: 390ms
x
x
Закрыть


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