Recently I was investigating a performance problem using the SQL Monitoring feature looking for SQLs taking more than 5 seconds, when one query retained my attention. I drilled down to its corresponding SQL Monitoring Report and started looking carefully to its execution plan. There was a particular index range scan operation which retained my attention because it was consuming a huge amount of logical I/O (buffers). A careful examination of the index and its underlying table reveals that the latter is range partitioned while the former is a local non prefixed index (a locally partitioned index which doesn’t include the partition key in its definition).
My curiosity is so that I issued a query to see how many non prefixed indexes exist in the whole bunch of partitioned tables this application owns. The query of course returned several rows. When I asked the developer about the reason, he said that this is a ”standard” they have adopted because they are, every couple of months, truncating old partitions; and having local indexes (even non prefixed ones) is a good idea in this case because they don’t have to rebuild any global indexes (by the way there is the UPDATE GLOBAL INDEXES clause for that).
And here where the problem resides: ignoring the technology. Partitioning is a nice feature which could damage the performance when it is wrongly designed. Creating a locally non-prefixed index without knowing the collateral effects they can produce if partition pruning is not guaranteed is something I have very often seen in my daily consultancy work. In order to explain this particular client situation I have engineered the following partitioned table with 1493 partitions (you should open the file in a new page copy the content into a .sql file and execute it). Table to which I have attached a locally non prefixed index (LC_NON_PREFIXED_TYP_I). Here below are the observations I can emphasize when selecting from this table:
SQL> desc partitioned_tab Name Null? Type ------------------ --------- ------------------------------- MHO_ID NOT NULL NUMBER(10) MHO_DATE NOT NULL DATE -- partition key MHO_CODE NOT NULL VARCHAR2(1 CHAR) MHO_TYP_ID NOT NULL NUMBER(10) -- indexed column
The important question here is how would react the database to a query that doesn’t eliminate partitions (because it doesn’t include the partition key in its predicate) and which will be honored via a locally partitioned non prefixed index. Something like this:
select * from partitioned_tab where mho_typ_id = 0;
In the presence of an index of this type:
CREATE INDEX LC_NON_PREFIXED_TYP_I ON partitioned_tab (MHO_TYP_ID) LOCAL;
Here are the results
----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows |Buffers |Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1493 | 2984 | | | | 1 | PARTITION RANGE ALL | | 1 | 1493 | 1493 | 2984 | 1 | 1493 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TAB | 1493 | 1493 | 1493 | 2984 | 1 | 1493 | |* 3 | INDEX RANGE SCAN | LC_NON_PREFIXED_TYP_I | 1492 | 1493 | 1493 | 1492 | 1 | 1493 | ----------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access(MHO_TYP_ID=0) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2984 consistent gets 0 physical reads 0 redo size 28937 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1493 rows processed
Spot how many times the INDEX RANGE SCAN operation has been started: 1492 times. Compare this number to the number of table partitions (1493) and you will find that in such a kind of situation you will do N-1 INDEX RANGE SCAN operations (where N is the number of partitions). That is an enormous waste of time and energy.
Why 1492 INDEX RANGE SCANs?
It is simply because a locally partitioned index contains multiple segments in contrast to a b-tree index which consists of a single segment.
SQL> select count(1) from dba_segments where segment_name = 'LC_NON_PREFIXED_TYP_I'; COUNT(1) ---------- 1492
I am not saying that you don’t have to create a locally non prefixed index. What I am trying to emphasize is that when you decide to do so be sure that your queries will eliminate partitions and will hence prune down to a single index partition as it is shown here below when my query is doing partition pruning
SQL> select * from partitioned_tab where mho_typ_id = 0 and mho_date = to_date('01122012','ddmmyyyy'); MHO_ID MHO_DATE M MHO_TYP_ID ---------- ----------------- - ---------- 1 20121201 00:00:00 Z 0 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 | 2 | | | | 1 | PARTITION RANGE SINGLE | | 1 | 1 | 1 | 2 | 2 | 2 | |* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TAB | 1 | 1 | 1 | 2 | 2 | 2 | |* 3 | INDEX RANGE SCAN | LC_NON_PREFIXED_TYP_I | 1 | 1 | 1 | 1 | 2 | 2 | ------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(MHO_DATE=TO_DATE(' 2012-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 3 - access(MHO_TYP_ID=0)
Since Oracle has succeeded to eliminate non touched partitions (PARTITION RANGE SINGLE) it has pruned down to a single segment index range scan as shown by the Starts information which equals 1. In addition, the consistent gets (Buffers) has been drastically reduced from 2984 to only 2.
That’s when your query is able to eliminate partitions. However, if you have a particular query that can’t eliminate partitions and that you want to cover via an appropriate index then in this case you have better to not local partition the index. Let’s see this in action
SQL> alter index LC_NON_PREFIXED_TYP_I invisible; Index altered. SQL> create index gl_typ_i on partitioned_tab(mho_typ_id); create index gl_typ_i on partitioned_tab(mho_typ_id) * ERROR at line 1: ORA-01408: such column list already indexed
Damn!!! I can’t do it in 11.2.0.3.0
SQL> drop index LC_NON_PREFIXED_TYP_I; SQL> create index gl_typ_i on partitioned_tab(mho_typ_id);
SQL> select * from partitioned_tab where mho_typ_id = 0; ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows |Buffers | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1493 | 1496 | | | | 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| PARTITIONED_TAB | 1 | 1493 | 1493 | 1496 | ROWID | ROWID | |* 2 | INDEX RANGE SCAN | GL_TYP_I | 1 | 1493 | 1493 | 4 | | | ------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(MHO_TYP_ID=0) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1496 consistent gets 1493 physical reads 0 redo size 28937 bytes sent via SQL*Net to client 372 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1493 rows processed
And spot how many index range scan we did this time? Only one. Because there is only one segment for this type of index
SQL> select count(1) from dba_segments where segment_name = 'GL_TYP_I'; COUNT(1) ---------- 1
You can also point out that in contrast to the locally non prefixed index we did 50% less of logical I/O – from 2984 down to 1496.
By the way, why do you think Oracle allows the creation of the non prefixed index LC_NON_PREFIXED_TYP_I when it is declared as non unique and refuse to obey you when you want to create it as a unique index?
SQL> create unique index lc_non_prefixed_typ_i on partitioned_tab (mho_typ_id) local; * ERROR at line 1: ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
Simply because Oracle has already measured the impact this kind of index can have on the insert performance if it has allowed it to exist. In this case a mho_typ_id could go in any of the 1493 partitions. How would Oracle proceed to check if the inserted mho_typ_id value has not been already inserted (or is being inserted) without impeaching others to insert into the whole bunch of the 1492 partitions? Is this scalable and performant? Of course it is not.
Bottom Line: when you create a locally non prefixed index (index that doesn’t include the partition key in its definition) then be sure that queries using this index will eliminate partitions. Otherwise, the more partitions you have the more index partitions you will range scan and the more logical I/O you will do
very clear
Thanks
Frank
Comment by Polet Frank — November 8, 2013 @ 7:14 am |
Frank
Thanks for your comment.
Comment by hourim — November 8, 2013 @ 8:03 am |
Good observation!!
Thanks for sharing..
Thanks
Yousuf
Comment by Yousuf — November 9, 2013 @ 9:00 am |
Hi Mohammed,
a few comments:
1) it doesn’t really make sense to make the distinction between prefixed and non-prefixed local indexes for modern Oracle versions. Local indexes work well when there is partition pruning. This is true regardless to whether or not the local index starts with the partitioning key. It’s true when the local index doesn’t contain the partitioning key at all. See comments by Jonathan Lewis in the thread below:
https://forums.oracle.com/message/9248885
2) the developer you spoke with has made a very good point — local indexes provide perfect maintainability because you can do DDL operations on partition level. Sure, you can do DDL with global indexes as well, and you can even achieve this with one statement using UPDATE GLOBAL INDEXES clause. The point you seem to be missng is that this would be prohibitively slow if the table is big enough. Imagine this: you have a 10 Tb table on a standard Oracle server (i.e. not Exadata), you need to periodically archive (truncate, dropetc.) partitions your maintenance window is 12 hours. Will that work with global indexes? Most probably not. 12c is offering asynchronous update of global indexes, and not everybody is on 12c yet (and it’s not a perfect solution for those who is, because your ability to perform queries will still be affected during the asynchronous rebuild).
3) what you wrote about local indexes resulting in poor performance in absence of partition pruning is of course true. However, the reality is that performance and manageability often are conflicting requirements, and you may face situations when you have to sacrifice performance of some of your queries to make your data manageable. There is a very good article which touches upon this subject by Martin Widlake (http://mwidlake.wordpress.com/2009/10/29/partitions-are-not-for-performance/ ), and I myself am planning to explore this subject in more detail in near future.
Best regards,
Nikolay
Comment by savvinov — November 12, 2013 @ 2:19 pm |
Hi Nicolay,
Thanks a lot for your comment. I like very much your comment numbered 3. And by the way, you are doing a good work on otn.
Best regards
Comment by hourim — November 12, 2013 @ 2:43 pm |
[…] Global or Local Partitioned Index […]
Pingback by Index design | Mohamed Houri’s Oracle Notes — September 3, 2014 @ 2:56 pm |
[…] eliminating partitions (partition range all). This trouble shooting issue paved the way to the partitioned index: global or local article via which the designer of the “culprit” index has perfectly learnt the […]
Pingback by Partition by virtual column | Mohamed Houri’s Oracle Notes — October 1, 2014 @ 3:19 pm |