Top 10 Cool Things I Like About Snowflake Elastic Data Warehouse

I have now been with Snowflake Computing for a little over two months (my how time flies). In that time, I have run the demo, spoken at several trade shows, and written a few blogs posts. I have learned a ton about the product and what it means to be an Elastic Data Warehouse in […]

Apex API – call a package for all your DML

If you create an Apex form based on a table, Apex automatically creates processes of type Automatic Row Fetch and Automatic Row Processing (DML) as well as one item for each column in the table, each bound to the database column via its Source Type. This design is excellent as its fully declarative and is very quick and easy to build a data entry page for all your tables very quickly.

The downside to this (more...)

TAPI Generator MkII

The last few weeks I’ve made quite a few improvements to my TAPI generator which I thought I’d share. I’ve also added an Apex API generator which generates code suitable for interfacing between simple Apex applications and my TAPIs. This reduces the volume of PL/SQL required within Apex to a bare minimum.

  • Templates are now defined in a package spec, so they are easier to edit in a tool with syntax highlighting (more or less)
  • (more...)

Chunking tables 8: second solution

Here is the second complete solution to the problem of dividing a table into chunks of equal size. This is the solution whose first version was marked "correct" on OTN. It is the basis for what Bryn Llewellyn calls "Approx_Method_Sql_Ashton_2" in his white paper.

Parallel partition scans

When a table is accessed by multiple members of a parallel query server set, the execution plan may show the use of block range granules (PX BLOCK ITERATOR) or partition range granules (PX PARTITION [RANGE|LIST|HASH] ITERATOR or PX PARTITION [RANGE|LIST|HASH] ALL).

The basic ideas surrounding these concepts are discussed in numerous blogs and books, including my own, but discussion of partition range granules is normally restricted to either partition wise joins or the creation (more...)

Fire Sale #2: Loading Your Data Vault with Informatica

As I mentioned the other day, Dan and Sanjay have been hard at work re-vamping the Learn Data Vault site and have relaunched it. In celebration they are offering amazing deals on their world class Data Vault Implementation classes. The 2nd course that is ready to go is Implementing Data Vault with Informatica. This online course walks […]

Join mit Datums-Bereichen

Wenn man mit SQL arbeitet, dann gehört die Behandlung von Datumsintervallen zu den Themen mit eher beschränktem Unterhaltungswert. Insofern ist es sehr nützlich, dass Stew Ashton eine Artikelserie veröffentlicht hat, in der er diverse Spezialfälle genauer beleuchtet, mit denen man in diesem Kontext konfrontiert werden kann:

Chunking tables 7: prior sys_guid() ???

The second solution for dividing a table into equal chunks does not do a JOIN. Instead it expands extent rows that contain several chunk boundaries, using an obscure method that I need to explain.

Zeitmessung im Oracle Wait Interface

Frits Hoogland gehört zu den Autoren, bei denen mir die plausible Zusammenfassung ihrer Artikel große Mühe bereitet - was zunächst damit zu tun hat, dass sich diese Artikel häufig sehr intensiv mit OS-Fragestellungen beschäftigen und dorthin kann ich nur in beschränktem Umfang folgen. Diesmal aber hat der Herr Hooglands einen Artikel veröffentlicht, der sich mit der Umstellung der für die Zeitmessung im Wait Interface verwendeten Timer-Funktion beschäftigt - und da will ich mal wieder mein (more...)

Chunking tables 6: JOIN solution

At last, here is the first complete solution to the problem of dividing a table into 12 chunks with equal numbers of blocks. James Su and I proposed this solution on OTN, but without the "filtered_extents" optimisation.

Would You Like to Load a Data Vault like a Master?

If you are about to embark on a Data Vault project this year, and not quite sure the best way to load your data into a Data Vault efficiently, I have a great opportunity for you. Dan and Sanjay have been hard at work re-vamping the Learn Data Vault site and are now ready to […]

Chunking tables 5: chunk boundaries

We still want to split a table into 12 chunks with equal numbers of blocks, so we can access each chunk using a rowid range. We have the extents we need and now we need to calculate the exact chunk boundaries.

Recover Modified Oracle APEX Page

I was working on a page in an Oracle APEX application and wanted to rollback all the changes I made in the last 30 minutes on one particular page without losing all the changes I made prior to then.  I had no export of the application, so I used a very cool feature of Oracle and APEX as documented in Peter Raganitsch's blog way back in 2011.

I basically did a page export as (more...)

OPT_ESTIMATE-Hint für Materialized View Fast Refresh

Die folgende Beobachtung verdanke ich Randolf Geist, der mir auf meine Anfrage im OTN-Forum General Database Discussions den entscheidenden Hinweis gegeben hat. Beim Fast Refresh für Materialized Views in 11g ergeben sich regelmäßig Merge-Statements der folgenden Form:

/* MV_REFRESH (MRG) */ 
MERGE INTO "TEST"."T_MV" "SNA$"
USING (SELECT /*+ OPT_ESTIMATE(QUERY_BLOCK MAX=1000) */

Grunsätzlich sind die zugehörigen Mechanismen bekannt - insbesondere Alberto Dell'Era hat dazu in seinem Blog umfassende Erläuterungen geliefert -, aber zum enthaltenen OPT_ESTIMATE-Hint (more...)

Chunking tables 4: Histograms

After "only" 3 posts on chunking tables, we now have the raw materials for breaking up a table into 12 chunks with equal numbers of blocks, and for getting ROWID ranges that let us access the data. Now let's chunk!

Refresh Apex Calendar

calendarwithrefreshbutton.PNG
Sometimes it’s the simple little things that can add polish and make your Apex application shine. One simple little thing that you can do is add a Refresh button to improve the usability of your Apex 5 calendar. This makes it easy for the user to see recent changes on the database, e.g. if events had been added or changed since the page had last been loaded.

  1. Set the Static ID on the Calendar region (e.g. (more...)

Chunking tables 3: working with blocks

So far we've decided to access a table chunk by chunk, each chunk containing one twelfth of the tables's blocks. But first, how do we access data in a chunk of blocks? How do we even know what blocks a table has?

The Data Warrior Speaks 2016: Updated

As expected, I have been booked to speak a few more places this year. Here is my updated speaking schedule as of today: RMOUG Training Days 2016 – February 9-11 in Denver, CO (I have 2 hour deep dive on Feb 9th). Register here. TDWI Nashville – March 8th in Nashville (of course). I will be […]

Chunking tables 2: Requirement

As I mentioned in my previous post, the basic idea is to divide a table into 12 "equal chunks", each "chunk" being a rowid range that covers the same number of blocks (give or take 1). What does this mean? Why do this? Why divide by blocks and not rows? Here are my thoughts.

Oracle Forms 12c: Run Oracle Forms WITHOUT a Browser! New Runtime Options for More Flexibility Without a Browser

We are excited to post the first in our Oracle Forms 12c guest series and introduce our first contributor and a loyal member of the Oracle Forms Community, Holger Lehmann.  If you’re a Forms guru and want to contribute click here  for more information

We also have an awesome upcoming Oracle Forms 12c webinar with Michael :WEBINAR: ORACLE FORMS 12C: NEW FEATURES UNVEILED AND EXPLAINED – Register here!

Oracle Forms 12: New Runtime Options (more...)