The Hitchhiker’s Guide to the EXPLAIN PLAN (Act II)

Over at ToadWorld … Part 5: SQL Sucks! Part 6: Trees Rule Part 7: Don’t pre-order your EXPLAIN PLAN Part 8: Tree Menagerie The story so far: A relational database is “a database in which: the data is perceived by the user as tables (and nothing but tables)  and the operators available to the user for (for […]

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...)

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:

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...)

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:

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...)

Recursive Subquery Factoring to the Rescue

Recursive subquery factoring (AKA Recursive Common Table Expressions) has been available in Oracle since version 11g R2 was launched sometime around 2009. I always thought of it as a replacement for the connect by clause and just another way to write hierarchical queries, so I never really took the time (more...)

PIVOT and UNPIVOT

The PIVOT and UNPIVOT operators were introduced in Oracle 11g and the other day I helped out a friend using these operators.
He wanted the data in the columns to be "shifted to the left". To explain a little more, take a look at the following data:

ID V1 V2 (more...)

Fun with SQL – My Birthday

This year is kind of fun, my birthday is on November 12th (next Tuesday, if you want to send gifts). That means it will fall on 11/12/13. Even better perhaps, katezilla's birthday is December 13th. 12/13/14. What does this have to do with SQL?

Someone mentioned to me last night (more...)

Strange ORA-14196

It seems that sometimes you need a non-unique index to enforce a unique constraint even if this constraint is declared as not deferrable.

sokrates@11.2 > create table strange(i int not null, j int not null);

Table created.

sokrates@11.2 > alter table strange add constraint unique_i unique(i) not deferrable
   (more...)

New York Oracle User Group Fall Conference Materials

Thank you all who attended my sessions at NYOUG Fall Conference this morning. I appreciate spending you most precious commodity - your time - with me. I sincerely hope you found both the presentations enlightening as well as entertaining.

Please see the details of the sessions below along with the (more...)