Oracle 12c IDENTITY and popular ORMs.

Since Oracle 12c came to the market with new IDENTITY options I faced few times with the cases what developers using ORM like ExclipseLink and Hibernate experienced issues. I decided to summarize something from my work with this option here.

In normal when we use similar option in MySQL 5.:

CREATE TABLE accounts (
     id INT NOT NULL AUTO_INCREMENT,
     username VARCHAR(30) NOT NULL,
     password VARBINARY(255) NOT NULL,
     email  VARCHAR(255) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=INNODB;

nothing prevents us from using standard JPA GenerationType.IDENTITY strategy both in EclipseLink and Hibernate:

package com.volt.master;

import javax.persistence.Entity;
import javax.persistence.Table;
import javax.persistence.Id;
import javax.persistence.GenerationType;
import javax.persistence.GeneratedValue;
import javax.persistence.Column;

@Entity
@Table(name="accounts", schema="dmitry")
public class Account {

	@Id
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	private Integer id;
	
	@Column
	private String username;
...

We can use the following code to persist our entity and get key value, generated in auto:

       EntityManagerFactory emf = Persistence.createEntityManagerFactory("MySQLUnit");
       EntityManager em = emf.createEntityManager(); 
       em.getTransaction().begin(); 
       Account acc = new Account();
...
	acc.setUsername(username);
	acc.setPassword(byteData);
	acc.setEmail(email);
	em.persist(acc);
	em.getTransaction().commit();
	System.out.println( acc.getId() );
       em.close();
       emf.close();

 Hibernate uses the following piece of code to provide AUTO_INCREMENT column extraction:

 @Override
 public String getIdentitySelectString() {
  return "select last_insert_id()";
 }

Eclipselink uses the very same:

    @Override
    public ValueReadQuery buildSelectQueryForIdentity() {
        ValueReadQuery selectQuery = new ValueReadQuery();
        StringWriter writer = new StringWriter();
        writer.write("SELECT LAST_INSERT_ID()");
        selectQuery.setSQLString(writer.toString());
        return selectQuery;
    }

The case becomes freak when we come to Oracle IDENTITY.

EclipseLink provides the functionality what supports Oracle-specific syntax statement returning generated values of columns:

    @Override
    public DatabaseCall buildCallWithReturning(SQLCall sqlCall, Vector returnFields) {
        SQLCall call = new SQLCall();
        call.setParameters(sqlCall.getParameters());
        call.setParameterTypes(sqlCall.getParameterTypes());

        Writer writer = new CharArrayWriter(200);
        try {
            writer.write("BEGIN ");
            writer.write(sqlCall.getSQLString());
            writer.write(" RETURNING ");

            for (int i = 0; i < returnFields.size(); i++) {
                DatabaseField field = (DatabaseField)returnFields.elementAt(i);
                writer.write(field.getNameDelimited(this));
                if ((i + 1) < returnFields.size()) {
                    writer.write(", ");
                }
            }

            writer.write(" INTO ");

            for (int i = 0; i < returnFields.size(); i++) {
                DatabaseField field = (DatabaseField)returnFields.elementAt(i);
                call.appendOut(writer, field);
                if ((i + 1) < returnFields.size()) {
                    writer.write(", ");
                }
            }

            writer.write("; END;");

            call.setQueryString(writer.toString());

        } catch (IOException exception) {
            throw ValidationException.fileError(exception);
        }

        return call;
    }

Hibernate doesn’t though. OK, the time of experiments has come. Let’s take Oracle 12c and create the correspondent table (I don’t use JPA schema autogeneration feature – this is important):

CREATE TABLE accounts (
     id INT GENERATED AS IDENTITY,
     username VARCHAR(30) NOT NULL,
     password RAW(255) NOT NULL,
     email  VARCHAR(255) NOT NULL,
     PRIMARY KEY (id)
) TABLESPACE users

Try now the code where GenerationType.IDENTITY strategy is used.

       EntityManagerFactory emf = Persistence.createEntityManagerFactory("OracleUnit");
       EntityManager em = emf.createEntityManager(); 
       em.getTransaction().begin(); 
       Account acc = new Account();
	acc.setUsername("Test");
	acc.setPassword("Test".getBytes());
	acc.setEmail("Test@Test.com");
	em.persist(acc);
	em.getTransaction().commit();
	System.out.println( "New ID is "+acc.getId() );
       em.close();
       emf.close();   

We get «ORA-02289: sequence not exists.». What’s up ?

When we use Oracle provider GenerationType.IDENTITY strategy inside uses predefined sequence object SEQ_GEN_IDENTITY:

public static final String DEFAULT_IDENTITY_GENERATOR = "SEQ_GEN_IDENTITY

Sure we get a problem, because we did not create this sequence ! But – we don’t want this sequence to use, we have autogenerated ID and want to get a value back post insertion. Do we have an option ?

Yes, we have , and this option was previously used when triggers were the standard way of autogenerated keys in Oracle. This is EclipseLink-specific @ReturnInsert annotation:

    @Id
    @Column(name = "ID")
    @ReturnInsert
    private BigDecimal id;

But when we try. we get another fail:

Error Code: 32795
Call: BEGIN INSERT INTO ACCOUNTS (ID, EMAIL, PASSWORD, USERNAME) VALUES (?, ?, ?, ?) RETURNING ID INTO ?; END;
...
Internal Exception: java.sql.SQLException: ORA-32795: cannot insert into a generated always identity column

Problems are very clear – IDENTITY in it’s pure incarnation prevents the client from the explicit insertion of ID value into auto-generated ID. Very logical BTW, isn’t it ? Else how can we guarantee uniqueness ?

Now one point to remember. Oracle allows to overrode this problem using DEFAULT ON NULL option (we CAN insert null into id and OLNY in this case IDENTIRY shall work – poor idea IMHO for the reasons I described above, but it works):

CREATE TABLE accounts (
id INT GENERATED BY DEFAULT ON NULL AS IDENTITY,
username VARCHAR(30) NOT NULL,
password RAW(255) NOT NULL,
email  VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
) TABLESPACE users

In this case we get the generated ID:

New ID is 1

Fine. But I don’t want to allow clients non-consistent values insert, let’s get back to original option:

CREATE TABLE accounts (
     id INT GENERATED AS IDENTITY,
     username VARCHAR(30) NOT NULL,
     password RAW(255) NOT NULL,
     email  VARCHAR(255) NOT NULL,
     PRIMARY KEY (id)
) TABLESPACE users

And my purpose now to exclude ID from the column and value list in INSERT statement. Can I do it and how ? Yes, I can, using “returnOnly” option of the annotation:

@ReturnInsert(returnOnly=true)

Now everything works like a charm and as expected:

New ID is 1

Now – come to Hibernate.

Hibernate’s Oracle10gDialect prevents IDENTITY strategy from use at all:

Dialect does not support identity key generation

But Hibernate allows us to customize the value generator and solve this problem. Of course this requires a lot of coding – much more than in EclipseLink. There is the thread in Hibernate forum describing this methos: https://forum.hibernate.org/viewtopic.php?t=973262 — thanks  much for Jean-Pol Landrain !
I shall provide here the example what is  based purely on OraclePreparedStatement approach to illustrate how  Oracle-specific JDBC  code  can handle the problem:

package org.hibernate.id;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import oracle.jdbc.OraclePreparedStatement;

import org.hibernate.HibernateException;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.id.SequenceIdentityGenerator.NoCommentsInsert;
import org.hibernate.id.insert.AbstractReturningDelegate;
import org.hibernate.id.insert.IdentifierGeneratingInsert;
import org.hibernate.id.insert.InsertGeneratedIdentifierDelegate;

public class Ora12IdentityGenerator extends AbstractPostInsertGenerator {

   @Override
   public InsertGeneratedIdentifierDelegate getInsertGeneratedIdentifierDelegate(
      PostInsertIdentityPersister persister, Dialect dialect, boolean isGetGeneratedKeysEnabled)
      throws HibernateException {
      return new Delegate(persister, dialect);
   }

   public static class Delegate extends AbstractReturningDelegate {

      private Dialect dialect;
      private String[] keyColumns;
      private int keyId;

      public Delegate(PostInsertIdentityPersister persister, Dialect dialect) {
         super(persister);
         this.dialect = dialect;
         this.keyColumns = getPersister().getRootTableKeyColumnNames();
         if (keyColumns.length > 1) {
            throw new HibernateException(
               "trigger assigned identity generator cannot be used with multi-column keys");
         }
      }

      @Override
      public IdentifierGeneratingInsert prepareIdentifierGeneratingInsert() {
         return new NoCommentsInsert(dialect);
      }

      @Override
      protected PreparedStatement prepare(String insertSQL, SessionImplementor session) 
              throws SQLException {
          insertSQL = insertSQL + " returning "+keyColumns[0]+" into ?";
          OraclePreparedStatement os = (OraclePreparedStatement)session.connection().prepareStatement(insertSQL);
          keyId = insertSQL.split("\\?").length;
          os.registerReturnParameter(keyId, Types.DECIMAL);
          return os;
      }

      @Override
      protected Serializable executeAndExtract(PreparedStatement insert, SessionImplementor session)
         throws SQLException {
          
        OraclePreparedStatement os = (OraclePreparedStatement)insert;
        os.executeUpdate();
      
        ResultSet generatedKeys = os.getReturnResultSet();
        if (generatedKeys == null) {
            throw new HibernateException("Nullable Resultset");
        }
        try {
           return IdentifierGeneratorHelper.getGeneratedIdentity(
                   generatedKeys, 
                   keyColumns[0],
                   getPersister().getIdentifierType());
        } finally {
            generatedKeys.close();
        }
      }
   }
}

Correspondent  changes in entry code  with @GenericGenerator Annotation should  be:

    @Id
    @Column(name = "ID")
    @Basic(optional=false)
    @GeneratedValue(generator="Identity12c")
    @GenericGenerator(name="Identity12c", strategy="org.hibernate.id.Ora12IdentityGenerator")
    private BigDecimal id;

Now  we have

New ID: 3

Another example what is Oracle-independent (in terms of JDBC library, not in syntax !) you can see here.

Posted in Uncategorized | Leave a comment

InitialContext parameters for different servers (just a reminder)

I often switch in between WebLogic and GlassFish on my machine. This post is just a reminder to myself – what are parameters for the InitialContext creation in both cases: So WebLogic:

        Hashtable env = new Hashtable(5);
        env.put(Context.INITIAL_CONTEXT_FACTORY,
           "weblogic.jndi.WLInitialContextFactory");
        env.put(Context.PROVIDER_URL,
           "t3://localhost:7001");
        Context ctx = new InitialContext(env);  

And don’t miss the library JAR – it should be wlthint3client.jar

In turn, GlassFish “template” is


Properties props = new Properties();
props.setProperty(Context.INITIAL_CONTEXT_FACTORY, "com.sun.enterprise.naming.SerialInitContextFactory");
props.setProperty(Context.URL_PKG_PREFIXES, "com.sun.enterprise.naming");
props.setProperty(Context.STATE_FACTORIES, "com.sun.corba.ee.impl.presentation.rmi.JNDIStateFactoryImpl");
props.put(Context.PROVIDER_URL, "http://localhost:3700");
Context ct = new InitialContext(props);

JAR file for EJB client should be gf-client.jar

Posted in Uncategorized | Leave a comment

Dependency injection and custom annotation

Java EE provides a couple of extremely attractive options – DI (dependency injection feature) and custom annotations what allow qualify classes you develop. They bring remarkable flexibility in the job – but if you keep in mind the technique they use.

Couple of days ago I had to resolve some issue in the third-party code. I will simulate the case and keep the main idea of design to demonstrate what was the issue. Let’s create the basic interface and a custom annotation:

public interface SimpleInterface {
     String msg();
}
@Target({TYPE})
@Retention(RUNTIME)
public @interface QualifyerA {
    
} 

Also we create simple class and allow DI using @SessionScoped annotation.

@SessionScoped
@QualifyerA
public class SimpleClass 
implements SimpleInterface, Serializable {
    public String msg() { return "Got it"; }
}

Finally let’s create managed bean and the simple JSF to check results:

@ManagedBean
public class SimpleBean {
    SimpleInterface si = new SimpleClass();
    public String result() {
        if (si.getClass().isAnnotationPresent(QualifyerA.class))
                return si.msg();
        else
                return "Not annotated";
    }
    public String showClass() {
        return si.getClass().getName();
    }
}

A piece of JSF code:

    
        Hello, #{simpleBean.result()}
    

If we start the application on Web application server we get:

Hello, Got it

OK, everything is well so far. Let’s now use the injection instead of explicit instantiation:

public class SimpleBean {
    
    @Inject SimpleInterface si;
    
    public String result() {
        if (si.getClass().isAnnotationPresent(QualifyerA.class))
                return si.msg();
...

And we have failed:

Hello, Not annotated

Oops, we lost something, but what ? We can understand what happens if we look as the variable real class (
let’s change JSF code):

Hello, #{simpleBean.showClass()}

We get:

Hello, com.tray.SimpleClass$Proxy$_$$_WeldClientProxy

As you can see, Injection uses proxy class and, hence, we lost annotation information.
What we can do is to apply @Inherited to our custom annotation:

@Target({TYPE})
@Retention(RUNTIME)
@Inherited
public @interface QualifyerA {
   
}

After forcing the inheritance we gat what we need (don’t forget to restore original JSF code)

Hello, Got it 
Posted in Uncategorized | Leave a comment

12c identity column in PL/SQL record type

Identity columns are involved in 12c and this is amazing feature as it simplifies many aspects of program design.
But everything has a drawback. The first one is related to GENERAL ALWAYS default option and is similar to the problem what virtual columns cause when we try to use %ROWTYPE – based approach to insert records into table.

SQL> create table t (x int,
2 y generated always as (x+1))
3 /

Table created.

SQL> declare
2 tr t%rowtype;
3 begin
4 tr.x := 1;
5 insert into t values tr;
6 end;
7 /
declare

ORA-54013: INSERT operation disallowed on virtual columns

if we specify GENERATED ALWAYS identity column we get the similar problem (at least of the similar nature):

SQL> create table t (x int generated always as identity,
2 y varchar2(10))
3 /

Table created.

SQL> declare
2 tr t%rowtype;
3 begin
4 tr.y := ‘m';
5 insert into t values tr;
6 end;
7 /
declare
*

ORA-32795: cannot insert into a generated always identity column

Identity columns have few more options – BY DEFAULT and BY DEFAULT ON NULL.
Let’s look on BY DEFAULT:

SQL> create table t (x int generated by default as identity,
2 y varchar2(10))
3 /

Table created.

SQL> declare
2 tr t%rowtype;
3 begin
4 tr.y := ‘m';
5 insert into t values tr;
6 end;
7 /
declare
*

ORA-01400: cannot insert NULL into (“SCOTT”.”T”.”X”)

OK, now we can insert into column, but can’t insert NULL. The last step:

SQL> create table t (x int generated by default on null as identity,
2 y varchar2(10))
3 /

Table created.

SQL> declare
2 tr t%rowtype;
3 begin
4 tr.y := ‘m';
5 insert into t values tr;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select * from t
2 /

X Y
———- ———-
1 m

OK, now we’ve got the desirable result. So if we use BY DEFAULT ON NULL option we can continue to use ROW-based insert syntax in PL/SQL.

Posted in Uncategorized | Leave a comment

12c partial indexes and list partitioning

Oracle 12c release introduces the brand new and impressive feature – partial indexes on partitioned tables. Richard Foote spent extremely useful articles to this aspect of 12c:

http://richardfoote.wordpress.com/2013/07/08/12c-partial-indexes-for-partitioned-tables-part-i-ignoreland/#comment-104322

http://richardfoote.wordpress.com/2013/07/12/12c-partial-indexes-for-partitioned-tables-part-ii-vanishing-act/

And one of the most attractive features is CBO ability to use index access where possible and partition full scan there index can not be used in one statement. But does this always work ?
Let’s make few experiments for LIST partitioned tables.

SQL> create table t (x int, y int)
  2  partition by list (y) (
  3  partition p_1 values(1)
  4  )
  5  /

SQL> begin
  2  for i in 2..8 loop
  3    execute immediate
  4   'alter table t add partition p_'||i||' values('||i||')';
  5  end loop;
  6  end;
  7  /

SQL> alter table t modify partition p_2 indexing off
  2  /

SQL> create sequence t_seq
  2  /

SQL> insert into t select t_seq.nextval, mod(rownum, 8)+1
  2  from dual connect by level  commit
  2  /

SQL> create index t_i on t(x) indexing partial
  2  /

SQL> begin
  2    dbms_stats.gather_table_stats(ownname => user,tabname => 'T',cascade => true);
  3  end;
  4  /

SQL> select * from t where x = 10
  2  /

         X          Y                                                           
---------- ----------                                                           
        10          3                                                           

SQL> select * from table(dbms_xplan.display_cursor)
  2  /

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  61c22yvcwkaut, child number 0                                           
-------------------------------------                                           
select * from t where x = 10                                                    
                                                                                
Plan hash value: 1177543047                                                     
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
| Id  | Operation                                    | Name    | Rows  | Bytes |
 Cost (%CPU)| Time     | Pstart| Pstop |                                        

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
|   0 | SELECT STATEMENT                             |         |       |       |
    71 (100)|          |       |       |                                        
                                                                                
|   1 |  VIEW                                        | VW_TE_2 |     2 |    52 |
    71   (2)| 00:00:01 |       |       |                                        
                                                                                
|   2 |   UNION-ALL                                  |         |       |       |

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
            |          |       |       |                                        
                                                                                
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T       |     1 |     8 |
     4   (0)| 00:00:01 | ROWID | ROWID |                                        
                                                                                
|*  4 |     INDEX RANGE SCAN                         | T_I     |     1 |       |
     3   (0)| 00:00:01 |       |       |                                        
                                                                                
|   5 |    PARTITION LIST SINGLE                     |         |     1 |     8 |
    67   (2)| 00:00:01 |     2 |     2 |                                        
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|*  6 |     TABLE ACCESS FULL                        | T       |     1 |     8 |
    67   (2)| 00:00:01 |     2 |     2 |                                        
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   3 - filter(("T"."Y"=1 OR "T"."Y"=3 OR "T"."Y"=4 OR "T"."Y"=5 OR "T"."Y"=6 OR 

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
"T"."Y"=7 OR "T"."Y"=8))                                                        
                                                                                
   4 - access("X"=10)                                                           
   6 - filter("X"=10)                                                           
                                                                                

As expected CBO uses the combination of the index access and partition full scan. But we should take into special account the FILTER clause in EXPLAIN PLAN – it consists of 7 comparison predicates with OR between them.
Now let’s increase the number of partitions but still leave just one INDEXING OFF. Get the result for 1001 partitions (I omit most of statements – they are similar with above scenario):

SQL> create table t (x int, y int)
  2  partition by list (y) (
  3  partition p_1 values(1)
  4  )
  5  /

SQL> begin
  2  for i in 2..1001 loop
  3    execute immediate
  4   'alter table t add partition p_'||i||' values('||i||')';
  5  end loop;
  6  end;
  7  /

SQL> alter table t modify partition p_2 indexing off
  2  /
...

SQL> select * from t where x = 10
  2  /

         X          Y                                                           
---------- ----------                                                           
        10         11                                                           

SQL> 
SQL> select * from table(dbms_xplan.display_cursor)
  2  /

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  61c22yvcwkaut, child number 0                                           
-------------------------------------                                           
select * from t where x = 10                                                    
                                                                                
Plan hash value: 1177543047                                                     
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
| Id  | Operation                                    | Name    | Rows  | Bytes |
 Cost (%CPU)| Time     | Pstart| Pstop |                                        

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
|   0 | SELECT STATEMENT                             |         |       |       |
    18 (100)|          |       |       |                                        
                                                                                
|   1 |  VIEW                                        | VW_TE_2 |     2 |    52 |
    18   (0)| 00:00:01 |       |       |                                        
                                                                                
|   2 |   UNION-ALL                                  |         |       |       |

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
            |          |       |       |                                        
                                                                                
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T       |     1 |     9 |
     4   (0)| 00:00:01 | ROWID | ROWID |                                        
                                                                                
|*  4 |     INDEX RANGE SCAN                         | T_I     |     1 |       |
     3   (0)| 00:00:01 |       |       |                                        
                                                                                
|   5 |    PARTITION LIST SINGLE                     |         |     1 |     8 |
    14   (0)| 00:00:01 |     2 |     2 |                                        
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|*  6 |     TABLE ACCESS FULL                        | T       |     1 |     8 |
    14   (0)| 00:00:01 |     2 |     2 |                                        
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   3 - filter(("T"."Y"=1 OR "T"."Y"=3 OR "T"."Y"=4 OR "T"."Y"=5 OR "T"."Y"=6 OR 

Easy to see the optimization is still actual. But let’s add one more partition.

SQL> create table t (x int, y int)
  2  partition by list (y) (
  3  partition p_1 values(1)
  4  )
  5  /

SQL> begin
  2  for i in 2..1002 loop
  3    execute immediate
  4   'alter table t add partition p_'||i||' values('||i||')';
  5  end loop;
  6  end;
  7  /
...
SQL> select * from t where x = 10
  2  /
select * from t where x = 10
                           *
error in row 1:
ORA-01795: maximum expression number in list - 1000 

Oooops… We’ve got the error !
This has the natural explanation – we have 1002 partitions total and just one is INDEXING OFF.
Oracle tries to execute the in-list filter with 1001 element what causes the error.
Another example shows what we can get full scan just adding DEFAULT partition to the list:

SQL> create table t (x int, y int)
  2  partition by list (y) (
  3  partition p_1 values(1)
  4  )
  5  /

SQL> begin
  2  for i in 2..8 loop
  3    execute immediate
  4   'alter table t add partition p_'||i||' values('||i||')';
  5  end loop;
  6  end;
  7  /

SQL> alter table t add partition p_default values(default)
  2  /

SQL> alter table t modify partition p_2 indexing off
  2  /

SQL> create sequence t_seq
  2  /

SQL> insert into t select t_seq.nextval, mod(rownum, 8)+1
  2  from dual connect by level  commit
  2  /

SQL> create index t_i on t(x) indexing partial
  2  /
 
SQL> begin
  2    dbms_stats.gather_table_stats(ownname => user,tabname => 'T',cascade => true);
  3  end;
  4  /

SQL> select * from t where x = 10
  2  /

         X          Y                                                           
---------- ----------                                                           
        10          3                                                           

SQL> 
SQL> select * from table(dbms_xplan.display_cursor)
  2  /

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  61c22yvcwkaut, child number 0                                           
-------------------------------------                                           
select * from t where x = 10                                                    
                                                                                
Plan hash value: 3845649146                                                     
                                                                                
--------------------------------------------------------------------------------
-----------                                                                     
                                                                                
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Psta
rt| Pstop |                                                                     

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
--------------------------------------------------------------------------------
-----------                                                                     
                                                                                
|   0 | SELECT STATEMENT   |      |       |       |   522 (100)|          |     
  |       |                                                                     
                                                                                
|   1 |  PARTITION LIST ALL|      |     1 |     8 |   522   (1)| 00:00:01 |     
1 |     9 |                                                                     
                                                                                
|*  2 |   TABLE ACCESS FULL| T    |     1 |     8 |   522   (1)| 00:00:01 |     

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
1 |     9 |                                                                     
                                                                                
--------------------------------------------------------------------------------
-----------                                                                     
                                                                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   2 - filter("X"=10)                                                           

Seems Oracle can not compose clear in-list filter due to undefined DEFAULT value. But we can
get the situation back if we mark DEFAULT partition as non-indexed:

SQL> create table t (x int, y int)
  2  partition by list (y) (
  3  partition p_1 values(1)
  4  )
  5  /

SQL> begin
  2  for i in 2..8 loop
  3    execute immediate
  4   'alter table t add partition p_'||i||' values('||i||')';
  5  end loop;
  6  end;
  7  /

SQL> alter table t add partition p_default values(default)
  2  /

SQL> alter table t modify partition p_2 indexing off
  2  /

SQL> alter table t modify partition p_default indexing off
  2  /

SQL> create sequence t_seq
  2  /

SQL> insert into t select t_seq.nextval, mod(rownum, 8)+1
  2  from dual connect by level  commit
  2  /

SQL> create index t_i on t(x) indexing partial
  2  /

SQL> begin
  2    dbms_stats.gather_table_stats(ownname => user,tabname => 'T',cascade => true);
  3  end;
  4  /

SQL> select * from t where x = 10
  2  /

         X          Y                                                           
---------- ----------                                                           
        10          3                                                           

SQL> 
SQL> select * from table(dbms_xplan.display_cursor)
  2  /

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  61c22yvcwkaut, child number 0                                           
-------------------------------------                                           
select * from t where x = 10                                                    
                                                                                
Plan hash value: 1776253938                                                     
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
| Id  | Operation                                    | Name    | Rows  | Bytes |
 Cost (%CPU)| Time     | Pstart| Pstop |                                        

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
|   0 | SELECT STATEMENT                             |         |       |       |
    71 (100)|          |       |       |                                        
                                                                                
|   1 |  VIEW                                        | VW_TE_2 |     2 |    52 |
    71   (2)| 00:00:01 |       |       |                                        
                                                                                
|   2 |   UNION-ALL                                  |         |       |       |

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
            |          |       |       |                                        
                                                                                
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T       |     1 |     8 |
     4   (0)| 00:00:01 | ROWID | ROWID |                                        
                                                                                
|*  4 |     INDEX RANGE SCAN                         | T_I     |     1 |       |
     3   (0)| 00:00:01 |       |       |                                        
                                                                                
|   5 |    PARTITION LIST OR                         |         |     1 |     8 |
    67   (2)| 00:00:01 |KEY(OR)|KEY(OR)|                                        
                                                                                

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|*  6 |     TABLE ACCESS FULL                        | T       |     1 |     8 |
    67   (2)| 00:00:01 |KEY(OR)|KEY(OR)|                                        
                                                                                
--------------------------------------------------------------------------------
----------------------------------------                                        
                                                                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   3 - filter(("T"."Y"=1 OR "T"."Y"=3 OR "T"."Y"=4 OR "T"."Y"=5 OR "T"."Y"=6 OR 

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
"T"."Y"=7 OR "T"."Y"=8))                                                        
                                                                                
   4 - access("X"=10)                                                           
   6 - filter(("X"=10 AND (("T"."Y"1 AND "T"."Y"3 AND "T"."Y"4 AND "T"."Y"
5 AND "T"."Y"6 AND                                                          
                                                                                
              "T"."Y"7 AND "T"."Y"8) OR "T"."Y" IS NULL) AND ((TBL$OR$IDX$PA
RT$NUM(,0,0,65535,ROWID)>=2 AND                                              
                                                                                
              TBL$OR$IDX$PART$NUM(,0,0,65535,ROWID)<=2) OR (TBL$OR$IDX$PART$N
UM(,0,0,65535,ROWID)>=9 AND                                                  

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
                                                                                
              TBL$OR$IDX$PART$NUM(,0,0,65535,ROWID)<=9))))                   
Posted in Uncategorized | 3 Comments

Partition pruning in INDEX access to partitioned table

Few days ago I have been asked to look at the specific problem, related to combinations of several factors.
There is the big table “T” (about 100 mln rows and 400 Gb size) what is partitioned by list on column “Y”. There is the global index on column “T”(“X”) with acceptable selectivity factor. There is a staging table “T_S” what is used as a “golden source” of “X” column values. We need to get data from table “T” what have “X” values from “T_S” list and “Y” column equal to ‘C’. The case is that such data are just a tiny part of all rows corresponding to (“T”.”X” = “T_S”.”X”) condition. Let me illustrate the scenario:

SQL> CREATE TABLE t (x int, big_column char(200), y char(1))
  2  PARTITION BY LIST (y) (
  3   PARTITION P_A VALUES('A'),
  4   PARTITION P_B VALUES('B'),
  5   PARTITION P_C VALUES('C'),
  6   PARTITION P_D VALUES('D'),
  7   PARTITION P_E VALUES('E')
  8  )
  9  /

Table created.

SQL> 
SQL> INSERT INTO t
  2  SELECT id,stub,pkey FROM
  3  (SELECT rownum id,
  4  'x' stub
  5  FROM dual
  6  CONNECT BY LEVEL <=100000
  7  ),
  8  (
  9  SELECT decode(rownum,1,'A',2,'B',3,'C',4,'D',5,'E') pkey
 10  FROM dual
 11  CONNECT BY LEVEL  
SQL> COMMIT
  2  /

Commit complete.

SQL> 
SQL> CREATE TABLE t_lead(x int)
  2  /

Table created.

SQL> 
SQL> INSERT INTO t_lead
  2  SELECT rownum*1000 FROM dual CONNECT BY LEVEL  
SQL> COMMIT
  2  /

Commit complete.

SQL> 
SQL> CREATE INDEX t_i ON t(x)
  2  /

Index created.

SQL> 
SQL> BEGIN
  2   dbms_stats.gather_table_stats(
  3  	 ownname=>user,
  4  	 tabname=>'T',
  5  	 cascade=>true
  6  	 );
  7   dbms_stats.gather_table_stats(
  8  	 ownname=>user,
  9  	 tabname=>'T_LEAD'
 10  	 );
 11  END;
 12  /

PL/SQL procedure successfully completed.

Now let’s look at the query and it’s plan:

SQL> SET AUTOTRACE TRACEONLY
SQL> SET PAGESIZE 2000
SQL> SET LINESIZE 120
SQL> 
SQL> 
SQL> SELECT t.x FROM t_lead, t
  2  WHERE t_lead.x = t.x
  3  AND t.big_column = 'x'
  4  AND t.y = 'C'
  5  /

50 rows selected.

Execution Plan
----------------------------------------------------------                                                              
Plan hash value: 3015557880                                                                                             

--------------------------------------------------------------------------------------------------------------          
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |          
--------------------------------------------------------------------------------------------------------------          
|   0 | SELECT STATEMENT                    |        |    50 | 10600 |   353   (0)| 00:00:05 |       |       |          
|   1 |  NESTED LOOPS                       |        |       |       |            |          |       |       |          
|   2 |   NESTED LOOPS                      |        |    50 | 10600 |   353   (0)| 00:00:05 |       |       |          
|   3 |    TABLE ACCESS FULL                | T_LEAD |    50 |   200 |     3   (0)| 00:00:01 |       |       |          
|*  4 |    INDEX RANGE SCAN                 | T_I    |     5 |       |     2   (0)| 00:00:01 |       |       |          
|*  5 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T      |     1 |   208 |     7   (0)| 00:00:01 |     3 |     3 |          
--------------------------------------------------------------------------------------------------------------          

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

   4 - access("T_LEAD"."X"="T"."X")                                                                                     
   5 - filter("T"."BIG_COLUMN"='x' AND "T"."Y"='C')                                                                     

Statistics
----------------------------------------------------------                                                              
          0  recursive calls                                                                                            
          0  db block gets                                                                                              
        170  consistent gets                                                                                            
          0  physical reads                                                                                             
          0  redo size                                                                                                  
       1415  bytes sent via SQL*Net to client                                                                           
        557  bytes received via SQL*Net from client                                                                     
          5  SQL*Net roundtrips to/from client                                                                          
          0  sorts (memory)                                                                                             
          0  sorts (disk)                                                                                               
         50  rows processed

OK, we should notice Pstart and Pstop columns in TABLE ACCESS BY GLOBAL INDEX ROWID (5th row in plan) have value 3 what (in accordance to Oracle documentation) means partition pruning. E.g. Oracle accesses just one – required – partition and does not try to reach other ones when reaches table blocks.
We may reach the same using PARTITION() clause in FROM part of the query:

SQL> SELECT t.* FROM t_lead, t partition(p_c) t
  2  WHERE t_lead.x = t.x
  3  AND t.big_column = 'x'
  4  /

50 rows selected.

Execution Plan
----------------------------------------------------------                                                              
Plan hash value: 3015557880                                                                                             

--------------------------------------------------------------------------------------------------------------          
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |          
--------------------------------------------------------------------------------------------------------------          
|   0 | SELECT STATEMENT                    |        |    50 | 10600 |   353   (0)| 00:00:05 |       |       |          
|   1 |  NESTED LOOPS                       |        |       |       |            |          |       |       |          
|   2 |   NESTED LOOPS                      |        |    50 | 10600 |   353   (0)| 00:00:05 |       |       |          
|   3 |    TABLE ACCESS FULL                | T_LEAD |    50 |   200 |     3   (0)| 00:00:01 |       |       |          
|*  4 |    INDEX RANGE SCAN                 | T_I    |     5 |       |     2   (0)| 00:00:01 |       |       |          
|*  5 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T      |     1 |   208 |     7   (0)| 00:00:01 |     3 |     3 |          
--------------------------------------------------------------------------------------------------------------          

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

   4 - access("T_LEAD"."X"="T"."X")                                                                                     
       filter(TBL$OR$IDX$PART$NUM("T",0,1,0,ROWID)=3)                                                                   
   5 - filter("T"."BIG_COLUMN"='x')                                                                                     

Statistics
----------------------------------------------------------                                                              
          0  recursive calls                                                                                            
          0  db block gets                                                                                              
        170  consistent gets                                                                                            
          0  physical reads                                                                                             
          0  redo size                                                                                                  
       2083  bytes sent via SQL*Net to client                                                                           
        557  bytes received via SQL*Net from client                                                                     
          5  SQL*Net roundtrips to/from client                                                                          
          0  sorts (memory)                                                                                             
          0  sorts (disk)                                                                                               
         50  rows processed

We can notice the difference between two plans despite they look almost alike and costs/rows estimations are the same. The second plan involves additional filtering: TBL$OR$IDX$PART$NUM(“T”,0,1,0,ROWID)=3 and this step seems being executed at the level of the index scan. E.g. Oracle gets rid of ROWIDs what don’t fit to particular partition.
We may easily notice this in the tracefile (I executed 10046 event level 12 for both ones):

PARSING IN CURSOR #2 len=83 dep=0 uid=84 oct=3 lid=84 tim=199354375853 hv=1590834553 ad='7ffb3dfa4a8' sqlid='984xy4jgd4dbt'
SELECT t.x FROM t_lead, t
WHERE t_lead.x = t.x
AND t.big_column = 'x'
AND t.y = 'C'
END OF STMT
PARSE #2:c=0,e=125,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3015557880,tim=199354375852
EXEC #2:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3015557880,tim=199354376052
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354376132
WAIT #2: nam='Disk file operations I/O' ela= 863 FileOperation=2 fileno=4 filetype=2 obj#=-1 tim=199354377098
FETCH #2:c=0,e=1140,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,plh=3015557880,tim=199354377321
WAIT #2: nam='SQL*Net message from client' ela= 163 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354377555
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354377720
FETCH #2:c=0,e=513,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354378141
WAIT #2: nam='SQL*Net message from client' ela= 5458 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354383664
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354383822
FETCH #2:c=0,e=412,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354384178
WAIT #2: nam='SQL*Net message from client' ela= 4449 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354388682
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354388782
FETCH #2:c=0,e=435,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354389164
WAIT #2: nam='SQL*Net message from client' ela= 4593 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354393810
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354393958
FETCH #2:c=0,e=208,p=0,cr=19,cu=0,mis=0,r=4,dep=0,og=1,plh=3015557880,tim=199354394109
STAT #2 id=1 cnt=50 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=170 pr=0 pw=0 time=0 us)'
STAT #2 id=2 cnt=250 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=120 pr=0 pw=0 time=498 us cost=353 size=10600 card=50)'
STAT #2 id=3 cnt=50 pid=2 pos=1 obj=94055 op='TABLE ACCESS FULL T_LEAD (cr=11 pr=0 pw=0 time=441 us cost=3 size=200 card=50)'
STAT #2 id=4 cnt=250 pid=2 pos=2 obj=94056 op='INDEX RANGE SCAN T_I (cr=109 pr=0 pw=0 time=90 us cost=2 size=0 card=5)'
STAT #2 id=5 cnt=50 pid=1 pos=2 obj=94049 op='TABLE ACCESS BY GLOBAL INDEX ROWID T PARTITION: 3 3 (cr=50 pr=0 pw=0 time=0 us cost=7 size=208 card=1)'
WAIT #2: nam='SQL*Net message from client' ela= 6502 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354400849
CLOSE #2:c=0,e=17,dep=0,type=0,tim=199354400917
=====================
PARSING IN CURSOR #3 len=86 dep=0 uid=84 oct=3 lid=84 tim=199354401047 hv=1072898565 ad='7ffb3dd24f0' sqlid='bd34s5szz68h5'
SELECT t.* FROM t_lead, t partition(p_c) t
WHERE t_lead.x = t.x
AND t.big_column = 'x'
END OF STMT
PARSE #3:c=0,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3015557880,tim=199354401047
EXEC #3:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3015557880,tim=199354401292
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354401372
FETCH #3:c=0,e=95,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,plh=3015557880,tim=199354401512
WAIT #3: nam='SQL*Net message from client' ela= 182 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354401755
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354401855
FETCH #3:c=0,e=377,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354402176
WAIT #3: nam='SQL*Net message from client' ela= 72296 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354474524
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354474808
FETCH #3:c=0,e=481,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354475173
WAIT #3: nam='SQL*Net message from client' ela= 22029 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354497275
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354497514
FETCH #3:c=0,e=482,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354497892
WAIT #3: nam='SQL*Net message from client' ela= 21923 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354519880
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354520063
FETCH #3:c=0,e=230,p=0,cr=19,cu=0,mis=0,r=4,dep=0,og=1,plh=3015557880,tim=199354520220
STAT #3 id=1 cnt=50 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=170 pr=0 pw=0 time=0 us)'
STAT #3 id=2 cnt=50 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=120 pr=0 pw=0 time=1911 us cost=353 size=10600 card=50)'
STAT #3 id=3 cnt=50 pid=2 pos=1 obj=94055 op='TABLE ACCESS FULL T_LEAD (cr=11 pr=0 pw=0 time=245 us cost=3 size=200 card=50)'
STAT #3 id=4 cnt=50 pid=2 pos=2 obj=94056 op='INDEX RANGE SCAN T_I (cr=109 pr=0 pw=0 time=0 us cost=2 size=0 card=5)'
STAT #3 id=5 cnt=50 pid=1 pos=2 obj=94049 op='TABLE ACCESS BY GLOBAL INDEX ROWID T PARTITION: 3 3 (cr=50 pr=0 pw=0 time=0 us cost=7 size=208 card=1)'
WAIT #3: nam='SQL*Net message from client' ela= 10514 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354530980
CLOSE #3:c=0,e=20,dep=0,type=0,tim=199354531107

Bold font stresses what in the first operator index scan operation reports 250 rows while in the second one – just 50.

Posted in Uncategorized | Leave a comment

Tom Kyte in Moscow

11th of Dec Tom Kyte presented his session in Moscow. Brilliant as always! Materials are published there: .

P.S. I won 2th edition of “Oracle for Experts” book, This is the second time I win and I have a chance t compare 2 different editions :)

Posted in Uncategorized | Leave a comment