April 2, 2007

When I was testing the virtual indexes for the previous post, I got ORA-12838 from the subsequent select after inserting 4 million rows without commit (I mean I forgot committing). The error was

ORA-12838: cannot read/modify an object after modifying it in parallel

The error documentation says ;

Cause: Within the same transaction, an attempt was made to add read or modification statements on a table after it had been modified in parallel or with direct load. This is not permitted.Action: Rewrite the transaction, or break it up into two transactions: one containing the initial modification and the second containing the parallel modification operation.

Action was not clear for me. When I look at the code I saw that there wasnt any commit when i committed the transaction the error solved.

As a main idea for the blog entry “look twice to your code before taking an action” :)

While looking at Mr Julian Dyke’s presentation about index internals I saw a new (at least new for me) feature Virtual Indexes. It is there since the 8.1.5 release but i was unaware :( .

These indexes are not physically located on a segment but they have a data dictionary definition (you can’t add a real index with same name of virtual index). Virtual indexes allow user to test a potential advantages or disadvantages of a new index prior to actually building the new index in the database . For example you want an index on a table with 10 million rows and you do not sure about using an index then you can use virtual indexes for having idea about new execution plans.

CBO do not use virtual indexes by default without setting the undocumented parameter “_use_nosegment_indexes” to true and this is something you must consider before deciding to use virtual indexes. Since it is undocumented then it is not supported, unless the Oracle say that you can set. In my tests with virtual index i got similar cost results and same access paths from CBO comparing to real index usage.

You have to generate statistics for the virtual index to get realistic costs. Creation of the index is not enough, it is called virtual but it acts like if it is real so if you want a good index simulation don’t forget the statistics. You can use the syntax below to generate statistics;

SQL>EXECUTE DBMS_STATS.GENERATE_STATS (user,’index_name’)

Don’t forget to set the session wide “_use_nosegment_indexes” parameter .