Press "Enter" to skip to content

Curated SQL Posts

Indexing Multiple Columns in Oracle with DBMS_SEARCH

Published 2023-06-27 by Kevin Feasel

Brendan Tierney rounds up the usual suspects:

This type of index is a little different to your traditional index. With DBMS_SEARCH we can create an index across multiple schema objects using just a single index. This gives us greater indexing capabilities for scenarios where we need to search data across multiple objects. You can create a ubiquitous search index on multiple columns of a table or multiple columns from different tables in a given schema. All done using one index, rather than having to use multiples. Because of this wider search capability, you will see this (DBMS_SEARCH) being referred to as a Ubiquitous Search Index. A ubiquitous search index is a JSON search index and can be used for full-text and range-based searches.

This is an interesting approach to the problem, though as I think about it, it makes me wonder, if you’re constantly searching in A+B+C+D, is that really four separate attributes or has something gone wrong in the design? It’s early enough in the morning for me that I’m willing to accede to there being use cases in a well-designed database.

Comments closed

Using Subqueries in a SELECT Statement

Published 2023-06-27 by Kevin Feasel

Greg Larsen builds a subquery:

Did you know you can include a SELECT statement within another SELECT statement? When a SELECT statement is embedded within another statement it is known as a subquery. There are two types of subqueries: basic subquery and correlated subquery.

In this article I will be discussing both types of subqueries and will be providing examples of how to use a subquery in different places within in a SELECT statement.

Greg has a good write-up on the topic of subqueries and does well to separate correlated from non-correlated subqueries.. And if you want to learn more about those, as well as common table expressions, I put out a video on the topic just last week.

Comments closed

A Complex Example of ADF Pipeline Return Value

Published 2023-06-27 by Kevin Feasel

Andy Leonard goes beyond the simple example:

In this post, I demonstrate one way to create a child pipeline that returns the SubscriptionId for a data factory. I then call the child pipeline from a parent package.

To build this demonstration, please follow the instructions that follow.

This is definitely more complicated than Andy’s simple example, but there are plenty of screenshots to take you through the process.

Comments closed

Loading Multiple Extended Events Files in SQL Server

Published 2023-06-27 by Kevin Feasel

Jose Manuel Jurado Diaz reviews the tapes:

As the volume of data grows, SQL Server creates multiple extended event files to store the captured information efficiently. These files are usually saved in a designated target folder. However, when it comes to loading and analyzing these files, administrators often face the challenge of dealing with multiple files individually. Manually loading each file can be time-consuming and inefficient, especially when dealing with a large number of extended event files.

Read on to see which function you can use to read multiple Extended Events files and how it works.

Comments closed

Repetition in R with rep()

Published 2023-06-26 by Kevin Feasel

Steven Sanderson shows off a function in R:

As a programmer, you’re constantly faced with the need to repeat tasks efficiently. Repetition is a fundamental concept in programming, and R provides a powerful tool to accomplish this: the rep() function. In this blog post, we will explore the syntax of the rep() function and delve into several examples to showcase its versatility and practical applications. Whether you’re working with data manipulation, generating sequences, or creating repeated patterns, rep() will become your go-to function for mastering repetition in R.

Read on for the basic syntax, as well as several examples of how to use the rep() function.

Comments closed

Trying the Azure OpenAI Playground

Published 2023-06-26 by Kevin Feasel

Obaro Alordiah gives us a primer:

The Azure OpenAI Service has been a trending topic in the tech world this year as it combines the power of OpenAI’s advanced generative AI models with the comprehensive suite of services available on the Azure cloud. It has given developers the opportunity to create and embed high performing AI models into the Azure environment to deliver more efficient, insightful & innovative solutions. In this blog, we will take a high level look at some of the key features within the Azure OpenAI playground and how we can get the best out of it.

Generative AI via OpenAI is an area in which Microsoft is putting an inordinate amount of focus.

Comments closed

A Review of Fabric Lakehouse

Published 2023-06-26 by Kevin Feasel

Teo Lachev talks lakehouses:

The Microsoft’s Lakehouse definition is less ambitious and exclusive. “Microsoft Fabric Lakehouse is a data architecture platform for storing, managing, and analyzing structured and unstructured data in a single location. It is a flexible and scalable solution that allows organizations to handle large volumes of data using a variety of tools and frameworks to process and analyze that data. It integrates with other data management and analytics tools to provide a comprehensive solution for data engineering and analytics”. In other words, a lakehouse is whatever you want it to be if you want something better than a data lake.

Read on for Teo’s classic The Good, The Bad, and The Ugly format.

Comments closed