Greatly Improved SQL Index Performance for Oracle Berkeley Database, Version 6.2

A new whitepaper has been published, "Greatly Improved SQL Index Performance for Oracle Berkeley Database, Version 6.2". Learn how to increase the performance of SQL Queries using BDB. Go to: http://www.oracle.com/technetwork/database/database-technologies/berkeleydb/learnmore/index.html   ... [Read More]

SQL Pattern Matching Deep Dive – Part 3, greedy vs. reluctant quantifiers

Picture courtesy of Pixabay Welcome to the third post in this deep-dive series on SQL pattern matching using the MATCH_RECOGNIZE feature that is part of Database 12c.  In the first part of this series we looked at a wide range of topics including ensuring query consistency, how to...... [Read More]

Video : Flashback Version Query

Today’s video gives a quick run through of flashback version query.

If you prefer to read articles, rather than watch videos, you might be interested in these.

The star of today’s video is Tanel Poder. I was filming some other people, (more...)

ANSI SQL with Analytic Functions on Snowflake DB

Here is another installment of my Top 10 blog list of cool features of Snowflake Elastic Data Warehouse: At Snowflake, we believe that it should be easy to access, query, and derive insights from your data. To support that, we provide our users with the ability to query all their data using ANSI compliant SQL […]

Common Distribution Methods in Parallel Execution

Parallel execution uses the producer/consumer model when executing a SQL statement. The execution plan is divided up into DFOs, each DFO is executed by a PX server set. Data is sent from one PX server set (producer) to another PX server set (consumer) using different types of distribution methods. In this post we... [Read More]

Parse JSON Array in SQL and PL/SQL – turn to a Nested Table

Transferring data between technologies and application tiers is done using various formats – binary, native on the one hand and open, text based such as CSV, XML and JSON on the other. Use of JSON is rapidly growing as a growing number of platforms and technologies provides support for JSON.

I recently was working on a Node.js application that exposed a REST API to HTTP consumers. The consumers could send POST requests with a (more...)

Side-effect of ALTER SESSION SET CURRENT_SCHEMA

For most of our database set-ups we use a different TEMP space for application users than for end-user/support/developer/reporting usage.
The intention is to minimise the potential impact of a rogue ad-hoc query on the main applicaiton.

However, turns out this is ineffective IF you use:

ALTER SESSION SET CURRENT_SCHEMA = main_app_schema;

This is documented but I wasn’t previously aware.
No surprise that this learning opportunity was presented as a direct result of a rogue query (more...)

Who Rewrote My SQL?

There are several new features in Oracle 12c that are implemented under the hood by changing the SQL statement that we write to a different statement (e.g., by adding some hidden predicates).
In OUG Ireland 2016 I talked about two such features – In Database Archiving and Temporal Validity – as part of my “Write Less (Code) with More (Oracle12c New Features)” presentation. I usually talk about another such feature in this presentation (more...)

Mustererkennung mit MATCH_RECOGNIZE in 12c

Keith Laker hat im Data Warehouse Insider Blog eine Artikelserie begonnen, die sich mit der Verwendung des MATCH_RECOGNIZE Features in 12c beschäftigt. Ich will jetzt nicht behaupten, dass ich die Artikel umfassend wiedergeben würde (ja nicht einmal, dass ich sie komplett gelesen hätte), aber sie wären sicherlich ein sehr nützlicher Einstieg, sollte ich gelegentlich auf die Idee kommen, das Feature irgendwo einzusetzen. In erster Näherung greife ich hier aber nur die Zusammanfassungs-Abschnitte am jeweiligen Artikelende (more...)

Gear up for #AIOUG OTN Yathra’ 2016

Guys, AIOUG is back again with OTN Yathra’ 2016. It is a series of technology evangelist events organized by All India Oracle Users Group in six cities touring across the length and breadth of the country. It was my extreme pleasure to be the part of it in 2015 and I’m pleased to announce that … Continue reading

The First and Last Word on NoSQL

Amazon had the first word on NoSQL but the creator of relational theory, Dr. E. F. Codd, gets the last word.(read more)

Auto DDL: delete obsolete columns from table

A quick one. In the past I used to generate ddl based on queries, like the following. But I find myself to re-invent them again. So to have it saved for my offspring: here's one on deleting obsolete columns as generated on importing an excel sheet in SQLDeveloper:


declare
l_schema_name varchar2(30) := 'MY_SCHEMA';
l_table_name varchar2(30) := 'A_TABLE';
cursor c_cols is
select column_name
from all_tab_columns col
where col.table_name = l_table_name
and col.owner = l_schema_name
(more...)

Temporal validity, multiple end dates

Recently I got involved in a question on Temporal Validity Periods together with Chris Saxon, one of the askTom-answer team.

The question was along the lines of: "What if I have a single start date but two possible end dates. One of the end dates is filled automatically by a background proces (could be a job) while the other one is to signal that the end date is set manually by the user. Could you (more...)

Simple Unpivot

I came across the need for an UNPIVOT today that require fairly basic syntax, so this is me noting it for later. A single column unpivot, not multiple.

I had a discrete set of values in local variables that I wanted to use within a merge, so I selected them from dual. Here is a literal representations
SELECT 'SCOTT' login_id 
,'X' alpha, 'Y' beta
,10 catgy1
,20 catgy2
,30 catgy3
,40 catgy4
,50 catgy5
,60 (more...)

WINDOW NOSORT STOPKEY + RANK()

Recently I found that WINDOW NOSORT STOPKEY with RANK()OVER() works very inefficiently: http://www.freelists.org/post/oracle-l/RANKWINDOW-NOSORT-STOPKEY-stopkey-doesnt-work
The root cause of this behaviour is that Oracle optimizes WINDOW NOSORT STOPKEY with RANK the same way as with DENSE_RANK:

rnk1
create table test(n not null) as 
  with gen as (select level n from dual connect by level<=100)
  select g2.n as n
  from gen g1, gen g2
  where g1.n<=10
/
create index ix_test on test(n)
/
exec  (more...)

Implementing Arc Relationships with Virtual Columns

Virtual columns were added in Oracle 11g Release 1. We can implement the same concept using views, but one of the advantages that I see in virtual columns is that we can define foreign key constraints on them. Well, we can define foreign key constraints on views as well, but only in DISABLE NOVALIDATE mode, which makes them a decoration rather than a data integrity protector. So to be more precise, we can define enabled (more...)

Renaming #EM12c / #EM13c Targets

Oracle Enterprise Manager is a complex piece of software that many organizations are running now. Some organizations set out with a formalized naming standard; some do not. Those who do not ofter end up identifying a naming standard later down the road and then making requests to change the names of the targets being monitored. In order to do this, there are two ways:

1. Delete and rediscover the target and rename at time of (more...)

SQL is Huuuuuuuge (Making SQL Great Again) Part III

Lightly-edited partial transcript of a panel discussion titled “Making SQL Great Again (SQL is Huuuuuge)” at YesSQL Summit 2016 organized by the Northern California Oracle Users Group (NoCOUG) at Oracle Corporation’s headquarters in Redwood City, California. NoCOUG is the longest-running and most-active Oracle users group in the world. An individual membership only costs $95 and entitles the member to free admission to the four consecutive quarterly NoCOUG conferences (one-day events) that follow the membership’s start (more...)

SQL is Huuuuuuuge (Making SQL Great Again) Part II

Lightly-edited partial transcript of a panel discussion titled “Making SQL Great Again (SQL is Huuuuuge)” at YesSQL Summit 2016 organized by the Northern California Oracle Users Group (NoCOUG) at Oracle Corporation’s headquarters in Redwood City, California. NoCOUG is the longest-running and most-active Oracle users group in the world. An individual membership only costs $95 and entitles the member to free admission to the four consecutive quarterly NoCOUG conferences (one-day events) that follow the membership’s start (more...)

Making SQL Great Again (SQL is Huuuuuge) Part VI

Lightly-edited partial transcript of a panel discussion titled “Making SQL Great Again (SQL is Huuuuuge)” at YesSQL Summit 2016 organized by the Northern California Oracle Users Group (NoCOUG) at Oracle Corporation’s headquarters in Redwood City, California. NoCOUG is the longest-running and most-active Oracle users group in the world. An individual membership only costs $95 and entitles the member to free admission to the four consecutive quarterly NoCOUG conferences (one-day events) that follow the membership’s start (more...)