Jonathan Lalou's Blog

Posts Tagged ‘Oracle’

On last Friday I attented a presentation about Oracle (10g) Partitioning. Here is a summary of interesting points:

Why?

On some projects, data bases reach tens of TB.  For so huge volume of data, developpers often are used to archiving or historizing datas. Partitioning the base is another way to face mass data.

What?

Partitioning is allowing a table or an index to be divided into smaller pieces, called “partitions”. Please notice that, from a logical point of view, we keep one object (table or index) ; yet, physically, we have many physical partitions.

When?

• DBAs advise to consider partitioning prior to create the tables. Indeed, partitioning when the table reaches a critical size is more expensive than sooner.
• Tables may be partionioned if they are or are assumed to be 2GB or more.

Partition key/ pruning

• From a certain viewpoint, a partition key may be seen as a “super index”
• Rows of the base are assigned to a unique partition.
• The partitioning key contains one column (or more) which determine the partition where any row is to be stored.
• During insert/update/delete operations, the right partition is automatically found owing to the partition key.
• Warning: any table with columns of type LONG or LONG_RAW cannot be partitioned.

Purpose

• Improvement of availability and performance of the base. At first glance, on select operations the benefit is small, but on insert operations the benefit is felt.
• Reduction of administrative burden and input/ouput access
• Anticipation of high volumes of data and cost of development

TYPES

Three types of partitions are available:

• range: eg by month, year, etc.
• list: eg by country, currency, etc. This is the case when the column values are among an enumeration
• hash

Composite keys are allowed, yet they are not advised to be used.

INDEX

Indexes can be partitioned, too. All combinations are allowed: partioned indexes for partioned tables, partioned indexes for global tables, etc.

Syntax

[sql]CREATE TABLE range_example
(
RANGE_KEY_COLUMN   DATE,
MY_DATA            VARCHAR2 (20)
)
PARTITION BY RANGE (range_key_column)
(
PARTITION part_1 VALUES LESS THAN (TO_DATE (’01/01/2005′, ‘dd/mm/yyyy’)),
PARTITION part_2 VALUES LESS THAN (TO_DATE (’01/01/2006′, ‘dd/mm/yyyy’)))[/sql]

Oracle 11g

Oracle 11g will bring a lot of improvements on partitioning: extended composite partitioning, reference partitioning, partitioning on virtual columns, etc.

Case

• let’s consider an EJB Bean AnimalBean, linked to a table JL_Animal
• let’s have a human understandable name for the Pojo, let’s say: Animal
• let an EJB entity have following finders:

[java]
* @ejb.finder
* signature = "Collection findByBirthDate(java.lang.Integer birthDate)"
* query = "SELECT OBJECT(o) FROM AnimalBean AS o WHERE o.birthDate = ?1"
*
* @ejb.finder
* signature = "Collection findByCountryAndBirthDate(java.lang.String from,java.lang.Integer birthDate)"
* query = "SELECT OBJECT(o) FROM AnimalBean AS o WHERE o.country = ?1 AND o.birthDate = ?2"[/java]

Read the rest of this entry »

Case:

I need copy the content of all tables from one Oracle schema to another one, specifically: I must copy raw data from a production DB to a developper one.

Context:

• The DBA do not allow DBLinks
• Oracle source and destination schemas share the same structure (name of the tables, names and types of columns, etc.

Fix:

Here is a small script I wrote, of course it can be improved:
[bash]
#!/usr/bin/bash
SRC_LOGIN=XYZ
SRC_PASSWORD=XYZ
SRC_DB=production
DBDEST_LOGIN=XYZ
DEST_PASSWORD=XYZ
DEST_DB=developmentDB

echo "select object_name from user_objects where object_type = ‘TABLE’ order by object_name;" | \
sqlplus $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB | \
grep -v OBJECT_NAME | grep -v "-" | grep -v "^$" | \
sed "1,9d" | tac | sed "1,3d" | tac | \
sort | uniq > allTables.txt

for table in `more allTables.txt`
do
rm -rf $table.sql
echo "exporting $table"
exp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables="$table" direct=y
echo "importing $table"
echo "truncate table $table;" | \
sqlplus $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB
imp $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB file=$table.sql \
buffer=10485867 tables="$table" \
fromuser=$SRC_LOGIN touser=$DEST_LOGIN ignore=y
rm -rf $table.sql
done

rm -rf allTables.txt[/bash]

A little more explanations:

• echo "select object_name from user_objects where object_type = 'TABLE';" | sqlplus $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB: this allow to retrieve the names of all the tables from the DB
• grep -v OBJECT_NAME | grep -v "-" | grep -v "^$" : this removes empty lines and useless trace from Oracle SQLPlus
• sed "1,9d" | tac | sed "1,3d" | tac: removes the 9 first lines and the 3 last ones. tac in Unix allow to reverse the content of a file
• sort | uniq : sort lines and remove duplicates
• exp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables="$table" direct=y: exports the content of the table $table
• echo "truncate table $table;" | sqlplus $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB: truncates destination table. This is useful in case the script must be played more than once, for instance.
• imp $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB file=$table.sql buffer=10485867 tables="$table" fromuser=$SRC_LOGIN touser=$DEST_LOGIN ignore=y: import the content of the table

Some improvement ideas:

• handle sequences, triggers, stored procedures, etc.
• create the complete destination DB owing to source DB, ie do not assume anymore that the DBs share the same structure

Very interesting case. At firt, I spotted these errors:

SQL Error: -22, SQLState: S0002

and:

java.sql.SQLException: Table not found in statement

But here is the complete stacktrace:

"2009-10-13 12:29:17,934 ERROR hbm2ddl.SchemaExport - Unsuccessful: create table MY_ORACLE_TABLE (myOracleTableId integer not null, optimisticTimestamp timestamp not null, position double, primary key (myOracleTableId))
2009-10-13 12:29:17,934 ERROR hbm2ddl.SchemaExport - Unexpected token: POSITION in statement [create table MY_ORACLE_TABLE (myOracleTableId integer not null, optimisticTimestamp timestamp not null, position]
(...)
2009-10-13 12:29:17,949 ERROR hbm2ddl.SchemaExport - Table not found: MY_ORACLE_TABLE in statement [alter table MY_ORACLE_TABLE]
2009-10-13 12:29:17,980 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest-testFindmyOracleTableByPK_OK.xml
2009-10-13 12:29:17,996 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest.xml
2009-10-13 12:29:17,996 DEBUG util.HibernatePersistenceTestCase - --------------- Using dataset : HibernateMyOracleTableDAOUnitTest.xml
2009-10-13 12:29:18,355 WARN util.JDBCExceptionReporter - SQL Error: -22, SQLState: S0002
2009-10-13 12:29:18,355 ERROR util.JDBCExceptionReporter - Table not found in statement [select (...) where myOracleTable0_.myOracleTableId=?]
2009-10-13 12:29:18,371 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest-testFindmyOracleTableByPK_OK.xml
2009-10-13 12:29:18,371 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest.xml
2009-10-13 12:29:18,387 DEBUG util.HibernatePersistenceTestCase - --------------- Using dataset : HibernateMyOracleTableDAOUnitTest.xml

org.hibernate.exception.SQLGrammarException: could not load an entity: [com.(...)myOracleTable#147]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1799)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:47)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:41)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:2730)
at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:365)
at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:346)
at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:123)
at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:161)
at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:87)
at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:862)
at org.hibernate.impl.SessionImpl.load(SessionImpl.java:781)
at org.hibernate.impl.SessionImpl.load(SessionImpl.java:774)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
at $Proxy0.load(Unknown Source)
(...)HibernateMyOracleTableDAOUnitTest.java:37)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:40)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
Caused by: java.sql.SQLException: Table not found in statement [select (...) where myOracleTable0.myOracleTableId=?]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:442)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:368)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:105)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1561)
at org.hibernate.loader.Loader.doQuery(Loader.java:661)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1785)
... 42 more"

I have helped you with red bold words… Can you see the issue?

Yes, indeed position is a reserved keyword in SQL! To fix this issue, you have no choice but to rename your column.

Case: you need the list of all tables in an Oracle DB.

Solution: run this query:
[sql]select * from user_objects where object_type = ‘TABLE’;[/sql]
You can use a similar way to filter on table names, or get other types object such us triggers, procedures and indexes.

Abstract: we need the max of two dates

Read the rest of this entry »