SQL Analytics 101 – Break columns

SQL analytics can be used to generate break columns in your queries, without the need for break formatting attributes in APEX or the old fashioned break on option in SQL*Plus.

I came across an example recently where I wanted to apply the break formatting in my query to avoid extra sub-totals from being displayed after each break.
No sub-totals please
I could use jQuery to hide the rows instead of modifying them, but as (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN

On the Toad World site, I’m publishing a whole series of short blog posts on the subject of EXPLAIN PLAN. I’m actually using EXPLAIN PLAN as a central motif to teach not just SQL tuning but relational theory, logical database design, and physical database design. In a year’s time, I hope to have enough material for […]

Some things to share…

I have been awfully quiet on my blog lately. I think that is because I have been busy with other things, like my garden and stuff like that. There are some ‘techie’ thing I have done in the meantime, though,

Tech14-Ive_SubmittedI have submitted a couple of abstracts for Tech14. Hope at least one of them gets selected. I really like presenting and if it is in a different country, that is just a plus. That (more...)

Inserts on HCC tables

There are already a lot of blogposts and presentations done about Hybrid Columnar Compression and i am adding one more blogpost to that list. Recently i was doing some small tests one HCC and noticed that that inserts on a HCC row didn’t got compressed and yes i was using direct path loads:

DBA@TEST1> create table hcc_me (text1 varchar2(4000)) compress for archive high;

Table created.

KJJ@TEST1> insert /*+ append */ into hcc_me select dbms_random.string('x',100)  (more...)

SQLServer: date conversions

In my current project I need to query an MS SqlServer database.
Unfortunately the dates are stored as a BigInt instead of a proper date datatype.
So I had to find out how to do compare the dates with the systemdate, and how to get the system date. To log this for possible later use, as an exception, a blog about SqlServer.

To get the system date, you can do:

(SELECT dt=GETDATE()) a
It's maybe (more...)

Current_Schema and the Data Dictionary

Being a huge fan of Logger, the PL/SQL logging utility, I really wanted this be to included in the project that I'm currently working on. So I downloaded it (link at the bottom of this blog) and included it in our deployment scripts. Done.... at least I thought so, but of course this wasn't the case.

The regular install script for Logger looks something like the following (parts removed and table names are changed):


set (more...)

PL/SQL vs SQL

plsql_logoThere is a ‘rule’, I think it was created by Tom Kyte, stating: If you can do it in SQL, do it in SQL. I came across some code the other day that makes perfect sense to do then you are running an Oracle 10g (or earlier) instance. I rewrote the code to use only the EMP and DEPT tables to protect the suspects and maybe innocent.

The function defined is something like this:

Hierarchical XML from SQL

Years ago I wrote an article (in Dutch) on the XML functions in Oracle SQL. It can be found here.
It describes how to create an xml document as an XMLType with an Oracle SQL Query.

The query that is described is based on a pretty simple table, with no relationships. I'm creating a new course based on a datamodel we created years ago, that contains data. I wanted to abstract some of that data (more...)

Remote Surprise

Here is an example of surprising behaviour from a remote DB from an OTN forum thread

Setup a link to a remote DB (I’ve used an actual remote DB and not tested a loopback)

Remote DB:

create table t1
(col1  varchar2(1));

Local DB:

create or replace view v1 
as 
select count(*) c1 from t1@l1;

Then alternate variations on this sequence of events:

1. On local DB execute SELECT:

SELECT * FROM v1;

2. On remote (more...)

Yet Another Elementary SQL Bug

Environment

sokrates@12.1 > select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE	12.1.0.1.0	Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

sokrates@12.1 > select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8
sokrates@12.1 > ! (more...)

How 2 Bytes can fit into 1 Byte

Chris Saxon posted a nice quiz regarding a pitfall when creating tables with VARCHAR2-columns: when you are not explicit in specifying the length-semantics ( CHAR or BYTE ), a session parameter, which may vary, is used.

This is a short follow-up of his story, which shows more pitfalls when creating views and selecting from those.
We end up in a structure which can – by definition – contain only 1 byte, but actually shows containing (more...)

NoSQL? No Thanks

There continues to be a disproportionate amount of hype around 'NoSQL' data stores.  By disproportionate I mean 'completely and utterly out of scale with the actual problems of the vast majority of companies'.  I wrote before about 'how NoSQL became more SQL'.  The point I made there is now more apparent the more I work with companies on Big Data challenges. There are three worlds of data

KScope14

ODTUG_Kscope14

This year, from June 22 - June 26, the best convention in the world, ODTUG’s KScope14 will be held in Seattle, Washington. I am already looking forward to meeting some ‘old’ friends again, creating new friends and seeing some of the best content by presenters from all over the world.

Some of the presentations I am looking forward to:

In the Developer’s Toolkit track:

Six things to make your Big Data project succeed

So I wrote about why your Hadoop project will fail so I think its only right that I should follow up with some things that you can do to actually make the Big Data project you take on succeed.  The first thing you need to do is stop trying to make 'Big Data' succeed and instead start focusing on how you educate the business on the value of information and then work out how to deliver new (more...)

A Restriction of the Cardinality Hint

Here is a restriction of the cardinality hint in conjunction with the materialize-hint ( note: both are undocumented but sometimes of great use ):
we cannot tell the optimizer in the outer query ( the one that uses the materialized subquery ) about the cardinality of the materialization, this can only – and then not always – be done within the materializing query.

The example to show that is stolen from Tom Kyte’s Presentation S13961_Best_Practices_for_Managing_Optimizer_Statistics_Short. (more...)

Issue with updatable views

It’s sometimes amazing, how many bugs there are still with elementary SQL.

Here is one concerning updatable views:

sokrates@12.1 > create table t ( v varchar2(30) );

Table created.

sokrates@12.1 > create view v as
  2  select v as dontdothatman, v as canbelostwheninserted
  3  from t; 

View created.

sokrates@12.1 > insert /* this is fine */ into v 
  2  values('fine', 'fine');

1 row created.

sokrates@12.1 > select * from v;

DONTDOTHATMAN		        (more...)

My history with Big Data

Before I joined Cloudera, I hadn't had much formal experience with Big Data. But I had crossed paths with one of its major use cases before, so I found it easy to pick up the mindset. My previous big project involved a relational database hooked up to a web server. (more...)

Largest Tables Including Indexes and LOBs

Just a quick code snippit. I do a lot of data pumps to move schemas between different databases; for example taking a copy of a schema to an internal database to try to reproduce a problem. Some of these schemas have some very large tables. The large tables aren’t always (more...)

Invisible Not Null Column

Yesterday I attended John King (@royaltwit) session on Oracle 12c for developers. He gave an overview of all the goodies that are available to us as developers. The whole plugging and unplugging of database, though very cool and exiting, is most likely not very relevant to most developers.
When he (more...)

Best Practice in 12c

Since PL/SQL now is closely integrated into SQL, we hence can happily state
sokrates@12.1 > with function bestpractice return varchar2
  2  is
  3  begin
  4     return 'Do not use PL/SQL when it can be done with SQL alone !';
  5  end bestpractice;
  6   (more...)