Mohamed Houri’s Oracle Notes

September 3, 2014

Index design I

Filed under: Uncategorized — hourim @ 2:56 pm

Very often questions about the best position a column should be put in within a composite index come out into forums and Oracle discussions. The last question I have contributed to and tried to answer has been raised up in a French Forum. The original poster was wondering whether it is a good idea to place the very often repeated column (contains duplicates) at the leading edge of the index or not.

First of all, in contrast to my usual style of blogging I am not going to provide a SQL model on which I am going to expand my knowledge of index design. However, for those who want to learn and master indexes I would encourage them to read the world expert person in this field, Richard Foote. He has an excellent blog with several articles about almost all what one has to know about indexes and not only the widely used b-tree indexes but on all other types of indexes including bitmap indexes, function based indexes, partitioned indexes, exadata storage indexes etc..

The second reference is as always Jonathan Lewis blog in which you can find several articles about  index design, index efficiency and index maintenance. In addition, it is not sufficient to know how to design precise index; you need to know as well how your index will evolve with the delete, insert or update operations their underlined tables will undergo during the lifecycle of the application they participate to its normal functioning.

The third reference is the book Relational Database Index Design and the Optimizers which extends the index design to several databases including DB2, Oracle and SQL Server

I, from time to time, come to read very interesting articles about indexes in this web site that I am following via twitter. It contains valuable index design information which, according to what I have read up to now, is pertinent, correct and back up all what I have learned from Jonathan Lewis, Richard Foote and from my own professional experience.

That’s said, I will post here below few of my answers and articles(and Jonathan Lewis articles) about index design as a small answer to a lot of questions about index design

  1. On the importance of the leading index columns that should be the ones on which an equality predicate is applied
  2. Indexing Foreign keys
  3. Redundant Indexes
  4. Global or Local Partitioned Index
  5. Compressing indexes basic part and cost of index compression

I am planning to write several other articles on indexes and I will be completing the above list as far as I will go with this publishing task

My answer to the original poster question about the importance of the number of distinct values property of an index candidate column is that the starting index column decision is not driven by its number of distinct values. It is instead driven by:

  • The nature of the query where clause he has to cover
  • The nature of the predicate (equality, inequality, etc..) applied on the starting column
  • The constant desire to cover with the same index one or a couple of other queries
  • The constant desire to cover with the same index a foreign key deadlock threat : sometime just by reversing the columns order we succeed to cover the current query and an existing foreign key
  • The constant desire to avoid redundant indexes

And finally comes up the reason for which one has to consider placing the column with the small number of distinct values at the leading edge of the index: Compression. If you start your index with the more often duplicated column you will make a good index compression reducing, efficiently, the size of that index which means it will be very quickly put into the buffer cache and will be kept there more often due to its small size.

February 9, 2012

/*+ materialized */ hint and ORA-04091 error

Filed under: Uncategorized — hourim @ 12:12 pm

It is funny to know that the /*+ materialized */ hint is, behind the scene, materialized by Oracle with a creation of a global temporary table holding the data generated by the corresponding “with subquery”. What is also funny for me is, that when trouble-shooting oracle issues, I am always cross checking, all what I have read in the past that could have any relation to the problem I am confronted to at the present time. Dom Brook post is what I have recently read and is what “my cross checking” work pointed out to be eventually related to the situation explained below.

So, recently a question came up on an Oracle French forum about an insert/select statement that was working well in 10g while it started failing in error following an upgrade to 11g. There was an excellent discussion in that French forum with valuable remarks from different posters. But my curiosity was catalyzed by the fact that no one was able to clearly show which release is correct? 10g or 11g? Before coming back to the /*+ materialized */ hint which motivates this blog let me reproduce the insert/select test case here below:

10gR2 > create table t_read_consistency (id number, vc varchar2(15));
Table created.
10gR2 > insert into
2        t_read_consistency
3      select
4        rownum id,
5        rpad('a',15,'a')
6      from
7        dual
8      connect by
9        level <= 1000;
1000 rows created.

10gR2 > create type t_read_cs as object (id number,vc varchar2(15));
Type created. > create type t_read_cs_tab  as table of t_read_cs;
Type created.
10gR2 > create or replace function f_read_consistency_tab
  2  return t_read_cs_tab
  3    as
  4      lc_t_read_cs_tab t_read_cs_tab := t_read_cs_tab();
  5      j  binary_integer := 0;
  6    begin
  7        for x in (select
  8                      id,
  9                      vc
 10                   from  t_read_consistency trs
 11                  where <= 10
 12       ) loop
 14          j := j +1;
 15          lc_t_read_cs_tab.extend;
 16          lc_t_read_cs_tab(j) := t_read_cs(,;
 17     end loop;
 18     RETURN lc_t_read_cs_tab;
 19   end f_read_consistency_tab;
 20  /

 Function created.

This is the model. Lets now start the insert/select operations both in 10g and 11g

10gR2 > select * from v$version;

Oracle Database 10g Enterprise Edition Release - 64bi
PL/SQL Release - Production
CORE    Production
TNS for Solaris: Version - Production
NLSRTL Version - Production

10gR2 > insert into t_read_consistency
2         (id,vc)
3      select id,vc
4   from table(f_read_consistency_tab)
5  ;
10 rows created.

11gR2 > select * from v$version;
Oracle Database 11g Enterprise Edition Release - Production
PL/SQL Release - Production
CORE      Production
TNS for 32-bit Windows: Version - Production
NLSRTL Version - Production

11gR2 > insert into t_read_consistency
2            (id,vc)
3          select id,vc
4      from table(f_read_consistency_tab)
5     ;
from table(f_read_consistency_tab)
ERROR at line 4:
ORA-04091: table MOHAMED.T_READ_CONSISTENCY is mutating, trigger/function may
not see it

Spot the difference. What is working well in 10g is throwing an error in 11g!!! Starting from this point I was religiously trying to persuade myself that the 11g release is where the correct situation is in contrast to what were claiming several posters in the French forum. From otn, I got the link to the related documentation:

which clearly states this:

  • When called from an INSERT, UPDATE, or DELETE statement, the function cannot query or modify any database tables modified by that statement. (10g)
  • When invoked from an INSERT, UPDATE, or DELETE statement, the subprogram cannot query or modify any database tables modified by that statement (11g)

The 11g release is, at least, in accordance with its corresponding documentation.

Then the /*+ materialized */ hint entered the scene. From ask tom, I knew that the following slightly modified insert/select is not throwing any error in 11g:

11gR2> insert into t_read_consistency(id,vc)
2          with data as (select /*+ materialize */ id, vc from table(f_read_consistency_tab))
3           select * from data
4          ;

10 rows created.

Consequently, if the above insert is working well this means that the insert is

  1. Either not selecting from the pipelined function and hence from the inserted table
  2. Or it is selecting from this pipelined function but before the start of the insert (exactly if I have used a PL/SQL block in which I would have first declared a local t_read_cs_tab type in which I would have then sent the content of the pipelined function and finally  would have used a select from this local type in my insert/select operation)
11gR2> explain plan for
2  insert into t_read_consistency(id,vc)
3          with data as (select /*+ materialize */ id, vc from table(f_read_consistency_tab))
4           select * from data
5          ;


11gR2> select * from table(dbms_xplan.display);

Plan hash value: 1931462686
| Id  | Operation                           | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | INSERT STATEMENT                    |                             |  8168 |   175K|    27   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION          |                             |       |       |            |          |
|   2 |   LOAD AS SELECT                    | T_READ_CONSISTENCY          |       |       |            |          |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| F_READ_CONSISTENCY_TAB      |       |       |            |          |
|   4 |   VIEW                              |                             |  8168 |   175K|     2   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL                | SYS_TEMP_0FD9D6622_C1854848 |  8168 | 16336 |     2   (0)| 00:00:01 |


According to the above explain plan, the first executed operation is operation 3(read from the pipelined function) which feeds back its parent operation 2 followed by a read from that famous temporary table (SYS_TEMP_0FD9D6622_C1854848) created on the fly in response to the /*+ materialized */ hint. The insert is so that, before the first row is inserted, the function is read in its entirety and its returned rows are stored into the temporary table which is used by the insert statement. This is why the insert is not failing when the /*+ materialize */ hint is used.

Well, here below are the chronological steps

  1. I read Dom Brooks blog about the /*+ materialize */ hint where I learn that there is a creation of a temporary table behind the scene
  2. I was confronted to an issue about insert/select that is working well in 10g and not in 11g
  3. I knew that the insert/select will work in 11g if the hint /*+ materialized */ is used
  4. I cross check what I read recently and linked that to Dom Brooks article
  5. Wrote this blog article to put these chronological steps in action

August 23, 2007

Program testing

Filed under: Uncategorized — hourim @ 10:45 am

When I first read this

“Program testing can be used to show the presence of bugs, but never to show their absence!”

I was absolutely fascinated how with only a unique phrase we can clearly express what we are unable to do with several paragraphs, conversations and e-mails.

I have been using database software for more than 8 years, of which the last 3 years has been spent collaborating with the Quality Assurance (QA) team. If I was asked what best is to do to bring software from test to the production environment with less bugs, my answer would be summarized in one phrase: through doubt on your tests

Being in doubt will bring you to ask yourself more and more questions generating uncertainty on what you have implemented causing you to review your code and to test again.

As far as program testing aims to show the presence of bugs and not their absence then doubt will certainly increase the number of bugs we will discover much earlier in development process reducing the number of incidents in the production environment where is it not easy to intervene.

Hence, do you think that doubt is a good or an inadequate skill?

Blog at

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.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog


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