ODTUG 2006 Conference Handouts/Presentations available

Lucas Jellema

Last night, the ODTUG 2006 Conference Committee announced that all handouts and presentations from this year’s (well, in fact that is last week’s) conference are available for download from the ODTUG website at http://www.odtug.com. Until August 1st, all material can be downloaded by the general public. After that date, you […]

The minimum number of statements required for Inserting Records from Two Source Tables into Four Target Tables – Introducing Multi Table Insert

Lucas Jellema 2

It's Showtime! Today at ODTUG 2006, Alex and I are presenting our Oracle Quiz on SQL and PL/SQL – the water is still burning. We will present our audience with 15 questions on SQL and PL/SQL features and functions. Even though most questions are on core database features, many of […]

Cost Based Optimizing through time travel? – the value of meta-data for enabling Query Rewrite

Lucas Jellema 5

Query Rewrite is an increasingly more important feature of the Oracle database. Linked intimately to Materialized Views, Query Rewrite is used by the CBO to make queries leverage intermediate results – often aggregate values or pre-joined records – available in Materialized Views when executing a query, thus preventing the need […]

Database Systems 2006 – Conference on Service Oriented Architectures

Lucas Jellema

Today I attended – and presented at – the Database Systems 2006 conference – an annual Dutch conference on Database Architecture and Technology, Enterprise Integration and Service Oriented Architectures as well as Information Architecture and Business Intelligence. The main theme – at least in the sessions I attended – was […]

Summary results for all dates, including the ones that I do not have data for – example of using Partition Outer Join – Oracle10g SQL Feature

Lucas Jellema

Trying to make myself useful by answering some of the questions on the OTN SQL and PL/SQL Forum, I came across this question: Group Above Report problem…not able to display absent rows in date range. The question could be translated to: I am trying to aggregate records by date and […]

Bizgres 0.7 Released – Open Source platform for Business Intelligence and ETL

Lucas Jellema

I just read in a Dutch IT magazine (Computable) about the release of Bizgres 0.7. Bizgres is an open source project: The Bizgres Project (www.bizgres.org), a Greenplum-sponsored, community-supported, open source project with the goal of building a complete platform for Business Intelligence (BI) exclusively from free software, announced today new […]

Oracle Warehouse Builder 10g Paris Release … Oh ja, ook voor Data Warehouses! (article on the OWB 10gR2 Paris Release)

Lucas Jellema 1

This article is about a very interesting Oracle tool: Warehouse Builder. A tool that is much more versatile than its name suggests. OWB can be used for diverse activities as graphical Database Design and DDL generation, Data Profiling and Cleansing, Generation of PL/SQL code for loading and transforming data, generation […]

Introducing Materialized Views as mechanism for Business Rule implementation (complex declarative constraints)

Lucas Jellema 7

Put on the trail by Tom Kyte – and after many useful discussions with my colleague Andre, we will take a look in this article at implementing complex business rules declaratively using Materialized Views. A previous post – Declarative Implementation of Mandatory Master-Detail relationship (Order must have Order Lines) – […]

Paris for the Designer

Andre Crone 2

Today I looked into a pre release of Oracles Warehouse Builder 10gR2 (Paris). This comprehensive tool can be used to create complex data warehoused holding data from many different data sources like tables (in different databases), files etc. I was really pleased by the very good reverse engineering capabilities of […]

Pivoting in SQL using the 10g Model Clause

Lucas Jellema 4

In this post we discuss pivoting of records in SQL – either splitting or merging records. Typically this is done using PL/SQL based Table Functioins, UNION (for splitting records) and fairly complex, tricky SQL statements. In Oracle 10g, using the MODEL clause, there is a clean, fairly straightforward way to achieve elegant pivoting – even nested pivoting – in single SQL statements. This post shows the pivot operation using the Model clause.