Oracle 全局索引如何在分区表上运行

假设我有下面的表格，其中有10亿记录和基于Budget_Flag的分区

CREATE TABLE xx_tab1
(budget_flag  varchar(1), 
period_name VARCHAR2(10),
begin_dr   number,
begin_cr  number, 
creation_date    DATE)
PARTITION BY LIST(budget_flag)
(
PARTITION budget_flag_A VALUES('A'),
PARTITION budget_flag_B VALUES ('B'),
PARTITION budget_flag_Dflt VALUES(DEFAULT)
);

我在period_name列上有如下的全局索引

create index index_period_name on xx_tab1(period_name);

然后我运行查询如下

select (begin_cr-begin_dr) bal
from xx_tab1
where budget_flag = 'A'
and period_name = 'JAN-18'

这个查询将只查看分区的 “budget_flag_A”，因为我的查询中有谓词 “budget_flag”，或者这是要扫描整个表，因为我有谓词 “Period_Name” 在查询中没有分区。

在这种情况下，关于性能和可维护性，什么样的索引，局部或全局索引将是有用的？

另外，如果我有另一个查询扫描到分区之外，并且在 “Period_Name” 上具有与上述相同的索引

select (begin_cr-begin_dr) bal
from xx_tab1
where period_name = 'JAN-18'

我上面的查询是否会与非分区表执行相同？
分区会影响性能吗？

如果您在查询中包含分区键，则全局索引可以将访问权限限制为仅感兴趣的分区。

您可以通过查看执行计划中的Pstart & Pstop列来看到这一点。例如:

CREATE TABLE xx_tab1
(budget_flag  varchar(1), 
period_name VARCHAR2(10),
begin_dr   number,
begin_cr  number, 
creation_date    DATE)
PARTITION BY LIST(budget_flag)
(
PARTITION budget_flag_A VALUES('A'),
PARTITION budget_flag_B VALUES ('B'),
PARTITION budget_flag_Dflt VALUES(DEFAULT)
);

insert into xx_tab1 
  with rws as (
    select level x from dual
    connect by level < 200
  )
  select case mod ( x, 2 )
           when 0 then 'A'
           else 'B'
         end,
         'JAN-' || lpad ( floor ( x / 2 ), 2, '0' ) per,
         x, x, sysdate
  from   rws;
commit;

create index index_period_name on xx_tab1(period_name);

set serveroutput off
select /*+ gather_plan_statistics */(begin_cr-begin_dr) bal
from   xx_tab1
where  budget_flag = 'A'
and    period_name = 'JAN-18';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST +PARTITION'));

select /*+ gather_plan_statistics */(begin_cr-begin_dr) bal
from   xx_tab1
where  budget_flag = 'B'
and    period_name = 'JAN-18';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST +PARTITION'));

PLAN_TABLE_OUTPUT                                                                                                     
SQL_ID  d18y8wmpmcypp, child number 0                                                                                 
-------------------------------------                                                                                 
select /*+ gather_plan_statistics */(begin_cr-begin_dr) bal from                                                      
xx_tab1 where  budget_flag = 'B' and    period_name = 'JAN-18'                                                        
                                                                                                                      
Plan hash value: 3318394780                                                                                           
                                                                                                                      
-------------------------------------------------------------------------------------------------------------------   
| Id  | Operation                                  | Name              | Starts | E-Rows | Pstart| Pstop | A-Rows |   
-------------------------------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT                           |                   |      1 |        |       |       |      1 |   
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| XX_TAB1           |      1 |      1 |     2 |     2 |      1 |   
|*  2 |   INDEX RANGE SCAN                         | INDEX_PERIOD_NAME |      1 |      2 |       |       |      2 |   
-------------------------------------------------------------------------------------------------------------------   
                                                                                                                      
Predicate Information (identified by operation id):                                                                   
---------------------------------------------------                                                                   
                                                                                                                      
   1 - filter("BUDGET_FLAG"='B')                                                                                      
   2 - access("PERIOD_NAME"='JAN-18') 

注意开始/停止列如何显示1？和过滤器操作过滤器 ("BUDGET_FLAG" = 'A')？这意味着查询仅访问此分区。数据库能够使用索引进行过滤。

但是，如果您在没有分区键的情况下进行查询，则数据库将不知道这些值可能在哪些分区中。所以你访问他们所有:

select /*+ gather_plan_statistics */(begin_cr-begin_dr) bal
from   xx_tab1
where  period_name = 'JAN-18';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +PARTITION'));

PLAN_TABLE_OUTPUT                                                                                                                            
SQL_ID  68x4sdkun5uhc, child number 0                                                                                                        
-------------------------------------                                                                                                        
select /*+ gather_plan_statistics */(begin_cr-begin_dr) bal from                                                                             
xx_tab1 where  period_name = 'JAN-18'                                                                                                        
                                                                                                                                             
Plan hash value: 2470579760                                                                                                                  
                                                                                                                                             
------------------------------------------------------------------------------------------------------------------------------------------   
| Id  | Operation                                  | Name              | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   | Buffers |   
------------------------------------------------------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT                           |                   |      1 |        |       |       |      2 |00:00:00.01 |       3 |   
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| XX_TAB1           |      1 |      2 | ROWID | ROWID |      2 |00:00:00.01 |       3 |   
|*  2 |   INDEX RANGE SCAN                         | INDEX_PERIOD_NAME |      1 |      2 |       |       |      2 |00:00:00.01 |       1 |   
------------------------------------------------------------------------------------------------------------------------------------------   
                                                                                                                                             
Predicate Information (identified by operation id):                                                                                          
---------------------------------------------------                                                                                          
                                                                                                                                             
   2 - access("PERIOD_NAME"='JAN-18')

请注意，Pstart/stop没有指定特定的分区。只是一个rowid。

理查德·富特 (Richard Foote) 开始了一系列有关分区表上的全局 (非分区) 索引的文章。如果您想了解更多信息，请阅读此内容:https://richardfoote.wordpress.com/2018/10/04/hidden-efficiencies-of-non-partitioned-indexes-on-partitioned-tables-part-i-the-jean-genie/

Is my above query going to perform same as non-partitioned table?

否。查询没有分区键的分区表可能比在非分区表上的相同查询慢。例如:

drop table xx_tab1 cascade constraints purge;
CREATE TABLE xx_tab1
(budget_flag  varchar(1), 
period_name VARCHAR2(10),
begin_dr   number,
begin_cr  number, 
creation_date    DATE);

insert into xx_tab1 
  with rws as (
    select level x from dual
    connect by level < 200
  )
  select case mod ( x, 2 )
           when 0 then 'A'
           else 'B'
         end,
         'JAN-' || lpad ( floor ( x / 2), 2, '0' ) per,
         x, x, sysdate
  from   rws;
commit;

create index index_period_name on xx_tab1(period_name);

select /*+ gather_plan_statistics */(begin_cr-begin_dr) bal
from   xx_tab1
where  period_name = 'JAN-18';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +PARTITION'));

PLAN_TABLE_OUTPUT                                                                                                     
SQL_ID  68x4sdkun5uhc, child number 0                                                                                 
-------------------------------------                                                                                 
select /*+ gather_plan_statistics */(begin_cr-begin_dr) bal from                                                      
xx_tab1 where  period_name = 'JAN-18'                                                                                 
                                                                                                                      
Plan hash value: 3698929902                                                                                           
                                                                                                                      
-------------------------------------------------------------------------------------------------------------------   
| Id  | Operation                           | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |   
-------------------------------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT                    |                   |      1 |        |      2 |00:00:00.01 |       2 |   
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| XX_TAB1           |      1 |      2 |      2 |00:00:00.01 |       2 |   
|*  2 |   INDEX RANGE SCAN                  | INDEX_PERIOD_NAME |      1 |      2 |      2 |00:00:00.01 |       1 |   
-------------------------------------------------------------------------------------------------------------------   
                                                                                                                      
Predicate Information (identified by operation id):                                                                   
---------------------------------------------------                                                                   
                                                                                                                      
   2 - access("PERIOD_NAME"='JAN-18')

请注意，查询是否得到2而不是3？在非分区表中，所有数据库块都可以位于同一段中。而分区时，不同分区中的行必须位于不同的段中。

因此，可以从具有较少I/O访问的非分区表中获取所有数据。

is partitioning is going to affect the performance?

是的。可能更快，也可能更慢。这取决于您的查询!

分区是一个大话题。阅读Connor的指南以了解更多信息:

https://asktom.oracle.com/partitioning-for-developers.htm