My Presentations at #UKOUG #techfest19

Uncategorized
| Dec 3, 2019
I just finished my second presentation at TechFest19 in sunny Brighton, England. The conference this year is great from every point of view: venue, people, content, conversations… Thanks to those who came to my talks for your benevolent attention and excellent questions/remarks! Both my presentations are now on SlideShare. I beg you to download them […]

MATCH_RECOGNIZE Restrictions

Uncategorized
| Oct 31, 2019
Sometimes we think that certain restrictions are not documented when in fact they are. Where do we forget to look? Database Error Messages

Backtracking

Uncategorized
| Oct 30, 2019
By popular request, here are my thoughts about the impact of "backtracking" on performance when using the MATCH_RECOGNIZE clause. This came up again because of a query that Jonathan Lewis wrote recently; however, I will concentrate on the theory, not the query.

Predict_Clustering_Factor

Uncategorized
| Oct 26, 2019
Jonathan Lewis recently wrote about estimating the clustering factor of an index, taking into account the intended value of the TABLE_CACHED_BLOCKS parameter of DBMS_STATS.SET_TABLE_PREFS. He included a function I wrote called predict_clustering_factor. Here is a corrected and improved version.

LISTAGG() as a Cumulative Function

Uncategorized
| Sep 30, 2019
LISTAGG() can be used as an analytic function, but cannot be cumulative. What does that mean, and is there a workaround?

Making Longer Lists

Uncategorized
| Aug 18, 2019
For very long lists, we need the return values to be CLOBs. Alas, LISTAGG can only return VARCHAR2 lists, but XML and JSON aggregate functions can return CLOBs!

Interval expressions

Uncategorized
| Aug 11, 2019
I just learned a lot about these critters thanks to an ODC forum question: how to calculate the difference between two dates in calendar years and months, days, hours, minutes and seconds.

Making Lists

Uncategorized
| Jul 10, 2019
Strings that contain multiple values violate first normal form, which mandates "one value per column per row".  People still ask how to make or massage lists of values. This post is about aggregating values into lists.

Elegant MODEL clause

Uncategorized
| Jun 5, 2019
Frank Kulash recently asked a challenging question on OTN, which Chris227 answered using the MODEL clause. To help  fully understand his excellent answer, I'm going to go into detail.