Influencing the DB2 Optimizer: Part 4 – OPTIMIZE FOR n ROWS

In the first installments of this series of posts on ways to influence the DB2 optimizer we looked at standard methods like RUNSTATS and SQL tweaking techniques. Today, we will look at yet another method of influencing access path selection: specifying the OPTIMIZE FOR n ROWS for a SELECTstatement. 

This clause enables programmers to specify the estimated maximum number of rows that will be retrieved. By indicating that a different number (more...)

Apache Drill : How to Create a New Function?

Read this article on my new blog Apache Drill allows users to explore any type of data using ANSI SQL. This is great, but Drill goes even further than that and allows you to create custom functions to extend the query engine. These custom functions have all the performance of any of the Drill primitive operations, but allowing that performance makes writing these functions a little trickier

Use DB Vault to protect password strength policy

Suppose your organization wants to enforce a security policy on database password strength. The DBA’s have implemented a password strength verification function in PLSQL such as the oracle supplied ora12c_strong_verify_function in the DEFAULT profile of the database. There seems no way to get around it at first:

Database account u4 is created:

 

create-user-u4

 

U4 logs in and tries to keep it simple, i.e. the password:

 

u4-cannot-simplify-password

 

That password verification function got in (more...)

Object Type with Optional Attribute: Extra Constructor Function

When you have to create stored procedures which need to be called from an Oracle Service Bus, the most covenient way (at least for the one creating the mapping between the incoming message and the stored procedure) is to use Object Types.
The "downside" is that you might need lots of Object Types and Nested Table Types to get the right structure.
If you are unfamiliair with this technique, there are some links at the (more...)

Influencing the DB2 Optimizer: Part 3 – Tweaking SQL Statements

In Part 2 of this series we took a look at standard methods of influencing the DB2 optimizer, essentially boiling down to running RUNSTATS, reorganizing, encouraging parallelism and indexing.

But sometimes you may not want to change the DB2 Catalog statistics (or use hints, which we will cover later in this series). In such situations, you might consider tweaking the offending SQL statement. Tweaking is the process of changing a statement in a non-intuitive fashion, (more...)

Discover how the Toad Editor and Hover Properties can make writing queries even easier!

So, after months of filling out IT request forms, multiple unanswered emails to a council of learn-ed database elders and then finally outright begging, you got a brand-spankin’ new user account giving you access to that important database, time to write some queries! 

But wait, you don’t know anything about the tables in this database.  How do you do this efficiently and intelligently?  With Toad Extension for Eclipse and Toad Mac Edition (more...)

Exploring dynamic pivot options

In looking for information on pivoting variable number of columns, I stumbled across a question I once managed to AskTom, many moons ago.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7644594042547

Adrian Billington has an interesting lead into an XML solution with pivot, but would need more digging to finalise conversation of XML data for APEX to use.
http://www.oracle-developer.net/display.php?id=506

Then I found Tom's answer using easy to understand dynamic SQL (properly asserted, no less)
https://asktom. (more...)

ORDER BY CASE

Sometimes I give parts of a solution to increase the play time to solve a problem. I didn’t anticipate a problem when showing how to perform a sort operation with a CASE statement. It’s a sweet solution when you need to sort something differently than a traditional ascending or descending sort.

I gave my students this ORDER BY clause as an example:

  CASE
    WHEN filter = 'Debit' THEN 1
    WHEN filter = 'Credit' THEN 2
     (more...)

Influencing the DB2 Optimizer: Part 2 – Standard Methods

Last week, in Part 1 of this series, we introduced the concept and the 5 high-level methods of influencing the DB2 optimizer's access path choices. In today's post, we will tackle the first approach, which is using standard, DB2-based methods.

Of all the methods for influencing the DB2 optimizer, standard DB2 methods are the only mandatory ones. Try all the standard methods covered in this section before attempting any of the other methods (that we (more...)

Longer names coming to Oracle?

Have you ever, like me, been silently finger-counting the number of characters in a table name, column name or procedure name that you are about to create in the Oracle Database, to make sure it is short enough? It appears those days will soon (?) be at an end...

The current limit on names (identifiers such as table names, column names, procedure names, parameter names, etc) in Oracle SQL and PL/SQL is 30 characters. (more...)

ALL or ANY in SQL

The other day I was studying a SQL statement that was performing poorly, when I noticed a peculiar syntax that I have not come across before :

SELECT COUNT(*)
FROM   customer
WHERE  annual_spend > ALL (
  SELECT spend_threshold
  FROM   promotion
  WHERE  promo_name = 'Summer 2015'
);

The only time I’d ever come across the keyword ALL in SQL was in UNION ALL. However, a quick check of the manual led me to the Group Comparison (more...)

Query existing HBase tables with SQL using Apache Phoenix

Spending a bit more time with Apache Phoenix in my previous post I realised that you can use it to query existing HBase tables. That is NOT tables created using Apache Phoenix, but HBase - the columnar NoSQL database in Hadoop.

I think this is cool as it gives you the ability to use SQL on an HBase table.

To test this, let's say you login to HBase and you create an HBase table like (more...)

Splitting a Table into Rowid Ranges of Equal Size

In the OTN forum, Jonathan Lewis recently asked for an efficient SQL solution to split a table into 12 ROWID ranges having the same (+-1) number of blocks. I'm posting here a slightly cleaned-up version of my answer so I can change it if necessary after the question gets archived.

Apache Phoenix, SQL is getting closer to Big Data



Here is a post about another project in the Big Data world, like Apache Hive from my previous post, enables you to do SQL on Big Data. It is called Apache Phoenix.

Phoenix is a bit different, a bit closer to my heart too, as I read the documentation on Apache Phoenix, the word 'algebra' and 'relational algebra' came across few times, and that mean only one thing, SQL! The use of (more...)

Hive (HiveQL) SQL for Hadoop Big Data



In this  post I will share my experience with an Apache Hadoop component called Hive which enables you to do SQL on an Apache Hadoop Big Data cluster.

Being a great fun of SQL and relational databases, this was my opportunity to set up a mechanism where I could transfer some (a lot)  data from a relational database into Hadoop and query it with SQL. Not a very difficult thing to do these days, actually (more...)

MATCH_RECOGNIZE: gaps in date ranges

To find gaps between date ranges, there is an elegant solution using analytic functions. We can follow the same logic with MATCH_RECOGNIZE, using a technique that avoids the problems with MIN() and MAX() comparisons I just wrote about.

MATCH_RECOGNIZE: matching based on aggregates

The heart of row pattern matching is finding which row matches what part of the pattern. Within the 12c MATCH_RECOGNIZE clause, DEFINE lists the conditions a row may meet; it doesn't always work the way you expect, especially if you use aggregates in the condition.

Mac SQL Developer Install

This how you install SQL Developer on Mac OS Yosemite. The first thing you need to do is download and install Java 8, not Java 7 on your Mac OS Yosemite, as suggested on some web sites. You can determine whether or not Java is installed by running the following command:

Mac-Pro-3:~ username$ java -version
No Java runtime present, requesting install.

You must accept the Java license to install Java 8 on the (more...)

Deadlock with a Virtual Column

Virtual Columns are really cool. I like them a lot. If you've never heard of them, shame on you, learn about them.
In short: a Virtual Column is not a real column, it's an expression that looks like a column... more or less.
While using the Virtual Columns, we ran into a little oddity with them.

First of all let's start with the version of the database that I tested this on. Yes, I know (more...)

Merging Overlapping Date Ranges with MATCH_RECOGNIZE

I forgot to add a MATCH_RECOGNIZE solution to my last post on merging overlapping date ranges! That should take me just a few minutes, right? Wrong: it’s not that easy and here’s why. For test data, please refer to the previous post. To Merge or not to Merge? The idea is to merge date ranges if […]