Ilmar Kerm DB blog

Clustering MySQL instances with Oracle Clusterware 11gR2

noreply@blogger.com (Ilmar Kerm) — Wed, 09 Nov 2011 14:14:00 +0000

I've been doing lately quite many database clustering implementations; Oracle RAC and since we have many MySQL instances in production, had to find a good way to make MySQL instances highly available also.

One good solution for this is managing MySQL instances with clusterware and since we are planning to use Oracle RAC on Oracle Enterprise Linux anyway, then Oracle Clusterware is an excellent candidate for this task. Also... Oracle Clusterware is included with Oracle Enterprise Linux at no additional charge.

Requirements I had:

Multiple MySQL instances running in the same cluster, in case of node failure affected MySQL instances are moved to any other surviving node (least loaded)
Different MySQL instances may run different MySQL RDBMS software versions
Each MySQL instance is listening to its own dedicated and clusterware managed IP address (so MySQL can always respond on default 3306 port and can move independently around nodes without any dependencies to other MySQL instances)
Clusterware monitors each MySQL instance and in case of trouble automatically moves IP and starts instance on another cluster node

For this task I wrote some Bash scripts to help managing MySQL instances in Oracle Clusterware environment. All these scripts are available here: Google Code project page, also with documentation of the whole setup and how to use scripts: Documentation

All comments welcome!

Script to import a table to another schema in PostgreSQL

noreply@blogger.com (Ilmar Kerm) — Wed, 09 Feb 2011 18:10:00 +0000

Tested with Centos 5 and PostgreSQL 8.4.

Sadly PostgreSQL does not have remap_schema parameter in its data import tool pg_restore, so importing tables to another schema in destination database is a little bit tricky.

Here is a Bash script I use to connect to the source database over SSH tunnel, export two tables (srv.stat_export and disp.trip) and then import them to the destination database under schema etl.

Download the script: import_table_to_another_schema.sh

#!/bin/bash -l

# Open SSH tunnel to remote database
# Make sure, that passwordless authentication is enabled
ssh -C -N -L 15432:livedb2:5432 username@live.site.com &
sleep 10s

# Set source PostgreSQL connection parameters
# Make sure that PostgreSQL tools (psql, pg_dump) can connect with these parameters without password
# if necessary use ~/.pgpass file
export PGDATABASE=db_live
export PGHOST=localhost
export PGPORT=15432
export PGUSER=exportuser

# Generate temporary filename
SCHEMAFILE=`mktemp`

# Dump the data
pg_dump -t srv.stat_export -t disp.trip --file=$SCHEMAFILE -O --no-acl
RESULT=$?

# Kill SSH tunnel
kill %+

# If export was successfull, then start importing
if [ $RESULT -eq 0 ]; then
  # Replace source schema name with destination schema name
  sed -i '/^SET search_path = /s/disp,\|srv,/etl,/g' $SCHEMAFILE

  # Set destination PostgreSQL connection parameters
  # Make sure that PostgreSQL tools (psql, pg_dump) can connect with these parameters without password
  export PGDATABASE=db_dev
  export PGHOST=localhost
  export PGPORT=5432
  export PGUSER=postgres

  # Drop old tables
  echo 'drop table etl.stat_export; drop table etl.trip; ' | psql -q
  # Load new tables
  psql -q < $SCHEMAFILE
  # Fix access privileges
  echo 'alter table etl.stat_export owner to etl; alter table etl.trip owner to etl;' | psql -q
fi

# Remove temporary file
rm $SCHEMAFILE

Geolocation API

noreply@blogger.com (Ilmar Kerm) — Wed, 20 Oct 2010 13:19:00 +0000

There is an interesting W3C Draft, that enables websites to just simply ask web browser to report the users geographical location, and then the web browser will try the best available location method, like GeoIP, WIFI location or GPS. I have currently tested it on Firefox 3.6 and Google Chrome; Internet Explorer 8.0 does not support it yet.

W3C Geolocation API Draft
Mozilla documentation for Geolocation

How to use it in APEX?

If you are just interested in recording the users location, then using an on-demand application process should be the easiest solution:

First, create two application items: USER_LOC_LATITUDE and USER_LOC_LONGITUDE. They are used for storing users location.

Then, create an On Demand application process SAVE_USER_LOCATION. Create your necessary application logic in that process to handle the user location. The user location is available through application items USER_LOC_LATITUDE and USER_LOC_LONGITUDE.

And finally, include the following HTML code to your page. This uses APEX AJAX JavaScript API to call the created application process as soon as the users location becomes available for the browser. Please note also, that the browser asks for users permission for reporting the location.

<script type="text/javascript">

  if(navigator.geolocation) {
    navigator.geolocation.getCurrentPosition(function(position) {

      var get = new htmldb_Get(null, $x('pFlowId').value, 
        'APPLICATION_PROCESS=SAVE_USER_LOCATION', 0);
      get.add('USER_LOC_LATITUDE', position.coords.latitude);
      get.add('USER_LOC_LONGITUDE', position.coords.longitude);
      gReturn = get.get();
      get = null;

    });
  }

</script>

To continuously monitor user position, use the function navigator.geolocation.watchPosition instead of navigator.geolocation.getCurrentPosition.

Resolving coordinates to location name

Here is one package, that uses GeoNames.org database for resolving the location name. The package requires Oracle 11.2.

The geolocation package
One helper package, HTTP_UTIL, for downloading XML over HTTP

Authenticate database user against Active Directory - the poor man's version

noreply@blogger.com (Ilmar Kerm) — Sat, 28 Aug 2010 15:47:00 +0000

My use case for this: one big database, where applications have some shemas. But also, quite a lot of developers need access to the database, to see the production data and security requires each user to have his/her own personal account and of course also requires password expiration and complexity. Authenticating database users against organizations central Active Directory would be just perfect for this case.

Oracle offers this solution for Enterprise Edition, but its an extra cost option - Oracle Advanced Security. If these are not an option for you, then its also possible to achieve the same task, in its simplest form, using user profile password verification function and database logon trigger.

Update: Thanks to Job Miller for pointing out that password authentication does not require licensing ASO option anymore, but requires licensing Oracle Internet Directory: Link to 10.2 Oracle® Database Licensing Information - 2 Options and Packs

Basically, the procedure works as follows:

Create a new profile for AD-authenticated users with password verification function.
Assign this new profile to a database user.
When the database user changes password, the password verification function will try to connect to AD with the user specified password, if AD authentication is successful, then the password is correct and can be changed and stored by Oracle.
Every time the user connects to the database, after logon trigger will query AD for the user status: does the user still exist, is the user disabled or expired, when did the user last change password. If the user status is changed in AD, the trigger will lock or expire the database user accordingly.

The code

My solution consists of a PL/SQL package, that I create under my database administration account, named DBAUSER. The package will be called from password verification function and the logon trigger. The DBAUSER account needs network ACLs to permit it to connect to the AD server (11g+!) and the following system privileges:

GRANT create job TO dbauser;
GRANT alter user TO dbauser;
GRANT select ON sys.user$ TO dbauser;

Now the package:
Package
Package Body
NB! Look into the package body script! There are configuration parameters on top, that you MUST look over and change!

Now, create the password verification function under SYS schema, that will only call out the created package:

CREATE OR REPLACE FUNCTION sys.ad_profile_verify_function
  (username varchar2, password varchar2, old_password varchar2)
  RETURN boolean IS
BEGIN
  RETURN DBAUSER.PROFILE_AD_AUTH.VERIFY_PASSWORD(username, password);
END;
/

Next, create a new profile setting the password verification function and also would be good to set parameter PASSWORD_LIFE_TIME to the same value AD has for password expiration. An example profile:

CREATE PROFILE ad_user LIMIT 
SESSIONS_PER_USER 2 
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LIFE_TIME 90
PASSWORD_VERIFY_FUNCTION ad_profile_verify_function;

And finally the logon trigger, that will fire the user verification function for all users having the ad_user profile. Modify it for your own needs before executing. The following procedure also needs SELECT privilege on SYS.DBA_USERS granted to SYSTEM.

GRANT execute ON dbauser.profile_ad_auth TO system;

CREATE OR REPLACE TRIGGER system.AD_USER_LOGON AFTER LOGON ON DATABASE
DECLARE
  i NUMBER;
BEGIN
  IF user NOT IN ('SYS','SYSTEM','APPUSER1','APPUSER2') THEN
    -- Check if user belongs to a specific profile
    SELECT count(*) INTO i 
    FROM sys.dba_users
    WHERE username = user AND profile = 'AD_USER';
    -- Execute verification procedure
    IF i > 0 THEN
      dbauser.profile_ad_auth.verify_user(user);
    END IF;
  END IF;
END;
/

To create a user that will be authenticated from AD, first create a regular user with a temporary password, then issue ALTER USER to set the profile. This is needed because if profile is set in CREATE USER command, then the initial password will also be validated with the verification function.

CREATE USER ilmar IDENTIFIED BY test123;
ALTER USER ilmar PROFILE ad_user PASSWORD EXPIRE;

NB! 10g passwords are not case sensitive, so subsequent logins will be allowed with passwords using the wrong case. In 11g, be sure to enable initialization parameter SEC_CASE_SENSITIVE_LOGON, to benefit from case sensitive passwords.

One big problem... SQL Developer still does not support changing passwords and user must change the password from SQLPlus. What do you think may be a good way around it? When the procedure detects, that AD password has changed, then execute ALTER USER to expire the password, but do not raise an exception? Then the user must remember to change the password using ALTER USER, but of course, it means allowing authentication with the old password once...

I have not yet used it in production, so all feedback is welcome! I've tested the code on 10.2 EE and 11.2 EE, should work on standard edition also.

Download all the code from here

Update: Take a look at Oracle-L mailing list thread about the same subject: Click here.

FUSE Linux kernel module for DBFS

noreply@blogger.com (Ilmar Kerm) — Wed, 26 May 2010 06:25:00 +0000

I started to play around with DBFS (Database Filesystem), a new interesting feature in Oracle 11.2.

There are some excellent guides on how to set it up:
http://www.oracle-base.com/articles/11g/DBFS_11gR2.php
http://blog.ronnyegner-consulting.de/2009/10/08/the-oracle-database-file-system-dbfs/

But both of them had one "not very clean" part in them - compiling FUSE kernel driver from source. Actually this is not necessary and there are precompiled FUSE rpm packages available:
http://dag.wieers.com/rpm/packages/fuse/

After RPM package is installed, you need to add the OS user who will mount the filesystem (for example oracle) to OS group fuse.

usermod -a -G fuse oracle

Reading RSS with SQL

noreply@blogger.com (Ilmar Kerm) — Thu, 20 May 2010 17:38:00 +0000

A small SQL code sample this time, how to read RSS file in SQL. I'm using it to display and aggregate some RSS feeds in APEX application.
All columns are directly read from XML (plus some additinal code to get the time zone correct), except pubdate_local that is pubdate converted to date datatype in local time zone. The first example is reading from a local file localnews.rss under directory DIR1.

SELECT title,
       link,
       description,
       author,
       pubdate,
       CAST (pubdate AT TIME ZONE SESSIONTIMEZONE AS DATE) pubdate_local
  FROM (    SELECT title,
                   link,
                   description,
                   author,
                   TO_TIMESTAMP_TZ (
                      REPLACE(pubdate, 'PDT', 'PST PDT'),
                      CASE
                         WHEN REGEXP_LIKE (pubdate, '[[:digit:]]{4}$')
                         THEN
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZHTZM'
                         ELSE
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZR TZD'
                      END,
                      'NLS_DATE_LANGUAGE = American')
                      pubdate
              FROM XMLTABLE (
                      '/rss/channel/item'
                      PASSING xmltype (BFILENAME ('DIR1', 'localnews.rss'),
                                       NLS_CHARSET_ID ('AL32UTF8'))
                      COLUMNS title VARCHAR2 (250 CHAR) PATH 'title',
                              link VARCHAR2 (250 CHAR) PATH 'link',
                              author VARCHAR2 (250 CHAR) PATH 'author',
                              description CLOB PATH 'description',
                              pubdate VARCHAR2 (50) PATH 'pubDate'));

And the same SQL when reading RSS directly from internet over HTTP (BFile is replaced with HttpUriType).

SELECT title,
       link,
       description,
       author,
       pubdate,
       CAST (pubdate AT TIME ZONE SESSIONTIMEZONE AS DATE) pubdate_local
  FROM (    SELECT title,
                   link,
                   description,
                   author,
                   TO_TIMESTAMP_TZ (
                      REPLACE(pubdate, 'PDT', 'PST PDT'),
                      CASE
                         WHEN REGEXP_LIKE (pubdate, '[[:digit:]]{4}$')
                         THEN
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZHTZM'
                         ELSE
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZR TZD'
                      END,
                      'NLS_DATE_LANGUAGE = American')
                      pubdate
              FROM XMLTABLE (
                      '/rss/channel/item'
                      PASSING HttpUriType('http://feeds.feedburner.com/DougsOracleBlog').getXML()
                      COLUMNS title VARCHAR2 (250 CHAR) PATH 'title',
                              link VARCHAR2 (250 CHAR) PATH 'link',
                              author VARCHAR2 (250 CHAR) PATH 'author',
                              description CLOB PATH 'description',
                              pubdate VARCHAR2 (50) PATH 'pubDate'));

Thanks to Daniel Morgans session today at EMEA Harmony, I now have a personal goal to get the Oracle ACE title :) Need to get working towards it and become more active in the community.

20. - 21. May EMEA Harmony Conference in Tallinn

noreply@blogger.com (Ilmar Kerm) — Thu, 11 Mar 2010 07:03:00 +0000

An excellent joint conference for Estonian, Finnish, Latvian and Russian Oracle User Groups in Tallinn, 20. - 21. May 2010.
Speakers also include Tom Kyte, Tanel Põder, Chris J. Date and Steven Feuerstein.

Read the agenda here and register in OUGF home page (250€+VAT registration fee).

In addition, just before the conference, 17.-18. May, Chris Date will perform his "How to Write Correct SQL and Know It: A Relational Approach to SQL" seminar in Helsinki. More info here and more detailed information here.

The easy way of working with XML in Oracle database (part 2)

noreply@blogger.com (Ilmar Kerm) — Wed, 20 Jan 2010 13:10:00 +0000

In this part I'll look at some features of Oracle Database for getting the data out in XML format, with SQL code only. In part 1 I looked at some features for reading/parsing XML with pure SQL.

Populating the tables for examples

Loading the departments into table DEPARTMENTS.

CREATE TABLE departments AS
   SELECT dep.*
     FROM xml, 
     XMLTABLE ('/company/employees/item[1]/departments/item'
        PASSING x
        COLUMNS id FOR ORDINALITY,
                name VARCHAR2 (100 CHAR) PATH 'name') dep;

Employee data to table EMPLOYEES.

CREATE TABLE employees AS
   SELECT emp.id,
          emp.first_name,
          emp.last_name,
          emp.country,
          dep.id active_department_id
     FROM xml,
          departments dep,
          XMLTABLE (
             '/company/employees/item'
                PASSING xml.x
                COLUMNS id NUMBER PATH '@id',
                        first_name VARCHAR2 (10 CHAR) PATH 'first_name',
                        last_name VARCHAR2 (10 CHAR) PATH 'last_name',
                        country VARCHAR2 (10 CHAR) PATH 'country',
                        active_department VARCHAR2 (20 CHAR) PATH 'departments/item[@active="true"]/name') emp
    WHERE dep.name = emp.active_department;

Automatic XML generation + XML Stylesheet

Oracle provides functions sys_xmlgen and sys_xmlagg to automatically generate XML document based on table data. sys_xmlgen adds XML tags around a single value (returns XMLType data type from a single value) and sys_xmlagg aggregates/encloses rows into one XML document. Both of these functions also accept XMLFormat formatting parameter, that can be used to override the default enclosing XML tag name.

SELECT SYS_XMLAGG (x, XMLFormat (enclTag => 'COMPANY')) company_xml
  FROM (SELECT SYS_XMLAGG (emp_xml, XMLFormat (enclTag => 'EMPLOYEES')) x
          FROM (  SELECT tmp_id,
                         SYS_XMLAGG (xml_col, XMLFormat (enclTag => 'ITEM')) emp_xml
                    FROM (SELECT *
                            FROM (SELECT e.id tmp_id,
                                         SYS_XMLGEN (e.id) id,
                                         SYS_XMLGEN (e.first_name) first_name,
                                         SYS_XMLGEN (e.last_name) last_name,
                                         SYS_XMLGEN (e.country) country,
                                         SYS_XMLGEN (d.name, XMLFormat (enclTag => 'ACTIVE_DEPARTMENT')) active_department
                                    FROM employees e, departments d
                                   WHERE e.active_department_id = d.id) UNPIVOT ((xml_col)
                                                                        FOR tmp_col
                                                                        IN (id, first_name, last_name, 
                                                                          country, active_department)))
                GROUP BY tmp_id)
        UNION ALL
        SELECT SYS_XMLGEN ('Some Comany name', XMLFormat (enclTag => 'NAME')) x
          FROM DUAL)

The query is quite long, but the idea behind it is pretty simple - generate a XMLType row for every data attribute and use SYS_XMLAGG to group and aggregate the generated XMLType rows into desirable XML format.

And the result is one XML document.

<?xml version="1.0"?>
<COMPANY>
<EMPLOYEES>
  <ITEM>
    <ID>1</ID>
    <ACTIVE_DEPARTMENT>DBA</ACTIVE_DEPARTMENT>
    <COUNTRY>Estonia</COUNTRY>
    <LAST_NAME>Kerm</LAST_NAME>
    <FIRST_NAME>Ilmar</FIRST_NAME>
  </ITEM>
  <ITEM>
    <ID>2</ID>
    <ACTIVE_DEPARTMENT>Development</ACTIVE_DEPARTMENT>
    <COUNTRY>Estonia2</COUNTRY>
    <LAST_NAME>Kerm2</LAST_NAME>
    <FIRST_NAME>Ilmar2</FIRST_NAME>
  </ITEM>
</EMPLOYEES>
<NAME>Some Comany name</NAME>
</COMPANY>

If the XML is still not at the correct format, then XSL Transformation can be applied to convert the output to another XML format.

XSL Transformation is an XML document, that describes the rules how to convert one XML document to another XML. For this example I wrote the following XSLT document:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="COMPANY">
  <company>
    <name><xsl:value-of select="NAME"/></name>
  </company>
  <employees>
    <xsl:for-each select="EMPLOYEES/ITEM">
      <item>
        <xsl:attribute name="id">
          <xsl:value-of select="ID" />
        </xsl:attribute>
        <first_name><xsl:value-of select="FIRST_NAME" /></first_name>
        <last_name><xsl:value-of select="LAST_NAME" /></last_name>
        <country><xsl:value-of select="COUNTRY" /></country>
        <departments>
          <item active="true">
            <name><xsl:value-of select="ACTIVE_DEPARTMENT" /></name>
          </item>
        </departments>
      </item>
    </xsl:for-each>
  </employees>
</xsl:template>

</xsl:stylesheet>

Save it to XML table named comp_xml_cslt.

CREATE TABLE comp_xml_xslt of xmltype;

INSERT INTO comp_xml_xslt VALUES ('<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
...
</xsl:stylesheet>
');

COMMIT;

To apply the XSL Transformation to source XML, Oracle has a function XMLTransform(source_xml, transformation_xml). Both arguments are XML documents of XMLType and this function returns transformed XML document.

WITH company AS 
  (SELECT SYS_XMLAGG (x, XMLFormat (enclTag => 'COMPANY')) xml
     FROM (SELECT SYS_XMLAGG (
                     emp_xml,
                     XMLFormat (enclTag => 'EMPLOYEES'))
                     x
             FROM (  SELECT tmp_id,
                            SYS_XMLAGG (
                               xml_col,
                               XMLFormat (enclTag => 'ITEM'))
                               emp_xml
                       FROM (SELECT *
                               FROM (SELECT e.id tmp_id,
                                            SYS_XMLGEN (e.id) id,
                                            SYS_XMLGEN (e.first_name) first_name,
                                            SYS_XMLGEN (e.last_name) last_name,
                                            SYS_XMLGEN (e.country) country,
                                            SYS_XMLGEN (d.name,
                                               XMLFormat (enclTag => 'ACTIVE_DEPARTMENT')) active_department
                                       FROM employees e,
                                            departments d
                                      WHERE e.active_department_id =
                                               d.id) UNPIVOT ((xml_col)
                                                     FOR tmp_col
                                                     IN (id, first_name, last_name, country, active_department)))
                   GROUP BY tmp_id)
           UNION ALL
           SELECT SYS_XMLGEN ('Some Comany name',
                              XMLFormat (enclTag => 'NAME'))
                     x
             FROM DUAL))
SELECT XMLTRANSFORM (company.xml, comp_xml_xslt.object_value)
  FROM company, comp_xml_xslt

In this query, COMPANY inline view is the same XML generating query as in the example before, but the XMLTRANSFORM function in main query is used apply the transformation. Result is below:

<company>
 <name>Some Comany name</name>
</company>
<employees>
 <item id="1">
  <first_name>Ilmar</first_name>
  <last_name>Kerm</last_name>
  <country>Estonia</country>
  <departments>
   <item active="true">
    <name>DBA</name>
   </item>
  </departments>
 </item>
 <item id="2">
  <first_name>Ilmar2</first_name>
  <last_name>Kerm2</last_name>
  <country>Estonia2</country>
  <departments>
   <item active="true">
    <name>Development</name>
   </item>
  </departments>
 </item>
</employees>

XQuery

Oracle also implements the standard XQuery language, that can be used for querying XML data and Oracle has provided some XQuery views to access relational database tables.

SELECT XMLQuery(
         'for $emp in ora:view("EMPLOYEES")/ROW
          return <item id="{$emp/ID}">
                 {$emp/FIRST_NAME}
                 {$emp/LAST_NAME}
                 {$emp/COUNTRY}
                 <departments>
                   {for $dep in ora:view("DEPARTMENTS")/ROW 
                    return <item active="{if ($dep/ID eq $emp/ACTIVE_DEPARTMENT_ID) then "true" else ("false")}">{$dep/NAME}</item>}
                </departments>
                </item>'
          RETURNING CONTENT) x FROM DUAL

And the XML result is:

<item id="1"><FIRST_NAME>Ilmar</FIRST_NAME><LAST_NAME>Kerm</LAST_NAME><COUNTRY>Estonia</COUNTRY><departments><item active="false"><NAME>Development</NAME></item><item active="true"><NAME>DBA</NAME></item></departments></item><item id="2"><FIRST_NAME>Ilmar2</FIRST_NAME><LAST_NAME>Kerm2</LAST_NAME><COUNTRY>Estonia2</COUNTRY><departments><item active="true"><NAME>Development</NAME></item><item active="false"><NAME>DBA</NAME></item></departments></item>

The easy way of working with XML in Oracle database (part 1)

noreply@blogger.com (Ilmar Kerm) — Tue, 19 Jan 2010 16:42:00 +0000

I have tried to read Oracle XMLDB Developer Guide and my first reaction was that parsing XML is very complicated in Oracle... And lately I got a task to rewrite some PL/SQL code to use a different SOAP service and the existing code had a few hundred rows just to parse XML (with XML DOM API)! Actually, starting from 10.2, this job is much easier...

Here I will go over some methods for working with XML, that are usable directly from SQL. In this first part, reading and extracting data from XML.

XML for the examples

<?xml version="1.0" encoding="utf-8"?>

  Some Company name
  
    
      Ilmar
      Kerm
      Estonia
      
        
          Development
        
        
          DBA
        
      
    
    
      Ilmar2
      Kerm2
      Estonia2
      
        
          Development
        
        
          DBA

Load the XML data to a table, to special XMLTYPE data type.

create table xml (
  x xmltype
);

insert into xml values (
xmltype.createxml('<?xml version="1.0" encoding="utf-8"?>

  Some Company name
  
...
  
'));

COMMIT;

Extracting a single value

extractValue can be used to extract a single value from XML using XPath expression.

SQL> SELECT extractValue(x, '/company/name') FROM xml;

EXTRACTVALUE(X,'/COMPANY/NAME')
-------------------------------
Some Company name

extractValue only works with single values, otherwise exception will be raised.

SQL> SELECT extractValue(x, '/company/employees/item') FROM xml;
SELECT extractValue(x, '/company/employees/item') FROM xml
*
ERROR at line 1:
ORA-19025: EXTRACTVALUE returns value of only one node

Query XML as relational object

Oracle Database, since 10g, has a very easy way to map XML to a relational object and query it with SQL, so no PL/SQL code is needed for parsing XML - the XMLTABLE function.

SQL> SELECT emp.*
  FROM xml,
       XMLTABLE (
         '/company/employees/item'
         PASSING x
         COLUMNS id NUMBER PATH '@id',
                 first_name VARCHAR2 (10 CHAR) PATH 'first_name',
                 last_name VARCHAR2 (10 CHAR) PATH 'last_name',
                 country VARCHAR2 (10 CHAR) PATH 'country',
                 active_department VARCHAR2 (20 CHAR) PATH 'departments/item[@active="true"]/name'
       ) emp;

        ID FIRST_NAME LAST_NAME  COUNTRY    ACTIVE_DEPARTMENT
---------- ---------- ---------- ---------- --------------------
         1 Ilmar      Kerm       Estonia    DBA
         2 Ilmar2     Kerm2      Estonia2   Development

XmlTable takes the following arguments:

'/company/employees/item'	XPath expression of the "row"
PASSING x	Source of XML data - XMLTYPE data type
COLUMNS id NUMBER PATH '@id', first_name VARCHAR2 (10 CHAR) PATH 'first_name', last_name VARCHAR2 (10 CHAR) PATH 'last_name', country VARCHAR2 (10 CHAR) PATH 'country', active_department VARCHAR2 (20 CHAR) PATH 'departments/item[@active="true"]/name'	Column definitions with the corresponding Oracle data type and XPath expression

After XML is readable as a relational table, all the power of Oracle SQL can be used for querying.

Oracle Enterprise Manager Desktop Widgets

noreply@blogger.com (Ilmar Kerm) — Fri, 06 Nov 2009 11:40:00 +0000

I love database performance visualization tools and now Oracle has released small desktop widgets that give a quick overview of the targets in OEM Grid Control.

The one I really like is High-Load Databases widget. That shows a quick overview of how the top databases are performing at the moment - total number of average active sessions and a graph how this load is divided between the CPU/IO/Other wait classes.

With a click of a button, the screen changes to the new rectangular style performance graph, also showing the latest ADDM findings.

One feature request to Oracle - please add more lightweight skin and allow the user to configure how many databases the widget shows. I'd like to set that widget "stay on top", but the view with TOP 5 databases takes too much screen space.

Download the widgets here: http://www.oracle.com/technology/products/oem/widgets/index.html

Blog for these widgets: http://blogs.oracle.com/emwidgets/

Actually, this is the first application I'd like to have running on my mobile device...

Hello World with Oracle XE

noreply@blogger.com (Ilmar Kerm) — Tue, 27 Oct 2009 09:34:00 +0000

I needed to develop one small application that communicates with external mobile devices, being like a central server where mobile clients send their data and receive configuration parameters. Reporting will be added later using Oracle APEX. It's a small application for a customer demo, so it sounded like a perfect opportunity to have my first look at the Oracle XE database :)

Oracle XE comes with a pre-configured XDB HTTP listener for APEX. I don't need the full APEX engine for my simple request server, just a PL/SQL package using PL/SQL Web Toolkit will do just fine and there is no need to use PHP or some other scripting engine outside the database.

Here are the steps I used to configure Oracle XE XDB HTTP listener with additional path (DAD) to my custom procedure.

Make HTTP listener to listen on all network interfaces, not only localhost:

EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

Oracle XE has pre-defined DAD for APEX - /apex/
The following code will create a new DAD /mobileapp/, associate it with schema MOBILEAPP and authorize it to run without asking the user for a password.

BEGIN
  DBMS_EPG.create_dad (
    dad_name => 'mobileapp',
    path     => '/mobileapp/*');

  DBMS_EPG.set_dad_attribute (
    dad_name   => 'mobileapp',
    attr_name  => 'default-page',
    attr_value => 'home');

  DBMS_EPG.set_dad_attribute (
    dad_name   => 'mobileapp',
    attr_name  => 'database-username',
    attr_value => 'MOBILEAPP');

  DBMS_EPG.set_dad_attribute (
    dad_name   => 'mobileapp',
    attr_name  => 'nls-language',
    attr_value => 'american_america.al32utf8');

  DBMS_EPG.authorize_dad (
    dad_name => 'mobileapp',
    user     => 'MOBILEAPP');
END;
/

I used the following DAD attributes:

default-page	specifies the default procedure name, that will be used when the procedure name is not specified in a HTTP URL
database-username	associated database schema name
nls-language	NLS client value, for a web application the important part is the charset. al32utf8 means the result page is in UTF-8.
DBMS_EPG.authorize_dad	This will disable the HTTP basic authentication

There are other interesting DAD attributes and the current XDB HTTP configuration and APEX DAD configuration can be views using this SQL:

SQL> SELECT   DBMS_XDB.CFG_GET().getClobVal() FROM DUAL;

...
              
                APEX
                PL/SQL
                APEX
                
                  ANONYMOUS
                  apex
                  wwv_flow_file_objects$
                  docs
                  wwv_flow_file_mgr.process_download
                  american_america.al32utf8
                  wwv_flow_epg_include_modules.authorize
                
              
...

Now all MOBILEAPP PL/SQL procedures and packages can be accessed over HTTP and the HTTP URL has the following form:
http://oraclexeserver:8080/mobileapp/procedurename
or
http://oraclexeserver:8080/mobileapp/packagename.procedurename

So, a simple hello world procedure using PL/SQL Web Toolkit could look like this:

CREATE OR REPLACE PROCEDURE mobileapp.home IS
BEGIN
  HTP.htmlopen;
  HTP.headopen;
  HTP.title('Page title');
  HTP.headclose;
  HTP.bodyopen;
  HTP.print('Hello world');
  HTP.bodyclose;
  HTP.htmlclose;
END;
/

HTTP URL for this procedure will be http://oraclexeserver:8080/mobileapp/home or just http://oraclexeserver:8080/mobileapp/ (note the default-page DAD attribute).
The proceudres like HTP.htmlopen will just generate HTML tags.

Passing HTTP GET parameters is very easy and they will be set as a procedure parameters with the same name. Parameter data type is VARCHAR2 or if HTTP request has more than one parameter with the same name, then TABLE OF VARCHAR2 data type is used.

CREATE OR REPLACE PROCEDURE mobileapp.paramtest(p1 IN VARCHAR2 DEFAULT NULL,
  p2 IN VARCHAR2 DEFAULT NULL) IS
BEGIN
  IF p1 IS NULL THEN
    HTP.p('Hello world');
  ELSE
    HTP.p('Hello p1='||p1||' p2='||p2);
  END IF;
END;
/

This procedure can be called with HTTP URL:
http://oraclexeserver:8080/mobileapp/paramtest?p1=value1&p2=value2
Both parameters are optional (procedure parameters have DEFAULT value set).

If the procedure raises an exception or suitable procedure name with correct argument list cannot be found, then HTTP error code 404 (page not found) is the result.

High performance data unloading from Oracle to ODBC database

noreply@blogger.com (Ilmar Kerm) — Tue, 04 Aug 2009 20:20:00 +0000

I got a task some time ago, to copy a-lot-of-millions of rows from Oracle to MySQL. As fast as possible.
In the last year I have used Oracle Heterogeneous Services (HSODBC) a lot to select data from different non-Oracle databases, but I had yet to try to unload data from Oracle with best performance.

My first thought was, of course, that the process needs to run in parallel. Here I'll first take a look at the overall HSODBC performance and then look how can I make this process run in parallel.

In this test I use Oracle Database 11.1.0.7 as source, Oracle Heterogeneous Services 10.2.0.4 with MyODBC 5.1 driver, MySQL 5.1 destination database. In my setup, I have all these three components running on separate servers.

SQL> CREATE TABLE hsperftest
AS
   SELECT   * FROM all_objects
   UNION ALL
   SELECT   * FROM all_objects
   UNION ALL
   SELECT   * FROM all_objects;

SQL> DESC hsperftest
 Name                                        Null?    Type
 ------------------------------------------- -------- ---------------------
 OWNER                                                VARCHAR2(30)
 OBJECT_NAME                                          VARCHAR2(30)
 SUBOBJECT_NAME                                       VARCHAR2(30)
 OBJECT_ID                                            NUMBER
 DATA_OBJECT_ID                                       NUMBER
 OBJECT_TYPE                                          VARCHAR2(19)
 CREATED                                              DATE
 LAST_DDL_TIME                                        DATE
 TIMESTAMP                                            VARCHAR2(19)
 STATUS                                               VARCHAR2(7)
 TEMPORARY                                            VARCHAR2(1)
 GENERATED                                            VARCHAR2(1)
 SECONDARY                                            VARCHAR2(1)
 NAMESPACE                                            NUMBER
 EDITION_NAME                                         VARCHAR2(30)
 ID                                          NOT NULL NUMBER(10)

And I create the same table in MySQL.

CREATE TABLE `hsperftest` (
  `OWNER` varchar(30) DEFAULT NULL,
  `OBJECT_NAME` varchar(30) DEFAULT NULL,
  `SUBOBJECT_NAME` varchar(30) DEFAULT NULL,
  `OBJECT_ID` int(11) DEFAULT NULL,
  `DATA_OBJECT_ID` int(11) DEFAULT NULL,
  `OBJECT_TYPE` varchar(19) DEFAULT NULL,
  `CREATED` datetime DEFAULT NULL,
  `LAST_DDL_TIME` datetime DEFAULT NULL,
  `TIMESTAMP` varchar(19) DEFAULT NULL,
  `STATUS` varchar(7) DEFAULT NULL,
  `TEMPORARY` varchar(1) DEFAULT NULL,
  `GENERATED` varchar(1) DEFAULT NULL,
  `SECONDARY` varchar(1) DEFAULT NULL,
  `NAMESPACE` int(11) DEFAULT NULL,
  `EDITION_NAME` varchar(30) DEFAULT NULL,
  `ID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Set up HSODBC to connect to the MySQL database and create a database link on Oracle side. When setting up HSODBC, do not enable logging/tracing (HS_FDS_TRACE_LEVEL = off)! Tracing will serialize all database calls to the remote database.

I tested this with HSODBC running on Windows Vista and Linux (using unixODBC). The results were similar.

Test that MySQL table is accessible for Oracle:

SQL> DESC "hsperftest"@hsdb
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- -----------------
 OWNER                                                          VARCHAR2(30)
 OBJECT_NAME                                                    VARCHAR2(30)
 SUBOBJECT_NAME                                                 VARCHAR2(30)
 OBJECT_ID                                                      NUMBER(10)
 DATA_OBJECT_ID                                                 NUMBER(10)
 OBJECT_TYPE                                                    VARCHAR2(19)
 CREATED                                                        DATE
 LAST_DDL_TIME                                                  DATE
 TIMESTAMP                                                      VARCHAR2(19)
 STATUS                                                         VARCHAR2(7)
 TEMPORARY                                                      VARCHAR2(1)
 GENERATED                                                      VARCHAR2(1)
 SECONDARY                                                      VARCHAR2(1)
 NAMESPACE                                                      NUMBER(10)
 EDITION_NAME                                                   VARCHAR2(30)
 ID                                                    NOT NULL NUMBER(10)

Before continuing, check that all MySQL data types are mapped correctly to Oracle. And make sure, that none of the columns are using deprecated LONG datatype.

First of all... the following SQL will not work:

SQL> insert into "hsperftest"@hsdb select * from hsperftest;
insert into "hsperftest"@hsdb select * from hsperftest
                                     *
ERROR at line 1:
ORA-02025: all tables in the SQL statement must be at the remote database

Some PL/SQL is needed to first select from Oracle cursor and then bind the values to the remote database INSERT statement. The easiest way is:

BEGIN
   FOR rec IN (SELECT   * FROM HSPERFTEST)
   LOOP
      INSERT INTO "hsperftest"@hsdb
        VALUES   rec;
   END LOOP;
   COMMIT;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:14:28.38

When running this anonymous block I noticed, that the load on Oracle database and MySQL database was very light, but the HSODBC CPU usage was at the maximum. So HSODBC seems to be the bottleneck. What is it doing? With this kind of CPU usage its not going to scale much in parallel. If its performing some mappings between Oracle SQL and MySQL SQL, then can I remove this bottleneck by using DBMS_HS_PASSTHROUGH, that sends the query unmodified to the remote database?

DECLARE
   p_c   BINARY_INTEGER;
   nr    INTEGER;
BEGIN
   p_c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@hsdb;
   DBMS_HS_PASSTHROUGH.PARSE@hsdb (
      p_c,
      'insert into hsperftest (OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
      DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,
      TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME,ID)
      VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
   );

   FOR rec IN (SELECT   * FROM HSPERFTEST)
   LOOP
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 1, rec.owner);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 2, rec.object_name);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 3, rec.SUBOBJECT_NAME);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 4, rec.OBJECT_ID);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 5, rec.DATA_OBJECT_ID);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 6, rec.OBJECT_TYPE);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 7, rec.CREATED);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 8, rec.LAST_DDL_TIME);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 9, rec.TIMESTAMP);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 10, rec.STATUS);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 11, rec.TEMPORARY);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 12, rec.GENERATED);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 13, rec.SECONDARY);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 14, rec.NAMESPACE);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 15, rec.EDITION_NAME);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 16, rec.ID);
      nr := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@hsdb (p_c);
   END LOOP;

   DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@hsdb (p_c);
   COMMIT;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:07:37.77

That is a huge difference in execution time! And with DBMS_HS_PASSTHROUGH, the HSODBC process CPU usage is much less, making it more scalable running in parallel.

Now, how to run it in parallel?

Since the destination database is non-Oracle, the database built-in parallel execution cannot be used, so "DIY parallelism" is needed: split/partition the input data with some kind of criteria and start a new process for each partition.

In the real project I used each records CREATED_DATE value for partitioning, because underlying table was already partitioned by that column, but its also possible to use table ROWID values for partitioning. A good example of it is on book Expert Oracle database architecture By Thomas Kyte, chapter 14, Parallel Execution, Do-It-Yourself Parallelism.

Here I'm going to split the table just into 3 pieces to make it simple. To get the maximum IO performance from Oracle, Tom Kytes example needs to be followed. But I'll make my example just simple.
Table hsperftest_job will keep the metadata for every parallel slave.

CREATE TABLE hsperftest_job
(
   job_no       NUMBER NOT NULL,
   low_id       ROWID NOT NULL,
   hi_id        ROWID NOT NULL,
   max_in       NUMBER NOT NULL,
   start_date   DATE,
   end_date     DATE,
   rows_processed   NUMBER
);
CREATE SEQUENCE seq_hsperftest_job;

DECLARE
  p_min rowid;
  p_max rowid;
  p_row1 rowid;
  p_row2 rowid;
  p_count number;
BEGIN
  SELECT MIN(rowid), MAX(rowid), COUNT(*) INTO p_min, p_max, p_count
    FROM hsperftest;
  SELECT MAX(a) INTO p_row1 FROM (
    SELECT rowid a FROM hsperftest WHERE rownum < CEIL(p_count/3)
  );
  SELECT MAX(a) INTO p_row2 FROM (
    SELECT rowid a, rownum rn FROM hsperftest WHERE rowid > p_row1 
  ) WHERE rn < CEIL(p_count/3);
  INSERT INTO hsperftest_job (job_no, low_id, hi_id, max_in) VALUES (seq_hsperftest_job.nextval, p_min, p_row1, 0);
  INSERT INTO hsperftest_job (job_no, low_id, hi_id, max_in) VALUES (seq_hsperftest_job.nextval, p_row1, p_row2, 0);
  INSERT INTO hsperftest_job (job_no, low_id, hi_id, max_in) VALUES (seq_hsperftest_job.nextval, p_row2, p_max, 1);
  COMMIT;
END;
/

  SELECT   j.job_no, COUNT (1)
    FROM   hsperftest_job j, hsperftest h
   WHERE   h.ROWID BETWEEN j.low_id AND j.hi_id
GROUP BY   j.job_no;

    JOB_NO   COUNT(1)
---------- ----------
         1      59313
         2      59314
         3      59317

Looks like my very simple ROWID hack is good enough for this test.

Now I'll create a procedure that will be executed in parallel.

CREATE OR REPLACE PROCEDURE hsperftest_proc(p_job_no hsperftest_job.job_no%TYPE) IS
  p_min ROWID;
  p_max ROWID;
  p_max_in  NUMBER;
  p_c   BINARY_INTEGER;
  nr    INTEGER;
  p_count NUMBER:= 0;
BEGIN
  -- Read the job metadata
  SELECT low_id, hi_id, max_in INTO p_min, p_max, p_max_in
  FROM hsperftest_job
  WHERE job_no = p_job_no;
  UPDATE hsperftest_job SET start_date = sysdate WHERE job_no = p_job_no;
  COMMIT;
  -- Do the work
   p_c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@hsdb;
   DBMS_HS_PASSTHROUGH.PARSE@hsdb (
      p_c,
      'insert into hsperftest (OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,
      TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME,ID)
      VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
   );
   FOR rec IN (SELECT  * FROM HSPERFTEST WHERE rowid BETWEEN p_min AND p_max AND (p_max_in = 1 OR (p_max_in = 0 AND rowid < p_max)))
   LOOP
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 1, rec.owner);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 2, rec.object_name);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 3, rec.SUBOBJECT_NAME);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 4, rec.OBJECT_ID);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 5, rec.DATA_OBJECT_ID);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 6, rec.OBJECT_TYPE);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 7, rec.CREATED);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 8, rec.LAST_DDL_TIME);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 9, rec.TIMESTAMP);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 10, rec.STATUS);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 11, rec.TEMPORARY);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 12, rec.GENERATED);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 13, rec.SECONDARY);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 14, rec.NAMESPACE);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 15, rec.EDITION_NAME);
      DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsdb (p_c, 16, rec.ID);
      nr := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@hsdb (p_c);
      p_count:= p_count + 1;
   END LOOP;

   DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@hsdb (p_c);
   COMMIT;  
  -- Write the job end time
  UPDATE hsperftest_job SET end_date = sysdate, rows_processed = p_count WHERE job_no = p_job_no;
  COMMIT;
END;
/

And finally I'll execute the jobs by putting them on the job queue.

DECLARE
   x   PLS_INTEGER;
BEGIN
   FOR rec IN (SELECT   job_no FROM hsperftest_job)
   LOOP
      DBMS_JOB.submit (job => x, what => 'hsperftest_proc(' || rec.job_no || ');');
   END LOOP;

   COMMIT;
END;
/

SQL> SELECT   job, total_time, broken, what FROM   user_jobs;
       JOB TOTAL_TIME B WHAT
---------- ---------- - -----------------
     10967         95 N hsperftest_proc(1);
     10968         95 N hsperftest_proc(2);
     10969         95 N hsperftest_proc(3);

When the jobs are running, monitor the HSODBC processes CPU and memory usage. If there are too much parallel slaves running, then they may become a bottleneck. If HSODBC memory usage becomes a problem, then partition the source data into smaller chunks.

When everything is done, look at the results.

SQL> SELECT SUM(rows_processed) total_rows, (MAX(end_date)-MIN(start_date))*24*60 runtime_min FROM hsperftest_job;

TOTAL_ROWS RUNTIME_MIN
---------- -----------
    177942  3.58333333

3.58 min = 3 min 34.8 sec. Nice :)

Using linguistic indexes for sorting in open source databases

noreply@blogger.com (Ilmar Kerm) — Mon, 27 Jul 2009 19:15:00 +0000

Here I'm following up my previous post Using linguistic indexes for sorting in Oracle. I don't much like the Oracle solution, that requires creating a special index to speed up sorting, but... at the same time its very powerful, allows to index in many languages and no database changes are needed.

In this post I’ll take a look at the two popular open source databases MySQL and PostgreSQL. I'll take a look only at features, that the database has included and that can be used without any special application changes.

PostgreSQL 8.4

Starting from 8.4, collation (sorting) rules can be defined per database and there is no possibility to set it in session level. All sorting and all indexes are ordered according to the database collation locale. In previous versions there was only one collation locale allowed for the entire database cluster.

For my example I create two databases, one with Estonian locale and one with German.

$ createdb -E utf8 -l 'et_EE.utf8' -O ilmar ilmar_ee
$ createdb -E utf8 -l 'de_DE.utf8' -O ilmar -T template0 ilmar_de

Currently PostgreSQL relies on the underlying OS to do the collations, so the OS must also support the specified locale. Check it with:

$ locale -a | grep et_EE
et_EE
et_EE.iso88591
et_EE.iso885915
et_EE.utf8

To change the collation you need to dump the entire database to a text file (pg_dump), create a new database and reload the data. So, a pretty painful procedure for large databases.

A small test if it really works. In ilmar_ee and ilmar_de I create table test_coll and load it with 4 rows:

CREATE TABLE test_coll (
  t character varying(100) NOT NULL
);

begin;
insert into test_coll values ('a');
insert into test_coll values ('o');
insert into test_coll values ('õ');
insert into test_coll values ('ä');
commit;


ilmar_ee=> select t from test_coll order by t;
a
o
õ
ä

ilmar_de=> select t from test_coll order by t;
a
ä
o
õ

Now can index be used for sorting?

CREATE TABLE t (
  t character varying(100) NOT NULL
);

CREATE INDEX idxt
  ON t
  USING btree
  (t);

CREATE OR REPLACE FUNCTION fill_t(p_num_rows bigint) RETURNS bigint AS
$BODY$
declare
  s t.t%type;
  i integer;
begin
  for i in 1..p_num_rows loop
    s:= case mod(i, 4) when 0 then 'a' when 1 then 'ä' when 2 then 'o' when 3 then 'õ' end;
    if mod(i,2) = 0 then
      s:= upper(s);
    end if;
    s:= s ||' wqe wqe wqe wqeqwdsa asd asdasd sasss we qwewq dssas';
    insert into t (t) values (s);
  end loop;
  return 0;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

select fill_t(10000);
vacuum analyze t;

After the test data is created, some tests. Oracle users will find the following very strange:

ilmar=> explain select t from t order by t;
                          QUERY PLAN
--------------------------------------------------------------
 Sort  (cost=868.39..893.39 rows=10000 width=55)
   Sort Key: t
   ->  Seq Scan on t  (cost=0.00..204.00 rows=10000 width=55)

ilmar=> explain select t from t where t between 'a' and 'b' order by t;
                               QUERY PLAN
-------------------------------------------------------------------------
 Sort  (cost=395.10..401.35 rows=2500 width=55)
   Sort Key: t
   ->  Seq Scan on t  (cost=0.00..254.00 rows=2500 width=55)
         Filter: (((t)::text >= 'a'::text) AND ((t)::text <= 'b'::text))

ilmar=> explain select t from t where t between 'a' and 'ak' order by t;
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using idxt on t  (cost=0.00..8.27 rows=1 width=55)
   Index Cond: (((t)::text >= 'a'::text) AND ((t)::text <= 'ak'::text))

It seems that Postgres optimizer only consideres using index for sorting, when there is only a small fraction of the table filtered. A reason for this in the documentation is:

The planner will consider satisfying an ORDER BY specification either by scanning an available index that matches the specification, or by scanning the table in physical order and doing an explicit sort. For a query that requires scanning a large fraction of the table, an explicit sort is likely to be faster than using an index because it requires less disk I/O due to following a sequential access pattern. Indexes are more useful when only a few rows need be fetched.

So it seems that Postgres cannot fast full scan an index. If I fill the table up even more, then finally optimizer is costing the sort operation higher than index scan. But the query is slow, 4125ms on my system.

ilmar=> select fill_t(90000);
ilmar=> vacuum analyze t;

ilmar=> explain select t from t order by t;
                              QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using idxt on t  (cost=0.00..9771.11 rows=100000 width=55)

A note from documentation:

The drawback of using locales other than C or POSIX in PostgreSQL is its performance impact. It slows character handling and prevents ordinary indexes from being used by LIKE. For this reason use locales only if you actually need them.

Better collation supports seems to be a work in progress:
Todo:Collate
Todo:IDU
Its also possible to use third party Orafce package, that enables the use on NLSSORT function, that is similar to Oracle.

MySQL 5.1

MySQL supports collations at the column level.
All available collations for the given charset can be queried like this:

mysql> show collation where charset = 'utf8';
+--------------------+---------+-----+---------+----------+---------+
| Collation          | Charset | Id  | Default | Compiled | Sortlen |
+--------------------+---------+-----+---------+----------+---------+
| utf8_general_ci    | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin           | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci    | utf8    | 192 |         | Yes      |       8 |
| utf8_icelandic_ci  | utf8    | 193 |         | Yes      |       8 |
| utf8_latvian_ci    | utf8    | 194 |         | Yes      |       8 |
| utf8_romanian_ci   | utf8    | 195 |         | Yes      |       8 |
| utf8_slovenian_ci  | utf8    | 196 |         | Yes      |       8 |
| utf8_polish_ci     | utf8    | 197 |         | Yes      |       8 |
| utf8_estonian_ci   | utf8    | 198 |         | Yes      |       8 |
| utf8_spanish_ci    | utf8    | 199 |         | Yes      |       8 |
| utf8_swedish_ci    | utf8    | 200 |         | Yes      |       8 |
| utf8_turkish_ci    | utf8    | 201 |         | Yes      |       8 |
| utf8_czech_ci      | utf8    | 202 |         | Yes      |       8 |
| utf8_danish_ci     | utf8    | 203 |         | Yes      |       8 |
| utf8_lithuanian_ci | utf8    | 204 |         | Yes      |       8 |
| utf8_slovak_ci     | utf8    | 205 |         | Yes      |       8 |
| utf8_spanish2_ci   | utf8    | 206 |         | Yes      |       8 |
| utf8_roman_ci      | utf8    | 207 |         | Yes      |       8 |
| utf8_persian_ci    | utf8    | 208 |         | Yes      |       8 |
| utf8_esperanto_ci  | utf8    | 209 |         | Yes      |       8 |
| utf8_hungarian_ci  | utf8    | 210 |         | Yes      |       8 |
+--------------------+---------+-----+---------+----------+---------+
21 rows in set (0.04 sec)

I'll create a table test, in where column e uses Estonian sorting and column h uses hungarian sorting.

mysql> create table test (
  e varchar(100) not null collate 'utf8_estonian_ci',
  h varchar(100) not null collate 'utf8_hungarian_ci'
) charset=utf8;

mysql> insert into test values ('a','a');
mysql> insert into test values ('ä','ä');
mysql> insert into test values ('o','o');
mysql> insert into test values ('õ','õ');
mysql> select * from test;
+---+---+
| e | h |
+---+---+
| a | a |
| ä | ä |
| o | o |
| õ | õ |
+---+---+
4 rows in set (0.02 sec)

mysql> select * from test order by e;
+---+---+
| e | h |
+---+---+
| a | a |
| o | o |
| õ | õ |
| ä | ä |
+---+---+
4 rows in set (0.04 sec)

mysql> select * from test order by h;
+---+---+
| e | h |
+---+---+
| a | a |
| ä | ä |
| o | o |
| õ | õ |
+---+---+
4 rows in set (0.01 sec)

Perfect, now what about indexes?

mysql> create index idxe on test (e);
Query OK, 4 rows affected (0.71 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> create index idxh on test (h);
Query OK, 4 rows affected (0.18 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> explain select e from test order by e;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | index | NULL          | idxe | 302     | NULL |    4 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select h from test order by h;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | index | NULL          | idxh | 302     | NULL |    4 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

If I force a different collation on a column, then values are read from index, but extra filesort step is needed:

mysql> explain select h from test order by h collate utf8_estonian_ci;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | test  | index | NULL          | idxh | 302     | NULL |    4 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

I'll add more rows to see how much difference there is when reading the order from index and when doing extra sorting.

mysql> delimiter //
mysql> create procedure load_data(p_num_rows INT)
    -> BEGIN
    ->   SET @i = 0;
    ->   REPEAT
    ->     SET @i = @i + 1;
    ->     INSERT INTO test (e, h) VALUES (CONCAT('eeeaad sadsa dasd asd', @i),
    ->       CONCAT('213aad sadsa dasd asd', @i));
    ->   UNTIL @i > p_num_rows END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.44 sec)

mysql> delimiter ;
mysql> call load_data(10000);
mysql> call load_data(20000);
mysql> ANALYZE TABLE test;

mysql> select sql_no_cache count(*) from (select h from test order by h collate utf8_estonian_ci) a;
+----------+
| count(*) |
+----------+
|    30002 |
+----------+
1 row in set (1.22 sec)

mysql> select sql_no_cache count(*) from (select h from test order by h) a;
+----------+
| count(*) |
+----------+
|    30002 |
+----------+
1 row in set (0.09 sec)

It is also possible to set collation at connection level, but this does not change the row sorting order like in Oracle.

mysql> truncate table test;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test values ('a','a');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test values ('ä','ä');
Query OK, 1 row affected (0.08 sec)

mysql> insert into test values ('o','o');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test values ('õ','õ');
Query OK, 1 row affected (0.03 sec)

mysql> set collation_connection=utf8_estonian_ci;
Query OK, 0 rows affected (0.01 sec)

mysql> select h from test order by h;
+---+
| h |
+---+
| a |
| ä |
| o |
| õ |
+---+
4 rows in set (0.00 sec)

mysql> set collation_connection=utf8_hungarian_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> select h from test order by h;
+---+
| h |
+---+
| a |
| ä |
| o |
| õ |
+---+
4 rows in set (0.00 sec)

Refreshing test database from production using incremental backups

noreply@blogger.com (Ilmar Kerm) — Sat, 25 Jul 2009 13:00:00 +0000

It's common to use a production database copy for testing. But how to keep it fresh? If production database is large and located on a remote site (even on another country/continent), then copying the full database backup over network may be too unreasonable and if the test database needs to be open for a longer time, then transporting and storing production archived logs to test site may be too unreasonable also.
In this situation Oracle has three great technologies that can help: physical standby database, flashback database and RMAN.

Here I'm using Oracle Database 10.2.0.4 EE.

Initial setup

This is just to create a test database as physical standby database. Do it in any way you like. Just make sure you create a guaranteed restore point before activating the standby.

Create backup and standby controlfile. Give backupsets some tag, that can later be used for cleaning up the backups from primary database.

RMAN> backup device type disk format '/home/oracle/backup/%U' as compressed backupset
  tag 'testdb_init' database INCLUDE CURRENT CONTROLFILE FOR STANDBY
  plus archivelog;

Starting backup at 24-JUL-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting compressed full datafile backupset
... and so on
Finished backup at 24-JUL-09

I also need to take spfile and password file.

$ cp $ORACLE_HOME/dbs/spfiletest1.ora /home/oracle/backup/
$ cp $ORACLE_HOME/dbs/orapwtest1 /home/oracle/backup/

Now, copy everything over to the test database. I'm leaving all file locations exactly the same as in primary database.

$ scp /home/oracle/backup/* oracle@testdb:/home/oracle/backup/

On test server:

$ cd backup/
$ cp spfiletest1.ora $ORACLE_HOME/dbs/
$ cp orapwtest1 $ORACLE_HOME/dbs/

Start up the test database instance and change service name to be different from the one used in production.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1268920 bytes
Variable Size             167773000 bytes
Database Buffers          436207616 bytes
Redo Buffers                2924544 bytes
SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      test1

SQL> alter system set service_names='test_test1' scope=both;

System altered.

Restore controlfile and restore the datafiles.

RMAN> restore controlfile from '/home/oracle/backup/0tkkuvob_1_1';

Starting restore at 24-JUL-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output filename=/u01/app/oracle/oradata/TEST1/controlfile/o1_mf_56lyof8g_.ctl
output filename=/u01/app/oracle/flash_recovery_area/TEST1/controlfile/o1_mf_56lyofm4_.ctl
Finished restore at 24-JUL-09

RMAN> alter database mount;
RMAN> sql "alter database flashback off";

RMAN> catalog start with '/home/oracle/backup';

searching for all files that match the pattern /home/oracle/backup

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/0ukkuvok_1_1
File Name: /home/oracle/backup/0tkkuvob_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/backup/0ukkuvok_1_1
File Name: /home/oracle/backup/0tkkuvob_1_1

RMAN> restore database;

Starting restore at 24-JUL-09
...
Finished restore at 24-JUL-09

RMAN> recover database;

Starting recover at 24-JUL-09
using channel ORA_DISK_1

starting media recovery
unable to find archive log
archive log thread=1 sequence=51
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/24/2009 17:09:50
RMAN-06054: media recovery requesting unknown log: thread 1 seq 51 lowscn 360057

Recover database will fail at the end, but this is only because it didn't find the current log. This doesn't matter here.

Test that media recovery process will start.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process, status, sequence# from v$managed_standby where process like 'MRP%';

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      WAIT_FOR_LOG         51

SQL> alter database recover managed standby database cancel;

Database altered.

If MRP* is started and waiting for log (and no error in alert.log), then everything is good.

Set up flashback. Make sure that you have enough diskspace in FRA (flashback recovery area) for all flashback logs generated while the test database is open. After flashback has been enabled, create a guaranteed restore point.

SQL> alter database flashback on;

Database altered.

SQL> create restore point prod_point guarantee flashback database;

Restore point created.

Now, just activate standby database and open it.

SQL> alter database activate standby database;
SQL> startup mount force
SQL> alter database set standby database to maximize performance;
SQL> alter database open;

Delete the backups from both databases. Just for cleaning up.

RMAN> delete backup tag 'testdb_init';

Syncing with production

To make it more interesting, I'll add some new datafiles to the primary database.

SQL> alter tablespace sysaux add datafile size 10m autoextend on next 10m maxsize 1g;

Tablespace altered.

SQL> create tablespace test5891 datafile size 10m autoextend on next 10m maxsize 1g;

Tablespace created.

First, in test database find out the restore point SCN. This is the point from where to create the incremental backup.

SQL> select to_char(scn) from v$restore_point where name = 'PROD_POINT';

TO_CHAR(SCN)
---------------
390703

Find out the current production database log sequence.

SQL> select sequence# from v$log where status = 'CURRENT';

 SEQUENCE#
----------
        57

SQL> alter system checkpoint;

Create incremental backup from production and transport it to the test site. Also include all archived logs that were generated during backup.

RMAN> BACKUP INCREMENTAL FROM SCN=390703 device type disk format '/home/oracle/backup/%U'
  as compressed backupset database INCLUDE CURRENT CONTROLFILE FOR STANDBY;

RMAN> sql "alter system archive log current";

RMAN> backup device type disk format '/home/oracle/backup/%U' as compressed backupset archivelog from sequence 57;

Close test database and flash it back to the restore point.

SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to restore point PROD_POINT;
SQL> drop restore point PROD_POINT;
SQL> alter database flashback off;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> startup mount force

Perform any cleanups, like added datafiles while the test database was open.

RMAN> catalog start with '/home/oracle/backup';

searching for all files that match the pattern /home/oracle/backup

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/13kl0v38_1_1
File Name: /home/oracle/backup/12kl0uuv_1_1
File Name: /home/oracle/backup/14kl0v87_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/backup/13kl0v38_1_1
File Name: /home/oracle/backup/12kl0uuv_1_1
File Name: /home/oracle/backup/14kl0v87_1_1

RMAN> recover database;

Starting recover at 25-JUL-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
...

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
...
unable to find archive log
archive log thread=1 sequence=59
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/25/2009 12:10:43
RMAN-06054: media recovery requesting unknown log: thread 1 seq 59 lowscn 391208

At the end, recover database will fail, because it cant find the current log, but it doesn't matter here. Looking at the datafiles, RMAN has automatically created all newly added datafiles. That's nice and easy.

Now, I'll check that standby database is working, create a new restore point and open the database.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process, status, sequence# from v$managed_standby where process like 'MRP%';

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      WAIT_FOR_LOG         59

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> create restore point prod_point guarantee flashback database;

Restore point created.

SQL> alter database activate standby database;

Database altered.

SQL> startup mount force
ORACLE instance started.
...
Database mounted.
SQL> alter database set standby database to maximize performance;

Database altered.

SQL> alter database open;

Database altered.

That's it. Pretty easy actually :)

So if you think this method can be useful to you, then try it out. I needed to work this out, because test database is on another continent from primary and physical standbys and the network link is pretty slow. Test database is open usually about a month so using incremental backups for refreshing is pretty much the only thing reasonable.

Actually I use this method with backups taken from physical standby database, so in that case it's not necessary to take the archivelogs also, just shut down media recovery process before starting the backup.

Using linguistic indexes for sorting in Oracle

noreply@blogger.com (Ilmar Kerm) — Wed, 22 Jul 2009 11:42:00 +0000

In Estonia we have our own language and Estonian language has its own alphabet that is a little bit different from English :) And it would be good if a database application sorts text fields according to the correct language rules (I have seen many bug reports from clients, that "the sort order is wrong").

In Oracle database its quite easy to sort according to the correct language rules, you just need to set the correct database session language from the client. This can be done using ALTER SESSION SET NLS_LANGUAGE or quite often the database client picks up the correct language from OS locale.

Small example to demonstrate this (I use Oracle Database 11.1.0.7 for these examples):

create table t (
  t varchar2(100) not null
);

insert into t values ('a');
insert into t values ('ä');
insert into t values ('o');
insert into t values ('õ');
commit;

SQL> alter session set nls_language='estonian';

Session altered.

SQL> select t from t order by t;

T
---------
a
o
õ
ä

SQL> alter session set nls_language='german';

Session altered.

SQL> select t from t order by t;

T
---------
a
ä
o
õ

But what happens if I create an index to speed up the sorting?

SQL> truncate table t;

SQL> create index idxt on t(t);

SQL> 
declare
  s t.t%type;
begin
  for i in 1..100000 loop
    s:= case mod(i,4) 
          when 0 then 'a'
          when 1 then 'ä'
          when 2 then 'o'
          when 3 then 'õ'
        end;
    if mod(i,3) = 0 then
      s:= upper(s);
    end if;
    s:= s||dbms_random.string('x',50);
    insert into t values (s);
  end loop;
  commit;
end;
/
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);

PL/SQL procedure successfully completed.

SQL> alter session set nls_language='estonian';

Session altered.

SQL> select t from t order by t;

100000 rows selected.

Elapsed: 00:00:02.89

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100K|  5175K|       |  1482   (2)| 00:00:18 |
|   1 |  SORT ORDER BY     |      |   100K|  5175K|  5896K|  1482   (2)| 00:00:18 |
|   2 |   TABLE ACCESS FULL| T    |   100K|  5175K|       |   162   (2)| 00:00:02 |
-----------------------------------------------------------------------------------

SQL> alter session set nls_language='american';

Session altered.

SQL> select t from t order by t;

100000 rows selected.

Elapsed: 00:00:01.56

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   100K|  5175K|   895   (1)| 00:00:11 |
|   1 |  INDEX FULL SCAN | IDXT |   100K|  5175K|   895   (1)| 00:00:11 |
-------------------------------------------------------------------------

First I did a query with Estonian language and then for comparison with American language. If you compare these two, the query in American language works as expected, it reads the correct sort order directly from index. But query in Estonian language requires extra sorting. This is also clearly demonstrated in the query elapsed times. Usually this is not a problem at all, but if there is a need for more performance, what if the data sets are really large and could benefit a sorting help from an index?

This is because normal indexes in oracle are always built with BINARY sort mode. This is the fastest sorting mode that orders the characters by their numerical value in the character set. But this is only good for the English language.

Here come the linguistic indexes.

SQL> create index idx_ling on t (nlssort(t, 'nls_sort=estonian'));

Index created.

SQL> alter session set nls_language='estonian';

Session altered.

SQL> select /*+index(t idx_ling)*/ t from t order by t;

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   100K|    14M|   101K  (1)| 00:20:20 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |   100K|    14M|   101K  (1)| 00:20:20 |
|   2 |   INDEX FULL SCAN           | IDX_LING |   100K|       |  1646   (1)| 00:00:20 |
----------------------------------------------------------------------------------------

So it works, there is no extra sorting step anymore. But it's useless in this case, because the index contains only the sorting value, not the actual column value and the slow TABLE ACCESS BY INDEX ROWID needs to be performed.

This isn't a problem when I do not need to return the actual value of column t and only need to do the sorting:

SQL> select 1 from t order by t;

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |   100K|    14M|  1646   (1)| 00:00:20 |
|   1 |  INDEX FULL SCAN | IDX_LING |   100K|    14M|  1646   (1)| 00:00:20 |
-----------------------------------------------------------------------------

Or I could include the actual column value in the index itself:

SQL> create index idx_ling2 on t (nlssort(t, 'nls_sort=estonian'), t);

SQL> select t from t order by t;

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |   100K|    14M|  2388   (1)| 00:00:29 |
|   1 |  INDEX FULL SCAN | IDX_LING2 |   100K|    14M|  2388   (1)| 00:00:29 |
------------------------------------------------------------------------------

If I change the language, the index is not used anymore.

SQL> drop index idxt;

SQL> alter session set nls_language='american';

Session altered.

SQL> select 1 from t order by t;

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100K|  5175K|       |  1482   (2)| 00:00:18 |
|   1 |  SORT ORDER BY     |      |   100K|  5175K|  5896K|  1482   (2)| 00:00:18 |
|   2 |   TABLE ACCESS FULL| T    |   100K|  5175K|       |   162   (2)| 00:00:02 |
-----------------------------------------------------------------------------------

The same linguistic index can also be used for searching, but for that NLS_COMP needs to be set to LINGUISTIC:

SQL> alter session set nls_language='estonian';

Session altered.

SQL> select t from t where t = 'x';

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    53 |   163   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    53 |   163   (3)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("T"='x')

SQL> alter session set nls_comp='linguistic';

Session altered.

SQL> select t from t where t = 'x';

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |   157 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_LING2 |     1 |   157 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access(NLSSORT("T",'nls_sort=''ESTONIAN''')=HEXTORAW('80000100')
              )

It is also possible to combine search and sorting:

SQL> select t from t where t between 'a' and 'b' order by t;

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |   250 | 39250 |    13   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_LING2 |   250 | 39250 |    13   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access(NLSSORT("T",'nls_sort=''ESTONIAN''')>=HEXTORAW('14000100')
                AND NLSSORT("T",'nls_sort=''ESTONIAN''')<=HEXTORAW('19000100') )

In conclusion, its possible to use index for sorting in non-English languages, but the solution is not very straight-forward and simple. But at the same time it is powerful, you can index the same column in different languages at the same time! But I'm not yet sure, that I like this FBI kind of solution by Oracle, because when doing SQL tuning I really like the possibility of reading the actual column value from index and not doing the always painfully slow TABLE ACCESS BY INDEX ROWID, if at all possible.
In one of the next posts I will take a look at other databases, how have they solved sorting according to national language rules.

More examples about Linguistic Indexes can be found from Richard Foote blog, he focuses on the case-insensitive search feature:
Introduction To Linguistic Indexes – Part I

Also take a look at Metalink note 227335.1:
Linguistic Sorting - Frequently Asked Questions

And of course... documentation:
Oracle® Database Globalization Support Guide - Linguistic Sorting and String Searching

APEX application running on "another" database

noreply@blogger.com (Ilmar Kerm) — Wed, 22 Jul 2009 09:55:00 +0000

I have attended some APEX presentations/seminars and one question seems to be repeating and got my attention. Can APEX run on another database?

Well, APEX itself or APEX application itself cannot run in any way on another database besides Oracle. APEX is PL/SQL application and no other database supports Oracle PL/SQL language.

But it's possible to select and modify data from another database from your APEX application.

First of all you need to set up a database link to the other database using Oracle Heterogeneous Services/Transparent Gateways. I'm not going to get into that right now, please use documentation/google for that.
I'm going to name my database link HSDB:

create database link hsdb 
  connect to "lowercaseusername" 
  identified by "randomCasepaSSword"
  using 'MYSQL_DB_SERVICE';

I'm going to use MySQL database and I'll create table employees on MySQL database:

CREATE TABLE `employees` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `personal_code` varchar(20) NOT NULL,
  `birthday` date DEFAULT NULL,
  `salary` decimal(10,2) NOT NULL,
  `is_active` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Test that table employees is accessible from Oracle:

select * from "employees"@hsdb;

NB! I created the table in MySQL with a lower case table name, but Oracle uses upper case identifiers by default, unless you force the correct case with double-quotes.

One other thing to look out for: check that ODBC/Oracle do not translate any of the columns to the deprecated LONG datatype. LONG columns seems to be used for long/unlimited text fields (*text in MySQL, text in PostgreSQL). You can check it by running DESC command from SQL*PLUS.

desc "employees"@hsdb
 Name                       Null?    Type
 -------------------------- -------- -----------------
 id                                  NUMBER(10)
 first_name                 NOT NULL VARCHAR2(100)
 last_name                  NOT NULL VARCHAR2(100)
 personal_code              NOT NULL VARCHAR2(20)
 birthday                   DATE
 salary                     NOT NULL NUMBER(10,2)
 is_active                  NOT NULL NUMBER(3)

Make sure, that none of the data types is LONG. If you have LONG column, then you need to modify the data type from the source database or create a "Oracle friendly" view on top of it.
In PostgreSQL ODBC driver its also possible to modify the maximum VARCHAR length before its mapped to LONG.
Anyway, this is the most critical part in my opinion. Make sure that you are happy with the data type mappings before continuing!

Now create an Oracle view on top that MySQL table. This view will translate all column names to upper case and will simplify writing the SQL code.

  CREATE VIEW EMPLOYEES_MYSQL AS 
  SELECT   "id" id,
            "first_name" first_name,
            "last_name" last_name,
            "personal_code" personal_code,
            "birthday" birthday,
            "salary" salary,
            "is_active" is_active
     FROM   "employees"@hsdb;

desc employees_mysql
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                               NUMBER(10)
 FIRST_NAME              NOT NULL VARCHAR2(300)
 LAST_NAME               NOT NULL VARCHAR2(300)
 PERSONAL_CODE           NOT NULL VARCHAR2(60)
 BIRTHDAY                DATE
 SALARY                  NOT NULL NUMBER(10,2)
 IS_ACTIVE               NOT NULL NUMBER(3)

Simple report

Creating a simple report is very easy, just use the created EMPLOYEES_MYSQL view in APEX like any other normal Oracle table/view.

Complex report

Writing vanilla-SQL against non-Oracle table over database link usually works, when the SQL is really simple. But there are pretty serious limitations and in some cases the performance "may not be very good" (indexes not used for some data type conversions).
To solve that problem, Oracle has a package DBMS_HS_PASSTHROUGH, that sends unmodified native SQL query to the linked database.

For this example I'll use the following native MySQL SQL statement for a report:

select sql_cache id, first_name, last_name from employees limit 10

To be able to use this result in a SQL statement, I'm going to use PIPELINED function and for that I first need to create TYPE, that will define the structure of the query output. And after that I'll create the actual function that will query the remote database.

create or replace type mysql_hstest_type as object (
  id number,
  first_name varchar2(250),
  last_name varchar2(250)
);
/

create or replace type tab_mysql_hstest_type is 
  table of mysql_hstest_type;
/

create or replace FUNCTION mysql_hstest_query 
  RETURN tab_mysql_hstest_type PIPELINED
IS
  p_row mysql_hstest_type:= mysql_hstest_type(null, null, null);
  p_c binary_integer;
BEGIN
  p_c:= DBMS_HS_PASSTHROUGH.OPEN_CURSOR@hsdb;
  DBMS_HS_PASSTHROUGH.PARSE@hsdb(p_c, 
    'select sql_cache id, first_name, last_name from employees limit 10');
  WHILE DBMS_HS_PASSTHROUGH.FETCH_ROW@hsdb(p_c) > 0 LOOP
    DBMS_HS_PASSTHROUGH.GET_VALUE@hsdb(p_c, 1, p_row.id);
    DBMS_HS_PASSTHROUGH.GET_VALUE@hsdb(p_c, 2, p_row.first_name);
    DBMS_HS_PASSTHROUGH.GET_VALUE@hsdb(p_c, 3, p_row.last_name);
    PIPE ROW(p_row);
  END LOOP;
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@hsdb(p_c);
  RETURN;
END;
/

Now, to use it in an APEX form just use the following SQL in report:

select * from table(mysql_hstest_query)

Form

First create a form on a view EMPLOYEES_MYSQL with the wizard like for any normal table. This will just create all the necessary page elements quickly.

Its necessary to write optimistic locking feature, because "Automatic Row Processing (DML)" process cannot be used and optimistic locking is a "silent" built in feature of that process. If for some weird reason you do not want the optimistic locking feature, then you can skip the checksum and validation steps.
For checksumming I'll create hashing function (this one needs execute privileges on DBMS_CRYPTO):

CREATE OR REPLACE FUNCTION form_md5_checksum(
  p1 varchar2 default null, p2 varchar2 default null,
  p3 varchar2 default null, p4 varchar2 default null,
  p5 varchar2 default null, p6 varchar2 default null,
  p7 varchar2 default null, p8 varchar2 default null,
  p9 varchar2 default null, p10 varchar2 default null)
RETURN varchar2 DETERMINISTIC IS
BEGIN
  RETURN rawtohex(DBMS_crypto.hash(UTL_RAW.CAST_TO_RAW(
    p1||'|'||p2||'|'||p3||'|'||p4||'|'||
    p5||'|'||p6||'|'||p7||'|'||p8||'|'||
    p9||'|'||p10), DBMS_CRYPTO.HASH_MD5));
END;
/

Now create a new hidden and protected item in the APEX page, I'll call it P3_CHECKSUM.
Then create a PL/SQL anonymous block process:

  Name: Calculate checksum
  Sequence: 20 (just after Automated Row Fetch)
  Process Point: On Load - After Header
  Process:

BEGIN
  :p3_checksum:= form_md5_checksum(
    :p3_ID, :p3_FIRST_NAME, :p3_LAST_NAME, :p3_PERSONAL_CODE, 
    :p3_BIRTHDAY, :p3_SALARY, :p3_IS_ACTIVE);
END;

Then I removed "Database Action" from the form buttons and changed the Button Name (the page submit REQUEST value):

Delete - DELETE
Apply Changes - UPDATE
Create - INSERT

The default "Automatic Row Processing (DML)" process cannot be used for saving the chages back to the database, because the ODBC database/driver lacks the support for SELECT FOR UPDATE. Because of it, delete the existing "Automatic Row Processing (DML)" process.

To save the changes, a new procedure is needed:

CREATE OR REPLACE PROCEDURE modify_employees_mysql(
  p_action IN VARCHAR2, p_row IN employees_mysql%rowtype,
  p_md5 varchar2)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  p_new_md5 varchar2(50);
  p_new_row employees_mysql%rowtype;
BEGIN
  -- Calculate checksum
  IF p_action IN ('UPDATE','DELETE') AND p_row.id IS NOT NULL THEN
    -- Lock the row
    UPDATE employees_mysql SET first_name = first_name
      WHERE id = p_row.id;
    -- Calculate new checksum
    SELECT * INTO p_new_row FROM employees_mysql WHERE id = p_row.id;
    p_new_md5:= form_md5_checksum(p_new_row.ID, p_new_row.FIRST_NAME,
      p_new_row.LAST_NAME, p_new_row.PERSONAL_CODE, 
      p_new_row.BIRTHDAY, p_new_row.SALARY, p_new_row.IS_ACTIVE);
    -- Check if the checksum has changed
    IF NVL(p_new_md5, '-') <> NVL(p_md5, '-') THEN
      ROLLBACK;
      raise_application_error(-20000, 'Data has changed');
    END IF;
    --
  END IF;
  -- Do the data modifications
  IF p_action = 'INSERT' THEN
    INSERT INTO employees_mysql VALUES p_row;
  ELSIF p_action = 'UPDATE' AND p_row.id IS NOT NULL THEN
    UPDATE employees_mysql SET ROW = p_row WHERE id = p_row.id;
  ELSIF p_action = 'DELETE' AND p_row.id IS NOT NULL THEN
    DELETE FROM employees_mysql WHERE id = p_row.id;
  ELSE
    raise_application_error(-20099, 'Invalid action.');
  END IF;
  commit;
END;
/

Note the "PRAGMA AUTONOMOUS_TRANSACTION" in the above code. I used the default open source MySQL ODBC driver that lacks the support for 2PC (Two Phase Commit). The symptom for this "ORA-02047: cannot join the distributed transaction in progress" when running the procedure inside APEX transaction.
If you are using some commercial ODBC driver with 2PC support or drivers supplied by Oracle HS or Oracle Transparent Gateways, then you don't need autonomous transaction for this procedure and you also need to remove commit/rollback statements from the procedure.

And finally put this procedure to the APEX page flow.
Create a new PL/SQL anonymous block process:

  Name: Save changes
  Sequence: 30
  Process Point: On Submit - After Computations and Validations
  Process:

DECLARE
  p_row employees_mysql%rowtype;
BEGIN
  p_row.id:= :P3_ID;
  p_row.first_name:= :P3_FIRST_NAME;
  p_row.last_name:= :P3_LAST_NAME;
  p_row.personal_code:= :P3_PERSONAL_CODE;
  p_row.birthday:= :P3_BIRTHDAY;
  p_row.salary:= :P3_SALARY;
  p_row.is_active:= :P3_IS_ACTIVE;
  modify_employees_mysql(:REQUEST, p_row, :p3_checksum);
END;

And now you have it - APEX running on another database :)

APEX instrumentation

noreply@blogger.com (Ilmar Kerm) — Tue, 21 Jul 2009 17:55:00 +0000

For every Oracle database session it is always good to set MODULE, ACTION and CLIENT_ID values for instrumentation. This allows DBA to see and debug in database level what the session is/was doing in detail. Oracle diagnostic tools are all powered up for using these values and a lot of diagnostic power is just lost, when the application is not instrumented.

For JDBC one can use end-to-end metrics support in JDBC driver
For PL/SQL one can use DBMS_APPLICATION_INFO subprograms

All this is just great, but what about APEX? I think it's the best database application development tool in the market today, but has it got this instrumentation already built in?
Yes it has!

SELECT   module,
         action,
         client_id
  FROM   v$active_session_history
 WHERE   module LIKE 'APEX%';

MODULE                  ACTION          CLIENT_ID
----------------------- --------------- ------------------------
APEX:APPLICATION 109    PAGE 7          ILMAR:2697049844839191
APEX:APPLICATION 109    PAGE 12         ILMAR:2697049844839191
APEX:APPLICATION 109    PAGE 6          ILMAR:2697049844839191

MODULE is set to the application number
ACTION contains the page number
CLIENT_ID constains username and session id

This example is from APEX 3.2 and Oracle 11.1.0.7 database.

You can read more about using Oracle diagnostic tools and session tracing from Doug Burns blog:
Session Level ASH Reports

UNPIVOT with multi-column groups in Oracle 11g

noreply@blogger.com (Ilmar Kerm) — Tue, 21 Jul 2009 08:05:00 +0000

Welcome to my first blog :) And I'll start with something simple, but very cool at the same time.

I really like Oracle 11g database and one of my favourite features is the PIVOT/UNPIVOT clause in SQL. PIVOT is used to present rows as columns and UNPIVOT is the opposite operation, columns are presented as rows. I have found them very useful for APEX applications, when I need to present data in table rows as columns in an APEX form for user input.

PIVOT feature seems to have recieved a lot of bloggers attention, but I haven't read much about UNPIVOT. Using UNPIVOT with one column is pretty straight forward and easy, just a small example (I use Oracle Database 11.1.0.7 Enterprise Edition for all the examples here):

create table unpivot_ex1 (
 id number primary key,
 jan number,
 feb number,
 mar number,
 apr number
);

Fill it up with test data:

insert into unpivot_ex1 (id, jan, feb, mar, apr)
 values (1, 1, 2, null, 4);
insert into unpivot_ex1 (id, jan, feb, mar, apr)
 values (2, 5, 6, null, 8);
insert into unpivot_ex1 (id, jan, feb, mar, apr)
 values (3, 9, 10, null, null);
commit;

SELECT * FROM unpivot_ex1;

    ID        JAN        FEB        MAR        APR
---------- ---------- ---------- ---------- ----------
     1          1          2                     4
     2          5          6                     8
     3          9         10

Now, if you want to select JAN, FEB, MAR, APR columns as separate rows, then its possible to use the simplest UNPIVOT clause:

SELECT * FROM unpivot_ex1 
  UNPIVOT INCLUDE NULLS
    (monthly_value FOR month IN (jan, feb, mar, apr));

        ID MON MONTHLY_VALUE
---------- --- -------------
         1 JAN             1
         1 FEB             2
         1 MAR
         1 APR             4
         2 JAN             5
         2 FEB             6
         2 MAR
         2 APR             8
         3 JAN             9
         3 FEB            10
         3 MAR
         3 APR

12 rows selected.

Now, lets take a more interesting case. What if some of the columns are combined into logical groups and you want to preserve them in one row? For example:

create table unpivot_ex2 (
  id number primary key,
  jan_income number,
  jan_expense number,
  feb_income number,
  feb_expense number,
  mar_income number,
  mar_expense number
);

For every month there is income and expense column.

insert into unpivot_ex2 values (1, 1, 1, 2, 3, 5, 2);
insert into unpivot_ex2 values (2, 5, 6, 4, 4, null, null);
commit;

SELECT * FROM unpivot_ex2;

        ID JAN_INCOME JAN_EXPENSE FEB_INCOME FEB_EXPENSE MAR_INCOME MAR_EXPENSE
---------- ---------- ----------- ---------- ----------- ---------- -----------
         1          1           1          2           3          5           2
         2          5           6          4           4

UNPIVOT allows to create column groups, like this:

SELECT * FROM unpivot_ex2
  UNPIVOT INCLUDE NULLS
    ((income, expense) FOR month IN (
     (jan_income, jan_expense), (feb_income, feb_expense), 
     (mar_income, mar_expense)));

        ID MONTH                      INCOME    EXPENSE
---------- ---------------------- ---------- ----------
         1 JAN_INCOME_JAN_EXPENSE          1          1
         1 FEB_INCOME_FEB_EXPENSE          2          3
         1 MAR_INCOME_MAR_EXPENSE          5          2
         2 JAN_INCOME_JAN_EXPENSE          5          6
         2 FEB_INCOME_FEB_EXPENSE          4          4
         2 MAR_INCOME_MAR_EXPENSE

6 rows selected.

How cool is that? :)