Recently I’ve been asked to help explain why one query is running so slow based on only SQL text and table definition that is provided.
As query was pretty big (several hundreds lines of code), at the first I thought that is mission impossible, as I have no explain plan available, no running stats, no access to the system etc.
After a second though, I start to analyze what could be wrong with that query.
Finally, after a while I’ve spotted local non-prefixed index that is defined on problematic part of the code.
To explain why non prefixed indexes are rarely a good idea, I’ll use one trivial example to show what is going on behind the scene.
First I’ll create ttest – our test table, based on existing ttable with 8 million rows, with 16 partitions with two columns:
– item Varchar2
– id Number which is almost unique, generated by using random generator.
create table ttest
tablespace users
nologging
partition by range (item)
(
partition p1 values less than ('03160358'),
partition p2 values less than ('05083085'),
partition p3 values less than ('05774630'),
partition p4 values less than ('06480477'),
partition p5 values less than ('08403230'),
partition p6 values less than ('90003487'),
partition p7 values less than ('90026474'),
partition p8 values less than ('90053663'),
partition p9 values less than ('90087872'),
partition p10 values less than ('90112295'),
partition p11 values less than ('90142415'),
partition p12 values less than ('90174718'),
partition p13 values less than ('90209953'),
partition p14 values less than ('90244470'),
partition p15 values less than ('90276220'),
partition p16 values less than (maxvalue)
)
as
select item, ceil(dbms_random.value(1, 10000000)) as id from texample;
Now when we have a table to play with, let’s take statistics and create local non-prefixed index.
exec dbms_stats.gather_table_stats(ownname => user, tabname => 'ttest');
create index ix_non_prefixed on ttest(id) tablespace users nologging local;
At this point we are ready for the test.
SCOTT@orcl> select * from ttest where id = 7110603;
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 35 (100)| | | |
| 1 | PARTITION RANGE ALL | | 1 | 15 | 35 (0)| 00:00:01 | 1 | 16 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TTEST | 1 | 15 | 35 (0)| 00:00:01 | 1 | 16 |
|* 3 | INDEX RANGE SCAN | IX_NON_PREFIXED | 1 | | 33 (0)| 00:00:01 | 1 | 16 |
----------------------------------------------------------------------------------------------------------------------
As table ttest is partitioned by item column, non-prefixed index must search all 16 partitions to find all index entries that satisfy condition (WHERE part of SQL).
Let’s drop non-prefixed and create a “normal” – global index and repeat the test.
drop index ix_non_prefixed;
create index ix_global on ttest(id) tablespace users nologging;
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| TTEST | 2 | 54 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | IX_GLOBAL | 2 | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------
You need to observe two important points from the execution plans:
1. Cost column has dropped from 35 to 2
2. Pstart/Pstop columns in the local non-prefixed case are 1 – 16 (meaning no partition elimination will be performed, thus all 16 partitions will be examined), while in the former test where we use global index, we have rowid – rowid range scan which is very efficient in our case as id column is almost unique.
Similar behavior you might find in cases with min/max value on local non-prefixed, semijoins/antijoins, subquery factoring etc.
Still there are certain cases where local non-prefixed index is better solution than global index, although I have not found them very often in praxis.
Summary:
Example from this post is showing that even with only tiny fragment of information (SQL Text in this case), it is possible to troubleshoot and explain what is causing that something is running below our expectations, and to find a way how to fix it, but only if you really posses a deep understanding of Oracle internals.
We can conclude that it is not that local non-prefixed index is slow. Instead it is matter of index design (many partitions, large table) and SQL that we are executing that results in more time/resource consumption when comparing local non-prefixed index with it’s global opponent.
Comments