Отсутствует STOPKEY для раздела в Oracle для планирования поискового вызова по локальному индексу

Существует следующая секционированная таблица:

CREATE TABLE "ERMB_LOG_TEST_BF"."OUT_SMS"(
    "TRX_ID" NUMBER(19,0) NOT NULL ENABLE,
    "CREATE_TS" TIMESTAMP (3) DEFAULT systimestamp NOT NULL ENABLE,
    /* other fields... */
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "ERMB_LOG_TEST_BF"
  PARTITION BY RANGE ("TRX_ID") INTERVAL (281474976710656)
  (PARTITION "SYS_P1358"  VALUES LESS THAN (59109745109237760) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "ERMB_LOG_TEST_BF");

CREATE INDEX "ERMB_LOG_TEST_BF"."OUT_SMS_CREATE_TS_TRX_ID_IX" ON "ERMB_LOG_TEST_BF"."OUT_SMS" ("CREATE_TS" DESC, "TRX_ID" DESC)
    PCTFREE 10 INITRANS 2 MAXTRANS 255
    STORAGE(
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
    (PARTITION "SYS_P1358"
    PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "ERMB_LOG_TEST_BF");

У меня есть sql-запрос, который выбирает 20 записей, упорядоченных по дате и транзакции:

select rd from (
    select /*+ INDEX(OUT_SMS OUT_SMS_CREATE_TS_TRX_ID_IX) */ rowid rd
    from OUT_SMS     
    where  TRX_ID between 34621422135410688 and 72339069014638591       
       and CREATE_TS between to_timestamp('2013-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') 
                         and to_timestamp('2013-03-06 08:57:00', 'yyyy-mm-dd hh24:mi:ss')       
    order by CREATE_TS DESC, TRX_ID DESC
) where rownum <= 20

Oracle сформировал следующий план:

    -----------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                             |    20 |   240 |       |  4788K  (1)| 00:05:02 |       |       |
    |*  1 |  COUNT STOPKEY              |                             |       |       |       |            |          |       |       |
    |   2 |   VIEW                      |                             |   312M|  3576M|       |  4788K  (1)| 00:05:02 |       |       |
    |*  3 |    SORT ORDER BY STOPKEY    |                             |   312M|     9G|    12G|  4788K  (1)| 00:05:02 |       |       |
    |   4 |     PARTITION RANGE ITERATOR|                             |   312M|     9G|       |    19   (0)| 00:00:01 |     1 |    48 |
    |*  5 |      COUNT STOPKEY          |                             |       |       |       |            |          |       |       |
    |*  6 |       INDEX RANGE SCAN      | OUT_SMS_CREATE_TS_TRX_ID_IX |   312M|     9G|       |    19   (0)| 00:00:01 |     1 |    48 |
    -----------------------------------------------------------------------------------------------------------------------------------

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

    1 - filter(ROWNUM<=20)
    3 - filter(ROWNUM<=20)
    5 - filter(ROWNUM<=20)
    6 - access(SYS_OP_DESCEND("CREATE_TS")>=HEXTORAW('878EFCF9F6C5FEFAFF')  AND
    SYS_OP_DESCEND("TRX_ID")>=HEXTORAW('36F7E7D7F8A4F0BFA9A3FF')  AND
    SYS_OP_DESCEND("CREATE_TS")<=HEXTORAW('878EFDFEF8FEF8FF')  AND
    SYS_OP_DESCEND("TRX_ID")<=HEXTORAW('36FBD0E9D4E9DBD5F8A6FF') )
    filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))<=TIMESTAMP' 2013-03-06 08:57:00,000000000' AND
    SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))<=72339069014638591 AND
    SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))>=34621422135410688 AND
    SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))>=TIMESTAMP' 2013-02-01 00:00:00,000000000')

Он отлично работает.

Кстати, таблица OUT_SMS разделена на поле TRX_ID, а OUT_SMS_CREATE_TS_TRX_ID_IX - это локальный индекс (CREATE_TS DESC, TRX_ID DESC) на каждом разделе.

Но если я преобразую этот запрос в подготовленный оператор:

select rd from (
    select /*+ INDEX(OUT_SMS OUT_SMS_CREATE_TS_TRX_ID_IX) */ rowid rd
    from OUT_SMS     
    where  TRX_ID between ? and ?       
       and CREATE_TS between ? and ?
    order by CREATE_TS DESC, TRX_ID DESC
) where rownum <= 20

Oracle создает следующий план:

    ----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                             |    20 |   240 | 14743   (1)| 00:00:01 |       |       |
    |*  1 |  COUNT STOPKEY               |                             |       |       |            |          |       |       |
    |   2 |   VIEW                       |                             |  1964 | 23568 | 14743   (1)| 00:00:01 |       |       |
    |*  3 |    SORT ORDER BY STOPKEY     |                             |  1964 | 66776 | 14743   (1)| 00:00:01 |       |       |
    |*  4 |     FILTER                   |                             |       |       |            |          |       |       |
    |   5 |      PARTITION RANGE ITERATOR|                             |  1964 | 66776 | 14742   (1)| 00:00:01 |   KEY |   KEY |
    |*  6 |       INDEX RANGE SCAN       | OUT_SMS_CREATE_TS_TRX_ID_IX |  1964 | 66776 | 14742   (1)| 00:00:01 |   KEY |   KEY |
    ----------------------------------------------------------------------------------------------------------------------------

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

    1 - filter(ROWNUM<=20)
    3 - filter(ROWNUM<=20)
    4 - filter(TO_TIMESTAMP(:RR,'yyyy-mm-dd hh24:mi:ss')<=TO_TIMESTAMP(:T,'yyyy-mm-dd hh24:mi:ss') AND
    TO_NUMBER(:ABC)<=TO_NUMBER(:EBC))
    6 - access(SYS_OP_DESCEND("CREATE_TS")>=SYS_OP_DESCEND(TO_TIMESTAMP(:T,'yyyy-mm-dd hh24:mi:ss')) AND
    SYS_OP_DESCEND("TRX_ID")>=SYS_OP_DESCEND(TO_NUMBER(:EBC)) AND
    SYS_OP_DESCEND("CREATE_TS")<=SYS_OP_DESCEND(TO_TIMESTAMP(:RR,'yyyy-mm-dd hh24:mi:ss')) AND
    SYS_OP_DESCEND("TRX_ID")<=SYS_OP_DESCEND(TO_NUMBER(:ABC)))
    filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))>=TO_NUMBER(:ABC) AND
    SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))<=TO_NUMBER(:EBC) AND
    SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))>=TO_TIMESTAMP(:RR,'yyyy-mm-dd hh24:mi:ss') AND
    SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))<=TO_TIMESTAMP(:T,'yyyy-mm-dd hh24:mi:ss'))

Операция COUNT STOPKEY исчезает из плана. Эта операция должна быть после анализа индекса для получения 20 строк из каждого раздела, как и первый запрос.

Как я могу составить подготовленный оператор, чтобы иметь COUNT STOPKEY в плане?

Ответ 1

Когда вы используете переменные связывания, Oracle вынуждена использовать обрезку динамических разделов вместо статической обрезки разделов. Результатом этого является то, что Oracle не знает во время разбора, к которым будут доступны разделы, поскольку это изменяется на основе ваших входных переменных.

Это означает, что при использовании литеральных значений (вместо переменных привязки) мы знаем, к каким разделам будет обращаться ваш локальный индекс. Поэтому count stopkey может быть применена к выходу индекса, прежде чем обрезать разделы.

При использовании переменных связывания partition range iterator должен определить, к каким разделам вы обращаетесь. Затем он имеет проверку, чтобы убедиться, что первая из ваших переменных в операциях действительно имеет меньшее значение, а второе (операция filter во втором плане).

Это можно легко воспроизвести, как показывает следующий тестовый пример:

create table tab (
  x date,
  y integer,
  filler varchar2(100)
) partition by range(x) (
  partition p1 values less than (date'2013-01-01'),
  partition p2 values less than (date'2013-02-01'),
  partition p3 values less than (date'2013-03-01'),
  partition p4 values less than (date'2013-04-01'),
  partition p5 values less than (date'2013-05-01'),
  partition p6 values less than (date'2013-06-01')
);


insert into tab (x, y)
  select add_months(trunc(sysdate, 'y'), mod(rownum, 5)), rownum, dbms_random.string('x', 50)
  from   dual 
  connect by level <= 1000;

create index i on tab(x desc, y desc) local;

exec dbms_stats.gather_table_stats(user, 'tab', cascade => true);

explain plan for 
SELECT * FROM (
  SELECT rowid FROM tab
  where  x between date'2013-01-01' and date'2013-02-02'
  and    y between 50 and 100
  order  by x desc, y desc
)
where rownum <= 5;

SELECT * FROM table(dbms_xplan.display(null, null, 'BASIC +ROWS +PARTITION'));

--------------------------------------------------------------------                                                                                                                                                                                                                                         
| Id  | Operation                   | Name | Rows  | Pstart| Pstop |                                                                                                                                                                                                                                         
--------------------------------------------------------------------                                                                                                                                                                                                                                         
|   0 | SELECT STATEMENT            |      |     1 |       |       |                                                                                                                                                                                                                                         
|   1 |  COUNT STOPKEY              |      |       |       |       |                                                                                                                                                                                                                                         
|   2 |   VIEW                      |      |     1 |       |       |                                                                                                                                                                                                                                         
|   3 |    SORT ORDER BY STOPKEY    |      |     1 |       |       |                                                                                                                                                                                                                                         
|   4 |     PARTITION RANGE ITERATOR|      |     1 |     2 |     3 |                                                                                                                                                                                                                                         
|   5 |      COUNT STOPKEY          |      |       |       |       |                                                                                                                                                                                                                                         
|   6 |       INDEX RANGE SCAN      | I    |     1 |     2 |     3 |                                                                                                                                                                                                                                         
-------------------------------------------------------------------- 

explain plan for 
SELECT * FROM (
  SELECT rowid FROM tab
  where  x between to_date(:st, 'dd/mm/yyyy') and to_date(:en, 'dd/mm/yyyy')
  and    y between :a and :b
  order  by x desc, y desc
)
where rownum <= 5;

SELECT * FROM table(dbms_xplan.display(null, null, 'BASIC +ROWS +PARTITION'));

---------------------------------------------------------------------                                                                                                                                                                                                                                        
| Id  | Operation                    | Name | Rows  | Pstart| Pstop |                                                                                                                                                                                                                                        
---------------------------------------------------------------------                                                                                                                                                                                                                                        
|   0 | SELECT STATEMENT             |      |     1 |       |       |                                                                                                                                                                                                                                        
|   1 |  COUNT STOPKEY               |      |       |       |       |                                                                                                                                                                                                                                        
|   2 |   VIEW                       |      |     1 |       |       |                                                                                                                                                                                                                                        
|   3 |    SORT ORDER BY STOPKEY     |      |     1 |       |       |                                                                                                                                                                                                                                        
|   4 |     FILTER                   |      |       |       |       |                                                                                                                                                                                                                                        
|   5 |      PARTITION RANGE ITERATOR|      |     1 |   KEY |   KEY |                                                                                                                                                                                                                                        
|   6 |       INDEX RANGE SCAN       | I    |     1 |   KEY |   KEY |                                                                                                                                                                                                                                        
---------------------------------------------------------------------

Как и в вашем примере, второй запрос может только фильтровать разделы на key во время разбора, а не на точные разделы, как в первом примере.

Это один из тех редких случаев, когда литеральные значения могут обеспечить лучшую производительность, чем переменные связывания. Вы должны выяснить, возможно ли это для вас.

Наконец, вы говорите, что хотите 20 строк из каждого раздела. Ваш запрос как стенд не будет делать этого, он просто вернет вам первые 20 строк в соответствии с вашим заказом. Для 20 строк/разделов вам нужно сделать что-то вроде этого:

select rd from (
    select rowid rd, 
           row_number() over (partition by trx_id order by create_ts desc) rn
    from OUT_SMS     
    where  TRX_ID between ? and ?       
       and CREATE_TS between ? and ?
    order by CREATE_TS DESC, TRX_ID DESC
) where rn <= 20

ОБНОВИТЬ

Причина, по которой вы не получаете count stopkey с работой filter в строке 4 "плохого" плана. Вы можете увидеть это более четко, если повторить вышеприведенный пример, но без разделения.

Это дает вам следующие планы:

----------------------------------------                                                                                                                                                                                                                                                                     
| Id  | Operation               | Name |                                                                                                                                                                                                                                                                     
----------------------------------------                                                                                                                                                                                                                                                                     
|   0 | SELECT STATEMENT        |      |                                                                                                                                                                                                                                                                     
|*  1 |  COUNT STOPKEY          |      |                                                                                                                                                                                                                                                                     
|   2 |   VIEW                  |      |                                                                                                                                                                                                                                                                     
|*  3 |    SORT ORDER BY STOPKEY|      |                                                                                                                                                                                                                                                                     
|*  4 |     TABLE ACCESS FULL   | TAB  |                                                                                                                                                                                                                                                                     
----------------------------------------                                                                                                                                                                                                                                                                     

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

   1 - filter(ROWNUM<=5)                                                                                                                                                                                                                                                                                     
   3 - filter(ROWNUM<=5)                                                                                                                                                                                                                                                                                     
   4 - filter("X">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd                                                                                                                                                                                                                                              
              hh24:mi:ss') AND "X"<=TO_DATE(' 2013-02-02 00:00:00', 'syyyy-mm-dd                                                                                                                                                                                                                             
              hh24:mi:ss') AND "Y">=50 AND "Y"<=100)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

----------------------------------------                                                                                                                                                                                                                                                                     
| Id  | Operation               | Name |                                                                                                                                                                                                                                                                     
----------------------------------------                                                                                                                                                                                                                                                                     
|   0 | SELECT STATEMENT        |      |                                                                                                                                                                                                                                                                     
|*  1 |  COUNT STOPKEY          |      |                                                                                                                                                                                                                                                                     
|   2 |   VIEW                  |      |                                                                                                                                                                                                                                                                     
|*  3 |    SORT ORDER BY STOPKEY|      |                                                                                                                                                                                                                                                                     
|*  4 |     FILTER              |      |                                                                                                                                                                                                                                                                     
|*  5 |      TABLE ACCESS FULL  | TAB  |                                                                                                                                                                                                                                                                     
----------------------------------------                                                                                                                                                                                                                                                                     

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

   1 - filter(ROWNUM<=5)                                                                                                                                                                                                                                                                                     
   3 - filter(ROWNUM<=5)                                                                                                                                                                                                                                                                                     
   4 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B) AND                                                                                                                                                                                                                                                               
              TO_DATE(:ST,'dd/mm/yyyy')<=TO_DATE(:EN,'dd/mm/yyyy'))                                                                                                                                                                                                                                          
   5 - filter("Y">=TO_NUMBER(:A) AND "Y"<=TO_NUMBER(:B) AND                                                                                                                                                                                                                                                  
              "X">=TO_DATE(:ST,'dd/mm/yyyy') AND "X"<=TO_DATE(:EN,'dd/mm/yyyy'))

Как вы можете видеть, есть дополнительная операция filter когда вы используете переменные связывания, появляющиеся перед sort order by stopkey. Это происходит после доступа к индексу. Это проверяет, что значения для переменных позволят возвращать данные (первая переменная в вашем случае действительно имеет меньшее значение, чем вторая). Это не обязательно при использовании литералов, потому что оптимизатор уже знает, что 50 меньше 100 (в данном случае). Он не знает, будет ли: a меньше: b во время разбора.

Почему именно это я не знаю. Это может быть преднамеренный дизайн Oracle - нет смысла делать проверку стоп-ключа, если значения, установленные для переменных, приводят к нулевым строкам - или просто надзор.

Ответ 2

Я могу воспроизвести ваши выводы по 11.2.0.3. Вот мой тестовый пример:

SQL> -- Table with 100 partitions of 100 rows 
SQL> CREATE TABLE out_sms
  2  PARTITION BY RANGE (trx_id)
  3     INTERVAL (100) (PARTITION p0 VALUES LESS THAN (0))
  4  AS
  5  SELECT ROWNUM trx_id,
  6         trunc(SYSDATE) + MOD(ROWNUM, 50) create_ts
  7  FROM dual CONNECT BY LEVEL <= 10000;

Table created

SQL> CREATE INDEX OUT_SMS_IDX ON out_sms (create_ts desc, trx_id desc) LOCAL;

Index created

[static plan]

SELECT rd
  FROM (SELECT /*+ INDEX(OUT_SMS OUT_SMS_IDX) */
         rowid rd
          FROM out_sms
         WHERE create_ts BETWEEN systimestamp AND systimestamp + 10
           AND trx_id BETWEEN 1 AND 500
         ORDER BY create_ts DESC, trx_id DESC)
 WHERE rownum <= 20;    
---------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Pstart| Pstop |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |       |       |
|*  1 |  COUNT STOPKEY              |             |       |       |       |
|   2 |   VIEW                      |             |     1 |       |       |
|*  3 |    SORT ORDER BY STOPKEY    |             |     1 |       |       |
|   4 |     PARTITION RANGE ITERATOR|             |     1 |     2 |     7 |
|*  5 |      COUNT STOPKEY          |             |       |       |       |
|*  6 |       INDEX RANGE SCAN      | OUT_SMS_IDX |     1 |     2 |     7 |
---------------------------------------------------------------------------

[dynamic]     
----------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |       |       |
|*  1 |  COUNT STOPKEY               |             |       |       |       |
|   2 |   VIEW                       |             |     1 |       |       |
|*  3 |    SORT ORDER BY STOPKEY     |             |     1 |       |       |
|*  4 |     FILTER                   |             |       |       |       |
|   5 |      PARTITION RANGE ITERATOR|             |     1 |   KEY |   KEY |
|*  6 |       INDEX RANGE SCAN       | OUT_SMS_IDX |     1 |   KEY |   KEY |
----------------------------------------------------------------------------

Как и в вашем примере предикат ROWNUM помещается внутри, сканирование диапазона индекса раздела в первом случае, а не во втором случае. При использовании статических переменных план показывает, что Oracle извлекает только 20 строк на раздел, тогда как используя динамические переменные, Oracle будет извлекать строки all, которые удовлетворяют предложению WHERE в каждом разделе. Я не мог найти настройки или конфигурацию статистики, где предикат можно было бы нажать при использовании переменных привязки.

Я надеялся, что вы можете использовать динамические фильтры с более широкими статическими ограничениями для игры в системе, но кажется, что предикат ROWNUM не используется внутри отдельных разделов, как только присутствуют динамические переменные:

SELECT rd
  FROM (SELECT /*+ INDEX(OUT_SMS OUT_SMS_IDX) */
         rowid rd
          FROM out_sms
         WHERE nvl(create_ts+:5, sysdate) BETWEEN :1 AND :2
           AND nvl(trx_id+:6, 0) BETWEEN :3 AND :4
           AND trx_id BETWEEN 1 AND 500
           AND create_ts BETWEEN systimestamp AND systimestamp + 10
         ORDER BY create_ts DESC, trx_id DESC)
 WHERE rownum <= 20

Plan hash value: 2740263591

----------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |       |       |
|*  1 |  COUNT STOPKEY               |             |       |       |       |
|   2 |   VIEW                       |             |     1 |       |       |
|*  3 |    SORT ORDER BY STOPKEY     |             |     1 |       |       |
|*  4 |     FILTER                   |             |       |       |       |
|   5 |      PARTITION RANGE ITERATOR|             |     1 |     2 |     7 |
|*  6 |       INDEX RANGE SCAN       | OUT_SMS_IDX |     1 |     2 |     7 |
----------------------------------------------------------------------------

Если этот запрос важен и его производительность имеет решающее значение, вы можете преобразовать индекс в глобальный индекс. Это увеличит обслуживание разделов, но большинство операций с разделами можно будет использовать в Интернете с последними версиями Oracle. В этом случае глобальный индекс будет работать со стандартной несегментированной таблицей:

SQL> drop index out_sms_idx;

Index dropped

SQL> CREATE INDEX OUT_SMS_IDX ON out_sms (create_ts DESC, trx_id desc);

Index created

SELECT rd
  FROM (SELECT 
         rowid rd
          FROM out_sms
         WHERE create_ts BETWEEN :1 AND :2
           AND trx_id BETWEEN :3 AND :4
         ORDER BY create_ts DESC, trx_id DESC)
 WHERE rownum <= 20

------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     1 |    12 |     2   (0)|
|*  1 |  COUNT STOPKEY      |             |       |       |            |
|   2 |   VIEW              |             |     1 |    12 |     2   (0)|
|*  3 |    FILTER           |             |       |       |            |
|*  4 |     INDEX RANGE SCAN| OUT_SMS_IDX |     1 |    34 |     2   (0)|
------------------------------------------------------------------------

Ответ 3

Я могу подтвердить, что проблема, о которой идет речь, по-прежнему является проблемой для Oracle 12.1.0.2.0.

И даже жестко ограниченных границ исключения раздела недостаточно.

Вот таблица тестов в моем случае:

CREATE TABLE FR_MESSAGE_PART (
    ID NUMBER(38) NOT NULL CONSTRAINT PK_FR_MESSAGE_PART PRIMARY KEY USING INDEX LOCAL,
    TRX_ID NUMBER(38) NOT NULL, TS TIMESTAMP NOT NULL, TEXT CLOB)
    PARTITION BY RANGE (ID) (PARTITION PART_0 VALUES LESS THAN (0));
CREATE INDEX IX_FR_MESSAGE_PART_TRX_ID ON FR_MESSAGE_PART(TRX_ID) LOCAL;
CREATE INDEX IX_FR_MESSAGE_PART_TS ON FR_MESSAGE_PART(TS) LOCAL;

В течение нескольких месяцев таблица заполняется несколькими миллионами данных о производстве OLTP. Каждый месяц принадлежит отдельному разделу.

Значения первичных ключей этой таблицы всегда включают временную часть в более высоких битах, что позволяет использовать ID для разбиения диапазонов. Все сообщения наследуют более высокие биты времени TRX_ID. Это гарантирует, что все сообщения, относящиеся к одной и той же бизнес-операции, всегда попадают в один раздел.

Начните с жестко запрограммированного запроса для выбора страницы последних сообщений за определенный период времени с применяемыми ограничениями на удаление разделов:

select * from (select * from FR_MESSAGE_PART
where TS >= DATE '2017-11-30' and TS < DATE '2017-12-02'
  and ID >= 376894993815568384 and ID < 411234940974268416
order by TS DESC) where ROWNUM <= 40;

Но, свежая статистика таблицы, оптимизатор Oracle все еще ошибочно оценивает, что сортировка двух целых месячных разделов будет быстрее, чем сканирование диапазона в течение двух дней с помощью существующего локального индекса:

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    40 | 26200 |       |   103K  (1)| 00:00:05 |       |       |
|*  1 |  COUNT STOPKEY              |                 |       |       |       |            |          |       |       |
|   2 |   VIEW                      |                 |   803K|   501M|       |   103K  (1)| 00:00:05 |       |       |
|*  3 |    SORT ORDER BY STOPKEY    |                 |   803K|    70M|    92M|   103K  (1)| 00:00:05 |       |       |
|   4 |     PARTITION RANGE ITERATOR|                 |   803K|    70M|       | 86382   (1)| 00:00:04 |     2 |     3 |
|*  5 |      TABLE ACCESS FULL      | FR_MESSAGE_PART |   803K|    70M|       | 86382   (1)| 00:00:04 |     2 |     3 |
-----------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=40)
   3 - filter(ROWNUM<=40)
   5 - filter("TS"<TIMESTAMP' 2017-12-01 00:00:00' AND "TS">=TIMESTAMP' 2017-11-29 00:00:00' AND 
              "ID">=376894993815568384)

Фактическое время выполнения отображается на порядок больше, чем предполагалось в плане.

Поэтому нам нужно применить подсказку для принудительного использования индекса:

select * from (select /*+ FIRST_ROWS(40) INDEX(FR_MESSAGE_PART (TS)) */ * from FR_MESSAGE_PART
where TS >= DATE '2017-11-30' and TS < DATE '2017-12-02'
  and ID >= 376894993815568384 and ID < 411234940974268416
order by TS DESC) where ROWNUM <= 40;

Теперь план использует индекс, но все же включает медленную сортировку двух целых разделов:

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                       |    40 | 26200 |       |   615K  (1)| 00:00:25 |       |       |
|*  1 |  COUNT STOPKEY                                |                       |       |       |       |            |          |       |       |
|   2 |   VIEW                                        |                       |   803K|   501M|       |   615K  (1)| 00:00:25 |       |       |
|*  3 |    SORT ORDER BY STOPKEY                      |                       |   803K|    70M|    92M|   615K  (1)| 00:00:25 |       |       |
|   4 |     PARTITION RANGE ITERATOR                  |                       |   803K|    70M|       |   598K  (1)| 00:00:24 |     2 |     3 |
|*  5 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FR_MESSAGE_PART       |   803K|    70M|       |   598K  (1)| 00:00:24 |     2 |     3 |
|*  6 |       INDEX RANGE SCAN                        | IX_FR_MESSAGE_PART_TS |   576K|       |       |  2269   (1)| 00:00:01 |     2 |     3 |
-----------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=40)
   3 - filter(ROWNUM<=40)
   5 - filter("ID">=376894993815568384)
   6 - access("TS">=TIMESTAMP' 2017-11-30 00:00:00' AND "TS"<TIMESTAMP' 2017-12-02 00:00:00')

После некоторых попыток поиска ссылок на Oracle и google было обнаружено, что мы также должны явно указывать нисходящее направление для сканирования диапазона индексов с помощью указателя INDEX_DESC или INDEX_RS_DESC:

select * from (select /*+ FIRST_ROWS(40) INDEX_RS_DESC(FR_MESSAGE_PART (TS)) */ * from FR_MESSAGE_PART
where TS >= DATE '2017-11-30' and TS < DATE '2017-12-02'
  and ID >= 376894993815568384 and ID < 411234940974268416
order by TS DESC) where ROWNUM <= 40;

Это, наконец, дает быстрый план с COUNT STOPKEY каждого раздела, который сканирует разделы в порядке убывания и сортирует не более 40 строк из каждого раздела:

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                       |    40 | 26200 |       |   615K  (1)| 00:00:25 |       |       |
|*  1 |  COUNT STOPKEY                                 |                       |       |       |       |            |          |       |       |
|   2 |   VIEW                                         |                       |   803K|   501M|       |   615K  (1)| 00:00:25 |       |       |
|*  3 |    SORT ORDER BY STOPKEY                       |                       |   803K|    70M|    92M|   615K  (1)| 00:00:25 |       |       |
|   4 |     PARTITION RANGE ITERATOR                   |                       |   803K|    70M|       |   598K  (1)| 00:00:24 |     3 |     2 |
|*  5 |      COUNT STOPKEY                             |                       |       |       |       |            |          |       |       |
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FR_MESSAGE_PART       |   803K|    70M|       |   598K  (1)| 00:00:24 |     3 |     2 |
|*  7 |        INDEX RANGE SCAN DESCENDING             | IX_FR_MESSAGE_PART_TS |   576K|       |       |  2269   (1)| 00:00:01 |     3 |     2 |
------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=40)
   3 - filter(ROWNUM<=40)
   5 - filter(ROWNUM<=40)
   6 - filter("ID">=376894993815568384)
   7 - access("TS">=TIMESTAMP' 2017-11-30 00:00:00' AND "TS"<TIMESTAMP' 2017-12-02 00:00:00')
       filter("TS">=TIMESTAMP' 2017-11-30 00:00:00' AND "TS"<TIMESTAMP' 2017-12-02 00:00:00')

Это быстро растет, но расчетная стоимость плана по-прежнему слишком высока.

Все идет нормально. Теперь попробуйте сделать запрос параметризованным для использования в нашей пользовательской структуре ORM:

select * from (select /*+ FIRST_ROWS(40) INDEX_RS_DESC(FR_MESSAGE_PART (TS)) */ * from FR_MESSAGE_PART
where TS >= :1 and TS < :2
  and ID >= :3 and ID < :4
order by TS DESC) where ROWNUM <= 40;

Но тогда COUNT STOPKEY на раздел исчезает из плана, как указано в вопросе, и подтверждается в другом ответе:

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                       |    40 | 26200 | 82349   (1)| 00:00:04 |       |       |
|*  1 |  COUNT STOPKEY                                 |                       |       |       |            |          |       |       |
|   2 |   VIEW                                         |                       |   153 |    97K| 82349   (1)| 00:00:04 |       |       |
|*  3 |    SORT ORDER BY STOPKEY                       |                       |   153 | 14076 | 82349   (1)| 00:00:04 |       |       |
|*  4 |     FILTER                                     |                       |       |       |            |          |       |       |
|   5 |      PARTITION RANGE ITERATOR                  |                       |   153 | 14076 | 82348   (1)| 00:00:04 |   KEY |   KEY |
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FR_MESSAGE_PART       |   153 | 14076 | 82348   (1)| 00:00:04 |   KEY |   KEY |
|*  7 |        INDEX RANGE SCAN DESCENDING             | IX_FR_MESSAGE_PART_TS |   110K|       |   450   (1)| 00:00:01 |   KEY |   KEY |
----------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=40)
   3 - filter(ROWNUM<=40)
   4 - filter(TO_NUMBER(:4)>TO_NUMBER(:3) AND TO_TIMESTAMP(:2)>TO_TIMESTAMP(:1))
   6 - filter("ID">=TO_NUMBER(:3) AND "ID"<TO_NUMBER(:4))
   7 - access("TS">=TO_TIMESTAMP(:1) AND "TS"<TO_TIMESTAMP(:2))
       filter("TS">=TO_TIMESTAMP(:1) AND "TS"<TO_TIMESTAMP(:2))

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

select * from (select /*+ FIRST_ROWS(40) INDEX_RS_DESC(FR_MESSAGE_PART (TS)) */ * from FR_MESSAGE_PART
where TS >= :1 and TS < :2
  and ID >= 376894993815568384 and ID < 411234940974268416
order by TS DESC) where ROWNUM <= 40;

Но все же был медленный план:

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                       |    40 | 26200 |       | 83512   (1)| 00:00:04 |       |       |
|*  1 |  COUNT STOPKEY                                 |                       |       |       |       |            |          |       |       |
|   2 |   VIEW                                         |                       | 61238 |    38M|       | 83512   (1)| 00:00:04 |       |       |
|*  3 |    SORT ORDER BY STOPKEY                       |                       | 61238 |  5501K|  7216K| 83512   (1)| 00:00:04 |       |       |
|*  4 |     FILTER                                     |                       |       |       |       |            |          |       |       |
|   5 |      PARTITION RANGE ITERATOR                  |                       | 61238 |  5501K|       | 82214   (1)| 00:00:04 |     3 |     2 |
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FR_MESSAGE_PART       | 61238 |  5501K|       | 82214   (1)| 00:00:04 |     3 |     2 |
|*  7 |        INDEX RANGE SCAN DESCENDING             | IX_FR_MESSAGE_PART_TS | 79076 |       |       |   316   (1)| 00:00:01 |     3 |     2 |
------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=40)
   3 - filter(ROWNUM<=40)
   4 - filter(TO_TIMESTAMP(:2)>TO_TIMESTAMP(:1))
   6 - filter("ID">=376894993815568384)
   7 - access("TS">=TO_TIMESTAMP(:1) AND "TS"<TO_TIMESTAMP(:2))
       filter("TS">=TO_TIMESTAMP(:1) AND "TS"<TO_TIMESTAMP(:2))

@ChrisSaxon в своем ответе здесь упомянул, что отсутствующий вложенный STOPKEY COUNT имеет какое-то отношение к filter(TO_TIMESTAMP(:2)>TO_TIMESTAMP(:1)) которая проверяет, что верхняя граница действительно больше, чем нижняя.

Учитывая это, я попытался обмануть oprimizer, изменив TS between :a and :b в эквивалент :b between TS and TS + (:b - :a). И это сработало!

После некоторого дополнительного исследования основной причины этого изменения я обнаружил, что просто заменить TS >= :1 and TS < :2 на TS + 0 >= :1 and TS < :2 помогает достичь оптимального плана выполнения.

select * from (select /*+ FIRST_ROWS(40) INDEX_RS_DESC(FR_MESSAGE_PART (TS)) */ * from FR_MESSAGE_PART
where TS + 0 >= :1 and TS < :2
  and ID >= 376894993815568384 and ID < 411234940974268416
order by TS DESC) where ROWNUM <= 40;

У плана теперь есть правильный COUNT STOPKEY для раздела и понятие INTERNAL_FUNCTION("TS")+0 которое, как я полагаю, предотвратило фильтрацию токсичных дополнительных границ.

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                       |    40 | 26200 |       | 10120   (1)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY                                 |                       |       |       |       |            |          |       |       |
|   2 |   VIEW                                         |                       | 61238 |    38M|       | 10120   (1)| 00:00:01 |       |       |
|*  3 |    SORT ORDER BY STOPKEY                       |                       | 61238 |  5501K|  7216K| 10120   (1)| 00:00:01 |       |       |
|   4 |     PARTITION RANGE ITERATOR                   |                       | 61238 |  5501K|       |  8822   (1)| 00:00:01 |     3 |     2 |
|*  5 |      COUNT STOPKEY                             |                       |       |       |       |            |          |       |       |
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FR_MESSAGE_PART       | 61238 |  5501K|       |  8822   (1)| 00:00:01 |     3 |     2 |
|*  7 |        INDEX RANGE SCAN DESCENDING             | IX_FR_MESSAGE_PART_TS |  7908 |       |       |   631   (1)| 00:00:01 |     3 |     2 |
------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=40)
   3 - filter(ROWNUM<=40)
   5 - filter(ROWNUM<=40)
   6 - filter("ID">=376894993815568384)
   7 - access("TS"<TO_TIMESTAMP(:2))
       filter(INTERNAL_FUNCTION("TS")+0>=:1 AND "TS"<TO_TIMESTAMP(:2))

Нам пришлось реализовать упомянутые Oracle-specific + 0 обходные пути и ограничения исключения разделов для жесткого кодирования в нашей пользовательской структуре ORM. Это позволяет сохранить такую же производительность быстрого пейджинга после переключения на секционированные таблицы с локальными индексами.

Но я желаю много терпения и здравомыслия тем, кто рискует сделать тот же самый переключатель без полного контроля над кодом sql-building.

Похоже, что у Oracle слишком много ловушек, когда разделение и пейджинг смешиваются. Например, мы обнаружили, что синтаксис сахара Oracle 12 новых OFFSET ROWS/FETCH NEXT ROWS ONLY почти неприменим с локальными индексированными секционированными таблицами, как и большинство аналитических функций окон, на которых он основан.

Самый короткий рабочий запрос для получения некоторой страницы за первой

select * from (select * from (
    select /*+ FIRST_ROWS(200) INDEX_RS_DESC(FR_MESSAGE_PART (TS)) */* from FR_MESSAGE_PART
where TS + 0 >= :1 and TS < :2
  and ID >= 376894993815568384 and ID < 411234940974268416
order by TS DESC) where ROWNUM <= 200) offset 180 rows;

Ниже приведен пример фактического плана выполнения после запуска такого запроса:

SQL_ID  c67mmq4wg49sx, child number 0
-------------------------------------
select * from (select * from (select /*+ FIRST_ROWS(200)
INDEX_RS_DESC("FR_MESSAGE_PART" ("TS")) GATHER_PLAN_STATISTICS */ "ID",
"MESSAGE_TYPE_ID", "TS", "REMOTE_ADDRESS", "TRX_ID",
"PROTOCOL_MESSAGE_ID", "MESSAGE_DATA_ID", "TEXT_OFFSET", "TEXT_SIZE",
"BODY_OFFSET", "BODY_SIZE", "INCOMING" from "FR_MESSAGE_PART" where
"TS" + 0 >= :1 and "TS" < :2 and "ID" >= 376894993815568384 and "ID" <
411234940974268416 order by "TS" DESC) where ROWNUM <= 200) offset 180
rows

Plan hash value: 2499404919

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                  | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                       |      1 |        |       |       |   640K(100)|          |       |       |     20 |00:00:00.01 |     322 |       |       |          |
|*  1 |  VIEW                                     |                       |      1 |    200 |   130K|       |   640K  (1)| 00:00:26 |       |       |     20 |00:00:00.01 |     322 |       |       |          |
|   2 |   WINDOW NOSORT                           |                       |      1 |    200 |   127K|       |   640K  (1)| 00:00:26 |       |       |    200 |00:00:00.01 |     322 |   142K|   142K|          |
|   3 |    VIEW                                   |                       |      1 |    200 |   127K|       |   640K  (1)| 00:00:26 |       |       |    200 |00:00:00.01 |     322 |       |       |          |
|*  4 |     COUNT STOPKEY                         |                       |      1 |        |       |       |            |          |       |       |    200 |00:00:00.01 |     322 |       |       |          |
|   5 |      VIEW                                 |                       |      1 |    780K|   487M|       |   640K  (1)| 00:00:26 |       |       |    200 |00:00:00.01 |     322 |       |       |          |
|*  6 |       SORT ORDER BY STOPKEY               |                       |      1 |    780K|    68M|    89M|   640K  (1)| 00:00:26 |       |       |    200 |00:00:00.01 |     322 | 29696 | 29696 |26624  (0)|
|   7 |        PARTITION RANGE ITERATOR           |                       |      1 |    780K|    68M|       |   624K  (1)| 00:00:25 |     3 |     2 |    400 |00:00:00.01 |     322 |       |       |          |
|*  8 |         COUNT STOPKEY                     |                       |      2 |        |       |       |            |          |       |       |    400 |00:00:00.01 |     322 |       |       |          |
|*  9 |          TABLE ACCESS BY LOCAL INDEX ROWID| FR_MESSAGE_PART       |      2 |    780K|    68M|       |   624K  (1)| 00:00:25 |     3 |     2 |    400 |00:00:00.01 |     322 |       |       |          |
|* 10 |           INDEX RANGE SCAN DESCENDING     | IX_FR_MESSAGE_PART_TS |      2 |    559K|       |       | 44368   (1)| 00:00:02 |     3 |     2 |    400 |00:00:00.01 |       8 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      OPT_PARAM('_optimizer_dsdir_usage_control' 0)
      FIRST_ROWS(200)
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$4")
      NO_ACCESS(@"SEL$4" "from$_subquery$_004"@"SEL$4")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      NO_ACCESS(@"SEL$2" "from$_subquery$_002"@"SEL$2")
      INDEX_RS_DESC(@"SEL$3" "FR_MESSAGE_PART"@"SEL$3" ("FR_MESSAGE_PART"."TS"))
      END_OUTLINE_DATA
  */

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

   1 - filter("from$_subquery$_004"."rowlimit_$$_rownumber">180)
   4 - filter(ROWNUM<=200)
   6 - filter(ROWNUM<=200)
   8 - filter(ROWNUM<=200)
   9 - filter("ID">=376894993815568384)
  10 - access("TS"<:2)
       filter((INTERNAL_FUNCTION("TS")+0>=:1 AND "TS"<:2))

Обратите внимание, сколько фактических выбранных строк и времени лучше оценок оптимизатора.

Обновить

Остерегайтесь того, что даже этот оптимальный план может завершиться неудачей, чтобы замедлить сканирование локального индекса в случае, если нижняя граница исключения раздела была слишком мала, так как нижний раздел не содержит достаточно записей для соответствия фильтрам запросов.

rleishman Tuning "BETWEEN" Запросы:

Проблема в том, что индекс может сканировать только один столбец с предикатом диапазона (<,>, LIKE, BETWEEN). Таким образом, даже если индекс содержит как нижний, так и верхний столбцы, сканирование индекса возвращает все строки, соответствующие нижнему_ограничению <=: b, а затем фильтрует строки, которые не соответствуют upper_bound> =: b.

В случае, когда искомое значение находится где-то посередине, сканирование диапазона возвращает половину строк в таблице, чтобы найти одну строку. В худшем случае, когда наиболее часто используемые строки находятся в верхней части (самые высокие значения), сканирование индекса обрабатывает почти каждую строку таблицы для каждого поиска.

Это означает, что, к сожалению, Oracle не учитывает нижнюю границу фильтра сканирования диапазона до тех пор, пока не достигнет условия STOPKEY COUNT или не сканирует весь раздел!

Таким образом, нам пришлось ограничить ограничение эвристики, связанное с отменой раздела, в тот же месяц, в который попадает нижний временной интервал. Это защищает от полного сканирования индекса за счет риска не показывать в журнале некоторые сообщения о задержке транзакции. Но это может быть легко устранено путем продления срока поставки, если это необходимо.

Я также попытался применить тот же трюк + 0 чтобы заставить оптимальный план с привязкой к ограничениям динамического разделения разделов:

select * from (select /*+ FIRST_ROWS(40) INDEX_RS_DESC(FR_MESSAGE_PART (TS)) */ * from FR_MESSAGE_PART
where TS+0 >= :1 and TS < :2
  and ID >= :3 and ID+0 < :4
order by TS DESC) where ROWNUM <= 40;

Затем план по-прежнему сохраняет правильный STOPKEY COUNT каждого раздела, но устранение раздела теряется для верхней границы, что может быть отмечено Pstart таблицы планов:

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                       |    40 | 26200 |  9083   (1)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY                                 |                       |       |       |            |          |       |       |
|   2 |   VIEW                                         |                       |   153 |    97K|  9083   (1)| 00:00:01 |       |       |
|*  3 |    SORT ORDER BY STOPKEY                       |                       |   153 | 14076 |  9083   (1)| 00:00:01 |       |       |
|   4 |     PARTITION RANGE ITERATOR                   |                       |   153 | 14076 |  9082   (1)| 00:00:01 |    10 |   KEY |
|*  5 |      COUNT STOPKEY                             |                       |       |       |            |          |       |       |
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FR_MESSAGE_PART       |   153 | 14076 |  9082   (1)| 00:00:01 |    10 |   KEY |
|*  7 |        INDEX RANGE SCAN DESCENDING             | IX_FR_MESSAGE_PART_TS | 11023 |       |   891   (1)| 00:00:01 |    10 |   KEY |
----------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=40)
   3 - filter(ROWNUM<=40)
   5 - filter(ROWNUM<=40)
   6 - filter("ID">=TO_NUMBER(:3) AND "ID"+0<TO_NUMBER(:4))
   7 - access("TS"<TO_TIMESTAMP(:2))
       filter(INTERNAL_FUNCTION("TS")+0>=:1 AND "TS"<TO_TIMESTAMP(:2))

Ответ 4

Является ли Dynamic SQL опцией? Таким образом, вы можете "ввести" значения TRX_ID и CREATE_TS, исключая использование переменных привязки. Возможно, тогда сгенерированный план будет включать COUNT STOPKEY.

По Dynamic SQL я хотел, чтобы вы динамически строили SQL, а затем вызывали его с EXECUTE IMMEDIATE или OPEN. Используя это, вы можете напрямую использовать свои фильтры без переменных привязки. Пример:

    v_sql VARCHAR2(1000) :=
    'select rd from (
        select /*+ INDEX(OUT_SMS OUT_SMS_CREATE_TS_TRX_ID_IX) */ rowid rd
        from OUT_SMS     
        where  TRX_ID between ' || v_trx_id_min || ' and ' || v_trx_id_maxb || '      
           and CREATE_TS between ' || v_create_ts_min|| ' and ' || v_create_ts_max || '
        order by CREATE_TS DESC, TRX_ID DESC
    ) where rownum <= 20';

затем вызовите его, используя:

    EXECUTE IMMEDIATE v_sql;

или даже:

    OPEN cursor_out FOR v_sql;