Secondary indexes on IOT can be an issue. Rows in an IOT don't have a true rowid logical row identifiers (logical rowids) that are based on the table's primary key. A logical rowid optionally includes a physical guess, which identifies the block location of the row.
So, if you create, populate and then index an IOT - the secondary index will have in it:
a) the columns you indexed
b) a rowid HINT, a rowid GUESS, the rowid observed when the row was first seen in the index - this is the rowid of the leaf block, it is not a true rowid
c) the logical rowid (in actuality the primary key of the IOT row being indexed)
As you modify the IOT, rows will move due to block splits, the secondary indexes will become less efficient as the rowid GUESS goes stale.
After running through below test scripts in Oracle 9iR2, 10gR2 and 11gR1 founded the below observations.
CREATE TABLE T
(
owner,
object_name,
object_type,
object_id,
created,
last_ddl_time,
CONSTRAINT t_pk PRIMARY KEY(owner,object_name,object_type,object_id)
)
organization INDEX AS
SELECT owner,
object_name,
object_type,
object_id,
created,
last_ddl_time
FROM all_objects;
create index t_ind on t(object_type,created) nologging;
begin dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',cascade=>true,method_opt=>'for all indexed columns size 254');
end;
/
select /* first_query */ /*+ INDEX(t,t_ind) */ *
from t
where created between to_date('01/01/2006','mm/dd/yyyy') and sysdate
and object_type = 'INDEX';
update t
set object_name = lower(object_name);
commit;
select /* second_query */ /*+ INDEX(t,t_ind) */ *
from t
where created between to_date('01/01/2006','mm/dd/yyyy') and sysdate
and object_type = 'INDEX';
alter index t_ind rebuild;
select /* third_query */ /*+ INDEX(t,t_ind) */ *
from t
where created between to_date('01/01/2006','mm/dd/yyyy') and sysdate
and object_type = 'INDEX';
test@9iR2> select * from v$version;
BANNER
--------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
test@9iR2> select /* first_query */ /*+ INDEX(t,t_ind) */ *
2 from t
3 where created between to_date('01/01/2006','mm/dd/yyyy') and sysdate
4 and object_type = 'INDEX';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=958 Card=635 Bytes=36830)
1 0 FILTER
2 1 INDEX (UNIQUE SCAN) OF 'T_PK' (UNIQUE) (Cost=9 Card=635 Bytes=36830)
3 2 INDEX (RANGE SCAN) OF 'T_IND' (NON-UNIQUE) (Cost=9 Card=635)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
696 consistent gets
0 physical reads
0 redo size
33669 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
682 rows processed
As you modify the IOT, rows will move due to block splits, the secondary indexes will become less efficient as the rowid GUESS goes stale.
test@9iR2> select /* second_query */ /*+ INDEX(t,t_ind) */ *
2 from t
3 where created between to_date('01/01/2006','mm/dd/yyyy') and sysdate
4 and object_type = 'INDEX';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=958 Card=635 Bytes=36830)
1 0 FILTER
2 1 INDEX (UNIQUE SCAN) OF 'T_PK' (UNIQUE) (Cost=9 Card=635 Bytes=36830)
3 2 INDEX (RANGE SCAN) OF 'T_IND' (NON-UNIQUE) (Cost=9 Card=635)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
819 consistent gets
33 physical reads
3888 redo size
33669 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
682 rows processed
So, in a read/write system (eg: not a warehouse), the rowid guess'es go stale and we end up doing N range scans - one to find the entries in the secondary index and then N-1 to retrieve the N-1 rows from the IOT.And then you rebuild the secondary index and all is well.
test@9iR2> select /* third_query */ /*+ INDEX(t,t_ind) */ *
2 from t
3 where created between to_date('01/01/2006','mm/dd/yyyy') and sysdate
4 and object_type = 'INDEX';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=958 Card=635 Bytes=36830)
1 0 FILTER
2 1 INDEX (UNIQUE SCAN) OF 'T_PK' (UNIQUE) (Cost=9 Card=635 Bytes=36830)
3 2 INDEX (RANGE SCAN) OF 'T_IND' (NON-UNIQUE) (Cost=9 Card=635)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
696 consistent gets
0 physical reads
0 redo size
33669 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
682 rows processed
scott@10GR2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
scott@10GR2> select /* first_query */ /*+ INDEX(t,t_ind) */ *
2 from t
3 where created between to_date('01/01/2006','mm/dd/yyyy') and sysdate
4 and object_type = 'INDEX';
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 372 | 21576 | 935 (1)| 00:00:12 |
|* 1 | FILTER | | | | | |
|* 2 | INDEX UNIQUE SCAN| T_PK | 372 | 21576 | 935 (1)| 00:00:12 |
|* 3 | INDEX RANGE SCAN| T_IND | 372 | | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1067 consistent gets
0 physical reads
0 redo size
52440 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1049 rows processed
scott@10GR2> select /* second_query */ /*+ INDEX(t,t_ind) */ *
2 from t
3 where created between to_date('01/01/2006','mm/dd/yyyy') and sysdate
4 and object_type = 'INDEX';
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 372 | 21576 | 935 (1)| 00:00:12 |
|* 1 | FILTER | | | | | |
|* 2 | INDEX UNIQUE SCAN| T_PK | 372 | 21576 | 935 (1)| 00:00:12 |
|* 3 | INDEX RANGE SCAN| T_IND | 372 | | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3682 consistent gets
0 physical reads
0 redo size
52440 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1049 rows processed
scott@10GR2> select /* third_query */ /*+ INDEX(t,t_ind) */ *
2 from t
3 where created between to_date('01/01/2006','mm/dd/yyyy') and sysdate
4 and object_type = 'INDEX';
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 372 | 21576 | 960 (1)| 00:00:12 |
|* 1 | FILTER | | | | | |
|* 2 | INDEX UNIQUE SCAN| T_PK | 372 | 21576 | 960 (1)| 00:00:12 |
|* 3 | INDEX RANGE SCAN| T_IND | 372 | | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1067 consistent gets
0 physical reads
0 redo size
52440 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1049 rows processed
scott@11GR1> select * from v$version;
BANNER
--------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
scott@11GR1> select /* first_query */ /*+ INDEX(t,t_ind) */ *
2 from t
3 where created between to_date('01/01/2006','mm/dd/yyyy') and sysdate
4 and object_type = 'INDEX';
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3507 | 202K| 8700 (1)| 00:01:45 |
|* 1 | FILTER | | | | | |
|* 2 | INDEX UNIQUE SCAN| T_PK | 3507 | 202K| 8700 (1)| 00:01:45 |
|* 3 | INDEX RANGE SCAN| T_IND | 3507 | | 34 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3344 consistent gets
0 physical reads
0 redo size
150077 bytes sent via SQL*Net to client
647 bytes received via SQL*Net from client
23 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3292 rows processed
scott@11GR1> select /* second_query */ /*+ INDEX(t,t_ind) */ *
2 from t
3 where created between to_date('01/01/2006','mm/dd/yyyy') and sysdate
4 and object_type = 'INDEX';
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3507 | 202K| 8700 (1)| 00:01:45 |
|* 1 | FILTER | | | | | |
|* 2 | INDEX UNIQUE SCAN| T_PK | 3507 | 202K| 8700 (1)| 00:01:45 |
|* 3 | INDEX RANGE SCAN| T_IND | 3507 | | 34 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10920 consistent gets
0 physical reads
0 redo size
150093 bytes sent via SQL*Net to client
647 bytes received via SQL*Net from client
23 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3292 rows processed
scott@11GR1> select /* third_query */ /*+ INDEX(t,t_ind) */ *
2 from t
3 where created between to_date('01/01/2006','mm/dd/yyyy') and sysdate
4 and object_type = 'INDEX';
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3507 | 202K| 9008 (1)| 00:01:49 |
|* 1 | FILTER | | | | | |
|* 2 | INDEX UNIQUE SCAN| T_PK | 3507 | 202K| 9008 (1)| 00:01:49 |
|* 3 | INDEX RANGE SCAN| T_IND | 3507 | | 34 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3344 consistent gets
0 physical reads
0 redo size
150093 bytes sent via SQL*Net to client
647 bytes received via SQL*Net from client
23 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3292 rows processed
Hence secondary indexes can be an issue with IOT. But in some cases Secondary indexes provide fast and efficient access to IOT using columns other than primary keys.
rajesh@11GR2> create table t(
2 owner,
3 object_name,
4 object_type,
5 object_id,
6 created,
7 constraint t_pk primary key(object_id)
8 )organization index
9 nologging
10 as
11 select owner,object_name,object_type,object_id,created
12 from all_objects;
Table created.
Elapsed: 00:00:02.70
rajesh@11GR2>
rajesh@11GR2> create index t_ind on t(created) nologging;
Index created.
Elapsed: 00:00:00.65
rajesh@11GR2>
rajesh@11GR2> begin
2 dbms_stats.gather_table_stats(
3 ownname=>user,
4 tabname=>'T',
5 cascade=>true,
6 method_opt=>'for all columns size 254',
7 estimate_percent=>100);
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.64
rajesh@11GR2>
rajesh@11GR2>
rajesh@11GR2> set autotrace traceonly explain statistics;
rajesh@11GR2>
rajesh@11GR2> select * from t
2 where created = to_date('01/07/2010','dd/mm/yyyy')
3 /
no rows selected
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2448192542
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 2288 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| T_PK | 44 | 2288 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN| T_IND | 44 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("CREATED"=TO_DATE(' 2010-07-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
2 - access("CREATED"=TO_DATE(' 2010-07-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
539 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
rajesh@11GR2>
No comments:
Post a Comment