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

У меня есть таблица, диапазон которой разделен на числовое значение (parameterinstanceid). Я хочу выбрать максимальное + 1 значение одного и того же столбца. У меня есть глобальный индекс без разделов для параметра instanceid.

select /*+ parallel(a,32,8) */ max(parameterinstanceid) +1 from parameterinstance a;

При проверке плана объяснения я вижу, что он выполняет ПОЛНОЕ СКАНИРОВАНИЕ ИНДЕКСА (МИН./МАКС.) таблицы. я хочу сделать это, сначала проверив максимальный раздел, если он не содержит никаких данных, затем следующий раздел в порядке убывания. Я могу написать процедуру для этого, но я хочу знать, есть ли простой запрос для него. http://www.oramoss.com/blog/2009/06/no-pruning-for-minmax-of-partition-key.html .. кажется, что это нерешенная проблема.

РЕДАКТИРОВАТЬ :

Имена разделов: PI_P01,PI_P02,...PI_P10,PI_PMAXVALUE.

объяснить план:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2808487136

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     8 |    34   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE             |       |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL       |       |     1 |     8 |    34   (0)| 00:00:01 |     1 |    11 |
|   3 |    INDEX FULL SCAN (MIN/MAX)| PI_PK |     1 |     8 |    34   (0)| 00:00:01 |     1 |    11 |
-----------------------------------------------------------------------------------------------------

и структура таблицы:

 Name                           Null?    Type
 ------------------------------ -------- -----------------
 PARAMETERINSTANCEID            NOT NULL NUMBER
 PARAMINSTANCE2PARAMSETVERSION  NOT NULL NUMBER
 PARAMINSTANCE2PARAMDEFINITION  NOT NULL NUMBER
 PARAMINSTANCE2PARENTPARAM               NUMBER
 SEQUENCE                                NUMBER
 X_CTCV_CONV_ID                          VARCHAR2(50 CHAR)
 X_CONV_RUN_NO                           NUMBER

и индексы в таблице:

    INDEX_NAME                     POSITION COLUMN_NAME 
------------------------------ -------- -----------------------------
PI_UK                                 1 PARAMINSTANCE2PARAMSETVERSION
PI_UK                                 2 PARAMINSTANCE2PARAMDEFINITION
PI_PK                                 1 PARAMETERINSTANCEID            
PI_PAD_FK_I                           1 PARAMINSTANCE2PARAMDEFINITION
PI_PI_FK_I                            1 PARAMINSTANCE2PARENTPARAM

sql plsql database-partitioning
person subodh1989    schedule 11.10.2012    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Если у вас есть глобальный индекс для ключа раздела, вы должны получить такой план:

----------------------------------------------------------------------------
| Id  | Operation                  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |     1 |     9 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |     1 |     9 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)|     1 |     9 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Здесь MIN/MAX очень важен, это означает, что Oracle остановится на первая запись в обход таблицы разделов.

Предоставленная вами ссылка отличается, поскольку ключ раздела не индексируется (поэтому он создает FULL TABLE SCAN вместо INDEX FULL SCAN.

Если у вас нет индекса для ключа раздела, кажется, что запрос TOP-N, предложенный Джонатаном Льюисом, может сделать именно то, что вы хотите. Вот план, который я получаю с таблицей test, как в примере по вашей ссылке:

explain plan for 
select * from (select col_date_part_key 
                 from test 
                order by col_date_part_key desc) 
 where rownum = 1

-------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Pstart| Pstop |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |     1 |     9 |       |       |
|*  1 |  COUNT STOPKEY           |      |       |       |       |       |
|   2 |   PARTITION RANGE ALL    |      |     1 |     9 |    12 |     1 |
|   3 |    VIEW                  |      |     1 |     9 |       |       |
|*  4 |     SORT ORDER BY STOPKEY|      |     1 |     9 |       |       |
|   5 |      TABLE ACCESS FULL   | TEST |     1 |     9 |    12 |     1 |
-------------------------------------------------------------------------

Как видите, Oracle начнет с последней секции (Pstart=12) и будет подниматься к первой секции (Pstart=1), пока не получит одну строку (Rows=1).

Обновлять

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

create table parameterinstance  (
   PARAMETERINSTANCEID           NUMBER             NOT NULL,
   PARAMINSTANCE2PARAMSETVERSION NUMBER             NOT NULL,
   PARAMINSTANCE2PARAMDEFINITION NUMBER             NOT NULL,
   PARAMINSTANCE2PARENTPARAM     NUMBER                     ,
   SEQUENCE                      NUMBER                     ,
   X_CTCV_CONV_ID                VARCHAR2(50 CHAR)          ,
   X_CONV_RUN_NO                 NUMBER                     
) partition by range (PARAMETERINSTANCEID)
(  partition p1 values less than (1000) storage (initial 64k),
   partition p2 values less than (2000) storage (initial 64k),
   partition p3 values less than (3000) storage (initial 64k),
   partition p4 values less than (4000) storage (initial 64k),
   partition p5 values less than (5000) storage (initial 64k),
   partition p6 values less than (6000) storage (initial 64k),
   partition p7 values less than (7000) storage (initial 64k),
   partition p8 values less than (8000) storage (initial 64k),
   partition p9 values less than (9000) storage (initial 64k),
   partition p10 values less than (maxvalue) storage (initial 64k)
);

CREATE UNIQUE INDEX PI_PK ON parameterinstance(PARAMETERINSTANCEID) local;

insert into parameterinstance  
  (SELECT rownum, rownum, rownum, '', '', rpad('x', 50, 'x'), '' 
     from dual connect by level <= 1e4);

На 11gR2 я получаю следующий план:

-----------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Pstart| Pstop |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    13 |       |       |
|   1 |  PARTITION RANGE ALL        |       |     1 |    13 |    10 |     1 |
|   2 |   SORT AGGREGATE            |       |     1 |    13 |       |       |
|   3 |    INDEX FULL SCAN (MIN/MAX)| PI_PK |     1 |    13 |    10 |     1 |
-----------------------------------------------------------------------------

Обратите внимание, что разделы перечислены в соответствующем порядке (от 10 до 1). В 9iR2 план отличается:

-----------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Pstart| Pstop |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     4 |       |       |
|   1 |  SORT AGGREGATE             |       |     1 |     4 |       |       |
|   2 |   PARTITION RANGE ALL       |       |       |       |     1 |    10 |
|   3 |    INDEX FULL SCAN (MIN/MAX)| PI_PK | 10000 | 40000 |     1 |    10 |
-----------------------------------------------------------------------------

Так что кажется, что между 9i и 11g произошла некоторая оптимизация. Время для обновления?

person Vincent Malgrat    schedule 11.10.2012
comment
моя таблица содержит индекс в том же столбце, но содержит много данных (100 миллионов+), поэтому обработка запроса занимает много времени (30-40 минут). Вот почему я думал об использовании метода для сканирования каждый раздел в порядке убывания. Также план объяснения показывает pstart как 1 и pstop как 11 даже в максимальном запросе. Если это не наоборот, pstart = 11 и pstop = 1, я не могу опубликовать свой план объяснения, поскольку он находится на рабочем сервере, но объясните План выбора макс. из раздела намного меньше, чем выбор макс. из таблицы. - person subodh1989; 12.10.2012
comment
я только что проверил, если я выберу min из таблицы, это займет меньше секунды, потому что pstart равен 1, но выбор max из таблицы занимает много времени из-за того же самого. как мне изменить pstart на 11 и pstop на 1? - person subodh1989; 12.10.2012
comment
Пожалуйста, опубликуйте свои операторы CREATE TABLE с соответствующими индексами, а также свой план объяснения. Если у вас есть глобальный индекс на 11gR2, вы должны получить FULL INDEX SCAN (MIN/MAX), который будет извлекать только одну строку, даже если таблица огромна. - person Vincent Malgrat; 12.10.2012
comment
Если вы не хотите публиковать свои инструкции по планированию/созданию таблицы производственных объяснений, создайте аналогичный случай с тестовой таблицей, и тогда мы сможем сравнить наши результаты. - person Vincent Malgrat; 12.10.2012
comment
я добавил план объяснения, индексы и столбцы в таблицу - person subodh1989; 12.10.2012
comment
Хорошо, ваш индекс локально разделен, а не глобальный. Может быть, это объясняет разницу. Какую версию Oracle вы используете? - person Vincent Malgrat; 12.10.2012
comment
Oracle Database 10g Enterprise Edition, выпуск 10.2.0.5.0 — 64bi - person subodh1989; 12.10.2012
comment
Смотрите мой обновленный ответ: вы получаете тот же результат, что и в 9ir2, с этим тестовым примером? Если да, то вам может потребоваться запросить последний раздел вручную. - person Vincent Malgrat; 12.10.2012
comment
я думаю, что это из-за обновления версии. Хотя я не могу обновить свой оракул на производстве, я могу сделать это вручную, запросив последний раздел, как было предложено. спасибо! - person subodh1989; 12.10.2012