Bloom Pruning im Ausführungsplan

Maria Colgan hat mal wieder einen Artikel geschrieben, der sich nicht unmittelbar mit ihrem aktuellen Tätigkeitsfeld In-Memory beschäftigt, sondern ihr altes Thema betrifft: die Strategien des Optimizers. Im Artikel zeigt sie einen Plan, der in der Name-Spalte zwei Bloom Filter Erzeugungen aufführt (JOIN FILTER CREATE, PART JOIN FILTER CREATE), aber nur die Verwendung eines der beiden Filter. Der zweite Bloom Filter erscheint in den Spalten Pstart und Pstop und wird zum Partition Pruning verwendet (also (more...)

Oracle TZ_OFFSET Function with Examples

The Oracle TZ_OFFSET function is a useful datetime function. Learn how to use this function and see some examples in this article. Purpose of the Oracle TZ_OFFSET Function The purpose of the TZ_OFFSET function is to display a number that indicates the number of hours from UTC that a specified time is. You can specify […]

DB_LINK w/o tnsnames.ora

A question popped up, which I thought was interesting. How can you create a DB_LINK in Oracle without the DBA changing the tnsnames.ora file? It’s actually quite easy, especially if the DBA sets the TNS address name the same as the instance’s service name or in older databases SID value.

  1. Do the following with the tnsping utility:
    tnsping mohawk

    It should return this when the server’s hostname is mohawk and domain name is techtinker. (more...)

OPatch 12c: emocmrsp gone missing

Last week, the quarterly PSU came out, and just a few hours later I tried to apply it to a Oracle Restart + DB system under OL 6.7. This would have run smoothly if it wasn’t for the fact that this was the first patch for this system and therefore I needed to create a OCM […]

How to Find the Row That Has the Maximum Value for a Column in Oracle

Do you need to find the data for a row which has the maximum value for a specific column in Oracle? Learn how to do it and see some alternative answers in this article. The Table Structure Let’s say you have a table called sales_volume that looks like this: CITY START_DATE SALES El Paso 27/May/15 […]

Dot Qualify Everything?

Minimal privileges for Amazon S3 backup user

This is a follow-up to an old post I did about how to backup Oracle database schemas to Amazon S3 using PL/SQL.

In short, the packages provided in the Alexandria Utility Library for PL/SQL allow you to set up a schema-level backup of files from your database to Amazon's Simple Storage Service (S3).

At the end of that article I mentioned that you should use AWS Identity and Access Management (IAM) to create a separate (more...)

Partial Indexes für partitionierte Tabellen

Dani Schnider hat zuletzt eine dreiteilige Serie zur Verwendung partieller Indizes veröffentlicht. Dabei stellt sich als erstes die Frage: was ist ein Partial Index überhaupt? Die Antwort lautet: Partielle Indizes werden nur auf einer Teilmenge der Partitionen einer partitionierten Tabelle erzeugt: entweder, um die Ladeprozesse für aktive Partitionen nicht zu beeinträchtigen - in diesem Fall verzichtet man auf eine Indizierung der aktuellen Daten; oder um umgekehrt die Zugriffe auf die aktuellen Daten durch Indizes zu (more...)

Oracle INITCAP Function with Examples

The Oracle INITCAP function is a handy string manipulation function. Learn what it does and see some examples in this article. Purpose of the Oracle INITCAP Function The Oracle INITCAP function translates a specified string into another string which has the first letter of each word capitalised, and all other letters in lower case. The […]

Oracle LOWER Function with Examples

In this article, I’ll take a look at the Oracle LOWER function, explain how it works, and see some examples. Purpose of the Oracle LOWER Function The LOWER function converts all letters within the specified string to lower case.   Syntax The syntax for the Oracle LOWER function is: LOWER ( input_string )   Parameters […]

Where is the Data Warrior Now?

Hi folks. Time to update y’all on some upcoming speaking engagements for this summer and fall. Here are a few talks recently scheduled: Houston DAMA When: August 9, 2016 1:30 PM Where: Salon Consulting, 2200 Post Oak Boulevard, Suite 1450 , Houston, TX Topic: Harnessing the Elasticity of the Cloud for Analytics with Snowflake and Tableau Register: Houston […]

Hint-Verwendung zur Bestimmung der Join-Reihenfolge

Brendan Furey erläuert in seinem Blog die Möglichkeiten, die Oracle bietet, die Join-Reihenfolge über Hints zu beeinflussen. Darin weist er zunächst darauf hin, dass der USE_HASH Hint eigentlich nur einen Parameter benötigt und nicht die Angabe der beiden Aliase der Tabellen, die man miteinander verknüpfen will: bei der Angabe von zwei Aliasen betrachtet Oracle dies als zwei Hints und wird folglich einen der beiden übergehen. Welcher der Hints nicht berücksichtigt wird, hängt von der Reihenfolge (more...)

How Can You Create A Column With AUTO_INCREMENT in Oracle SQL?

If you’ve used MySQL, you’re probably familiar with the AUTO_INCREMENT feature. But it’s not available in Oracle. Read this article to find out how you can auto increment a column in Oracle SQL. What Is Auto Increment? An auto increment column, or an identity column in other databases, is a column that has its value […]

Spreadsheet-like Totals and Subtotals

We very often make spreadsheets with subtotals for each row and for each column. Someone on the OTN forum asked how to produce data in this format. I answered using the cool CUBE function.

Quote of the Day: Mark Twain

It is better to deserve honors and not have them         than to have them and not deserve them. — Mark TwainFiled under: Quotes Tagged: Mark Twain, quote of the day, quotes

Staleness IMPORT für Materialized Views in all_mviews nach data pump Import

Eine kurze Notiz, da ich bei meiner Recherche im Netz nicht arg viel zum Thema gefunden habe: durch den Transport einer Materialized View über data pump (expdp - impdp) werden zwar die Daten des Segments transportiert, aber die MView wird nicht mehr für Query Rewrite verwendet und auch ein Fast Refresh ist nicht mehr möglich und scheitert an einem Fehler ORA-12034:
ORA-12034: materialized view log on "XXX"."YYY" younger than last refresh
An dieser Stelle (more...)

What should I know about SQL?

Chris Saxon from the Developer Advocates group asked the following question on Twitter.

My immediate thought regarded features I'd be disappointed to live without. Looking at some other responses I realise that the answer matches Tom Kyte's creed: "it depends".

Here is a collection of responses that I think sums(sql) really well, (more...)

Outer Join with OR and Lateral View Decorrelation

Use of ANSI SQL is a personal thing.

Historically I have not been a fan apart from where it makes things easier/possible.

This reticence was mainly due to optimizer bugs and limitations in the earlier days.

Recently I have been using it much more because I find that the developers I interact with prefer it / understand it better.

You might/should be aware that Oracle will rewrite ANSI SQL to an Oracle syntax representation, this (more...)

APEX Survey Results: Editing Tools

Another preference question in my 2015 survey.

Q5: What editing tools do you use for PL/SQL and JavaScript

That Jeff Smith fellow should be pretty happy with the top result, but almost half of the "Other" responses said PL/SQL Developer. I used this many years ago while SQL Developer was still being born. Since SQL Developer was free and portable, it was an easy selection, though I only use if for queries, not PL/SQL development.


Introduction to Snowflake!

Heads up! I will be giving a webinar next week, called Enabling Cloud-Native Elastic Data Warehousing to introduce folks to the Snowflake Elastic Data Warehouse. Sign up here and join me on July 12th! Special thanks to DAMA International for inviting me to do this! See you there! Kent The Data WarriorFiled under: Big Data, Data Warehouse, […]