Oracle Instant Client 10.1.0.2
I have a PC that is primarily a Linux box, but it also has a small Windows 2000 partition to allow dual boot for some specific apps. The limited disk space available was all the encouragement I needed to test out the Oracle Instant Client in order to provide Oracle Database connectivity when running under Windows.
The installation is straight-forward: just download and unzip the freely available kit. The FAQ provides some basic guidance, and I must admit that's all the documentation I referred to. However that's also one of my minor gripes .. its not easy to find any documentation beyond the few blurbs you get when downloading (I didn't search very hard though!).
Nevertheless, I'm up and running 15 minutes later. Once downloaded, all I needed to do was this:
- Add the Instant client directory to the PATH
- I put a tnsnames.ora file on the machine to help with naming, and set TNS_ADMIN environment variable to point to this file
- Set the NLS_LANG environment variable (e.g. "set NLS_LANG=AMERICAN_AMERICA.UTF8"). A must, else you will get an ORA-12705 error.
Refs:
Download Instant Client
Instant Client FAQ
read more and comment..
Ucase names on Win2k partition going lcase under Linux
Hmm, mounted a Windows 2000 FAT32 partition under linux, but I find that any 8.3 name that is all uppercase on windows is appearing as all lowercase under Linux. Mixed case names are handled properly. Not a major issue until you try and do something like point gcvs at a cvs repository on the vfat partition .. and it can't find the "CVSROOT" directory (since it appreas as "cvsroot").
There are some references on the web that indicate the "check=s" vfat mount option might help, but that does not do the trick for me. A bit of testing later, and what I can report is that the "shortname=winnt" is what is needed. For example:[root@home #] mount -t vfat -o ro,shortname=winnt /dev/hda1 /mnt/win2k
[root@home #] ls -l /mnt/win2k/MyCVS
total 56
drwxr-xr-x 3 root root 8192 Jun 5 2003 CVSROOT
drwxr-xr-x 11 root root 8192 Sep 6 2003 MyConfig
drwxr-xr-x 35 root root 8192 Jun 5 2003 MyDev
drwxr-xr-x 26 root root 8192 Oct 9 2003 Testers
Ref
Using VFAT
read more and comment..
Using functional indexes to improve XMLTYPE query performance
Oracle XDB provides excellent facilities for handling XML data within the database. One aspect is the use of XmlType columns to store XML documents as-is within a table. XmlType object methods provide the means to extract and manipulate the XML doc in PL/SQL, in particular we can use XPath queries to extract data from the XML doc as part of a SELECT expression.
For example, lets take a simple "XML document Registry" table:
CREATE TABLE myxml (doc_id NUMBER PRIMARY KEY,xmldoc XMLTYPE);
And now fill it with lots (say 10000) rows, with XML documents that have the following structure:
<document>
<Author>
<last_name>Ng</last_name>
<first_name>Francis</first_name>
</Author>
<body>the body of the document</body>
</document>
We can SELECT an element from the stored documents thus:
set timing on
set autotrace on explain
SELECT x.xmldoc.extract('/document/Author/last_name/text()').getStringVal() as last_name
FROM myxml x
WHERE x.xmldoc.extract('/document/Author/last_name/text()').getStringVal() like '%JON%';
[ ... output (3 rows in my case) ... ]
Elapsed: 00:00:18.45
Execution Plan
---------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE
TABLE ACCESS (FULL) OF 'MYXML'
This is wonderful from an application developer's perspective, but as you can see the performance can be horrendous .. 18 seconds to select 3 rows out of 10000 (just imagine all table scanning and xml parsing going on under the covers). But relief is available in the form of functional indexes. For the previous query we could define an index thus:
CREATE INDEX myxml_last_name_idx ON myxml(xmldoc.extract('/document/Author/last_name/text()').getStringVal());
Under 10g, we immediately see a drammatic improvement:
[ ... output (3 rows in my case) ... ]
Elapsed: 00:00:00.22
But under 9i, things initially don't change at all. We need to add an index hint before the optimiser will properly choose to use the index and deliver stunning performance:
SELECT /*+ index(x myxml_last_name_idx) */ x.xmldoc.extract('/document/Author/last_name/text()').getStringVal() as last_name
FROM myxml x
WHERE x.xmldoc.extract('/document/Author/last_name/text()').getStringVal() like '%JON%';
Elapsed: 00:00:00.12
Execution Plan
---------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2054 Bytes=4112108)
INDEX (RANGE SCAN) OF 'MYXML_LAST_NAME_IDX' (NON-UNIQUE) (Cost=2 Card=2054 Bytes=4112108)
{in this example, note that you must use the table alias (x) in the index hint. If you use the table name, the optimiser won't get the hint}.
On metalink and the mailing lists you'll find a number of postings concerning performance of XML queries under 9i. It seems that there have been great improvements in the optimiser in 10g. As demonstrated you can solve many of the performance issues under 9i, but it may take a bit of manual tuning.
Refs:
Note:191106.1 How to use Index on XML datatype column
read more and comment..
Red Hat Linux and Oracle 10g installation
Just installed Oracle 10.1.0.2 on my RHEL 3 machine.
I used Werner Puschitz' installation guide, which I found very useful in conjunction with the Oracle Database Quick Installation Guide 10g for Linux
read more and comment..