The Hitchhiker’s Guide to the EXPLAIN PLAN Part 28: Throw Away That EXPLAIN PLAN! (Part II)

We tend to overuse the tool—EXPLAIN PLAN in this case—that we are most familiar with. There is an expression—attributed to behavioral scientist Abraham Maslow—that “if all you have is a hammer, everything looks like a nail.” Maslow also tells the story of a drunk searching under a street lamp for his house key, which he had dropped some distance away. Asked why he didn’t look much where he had dropped it, the drunk replied, (more...)

Boosting APEX menu SQL performance

If you're using dynamic SQL to source your menus and you're utilising the apex_application_pages dictionary view to build you list of menu options - I suggest you consider using Materialized Views.

We have a reporting application that does just this; and the query also links to an authorisation table that decides what types of users see which reports.

You can create a snapshot (materialized view) using something as simple as
create materialized view apx_application_pages
as (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...)

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

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

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

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

According to Ralph Kimball, there is only one basic evaluation approach—along with a “bail out” option—in a star schema: evaluate the constraints on the dimension tables, prepare a list of composite keys of the fact table, and use a composite index on the fact table to fetch the data from the fact table. When you notice that the list of composite candidate keys is too long, you bail out to a full-table scan in which (more...)

Oracle 12c: Temporal Validity, multiple on one table

During a trial run for my presentation at Oracle Open World "Oracle 12c for Developers", you can find the slides on slideshare. there was a question regarding "Temporal Validity".
What is Temporal Validity?
The documentation says it best:

Temporal Validity Support lets you associate one or more valid time dimensions with a table and have data be visible depending on its time-based validity, as determined by the start and end dates or time stamps of (more...)

DBMS_UTILITY.EXPAND_SQL_TEXT and temporal validity in 12c

12c gave us two new features among others: DBMS_UTILITY.EXPAND_SQL_TEXT and temporal validity. DBMS_UTILITY.EXPAND_SQL_TEXT is advertised as a means to expand SQL text that is using views, but it is also very useful to see how Oracle internally rewrites some SQL queries. Here I'm going to use it to see how temporal validity queries are actually executed. All tests are done using 12.1.0.2 Enterprise Edition.

First I'll create a table for (more...)