ANSI Join Syntax und Prädikatanordnung

Die ANSI-Syntax zur Formulierung von Joins finde ich grundsätzlich deutlich lesbarer als Oracles traditionelle Schreibweise und würde sie vermutlich regelmäßig einsetzen, wenn sie nicht so viele Bugs hervorrufen würde. Einer davon ist gestern in einem OTN-Thread aufgetaucht. Dort hatte sich der Fragesteller darüber gewundert, dass zwei Varianten eines Outer-Joins unterschiedliche Ergebnisse lieferten, je nachdem, ob die ON-Prädikate direkt bei den zugehörigen FROM-Klauseln erschienen, oder getrennt von diesen in einem gemeinsamen abschließenden Block. Mein erster Gedanke (more...)

Approximate Count Distinct (12.1.0.2 new feature)

With the release of the Oracle Database 12.1.0.2 there was a number of new features and options. Most of the publicity has been around the in-Memory option. But there was lots of other features for the DBA and a few for the developer.

One of the new SQL functions is the APPROX_COUNT_DISTINCT(). This function is different to the tradition count distinct, COUNT(DISTINCT expression), in that is performs an approximate count distinct. (more...)

Audio semi-Visual Presentation on Clustering Data in Oracle

I suppose it had to happen eventually but one of my presentations has ended up on YouTube. It’s a recent presentation I did for the Oracle Midlands user group in September.

The topic is (as the title of this blog post hints at!)Boosting select performance by clustering data. The video consists of the slides I presented, changing as the presentation progresses, with my audio over the top. It goes on for a bit, (more...)

What Day Is It If You Only Specify The Time?

What is the date if you only specify the time when you populate an Oracle date column (or variable)?

That was the question that came up a few days ago in the little DBA team I am currently working in. Of course, the question was posed by the “junior” (who is damned smart at this stuff as he keeps asking questions like that) and the answer from us two old hands was… “Ohh!… hang (more...)

Something new in 12c: FETCH FIRST x ROWS

In this post I want to show some example of using a new feature in 12c for selecting the first X number of records from the results set of a query.

See the bottom of this post for the background and some of the reasons for this post.

Before we had the 12c Database if we only wanted to see a subset or the initial set of records from the results of a query we (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 26: Answers to the Final Exam! (Part IV)

The answers to the third problem in Part 22 of this series “We Don’t Use Databases; We Don’t Use Indexes.”(read more)

Results of the NoCOUG SQL Mini-Challenge

As published in the November 2014 issue of the NoCOUG Journal The inventor of the relational model, Dr. Edgar Codd, was of the opinion that “[r]equesting data by its properties is far more natural than devising a particular algorithm or sequence of operations for its retrieval. Thus, a calculus-oriented language provides a good target language […]

We Have a Winner!

Kim Berg Hansen from Denmark wins the SQL mini-challenge for his UNION ALL materialized view with fast refresh on commit. An analysis of his solution has been published in the NoCOUG Journal. Judge’s statement: “Kim won on participation, efficiency, and accuracy. He kept submitting refinements, lowering the bar until he reached the theoretical lower limit of 1. His solutions survived the test cases written by the judging committee.”


The Hitchhiker’s Guide to the EXPLAIN PLAN Part 25: Answers to the Final Exam! (Part III)

The answers to the third problem in Part 21 of this series.(read more)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 24: Answers to the Final Exam! (Part II)

The answers to the second problem in Part 21 of this series.(read more)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 23: Answers to the Final Exam! (Part I)

The answers to the first problem in Part 21 of this series.(read more)

SQL Mini-Challenge Update

The SQL Mini-Challenge is now closed. The judges will be Kyle Hailey, Tim Gorman, and Iggy Fernandez. The winner will be announced in the fall issue of the NoCOUG Journal.

Correctness is the primary screening criterion that will be used by the judges. Submissions are expected to produce the same results as the original query even if the data changes. The following script has been constructed to test submissions:

update employees
set
  first_name='Stevie',
  last_name='Wonder',
  hire_date=sysdate,
  job_id='AD_VP',
  department_id=50
 (more...)

Check Constraints

Oracle Database 12c introduces a SEARCH_CONDITION_VC column to the CDB_, DBA_, ALL_, and USER_CONSTRAINTS views. The SEARCH_CONDITION_VC column is a VARCHAR2 data type equivalent to the search condition in the LONG data type SEARCH_CONDITION column. Unfortunately, Oracle Database 11g and earlier versions requires you to convert the LONG data type to a VARCHAR2 for the equivalent behavior. This post provides you with a function to help you do that in (more...)

Oracle 12c: Temporal Validity, multiple on one table – Part Deux

One of the most wonderful things of Oracle Open World are the Demo-Grounds. When you want to learn more about a certain feature, this is the place to go to. The actual developers and product managers are there to answer your questions and more!

In a previous blog I had written about Temporal Validity in Oracle 12c and whether it would be possible to have multiple validity periods on one table. You can read that (more...)

When to use the NOLOCK hint in SQL Server

I frequently hear of, and see, developers and DBA’s using the NOLOCK hint within SQL Server to bypass the locking mechanism and return their data sets as soon as possible. There are times when this is OK, such as when you are running an ad hoc query and are only interested in approximate results. It is somewhat less OK to write this hint into application code and reports, unless you don’t actually care whether the (more...)

Is it unique?

The Hitchhiker’s Guide to the EXPLAIN PLAN: The story so far (Part 11–22)

Before continuing with this series, please take a minute to read any installments that you may have missed.(read more)

The Hitchhiker’s Guide to the EXPLAIN PLAN: The story so far (Part 11–23)

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

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 22: The Final Exam! (Part III We Don’t Use Databases; We Don’t Use Indexes)

If you were the owner of a book store and you received a shipment of books, how would you go about putting the books on the shelves? Would you stand at the door of your store and fling each book onto any shelf where you saw some space? No! Then why do all of us store data that way? The default organization of data in table is “heap” which means that incoming records are stored (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 21: The Final Exam! (Part II)

In addition to the basic evaluation approach of Kimball and the bail-out option, Oracle Database offers a lightning-fast option which uses “bitmap” indexes. A bitmap index is composed of multiple “bitmaps” instead of primary key and ROWID pairs like conventional indexes. Each bitmap in a bitmap index is an array of bits (0s and 1s) and tracks exactly one of the values that occurs in the indexed column. The 1s in a bitmap indicate that (more...)