Mohamed Houri’s Oracle Notes

November 14, 2012

Recycle bin: what’s going on?

Filed under: Oracle — hourim @ 11:11 am

Have you ever been confronted to such a kind of explain plan?

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                                |     1 |    41 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE             |                                |     1 |    41 |     1   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   PARTITION LIST ALL                |                                |     1 |    41 |     1   (0)| 00:00:01 |     1 |     4 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| RANG_LIST_TAB                  |     1 |    41 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     INDEX RANGE SCAN                | BIN$FLEwdQFFQfu/hr2Q7TQeag==$0 |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("CREATION_DATE"=TRUNC(SYSDATE@!-5))
 

Look carefully to the operation 4

 INDEX RANGE SCAN   BIN$FLEwdQFFQfu/hr2Q7TQeag==$0 

Bizarre!!! From where does this index come from ?

More bizarre is this:

select * from recyclebin;
        no rows selected

select count(*) from user_objects where object_name like 'BIN%';

  COUNT(*)
----------
        45

The Recycle bin is empty while the user_objects table contains 45 objects having a name starting with the BIN characters!!!

I will explain how I came to this situation.

First this is the reproducible model:

create table rang_list_tab
(
  creation_date   date                not null,
  status_code     varchar2(30 char)   not null,
  n1              number              not null,
  v1              varchar2(1 char)    not null
)
partition by range (creation_date)
subpartition by list (status_code)
subpartition template
  (subpartition sub_p1 values ('FULL') ,
   subpartition sub_p2 values ('EMPTY') ,
   subpartition sub_p3 values ('UNKNOWN') ,
   subpartition others  values (default)
   )
(  
  partition p_20100131 values less than ( to_date('20100201','yyyymmdd') ),  
  partition p_20100201 values less than ( to_date('20100202','yyyymmdd') ),  
  partition p_20100202 values less than ( to_date('20100203','yyyymmdd') ), 
  partition p_20100203 values less than ( to_date('20100204','yyyymmdd') ),  
  partition p_20100204 values less than ( to_date('20100205','yyyymmdd')), 
  partition p_20100205 values less than (to_date('20100206','yyyymmdd')),  
  partition p_20100206 values less than ( to_date('20100207','yyyymmdd')),  
  partition p_20100207 values less than ( to_date('20100208','yyyymmdd'))
)
;

create  index idx_cdate on rang_list_tab (creation_date) local ;

create index idx_n1 on rang_list_tab(n1);

INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'FULL', 1000, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100202','yyyymmdd'), 'FULL', 30000, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100203','yyyymmdd'), 'EMPTY', 2000, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100204','yyyymmdd'), 'UNKNOWN', 1000, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100205','yyyymmdd'), 'EMPTY', 2400, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'EMPTY', 500, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'UNKNOWN', 700, 'P');

insert into rang_list_tab values (to_date('20100202','yyyymmdd'), 'UNKNOWN', 1200, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'UNKNOWN', 400, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100206','yyyymmdd'), 'FULL', 600, 'P');

INSERT INTO rang_list_tab VALUES (to_date('20100204','yyyymmdd'), 'FULL', 700, 'P');

commit;

Now, I will drop this table and flash back it again

drop table rang_list_tab;

flashback table rang_list_tab to before drop;

explain plan for
  2  select * from rang_list_tab
  3  where creation_date = trunc(sysdate-5)
  4  ;

Explained.

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                                |     1 |    41 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE             |                                |     1 |    41 |     1   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   PARTITION LIST ALL                |                                |     1 |    41 |     1   (0)| 00:00:01 |     1 |     4 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| RANG_LIST_TAB                  |     1 |    41 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     INDEX RANGE SCAN                | BIN$ZVFWHaVnTlS+4EFPATDblg==$0 |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("CREATION_DATE"=TRUNC(SYSDATE@!-5))

And here we are.

When you flash back a dropped table its attached index is not given back its original name. You need to manually rename it.

alter index "BIN$ZVFWHaVnTlS+4EFPATDblg==$0" rename to idx_cdate ;

Index altered.

mohamed@mhouri> explain plan for
  2  select * from rang_list_tab
  3  where creation_date = trunc(sysdate-5)
  4  ;

Explained.

mohamed@mhouri> select * from table(dbms_xplan.display);

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     1 |    41 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE             |               |     1 |    41 |     1   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   PARTITION LIST ALL                |               |     1 |    41 |     1   (0)| 00:00:01 |     1 |     4 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| RANG_LIST_TAB |     1 |    41 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     INDEX RANGE SCAN                | IDX_CDATE     |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("CREATION_DATE"=TRUNC(SYSDATE@!-5))

That is not only the indexes that are not giving back their original name but almost all the table attached objects. Let’s try.

create table t1 (n1 number primary key);
create table t2 (n1 number primary key, n2 number , d2 date);
alter table t2 add constraint t2_fk foreign key(n2) references t1(n1);
alter table t2 add constraint t2_ck_d2 check (d2 is not null);
alter table t2 add constraint t2_uk_d2 unique(d2);

create index t2_fk_i on t2 (n2);

create or replace trigger t2_trg 
before insert on t2
for each row
begin dbms_output.put_line('trigger exists'); end;
/

select constraint_name, constraint_type, r_constraint_name from user_constraints where table_name = 'T2';

CONSTRAINT_NAME                C R_CONSTRAINT_NAME
------------------------------ - ------------------------------
SYS_C0015809                   P
T2_FK                          R SYS_C0015804
T2_CK_D2                       C
T2_UK_D2                       U

select index_name, table_name, uniqueness from user_indexes where table_name     = 'T2';

INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
T2_FK_I                        T2                             NONUNIQUE
T2_UK_D2                       T2                             UNIQUE
SYS_C0015809                   T2                             UNIQUE

select trigger_name, table_name from user_triggers where table_name    = 'T2';

TRIGGER_NAME                   TABLE_NAME
------------------------------ ------------------------------
T2_TRG                         T2

Table t2 has 4 constraints, 3 indexes and 1 trigger. I will drop it, flash it back and see what happens

drop table t2;

select  original_name, object_name, type from recyclebin;

ORIGINAL_NAME                    OBJECT_NAME                    TYPE
-------------------------------- ------------------------------ -------------------------
T2                               BIN$aIjHZIW4SIOhKx5Igx+FeQ==$0 TABLE
T2_TRG                           BIN$ZFZjH11RTbeQL8nMB+erDA==$0 TRIGGER
T2_UK_D2                         BIN$ryvaxcC6TLmjTj1Z+R/l7Q==$0 INDEX
SYS_C0015816                     BIN$PoLuxzQtRLqJqnA/8HWmnA==$0 INDEX
T2_FK_I                          BIN$DcRcwh2oSP6Uqc3eyI6ntA==$0 INDEX

flashback table t2 to before drop;

select constraint_name, constraint_type, r_constraint_name from user_constraints where table_name = 'T2';

CONSTRAINT_NAME                C R_CONSTRAINT_NAME
------------------------------ - ------------------------------
BIN$gteIpe7bTF2eWYi8/78uuw==$0 P
BIN$28+EDP0yTBKep354YKfaEQ==$0 C
BIN$xUFESH5oSLOICYctWxvcmQ==$0 U

The Foreign Key constraint has not been flashed back. It’s gone. Lost. The unique and primary constraint are back with that very bizarre name.

select index_name, table_name, uniqueness from user_indexes where table_name     = 'T2';

INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
BIN$DcRcwh2oSP6Uqc3eyI6ntA==$0 T2                             NONUNIQUE
BIN$ryvaxcC6TLmjTj1Z+R/l7Q==$0 T2                             UNIQUE
BIN$PoLuxzQtRLqJqnA/8HWmnA==$0 T2                             UNIQUE

All indexes are back with their not very commun name

select trigger_name, table_name from user_triggers where table_name    = 'T2';

TRIGGER_NAME                   TABLE_NAME
------------------------------ ------------------------------
BIN$ZFZjH11RTbeQL8nMB+erDA==$0 T2

And trigger is back also but always without its original name.

In this context of name change, I was wondering what if an application is hinting indexes with their original name. What happens when their corresponding table is droped/flashed back?

create table t2 as select rownum n1 from dual connect by level <= 1E6;

create index ind_t2 on t2(n1);

explain plan for
select /*+ index (t2 ind_t2) */ *
from t2
where n1 > 100;

select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |   746K|  9482K|  2300   (1)| 00:00:28 |
|*  1 |  INDEX RANGE SCAN| IND_T2 |   746K|  9482K|  2300   (1)| 00:00:28 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("N1">100)
 
drop table t2;

flashback table t2 to before drop;

explain plan for
  2  select /*+ index (t2 ind_t2) */ *
  3  from t2
  4  where n1 > 100;

Explained.

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   746K|  9482K|   440   (4)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T2   |   746K|  9482K|   440   (4)| 00:00:06 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N1">100)

The hinted index is not anymore selected by the CBO. If you want to reproduce the explain plan before the drop then you need to hint again with the new bizarre index name or to rename the flashed back index name to the original name

Bottom Line: when you flash back a dropped table, do not be surprised to find that you are no longer protected by a foreign key constraint if it was the case before the drop. Be aware also that you will lose any naming standard you have used for the table depending objects such as triggers, indexes, constraints and so on. Due to this naming change, any hinted index could not be selected anymore by the CBO until you will explicitly give it back its original name. And in that case, where would you find this original name? Hmmm!!! Documentation rocks, documentation rocks.

November 2, 2012

Index unique scan and the Clustering Factor: does it matter?

Filed under: Oracle — hourim @ 10:58 am

When the CBO evaluates the cost of using an index it uses the following formula:

cost =
    blevel +
    ceiling(leaf_blocks * effective index selectivity) +
    ceiling(clustering_factor * effective table selectivity)

The third line of the above formula multiplies the index clustering factor by the effective table selectivity. This means that the more the index clustering factor value is big the less is the chance to see this index used by the CBO.

But wait a moment: is this formula still valid for a unique index?
What about a unique index with an enormous clustering factor? Will its chance to be used by the CBO be altered by its enormous clustering factor?

I am going to answer:

The clustering factor makes sense only when you access more than one row from a table block.

Do you think that this answer is 100% correct? Not really.

The clustering factor is still considered by the CBO even when you access only one row from a table block but you do that via an index range scan.

Is it still not clear?

Let me then try to be more precise:

The clustering factor does not influence the CBO when

1. you access one row from a table block and
2. when the CBO knows that it will access this one row via a unique index scan.

This blog article aims to demonstrate the above points

This is the model (thanks Jonathan !)

SQL> create table t1
    as
    with generator as (
        select  --+ materialize
            rownum id
        from dual
       connect by
            level >= 10000
    )
   select
       rownum          id1,
       trunc(dbms_random.value(1,10000)) n1,
       lpad(rownum,10,'0') small_vc,
       rpad('x',100)       padding
   from
       generator   v1,
       generator   v2
   where
       rownum  <= 1000000;

SQL > create index ind_id1 on t1(id1);
Index created.

SQL> create index ind_id1_n1 on t1(id1,n1);
Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t1', cascade =>; true);
PL/SQL procedure successfully completed.

SQL> select INDEX_NAME, uniqueness, LEAF_BLOCKS, NUM_ROWS
           ,clustering_factor
      from USER_INDEXES
      where table_name = 'T1';

INDEX_NAME                     UNIQUENES LEAF_BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------------------------ --------- ----------- ----------  -----------------
IND_ID1_N1                     NONUNIQUE        2784    1000000              17544
IND_ID1                        NONUNIQUE        2226    1000000              17544

SQL> select *
    from t1
    where id1 = 42;
       ID1         N1 SMALL_VC   PADDING
---------- ---------- ---------- -----------------------------------------
        42        491 0000000042 x

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
SQL_ID  08gbtm7g2pns8, child number 0
-------------------------------------
select * from t1 where id1 = 42
Plan hash value: 3333317548
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |   121 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_ID1 |     1 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID1"=42)

So far, the CBO chooses to range scan ind_id1 (id1) index to get only one row from table t1. What will happen if we alter the clustering factor of this index to a dramatic value?

SQL> exec DBMS_STATS.SET_INDEX_STATS(user,'ind_id1',clstfct => 1e7);

PL/SQL procedure successfully completed.

SQL> select INDEX_NAME, uniqueness, LEAF_BLOCKS, NUM_ROWS, clustering_factor
        from USER_INDEXES
        where table_name = 'T1';

INDEX_NAME                     UNIQUENES LEAF_BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------------------------ --------- ----------- ----------  ------------
IND_ID1_N1                     NONUNIQUE        2784    1000000           17544
IND_ID1                        NONUNIQUE        2226    1000000           10000000

SQL> select * from t1 where id1 = 42;
       ID1         N1 SMALL_VC   PADDING
---------- ---------- ---------- ---------------------------------------------------------
        42       4000 0000000042 x

SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  60qd5vf8jadn9, child number 0
-------------------------------------
select * from t1 where id1 = 42
Plan hash value: 862204649
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1         |     1 |   121 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_ID1_N1 |     1 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID1"=42)

We see that, even when accessing only one row, the CBO has decided to use the second index (ind_id1_n1) which possess a better clustering factor value. What will happen if we make the index ind_id1 unique?

SQL> drop index ind_id1;
Index dropped.

SQL> create unique index ind_id1 on t1(id1);
Index created.

SQL> exec DBMS_STATS.SET_INDEX_STATS(user,'ind_id1',clstfct => 1e7);
PL/SQL procedure successfully completed.

SQL> select * from t1 where id1 = 42;
       ID1         N1 SMALL_VC   PADDING
---------- ---------- ---------- --------------------------------------------
       42       4000 0000000042 x

SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  60qd5vf8jadn9, child number 0
-------------------------------------
select * from t1 where id1 = 42
Plan hash value: 2434187540
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |   121 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_ID1 |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID1"=42)

Even when we set the clustering factor of the unique index ind_id1 to the dramatic value of 1e7, it is still prefered and used by the CBO.

Bottom line: when you know that your index will be created on a column which is unique from the business point of view you had better then to let the CBO knows this information by creating this index as a UNIQUE index. As such you will enhance the propability of using this index when selecting one row from a table whatever will be the value of the clustering factor of this unique index

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)