24Mar
By: Steven Feuerstein On: March 24, 2021 In: APEX Developer Solutions, Feuertips Comments: 0

The indexes of associative arrays, that is, not tables.

Table indexes are…well, what they are.

But when it comes to associative arrays, one of the three types of PL/SQL collections, you can choose between an integer index and a string index.

Which means that when you assign a value to an element in the collection, you specify either an integer value or a string value as its index or location in the array.

In episode of 3 of Feuertips, I discussed string-indexed arrays. Watch the full episode below:

String-indexed associative arrays are interesting and fun. They can also result in greatly simplified code when used properly.

Let’s run through a simple example that also nicely demonstrates the power of these types of collections.

Our users need to have unique “handles.” They type in something, we check it. If the handle is already taken, they are asked to try again. And I need to keep track of all the handles they’ve tried. Don’t ask me why. I just do.

So I built a package to keep track of the handles, or strings. It’s called the string tracker package, and here’s the body:

PACKAGE BODY string_tracker
IS
   TYPE used_aat IS TABLE OF varchar2(1000) INDEX BY PLS_INTEGER;
   g_names_used   used_aat;

   PROCEDURE mark_as_used (value_in IN varchar2)
   IS
   BEGIN
      g_names_used (g_names_used.COUNT + 1) := value_in;
   END mark_as_used;

   FUNCTION string_in_use (value_in IN varchar2) RETURN BOOLEAN
   IS
      c_count   CONSTANT PLS_INTEGER := g_names_used.COUNT;
      l_index            PLS_INTEGER := g_names_used.FIRST; 
      l_found            BOOLEAN DEFAULT FALSE;
   BEGIN
      IF l_index IS NOT NULL
      THEN
         WHILE (NOT l_found AND l_index <= c_count)
         LOOP
            l_found := value_in = g_names_used (l_index);
            l_index := l_index + 1;
         END LOOP;
      END IF;

      RETURN l_found;
   END string_in_use;
END string_tracker;

In this approach, I declare a collection of strings indexed by integer. When I want to mark a string (handle) as being used, I add it to the end of the list:

g_names_used (g_names_used.COUNT + 1) := value_in;

To find out if a string has already been used, I loop through the collection in search of a match:

WHILE (NOT l_found AND l_index <= c_count)
LOOP
   l_found := value_in = g_names_used (l_index);
   l_index := l_index + 1;
END LOOP;

That all makes sense, right? OK, but here are some things that do not make sense:

  • The matching algorithm is not data neutral. By which I mean, as the list grows, performance decreases.
  • Essentially, I am doing a “full table scan” of the collection.
  • The way I use the collection implies that the order in which strings are added to the list is significant, but it is not.
  • string_in_use is not the most complicated piece of code you’ll ever see, but it’s the sort of algorithm that can easily introduce boundary condition errors and suck all the time out of your day.
PACKAGE BODY string_tracker
IS
   SUBTYPE who_cares_t IS BOOLEAN;
   c_does_not_matter   CONSTANT who_cares_t DEFAULT NULL;

   TYPE used_aat IS TABLE OF who_cares_t INDEX BY varchar2(1000);
   g_names_used        used_aat;

   PROCEDURE mark_as_used (value_in IN varchar2)
   IS
   BEGIN
      g_names_used (value_in) := c_does_not_matter;
   END mark_as_used;

   FUNCTION string_in_use (value_in IN varchar2) RETURN BOOLEAN
   IS
   BEGIN
      RETURN g_names_used.EXISTS (value_in);
   END string_in_use;
END string_tracker;

So first thing to notice: my, oh my, how the string_in_use function has shrunk! Now it’s just one line. Wow! And how is that possible? String-indexed arrays. Let’s go back up to the top of the package body and see how that happened.

My list of strings is now defined differently:

SUBTYPE who_cares_t IS BOOLEAN;
c_does_not_matter   CONSTANT who_cares_t DEFAULT NULL;

TYPE used_aat IS TABLE OF who_cares_t INDEX BY varchar2(1000);
g_names_used        used_aat;

First, I created a new type (a “subtype”) that is really just a renaming of Boolean. Why would I do that? Take a look at the type declaration. Two things of note:

  1. The collection is indexed by a string, not an integer.
  2. The type of data stored in the collection is “Who cares?”. My objective here is to self-document that the values stored in the collection are of no importance.
  3. Which means…hmmmm…it must be the index of the collection that’s important.

Sound familiar? Kind of like an index-organized table:  ” Effectively, the data is stored within the primary key index.” Tim Hall, https://oracle-base.com/articles/8i/index-organized-tables.

The code to add a string to the array is still one line, just like in the original integer-indexed version. But now the string itself is the index value; I do not add sequentially to the end of the list:

g_names_used (value_in) := c_does_not_matter;

and that, dear friends, is the secret sauce. Since I use the string itself as the index value, I can now oh so quickly and easily check to see if the string has been used: the answer is “Yes, used” if an element exists in the collection for that string:

RETURN g_names_used.EXISTS (value_in);

Thank you, exists method. Is that delightful or what?

Hopefully, that gives you a sense of the power of string-indexed collections and how they can have a big impact on your code.

Some other things to remember about string-indexed associative arrays (SIAA):

  • The methods first, last, next, prior return strings, not indexes.
  • Table functions cannot return SIAAs
  • You cannot bulk collect into an SIAA.
  • SIAAs are always sparse, which means you cannot use a numeric for loop (such as “for I in 1 .. my_siaa.count”) to iterate through the contents of the collection. OK, you can, but you should not.
  • The longer the index value, the slower it is to work with the collection.
  • National Language Support (NLS) parameters can affect the ordering of elements in a string-indexed collection.

Time to Explore!

When it comes to collections in general and SIAAs in particular, there’s a lot to learn and more than a few details to sort out when it comes to applying them to your application development.

Here are some handy resources to continue your education:

Working with Collections, from my PL/SQL 101 series:

https://blogs.oracle.com/oraclemagazine/working-with-collections

Working with String-indexed Associative Arrays, from my YouTube channel:

https://www.youtube.com/watch?v=8w8jxc11FKA

Associative Array documentation in PL/SQL Language Reference:

https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-collections-and-records.html#GUID-8060F01F-B53B-48D4-9239-7EA8461C2170

LiveSQL Script for String Tracker

The simplest string tracker package, demonstrating use of string indexing alone.

https://livesql.oracle.com/apex/livesql/file/content_LJT3ARATKFF659S024JG4ED9Q.html

This script demonstrates the power not only of string indexing, but also nested collections.

https://livesql.oracle.com/apex/livesql/file/content_CZUXPDAJ7WYGB0HEVEHPOSLVS.html

Feuertips

Every Wednesday at 11 am ET, I go live on YouTube and Facebook with Michelle Skamene to provide you with a not-to-be-missed PL/SQL tip and fun conversation. Sure, we record it so you can always watch later, but live is so much more rewarding!
Speaking of rewards, Steven finishes each show with a quick quiz to see if you were listening. Participation, of course, is optional!
One lucky quiz answerer will be selected at random to choose from three organizations dedicated to the preservation of our planet and its biodiversity: The Sheldrick Wildlife Trust, The Center for Biological Diversity, and Earthwatch.
Insum will then make a donation of $25 to that group in your name.
What could be better than levelling up your PL/SQL tips and helping one of these worthy organizations? Join us Wednesdays at 11!
Share this:
Share

Leave reply:

Your email address will not be published. Required fields are marked *