Monthly Archives: February 2009

SOUG-Tagung 09/2

I am impatient to speak in the swiss oracle user group next month

March 18, 2009, Baden-Daettwil
http://www.soug.ch

I will be presenting an XML session regarding selecting XML from the database.

select xmlserialize(document xmlcol) from agenda;


12:00	Empfang und Registrierung		SOUG Sekretariat
12:30	Stehlunch im Foyer Seminarraum Schottky
13:00	21. Ordentliche Generalversammlung der SOUG, Seminarraum Schottky Ehrung des SOUG Speaker of the Year 2008, Seminarraum Schottky
	Track 1: Oracle DB und Entwicklung Moderation Manfred Drozd, In&Out AG		Track 2: Oracle Applications Moderation Michele Savino, FI-Solutions AG
14:00	XML in the Oracle Database Use SQL to retrieve and insert XML data in the database. Learn about the SQL functions, the XMLTYPE and various techniques to improve your performance when dealing with XML documents.	Laurent Schneider, Credit Suisse	Oracle Applications Portfolio Strategie, Mehrwert und Eco System Durch die Akquisition von über 50 Firmen hat sich Oracle vom Technologie Leader zum Partner für Business Applikationen entwickelt. Dank seiner kompletten, offenen und integrierten Plattform, ermöglicht Oracle eine Reduktion der Integrationskosten. Industriespezifische Blueprints ermöglichen den Kunden eine effiziente Geschäftsprozess Modellierung	Beat Brunschwiler, Oracle
14:45	Optimiertes Backup mit RMAN und der LiteSpeed Engine für Oracle Der Vortrag gibt zunächst einmal einen Überblick über die unterschiedlichen Backupverfahren (RMAN, Hotbackup, Coldbackup) und die damit verbundenen Vor- und Nachteile. Am Beispiel von RMAN mit LiteSpeed Engine for Oracle wird dann erklärt, wie ein Backup optimiert werden kann und welche Auswirkungen dies auf ein Recovery hat.	Johannes Ahrends, Quest Software	Oracle Application Integrations-Architektur Ein grosser Teil des Implementierungsaufwandes wird in IT Projekten für die Integration von Applikations-Silos investiert. Dadurch erschwert sich die Anspassung von neuen Geschäfts-anforderungen. Die Applikation Integration Plattform standardisiert die Integration, vom Geschäftsprozess bis zum Daten-Layer. Innovation kann dadurch schnell umgesetzt werden	Jean-Claude Haupfleisch, Oracle
15:30	Pause (ca. 30′)
16:00	SQL Plan Baseline Diese Technologie ersetzt ab Oracle 11g die früheren Stored Outlines.	Chris Antognini, Trivadis	Siebel CRM Referenz Projekt Beispiel einer Siebel CRM Implementierung bei einem Schweizer Versicherer.	Lukas Ehrensperger, ec4u
16:45	Apéro und Informationsaustausch - Wir danken dem Sponsor Quest

date or timestamp literals

3 Replies

Yesterday I commented on a post on the French developer forums http://www.developpez.net about using DATE and TIMESTAMP literals.

A date literal is DATE '2000-01-01'. It is not possible to have a date literal at 6am, like DATE '2000-01-01 06:00:00', the syntax simply does not exist. You can either use TO_DATE function, or if you are really hooked on litterals, maybe DATE '2000-01-01'+INTERVAL '+0 06:00:00' DAY TO SECOND.

One suggestion was to use TIMESTAMP, because TIMESTAMP literals do have time in it.

However, comparing a date with a timestamp will result in an implicit conversion to timestamp, and if the date colum is indexed, the index will not be used.

SQL> create table lsc_t(x number, d date)
Table created.
SQL> create index lsc_i on lsc_t(d)
Index created.
SQL> insert into lsc_t(d) values (to_date('2000-01-01 06:00:00',
  'YYYY-MM-DD HH24:MI:SS'))
1 row created.
SQL> select * from lsc_t where d>=timestamp '2000-01-01 06:00:00'
         X D
---------- -------------------
           2000-01-01 06:00:00
Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=CHOOSE
   1    0    TABLE ACCESS FULL TGPOWNER.LSC_T
SQL> select * from lsc_t where d>=to_date('2000-01-01 06:00:00',
  'YYYY-MM-DD HH24:MI:SS')
         X D
---------- -------------------
           2000-01-01 06:00:00

Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=CHOOSE
   1    0    TABLE ACCESS BY INDEX ROWID TGPOWNER.LSC_T
   2    1      INDEX RANGE SCAN TGPOWNER.LSC_I

As shown above, the index is not used when comparing to timestamp.

Why does Oracle convert the date to timestamp ? Because timestamp may contain nanoseconds.

SQL> select * from lsc_t where 
  d>=timestamp '2000-01-01 06:00:00.000000001';

no rows selected

SQL> select * from lsc_t where 
  cast(d as timestamp)>=timestamp '2000-01-01 06:00:00.000000001';

no rows selected

SQL> select * from lsc_t where 
  d>=cast(timestamp '2000-01-01 06:00:00.000000001' as date);

         X D
---------- -------------------
           2000-01-01 06:00:00

Converting to date would deliver wrong result.

Tuning query over database link

4 Replies

I just learnt from a colleague a very useful hint for remote databases. Over a db link, Oracle does not have the necessary statistics to take the right decision.

Here is the test case :
DB01


create database link lsc_link_2 using 'DB02';
create table lsc_small_1 as 
  select rownum x from dual connect by level<100;
alter table lsc_small_1 add primary key(x);
create table lsc_big_1 as 
  select rownum x from dual connect by level<1000000;
alter table lsc_big_1 add primary key(x);

DB02


create table lsc_small_2 as 
  select rownum x from dual connect by level<100;
alter table lsc_small_2 add primary key(x);
create table lsc_big_2 as 
  select rownum x from dual connect by level<1000000;
alter table lsc_big_2 add primary key(x);

and here are the two test queries (run on DB01)


select count(*) from lsc_big_1 b1 
  join lsc_small_2@lsc_link_2 s2 using (x);
  COUNT(*)
----------
        99
Elapsed: 00:00:00.10

select count(*) from lsc_big_2@lsc_link_2 b2 
  join lsc_small_1 s1 using (x);
  COUNT(*)
----------
        99
Elapsed: 00:00:10.31

As shown above, first one is 100x faster. Anything to tune? For sure!

The trick is to execute the join remotely when the remote table is much larger than the local one!


select /*+ DRIVING_SITE(b2) */ count(*) 
  from lsc_big_2@lsc_link_2 b2 
  join lsc_small_1 s1 using (x);
  COUNT(*)
----------
        99
Elapsed: 00:00:00.06

Ref: the DRIVING_SITE hint