Archive for April, 2010

Oracle Text Start

23 April 2010

So my intention was to write a lot about Oracle Text in my blog. I use it now for over 2 years in application, and every time I find new options and new opportunities in it. I think Oracle Text is an underestimated feature/option or even maybe a technology of the Oracle Database. The advantage is also, it exists in all edition of the database, from XE to Enterprise Edition. And it is included in the license. So why don’t you start to use it.

Every user created in the database can use an Oracle Text index. There are 4 types of indexes in Oracle Text:

  1. Context – used mostly for large (text) documents, it is the most powerful index
  2. Ctxcat – used for mixed queries, so small documents with also other columns of a table. It has not al the options for Context indexes
  3. Ctxrule – only used for classification or routing
  4. Ctxxpath – only used to index ExistsNode() queries on XMLTYPE columns. For 11g and higher it is recommended to look at the new index XMLINDEX (not an Oracle Text Index). Ctxxpath will be deprecated.

An Oracle Text index will almost identically created as a normal index, you use CREATE INDEX. The difference is you need to make it aware of the Oracle Text index. For a context index the simplest syntax is:

create index textindex on testtable
indextype is ctxsys.context;

So you see you need to include the last line to get Oracle aware of the use of an text index. This index will use the default settings for a text index. These defaults can be seen in the view CTXSYS.CTX_PARAMETERS. It is possible to set new defaults for all these values, these will be used for the next new to create indexes. For this change you need extra privileges: you need the CTXAPP role and additional grants to execute packages owned by CTXSYS, but I will later explain.

How can you use such a index? You can now use index in SQL, but it has it own syntax. The syntax for a context index includes the word CONTAINS, a ctxcat index includes CATSEARCH and a ctxrule index includes MATCHES. So an example for a context index is:

select * from testtable
where contains(testcolumn,’test’) > 0;

So we have now a start, later more about Text Indexes and how they can be used.

Oracle text sources

17 April 2010

The page about Oracle Text on Technet is nowadays hard to find. It is not found on the products page, it is not listed as an option in the database, but fortunately it is there, see Is has pdf’s about the features of Oracle 11g, some white papers and a link to the sample codes. So it is still a good source to start with Oracle Text.

The other source for starting with Oracle text is on Here you can find all the manuals for the products of Oracle based on versions. There are 2 manuals for Oracle Text in 11g:

Both give you an idea of the possibilities of Oracle Text.

Oracle 11g and Documentum (part2)

17 April 2010

By not setting the given parameters for Documentum according to their documentation, we need to be sure the statistics in Oracle should be up-to-date. Beside the normal stats on the tables and indexes, the standard night batch is ok, we need also to be sure that the system statistics are ok. This comes in two ways, with workload and no-workload. For the start no-workload is ok, later as in production, workload can be used on a busy system. So I started with the next statement, performed by SYS:


After that some queries which went wrong, went better. So later I will perform the workload statement if we go to acceptance and production. Now back for searching the needed indexes, but I will be sure, our testers will also complain about performance, so it will not hard to find the missing ones.

Oracle 11g and Documentum

16 April 2010

In my current project my task is to migrate data from Oracle to a new Documentum environment. Beside they also asked me to keep baby sitting on the Oracle database lying under the Content Server of Documentum. The install was done by following the installation guide from Documentum. That is not that much for Oracle, just install and set some parameters. After that run some scripts and Oracle is ready to receive Documentum.

I looked at the database and saw those parameters:

  1. optimizer_index_cost_adj was 5
  2. optimizer_index_caching was 95
  3. optimizer_mode was choose

After some investigation I found out that these setting are common for a Documentum install in Oracle. It has been the best practice for more than 10 years and Documentum works this way. I made the next adjustments to the database, because the above settings come from before oracle 10g, which will be the standards for the next installation for Oracle for Documentum.

  1. optimizer_index_cost_adj back to 100
  2. optimizer_index_caching back to 0
  3. optimizer_mode to ALL_ROWS

Furthermore I set CURSOR_SHARING to FORCE. There is no way in the translation from DQL (The SQL language of Documentum) to SQL in which Documentum uses Bind Variables. So they made the variables hard-coded. So I made the decision to set CURSOR_SHARING. I tested also Similar, but then some queries went mad, mostly inserts and updates. So the safest setting is FORCE. Similar will also be desupported by Oracle in 11.2.

By default installation Documentum makes 2 tablespaces, one for data and one for indexes. But the default gives these a max size of 2GB. So after the first tests in the test environment, the tablespace of data became full. This is mostly due to the heavy auditing. This auditing happens in 1 table, DM_AUDITTRAIL_S. I made some adjustments by making another tablespace and put the table and indexes into it. This way the table can be easily monitored, by monitoring the tablespace.

The last thing what I now need to do, is monitoring the database for peaks. This is because Documentum can make new objects (which will lead to new tables), but will not make the indexes. So it is to the developer or the DBA to find these. They put me on this task. I keep monitoring and use the OEM for this. A look at the performance tab for a peak and also some jobs to find simple FULL TABLE scans. So some work to do.

The blog has arrived.

15 April 2010

Finally I decided also to start a blog.

During my daily work mostly as a project DBA, I come to some astonishing and very interesting issues in the world of the Oracle database. Sometimes I manage to solve the issues myself, sometimes I have to use the web.

The last couple of months I got more interested about the option of Oracle Text. It is a fascinating area, and I think it is getting more attention. Also I got fascinated by Data Mining, on the moment I don’t know much about it, but I will try to learn. So these topics I will highlight in this blog, but other topics in the database field also.

So let’s start….