A question came to me recently about whether a global index on a partitioned table can provide a performance benefit over a similar index on a non-partitioned table. As with many query related things, the answer is “it depends” but the simple answer is “yes it can do if your query contains the table partitioning key”. Let’s look at what I mean using a simple example…
We’ll use two tables this demo, run under Oracle 21 XE:
- NONPART_TABLE – the non-partitioned table
- PART_TABLE – the partitioned table
Each table will have the following columns:
- ID – unique value designed to represent the primary key, which we won’t actually use for this demo
- CAT_1 – column with 7 distinct values. This will be the list partitioning key for table PART_TABLE
- CAT_2 – column with 1,000 distinct values. This will be the indexed column.
A simple b-tree index will be created on the CAT_2 column. The index on the partitioned table, PART_TABLE, will be global.
Each table will be populated with 1,000,000 rows:
- ID – a unique integer
- CAT_1 – values VAL_0 through VAL_6, distributed evenly
- CAT_2 – values VAL_0 through VAL_999, distributed evenly
The values in columns CAT_1 and CAT_2 are set such that there is an even distribution between the combinations of (CAT_1, CAT_2).
The script for the above is:
create table nonpart_table
(id number not null
,cat_1 varchar2(10) not null
,cat_2 varchar2(10) not null)
/
create table part_table
(id number not null
,cat_1 varchar2(10) not null
,cat_2 varchar2(10) not null)
partition by list (cat_1) automatic
(partition p1 values ('VAL_0'))
/
insert into nonpart_table
(id, cat_1, cat_2)
with rowgen as (select rownum as rn from dual connect by level <= 1000)
select rownum
, 'VAL_' || trim(mod(rownum,7)) as cat_1
, 'VAL_' || trim(mod(rownum,1000)) as cat_2
from rowgen r1
, rowgen r2;
insert into part_table
select *
from nonpart_table;
commit;
create index nonpart_table_idx1
on nonpart_table (cat_2);
create index part_table_idx1
on part_table (cat_2);
exec dbms_stats.gather_table_stats ('','nonpart_table');
exec dbms_stats.gather_table_stats ('','part_table');
For our test, we’ll look at the following simple query, hinted to ensure we’re access the table via the index:
select /*+ index (nonpart_table nonpart_table_idx1) */ *
from nonpart_table
where cat_1 = 'VAL_5'
and cat_2 = 'VAL_42';Running this query with AUTOTRACE enabled, we get the following:
143 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3717371333
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 143 | 2717 | 1006 (1)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| NONPART_TABLE | 143 | 2717 | 1006 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | NONPART_TABLE_IDX1 | 1000 | | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CAT_1"='VAL_5')
2 - access("CAT_2"='VAL_42')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1016 consistent gets
0 physical reads
0 redo size
4004 bytes sent via SQL*Net to client
151 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
143 rows processedSo, against our non-partitioned table, we retrieved the 143 rows with 1,016 consistent gets. Now we repeat the test using the partitioned table and obtain the AUTOTRACE output of that:
143 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2417870909
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 143 | 2717 | 897 (0)| 00:00:01 | | |
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PART_TABLE | 143 | 2717 | 897 (0)| 00:00:01 | 4 | 4 |
|* 2 | INDEX RANGE SCAN | PART_TABLE_IDX1 | 143 | | 6 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CAT_1"='VAL_5')
2 - access("CAT_2"='VAL_42')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
144 consistent gets
0 physical reads
0 redo size
4004 bytes sent via SQL*Net to client
151 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
143 rows processedThis time we can see the 143 rows were retrieved with just 144 consistent gets, which is about one seventh of the 1,016 consistent gets for the non-partitioned table query. Clearly the query against the partitioned table is better from a performance perspective.
If we look at the execution plan for the partitioned table query we can why it performs better. Line 1 of the plan, operation TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED, has PSTART and PSTOP set to 4, i.e. only 1 partition is being accessed. What Oracle is doing is an index range scan of the global index, satisfying the CAT_2 = ‘VAL_42’ criteria of the query. This gets the table ROWIDs of the rows that satisfies that criteria but it doesn’t need to read the rows to evaluate the CAT_1 = ‘VAL_5’ criteria for the partitioned table. Since the index is global, the table ROWIDs specify the partition of the row and this is sufficient for Oracle to determine if the referenced row will be CAT_1 = ‘VAL_5’ or not.
If we contrast that with the query against the non-partitioned table, we see that Oracle had to visit the table row to evaluate the CAT_1 = ‘VAL_5’ query criteria and in doing so incurred an extra logical IO for every row where CAT_2 = ‘VAL_42’. This is why the consistent gets is seven times what the query against the partitioned table incurred.
You might be wondering that if our query contains predicates on both column CAT_1 and CAT_2 why not make the index local? Well, in most systems there are many different queries and we might have some that do not specify CAT_1 partitioning column. Queries that contain filter predicates on just CAT_2 will benefit better from a global index, rather than a local index.
In summary, despite our index being global, we can get a performance benefit from having our table partitioned for those queries that filter on the partitioning key of the table in addition to filters that would use the index key.
Mark Hoxey 