Suppose i have below table which has 1 billion record and partitioned based on 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)
);
and i have Global index on period_name column as below
create index index_period_name on xx_tab1(period_name);
and then i run the query as below
select (begin_cr-begin_dr) bal
from xx_tab1
where budget_flag = 'A'
and period_name = 'JAN-18'
is this query is going to look into only partitioned "budget_flag_A" because i have predicate "budget_flag" in my query or is this going to scan whole table because i have have predicate "Period_Name" in query which is not partitioned.
what kind of index, local or global index will be useful in this condition with regard to performance and maintainability?
Also if i have another Query which scan beyond the partition, and i have same index as mentioned above on "Period_Name"
select (begin_cr-begin_dr) bal
from xx_tab1
where period_name = 'JAN-18'
Is my above query going to perform same as non-partitioned table?
is partitioning is going to affect the performance?
Provided you include the partition key in the query, the global index can restrict access to only the partitions of interest.
You can see this by looking at the Pstart & Pstop columns in an execution plan. For example:
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') Notice how the start/stop columns both show 1? And the filter operation filter("BUDGET_FLAG"='A')? This means the query only accessed this partition. The database is able to do the filtering using the index.
But if you query without the partition key, the database doesn't know which partitions the values could be in. So you access them all:
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')Note that the Pstart/stop don't specify a specific partition. Just a rowid.
Richard Foote has started a series on global (non-partitioned) indexes on partitioned tables. Read this if you want to know more:
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?No. Querying a partitioned table without the partition key is likely to be slower than the same query on a non-partitioned table. For example:
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')Notice that the query does 2 gets instead of 3? In a non-partitioned table, all the database blocks can be in the same segment. Whereas with partitioning, rows in different partitions must be in different segments.
So it's possible to get all the data from a non-partitioned table with fewer I/O accesses.
is partitioning is going to affect the performance? Yes. Could be faster, could be slower. It depends on your queries!
Partitioning is a big topic. Read Connor's guide on this to learn more:
https://asktom.oracle.com/partitioning-for-developers.htm