Archive for the ‘Oracle 21c’ Category
A tkprof Korn Shell
Reviewing old files, I thought posting my tkprof.ksh would be helpful. So, here’s the script that assumes you’re using Oracle e-Business Suite (Demo database, hence the APPS/APPS connection); and if I get a chance this summer I’ll convert it to Bash shell.
#!/bin/ksh
# -------------------------------------------------------------------------
# Author: Michael McLaughlin
# Name: tkprof.ksh
# Purpose: The program takes the following arguments:
# 1. A directory
# 2. A search string
# 3. A target directory
# It assumes raw trace files have an extension of ".trc".
# The output file name follows this pattern (because it is
# possible for multiple tracefiles to be written during the
# same minute).
# -------------------------------------------------------------------------
# Function to find minimum field delimiter.
function min
{
# Find the whitespace that preceeds the file date.
until [[ $(ls -al $i | cut -c$minv-$minv) == " " ]]; do
let minv=minv+1
done
}
# Function to find maximum field delimiter.
function max
{
# Find the whitespace that succeeds the file date.
until [[ $(ls -al $i | cut -c$maxv-$maxv) == " " ]]; do
let maxv=maxv+1
done
}
# Debugging enabled by unremarking the "set -x"
# set -x
# Print header information
print =================================================================
print Running [tkprof.ksh] script ...
# Evaluate whether an argument is provide and if no argument
# is provided, then substitute the present working directory.
if [[ $# == 0 ]]; then
dir=${PWD}
str="*"
des=${PWD}
elif [[ $# == 1 ]]; then
dir=${1}
str="*"
des=${1}
elif [[ $# == 2 ]]; then
dir=${1}
str=${2}
des=${1}
elif [[ $# == 3 ]]; then
dir=${1}
str=${2}
des=${3}
fi
# Evaluate whether the argument is a directory file.
if [[ -d ${dir} ]] && [[ -d ${des} ]]; then
# Print what directory and search string are targets.
print =================================================================
print Run in tkprof from [${dir}] directory ...
print The files contain a string of [${str}] ...
print =================================================================
# Evaluate whether the argument is the present working
# directory and if not change directory to that target
# directory so file type evaluation will work.
if [[ ${dir} != ${PWD} ]]; then
cd ${dir}
fi
# Set file counter.
let fcnt=0
# Submit compression to the background as a job.
for i in $(grep -li "${str}" *.trc); do
# Evaluate whether file is an ordinary file.
if [[ -f ${i} ]]; then
# Set default values each iteration.
let minv=40
let maxv=53
# Increment counter.
let fcnt=fcnt+1
# Call functions to reset min and max values where necessary.
min ${i}
max ${i}
# Parse date stamp from trace file without multiple IO calls.
# Assumption that the file is from the current year.
date=$(ls -al ${i} | cut -c${minv}-${maxv})
mon=$(echo ${date} | cut -c1-3)
yr=$(date | cut -c25-28)
# Validate month is 10 or greater to pad for reduced whitespace.
if (( $(echo ${date} | cut -c5-6) < 10 )); then
day=0$(echo ${date}| cut -c5-5)
hr=$(echo ${date} | cut -c7-8)
min=$(echo ${date} | cut -c10-11)
else
day=$(echo ${date} | cut -c5-6)
hr=$(echo ${date} | cut -c8-9)
min=$(echo ${date} | cut -c11-12)
fi
fn=file${fcnt}_${day}-${mon}-${yr}_${hr}:${min}:${day}
print Old [$i] and new [$des/$fn]
tkprof ${i} ${des}/${fn}.prf explain=APPS/APPS sort='(prsela,exeela,fchela)'
# Print what directory and search string are targets.
print =================================================================
fi
done
else
# Print message that a directory argument was not provided.
print You failed to provie a single valid directory argument.
fi |
I hope this helps those looking for a solution.
GROUP BY Quirk
It’s always interesting to see how others teach SQL courses. It can be revealing as to whether they understand SQL or only understand a dialect of SQL. In this case, one of my old students was taking a graduate course in SQL and the teacher was using MySQL. The teacher made an issue of using ANSI SQL:1999 or SQL3 and asked the following question, which I suspect is a quiz bank question from a textbook:
“How would you get all students’ names and for each student the number of courses that the
student has registered for?”
They referenced the MySQL 5.7 documentation for the GROUP BY and SQL:1999 as if MySQL implemented the ANSI SQL:1999 specification defined the standard. I didn’t know whether to laugh or cry because they were referring to MySQL 5.7 when we’re all using MySQL 8 and anybody who’s worked in more than MySQL knows that the behavior for a GROUP BY in MySQL can work without listing the necessary non-aggregated columns in the SELECT-list.
For example, their working solution, which is from the instructor and the author of their MySQL textbook the correct perspective of ANSI:1999 behavior. It doesn’t matter that their solution is actually based on ANSI:1992 not ANSI:1999 because it will only succeed because of a quirk of MySQL:
SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid GROUP BY a.studentid; |
While it works in MySQL, it doesn’t work because it conforms to an ANSI standard. It works in MySQL, notwithstanding that standard because it violates the standard.
In Oracle, PostgreSQL, and SQL Server, it raises an exception. For example, Oracle raises the following exception:
SELECT a.studentname
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression |
The correct way to write the GROUP BY is:
SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid INNER JOIN courses c ON b.courseid = c.courseid GROUP BY a.studentname; |
Then, it would return:
Student Name Course IDs ------------------------------ ---------- Montgomery Scott 1 Leonard McCoy 2 James Tiberus Kirk 3 |
For reference, here’s a complete test case for MySQL:
/* Drop table conditionally. */ DROP TABLE IF EXISTS students; /* Create table. */ CREATE TABLE students ( studentID int unsigned primary key auto_increment , studentName varchar(30)); /* Drop table conditionally. */ DROP TABLE IF EXISTS courses; /* Create table. */ CREATE TABLE courses ( courseid int unsigned primary key auto_increment , coursename varchar(40)); /* Drop table conditionally. */ DROP TABLE IF EXISTS registeredcourses; /* Create table. */ CREATE TABLE registeredcourses ( courseid int unsigned , studentid int unsigned ); /* Insert into students. */ INSERT INTO students ( studentName ) VALUES ('James Tiberus Kirk') ,('Leonard McCoy') ,('Montgomery Scott'); /* Insert into courses. */ INSERT INTO courses ( coursename ) VALUES ('English Literature') ,('Physics') ,('English Composition') ,('Botany') ,('Mechanical Engineering'); /* Insert into registeredcourses. */ INSERT INTO registeredcourses ( studentid , courseid ) VALUES (1,1) ,(1,3) ,(1,4) ,(2,2) ,(2,5) ,(3,4); /* Check global sql_mode to ensure only_full_group_by is set. */ SELECT @@GLOBAL.SQL_MODE; /* Query with a column not found in the SELECT-list. */ SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid GROUP BY a.studentid; /* Query consistent with ANSI SQL:1992 */ SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid INNER JOIN courses c ON b.courseid = c.courseid GROUP BY a.studentname; |
and, another complete test case for Oracle:
/* Drop tabhe unconditionallly. */ DROP TABLE students; /* Create table. */ CREATE TABLE students ( studentID NUMBER PRIMARY KEY , studentName VARCHAR(30)); /* Drop table unconditionally. */ DROP TABLE courses; /* Create table. */ CREATE TABLE courses ( courseid NUMBER PRIMARY KEY , coursename VARCHAR(40)); /* Drop table unconditionally. */ DROP TABLE registeredcourses; /* Create table. */ CREATE TABLE registeredcourses ( courseid NUMBER , studentid NUMBER ); /* Insert values in student. */ INSERT INTO students ( studentid, studentName ) VALUES (1,'James Tiberus Kirk'); INSERT INTO students ( studentid, studentName ) VALUES (2,'Leonard McCoy'); INSERT INTO students ( studentid, studentName ) VALUES (3,'Montgomery Scott'); /* Insert values in courses. */ INSERT INTO courses ( courseid, coursename ) VALUES (1,'English Literature'); INSERT INTO courses ( courseid, coursename ) VALUES (2,'Physics'); INSERT INTO courses ( courseid, coursename ) VALUES (3,'English Composition'); INSERT INTO courses ( courseid, coursename ) VALUES (4,'Botany'); INSERT INTO courses ( courseid, coursename ) VALUES (5,'Mechanical Engineering'); /* Insert values into registeredcourses. */ INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,1); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,3); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,4); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (2,2); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (2,5); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (3,4); /* Non-ANSI SQL GROUP BY statement. */ SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid GROUP BY a.studentid; /* ANSI SQL GROUP BY statement. */ SELECT a.studentname AS "Student Name" , COUNT(b.courseid) AS "Course IDs" FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid INNER JOIN courses c ON b.courseid = c.courseid GROUP BY a.studentname; |
I hope this helps those learning the correct way to write SQL.
DML Event Management
Data Manipulation Language (DML)
DML statements add data to, change data in, and remove data from tables. This section examines four DML statements—the INSERT, UPDATE, DELETE, and MERGE statements—and builds on concepts of data transactions. The INSERT statement adds new data, the UPDATE statement changes data, the DELETE statement removes data from the database, and the MERGE statement either adds new data or changes existing data.
Any INSERT, UPDATE, MERGE, or DELETE SQL statement that adds, updates, or deletes rows in a table locks rows in a table and hides the information until the change is committed or undone (that is, rolled back). This is the nature of ACID-compliant SQL statements. Locks prevent other sessions from making a change while a current session is working with the data. Locks also restrict other sessions from seeing any changes until they’re made permanent. The database keeps two copies of rows that are undergoing change. One copy of the rows with pending changes is visible to the current session, while the other displays committed changes only.
ACID Compliant Transactions
ACID compliance relies on a two-phase commit (2PC) protocol and ensures that the current session is the only one that can see new inserts, updated column values, and the absence of deleted rows. Other sessions run by the same or different users can’t see the changes until you commit them.
ACID Compliant INSERT Statements
The INSERT statement adds rows to existing tables and uses a 2PC protocol to implement ACID- compliant guarantees. The SQL INSERT statement is a DML statement that adds one or more rows to a table. Oracle supports a VALUES clause when adding a single-row, and support a subquery when adding one to many rows.
The figure below shows a flow chart depicting an INSERT statement. The process of adding one or more rows to a table occurs during the first phase of an INSERT statement. Adding the rows exhibits both atomic and consistent properties. Atomic means all or nothing: it adds one or more rows and succeeds, or it doesn’t add any rows and fails. Consistent means that the addition of rows is guaranteed whether the database engine adds them sequentially or concurrently in threads.
Concurrent behaviors happen when the database parallelizes DML statements. This is similar to the concept of threads as lightweight processes that work under the direction of a single process. The parallel actions of a single SQL statement delegate and manage work sent to separate threads. Oracle supports all ACID properties and implements threaded execution as parallel operations. All tables support parallelization.
After adding the rows to a table, the isolation property prevents any other session from seeing the new rows—that means another session started by the same user or by another user with access to the same table. The atomic, consistent, and isolation properties occur in the first phase of any INSERT statement. The durable property is exclusively part of the second phase of an INSERT statement, and rows become durable when the COMMIT statement ratifies the insertion of the new data.
ACID Compliant UPDATE Statements
An UPDATE statement changes column values in one-to-many rows. With a WHERE clause, you update only rows of interest, but if you forget the WHERE clause, an UPDATE statement would run against all rows in a table. Although you can update any column in a row, it’s generally bad practice to update a primary or foreign key column because you can break referential integrity. You should only update non-key data in tables—that is, the data that doesn’t make a row unique within a table.
Changes to column values are atomic when they work. For scalability reasons, the database implementation of updates to many rows is often concurrent, in threads through parallelization. This process can span multiple process threads and uses a transaction paradigm that coordinates changes across the threads. The entire UPDATE statement fails when any one thread fails.
Similar to the INSERT statement, UPDATE statement changes to column values are also hidden until they are made permanent with the application of the isolation property. The changes are hidden from other sessions, including sessions begun by the same database user.
It’s possible that another session might attempt to lock or change data in a modified but uncommitted row. When this happens, the second DML statement encounters a lock and goes into a wait state until the row becomes available for changes. If you neglected to set a timeout value for the wait state, such as this clause, the FOR UPDATE clause waits until the target rows are unlocked:
WAIT n |
As the figure below shows, actual updates are first-phase commit elements. While an UPDATE statement changes data, it changes only the current session values until it is made permanent by a COMMIT statement. Like the INSERT statement, the atomic, consistent, and isolation properties of an UPDATE statement occur during the first phase of a 2PC process. Changes to column values are atomic when they work. Any column changes are hidden from other sessions until the UPDATE statement is made permanent by a COMMIT or ROLLBACK statement, which is an example of the isolation property.
Any changes to column values can be modified by an ON UPDATE trigger before a COMMIT statement. ON UPDATE triggers run inside the first phase of the 2PC process. A COMMIT or ROLLBACK statement ends the transaction scope of the UPDATE statement.
The Oracle database engine can dispatch changes to many threads when an UPDATE statement works against many rows. UPDATE statements are consistent when these changes work in a single thread-of-control or across multiple threads with the same results.
As with the INSERT statement, the atomic, consistent, and isolation properties occur during the first phase of any UPDATE statement, and the COMMIT statement is the sole activity of the second phase. Column value changes become durable only with the execution of a COMMIT statement.
ACID Compliant DELETE Statements
A DELETE statement removes rows from a table. Like an UPDATE statement, the absence of a WHERE clause in a DELETE statement deletes all rows in a table. Deleted rows remain visible outside of the transaction scope where it has been removed. However, any attempts to UPDATE those deleted rows are held in a pending status until they are committed or rolled back.
You delete rows when they’re no longer useful. Deleting rows can be problematic when rows in another table have a dependency on the deleted rows. Consider, for example, a customer table that contains a list of cell phone contacts and an address table that contains the addresses for some but not all of the contacts. If you delete a row from the customer table that still has related rows in the address table, those address table rows are now orphaned and useless.
As a rule, you delete data from the most dependent table to the least dependent table, which is the opposite of the insertion process. Basically, you delete the child record before you delete the parent record. The parent record holds the primary key value, and the child record holds the foreign key value. You drop the foreign key value, which is a copy of the primary key, before you drop the primary key record. For example, you would insert a row in the customer table before you insert a row in the address table, and you delete rows from the address table before you delete rows in the customer table.
The figure below shows the logic behind a DELETE statement. Like the INSERT and UPDATE statements, acid, consistency, and isolation properties of the ACID-compliant transaction are managed during the first phase of a 2PC. The durability property is managed by the COMMIT or ROLLBACK statement.
There’s no discussion or diagrams for the MERGE statement because it does either an INSERT or UPDATE statement based on it’s internal logic. That means a MERGE statement is ACID compliant like an INSERT or UPDATE statement.
AlmaLinux+SQLDeveloper
This post makes the assumption that you’ve installed the current version of Java and the Java SDK. If you haven’t, you can find instructions on my blog. You will also need to have an installation of the Oracle database running on your server or a remote service, or cloud micro service.
The remainder of this post deals with downloading, installing, and configuring Oracle’s SQL Developer for AlmaLinux 9, which is the GNU version of Red Hat Enterprise 9.
- Go to Oracle’s download site and download the sqldeveloper RPM file. You will need to provide your Oracle credentials to download SQL Developer. It will download to your sudoer account’s Download directory. In this example the sudoer user is the student user.
You should see the following web page and click on the Download link, provided you’re installing on Linux it’ll look like the next image.
Then, you need to accept the license and click the Download button. Oracle will prompt you for your credentials if you’re not logged in on the web page already.
- Next, you need to navigate to the Downloads directory and install the sqldeveloper RPM. Assuming your sudoer user is student, you can get to the Downloads directory with the following command.
cd ~student/Downloads
Assuming, you downloaded the SQL Developer package, you can use the following command to install any downloaded version of sqldeveloper package.
sudo rpm -Uvh `ls sqldeveloper*.rpm`
The log file for this is:
Display detailed console log →
warning: RPM v3 packages are deprecated: sqldeveloper-22.2.1-234.1810.noarch Verifying... ################################# [100%] Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Preparing... ################################# [100%] warning: RPM v3 packages are deprecated: sqldeveloper-22.2.1-234.1810.noarch Updating / installing... 1:sqldeveloper-22.2.1-234.1810 ################################# [100%]
- Click on Activities link in the upper left corner and then the clustered nine dots to view applications. Choose the SQL Developer icon and double click and you should see the following dialog if you’re a first time user. Unless you’re upgrading, click the No button to proceed.
- The first official screen after checking whether you need to transfer existing settings wants to know whether or allow or disallow user tracking. Click the OK button if you don’t mind Oracle tracking your use, or click the Allow automated usage reporting to Oracle checkbox to disallow Oracle tracking your use before you click the OK button.
- The next screen lets you set up a TNS names file or use an existing file. I clicked on the XE existing database to continue.
This is a password prompt for the TNS name resolution of XE, which should point to the Oracle Database 11g Express Edition. (I’d use a more current version but I couldn’t resist using the smaller footprint of the pre-containerized Oracle databases.)
Replace XE with the name of a sandboxed user, like student, and the password for the student user before you click the OK button. (If you don’t know what a sandboxed user is, you should. It’s a user with limited access to a database of the same name in the context of an Oracle database. A non-sandboxed user has global privileges like the system user.)
- The next screen lets you enter SQL statements agains the student database. You can click the X button in the top right corner to close the application.
You’ve now installed SQL Developer. However, sometimes I want to start SQL Developer from the command-line interface (CLI) but you’ll get a bunch of warnings and unnecessary Java non-critical errors. So, I create an alias to avoid the extraneous noise. I create the sqldeveloper alias in the .bashrc file for it. You can create a sqldeveloper alias by adding the following line to your .bashrc file:
The unnecessary noise when you don’t create a sqldeveloper alias.
Display detailed console log →
Oracle SQL Developer Copyright (c) 2005, 2021, Oracle and/or its affiliates. All rights reserved. OpenJDK 64-Bit Server VM warning: Options -Xverify:none and -noverify were deprecated in JDK 13 and will likely be removed in a future release. WARNING: A terminally deprecated method in java.lang.System has been called WARNING: System::setSecurityManager has been called by org.netbeans.TopSecurityManager (file:/opt/sqldeveloper/netbeans/platform/lib/boot.jar) WARNING: Please consider reporting this to the maintainers of org.netbeans.TopSecurityManager WARNING: System::setSecurityManager will be removed in a future release WARNING: A terminally deprecated method in java.lang.System has been called WARNING: System::setSecurityManager has been called by oracle.ide.IdeCore (file:/opt/sqldeveloper/ide/extensions/oracle.ide.jar) WARNING: Please consider reporting this to the maintainers of oracle.ide.IdeCore WARNING: System::setSecurityManager will be removed in a future release java.lang.IllegalAccessException: class oracle.ideimpl.config.EnvironOptionsPanel cannot access class com.sun.java.swing.plaf.gtk.GTKLookAndFeel (in module java.desktop) because module java.desktop does not export com.sun.java.swing.plaf.gtk to unnamed module @49c746f at java.base/jdk.internal.reflect.Reflection.newIllegalAccessException(Reflection.java:392) at java.base/java.lang.reflect.AccessibleObject.checkAccess(AccessibleObject.java:674) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:489) at java.base/java.lang.reflect.ReflectAccess.newInstance(ReflectAccess.java:128) at java.base/jdk.internal.reflect.ReflectionFactory.newInstance(ReflectionFactory.java:347) at java.base/java.lang.Class.newInstance(Class.java:645) at oracle.ideimpl.config.EnvironOptionsPanel._initLafCombo(EnvironOptionsPanel.java:540) at oracle.ideimpl.config.EnvironOptionsPanel.initComponents(EnvironOptionsPanel.java:238) at oracle.ideimpl.config.EnvironOptionsPanel.<init>(EnvironOptionsPanel.java:99) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480) at javax.ide.util.MetaClass.newInstance(MetaClass.java:145) at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:122) at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:65) at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833) java.lang.IllegalAccessException: class oracle.ideimpl.config.EnvironOptionsPanel cannot access class com.sun.java.swing.plaf.gtk.GTKLookAndFeel (in module java.desktop) because module java.desktop does not export com.sun.java.swing.plaf.gtk to unnamed module @49c746f at java.base/jdk.internal.reflect.Reflection.newIllegalAccessException(Reflection.java:392) at java.base/java.lang.reflect.AccessibleObject.checkAccess(AccessibleObject.java:674) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:489) at java.base/java.lang.reflect.ReflectAccess.newInstance(ReflectAccess.java:128) at java.base/jdk.internal.reflect.ReflectionFactory.newInstance(ReflectionFactory.java:347) at java.base/java.lang.Class.newInstance(Class.java:645) at oracle.ideimpl.config.EnvironOptionsPanel._initLafCombo(EnvironOptionsPanel.java:540) at oracle.ideimpl.config.EnvironOptionsPanel.initComponents(EnvironOptionsPanel.java:238) at oracle.ideimpl.config.EnvironOptionsPanel.<init>(EnvironOptionsPanel.java:99) at oracle.dbtools.raptor.config.EnvironOptionsPanelWrapper.<init>(EnvironOptionsPanelWrapper.java:30) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480) at javax.ide.util.MetaClass.newInstance(MetaClass.java:145) at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:122) at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:65) at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833) |
You create the alias like this:
alias sqldeveloper="sqldeveloper 2>/dev/null &" |
That completes the instructions. Good luck with SQL Developer. It’s a great tool.
Debugging PL/SQL Functions
Teaching student how to debug a PL/SQL function takes about an hour now. I came up with the following example of simple deterministic function that adds three numbers and trying to understand how PL/SQL implicitly casts data types. The lecture follows a standard Harvard Case Study, which requires the students to suggest next steps. The starting code is:
1 2 3 4 5 6 7 8 9 10 | CREATE OR REPLACE FUNCTION adding ( a DOUBLE PRECISION , b INTEGER , c DOUBLE PRECISION ) RETURN INTEGER DETERMINISTIC IS BEGIN RETURN a + b + c; END; / |
Then, we use one test case for two scenarios:
SELECT adding(1.25, 2, 1.24) AS "Test Case 1" , adding(1.25, 2, 1.26) AS "Test Case 2" FROM dual; |
It returns:
Test Case 1 Test Case 2
----------- -----------
4 5 |
Then, I ask why does that work? Somehow many students can’t envision how it works. Occasionally, a student will say it must implicitly cast the INTEGER to a DOUBLE PRECISION data type and add the numbers as DOUBLE PRECISION values before down-casting it to an INTEGER data type.
Whether I have to explain it or a student volunteers it, the next question is: “How would you build a test case to see if the implicit casting?” Then, I ask them to take 5-minutes and try to see how the runtime behaves inside the function.
At this point in the course, they only know how to use dbms_output.put_line to print content from anonymous blocks. So, I provide them with a modified adding function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | CREATE OR REPLACE FUNCTION adding ( a DOUBLE PRECISION , b INTEGER , c DOUBLE PRECISION ) RETURN INTEGER DETERMINISTIC IS /* Define a double precision temporary result variable. */ temp_result NUMBER; /* Define an integer return variable. */ temp_return INTEGER; BEGIN /* * Perform the calculation and assign the value to the temporary * result variable. */ temp_result := a + b + c; /* * Assign the temporary result variable to the return variable. */ temp_return := temp_result; /* Return the integer return variable as the function result. */ RETURN temp_return; END; / |
The time limit ensures they spend their time typing the code from the on screen display and limits testing to the dbms_output.put_line attempt. Any more time and one or two of them would start using Google to find an answer.
I introduce the concept of a Black Box as their time expires, and typically use an illustration like the following to explain that by design you can’t see inside runtime operations of functions. Then, I teach them how to do exactly that.
You can test the runtime behaviors and view the variable values of functions by doing these steps:
- Create a debug table, like
CREATE TABLE debug ( msg VARCHAR2(200));
- Make the function into an autonomous transaction by:
- Adding the PRAGMA (or precompiler) instruction in the declaration block.
- Adding a COMMIT at the end of the execution block.
- Use an INSERT statement to write descriptive text with the variable values into the debug table.
Here’s the refactored test code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | CREATE OR REPLACE FUNCTION adding ( a DOUBLE PRECISION , b INTEGER , c DOUBLE PRECISION ) RETURN INTEGER DETERMINISTIC IS /* Define a double precision temporary result variable. */ temp_result NUMBER; /* Define an integer return variable. */ temp_return INTEGER; /* Precompiler Instrunction. */ PRAGMA AUTONOMOUS_TRANSACTION; BEGIN /* * Perform the calculation and assign the value to the temporary * result variable. */ temp_result := a + b + c; /* Insert the temporary result variable into the debug table. */ INSERT INTO debug (msg) VALUES ('Temporary Result Value: ['||temp_result||']'); /* * Assign the temporary result variable to the return variable. */ temp_return := temp_result; /* Insert the temporary result variable into the debug table. */ INSERT INTO debug (msg) VALUES ('Temporary Return Value: ['||temp_return||']'); /* Commit to ensure the write succeeds in a separate process scope. */ COMMIT; /* Return the integer return variable as the function result. */ RETURN temp_return; END; / |
While an experienced PL/SQL developer might ask while not introduce conditional computation, the answer is that’s for another day. Most students need to uptake pieces before assembling pieces and this example is already complex for a newbie.
The same test case works (shown to avoid scrolling up):
SELECT adding(1.25, 2, 1.24) AS "Test Case 1" , adding(1.25, 2, 1.26) AS "Test Case 2" FROM dual; |
It returns:
Test Case 1 Test Case 2
----------- -----------
4 5 |
Now, they can see the internal step-by-step values with this query:
COL msg FORMAT A30 HEADING "Internal Variable Auditing" SELECT msg FROM debug; |
It returns:
Internal Variable Auditing ------------------------------ Temporary Result Value: [4.49] Temporary Return Value: [4] Temporary Result Value: [4.51] Temporary Return Value: [5] 4 rows selected. |
What we learn is that:
- Oracle PL/SQL up-casts the b variable from an integer to a double precision data type before adding the three input variables.
- Oracle PL/SQL down-casts the sum of the three input variables from a double precision data type to an integer by applying traditionally rounding.
I hope this helps those trying to understand implicit casting and discovering how to unhide an opaque function’s operations for debugging purposes.
Oracle PLS-00103 Gotcha
Teaching PL/SQL can be fun and sometimes challenging when you need to troubleshoot a student error. Take the Oracle PLS-00103 error can be very annoying when it return like this:
24/5 PLS-00103: Encountered the symbol "LV_CURRENT_DATE" WHEN expecting one OF the following: language |
Then, you look at the code and see:
22 23 24 25 | , pv_user_id NUMBER ) IS /* Declare local constants. */ lv_current_date DATE := TRUNC(SYSDATE); |
Obviously, there’s nothing wrong on the line number that the error message pointed. Now, here’s where it gets interesting because of a natural human failing. The student thought they had something wrong with declaring the variable and tested as stand alone procedure and anonymous block. Naturally, they were second guessing what they knew about the PL/SQL.
That’s when years of experience with PL/SQL kicks in to solve the problem. The trick is recognizing two things:
- The error message points to the first line of code in a package body.
- The error is pointing to the first character on the line after the error.
That meant that the package body was incorrectly defined. A quick check to the beginning of the package body showed:
1 2 3 4 5 6 | CREATE OR REPLACE PACKAGE account_creation AS PROCEDURE insert_contact ( pv_first_name VARCHAR2 , pv_middle_name VARCHAR2 := NULL |
The student failed to designate the package as an implementation by omitting the keyword BODY from line 2. The proper definition of the package body should be:
1 2 3 4 5 6 | CREATE OR REPLACE PACKAGE BODY account_creation AS PROCEDURE insert_contact ( pv_first_name VARCHAR2 , pv_middle_name VARCHAR2 := NULL |
That’s the resolution for the error message. I wrote this because I checked if they should have been able to find a helpful article with a google search. I discovered that there wasn’t an answer like this that came up after 10 minutes of various searches.
As always, I hope this helps those writing PL/SQL.
PL/SQL Overloading
So, I wrote an updated example of my grandma and tweetie_bird for my students. It demonstrates overloading with the smallest parameter lists possible across a transaction of two tables. It also shows how one version of the procedure can call another version of the procedure.
The tables are created with the following:
/* Conditionally drop grandma table and grandma_s sequence. */ BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name IN ('GRANDMA','GRANDMA_SEQ')) LOOP IF i.object_type = 'TABLE' THEN /* Use the cascade constraints to drop the dependent constraint. */ EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name; END IF; END LOOP; END; / /* Create the table. */ CREATE TABLE GRANDMA ( grandma_id NUMBER CONSTRAINT grandma_nn1 NOT NULL , grandma_house VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL , created_by NUMBER CONSTRAINT grandma_nn3 NOT NULL , CONSTRAINT grandma_pk PRIMARY KEY (grandma_id) ); /* Create the sequence. */ CREATE SEQUENCE grandma_seq; /* Conditionally drop a table and sequence. */ BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name IN ('TWEETIE_BIRD','TWEETIE_BIRD_SEQ')) LOOP IF i.object_type = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name; END IF; END LOOP; END; / /* Create the table with primary and foreign key out-of-line constraints. */ CREATE TABLE TWEETIE_BIRD ( tweetie_bird_id NUMBER CONSTRAINT tweetie_bird_nn1 NOT NULL , tweetie_bird_house VARCHAR2(30) CONSTRAINT tweetie_bird_nn2 NOT NULL , grandma_id NUMBER CONSTRAINT tweetie_bird_nn3 NOT NULL , created_by NUMBER CONSTRAINT tweetie_bird_nn4 NOT NULL , CONSTRAINT tweetie_bird_pk PRIMARY KEY (tweetie_bird_id) , CONSTRAINT tweetie_bird_fk FOREIGN KEY (grandma_id) REFERENCES GRANDMA (GRANDMA_ID) ); /* Create sequence. */ CREATE SEQUENCE tweetie_bird_seq; |
The sylvester package specification holds the two overloaded procedures, like:
CREATE OR REPLACE PACKAGE sylvester IS /* Three variable length strings. */ PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 , pv_system_user_name VARCHAR2 ); /* Two variable length strings and a number. */ PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 , pv_system_user_id NUMBER ); END sylvester; / |
The sylvester package implements two warner_brother procedures. One takes the system user’s ID and the other takes the system user’s name. The procedure that accepts the system user name queries the system_user table with the system_user_name to get the system_user_id column and then calls the other version of itself. This demonstrates how you only write logic once when overloading and let one version call the other with the added information.
Here’s the sylvester package body code:
CREATE OR REPLACE PACKAGE BODY sylvester IS /* Procedure warner_brother with user name. */ PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 , pv_system_user_id NUMBER ) IS /* Declare a local variable for an existing grandma_id. */ lv_grandma_id NUMBER; FUNCTION get_grandma_id ( pv_grandma_house VARCHAR2 ) RETURN NUMBER IS /* Initialized local return variable. */ lv_retval NUMBER := 0; -- Default value is 0. /* A cursor that lookups up a grandma's ID by their name. */ CURSOR find_grandma_id ( cv_grandma_house VARCHAR2 ) IS SELECT grandma_id FROM grandma WHERE grandma_house = cv_grandma_house; BEGIN /* Assign a grandma_id as the return value when a row exists. */ FOR i IN find_grandma_id(pv_grandma_house) LOOP lv_retval := i.grandma_id; END LOOP; /* Return 0 when no row found and the grandma_id when a row is found. */ RETURN lv_retval; END get_grandma_id; BEGIN /* Set the savepoint. */ SAVEPOINT starting; /* * Identify whether a member account exists and assign it's value * to a local variable. */ lv_grandma_id := get_grandma_id(pv_grandma_house); /* * Conditionally insert a new member account into the member table * only when a member account does not exist. */ IF lv_grandma_id = 0 THEN /* Insert grandma. */ INSERT INTO grandma ( grandma_id , grandma_house , created_by ) VALUES ( grandma_seq.NEXTVAL , pv_grandma_house , pv_system_user_id ); /* Assign grandma_seq.currval to local variable. */ lv_grandma_id := grandma_seq.CURRVAL; END IF; /* Insert tweetie bird. */ INSERT INTO tweetie_bird ( tweetie_bird_id , tweetie_bird_house , grandma_id , created_by ) VALUES ( tweetie_bird_seq.NEXTVAL , pv_tweetie_bird_house , lv_grandma_id , pv_system_user_id ); /* If the program gets here, both insert statements work. Commit it. */ COMMIT; EXCEPTION /* When anything is broken do this. */ WHEN OTHERS THEN /* Until any partial results. */ ROLLBACK TO starting; END; PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 , pv_system_user_name VARCHAR2 ) IS /* Define a local variable. */ lv_system_user_id NUMBER := 0; FUNCTION get_system_user_id ( pv_system_user_name VARCHAR2 ) RETURN NUMBER IS /* Initialized local return variable. */ lv_retval NUMBER := 0; -- Default value is 0. /* A cursor that lookups up a grandma's ID by their name. */ CURSOR find_system_user_id ( cv_system_user_id VARCHAR2 ) IS SELECT system_user_id FROM system_user WHERE system_user_name = pv_system_user_name; BEGIN /* Assign a grandma_id as the return value when a row exists. */ FOR i IN find_system_user_id(pv_system_user_name) LOOP lv_retval := i.system_user_id; END LOOP; /* Return 0 when no row found and the grandma_id when a row is found. */ RETURN lv_retval; END get_system_user_id; BEGIN /* Convert a system_user_name to system_user_id. */ lv_system_user_id := get_system_user_id(pv_system_user_name); /* Call the warner_brother procedure. */ warner_brother ( pv_grandma_house => pv_grandma_house , pv_tweetie_bird_house => pv_tweetie_bird_house , pv_system_user_id => lv_system_user_id ); EXCEPTION /* When anything is broken do this. */ WHEN OTHERS THEN /* Until any partial results. */ ROLLBACK TO starting; END; END sylvester; / |
The following anonymous block test case works with the code:
BEGIN sylvester.warner_brother( pv_grandma_house => 'Blue House' , pv_tweetie_bird_house => 'Cage' , pv_system_user_name => 'DBA 3' ); sylvester.warner_brother( pv_grandma_house => 'Blue House' , pv_tweetie_bird_house => 'Tree House' , pv_system_user_id => 4 ); END; / |
You can now query the results with this SQL*PLus formatting and query:
/* Query results from warner_brother procedure. */ COL grandma_id FORMAT 9999999 HEADING "Grandma|ID #" COL grandma_house FORMAT A14 HEADING "Grandma House" COL created_by FORMAT 9999999 HEADING "Created|By" COL tweetie_bird_id FORMAT 9999999 HEADING "Tweetie|Bird ID" COL tweetie_bird_house FORMAT A18 HEADING "Tweetie Bird House" SELECT * FROM grandma g INNER JOIN tweetie_bird tb ON g.grandma_id = tb.grandma_id; |
You should see the following data:
Grandma Created Tweetie Grandma Created
ID # Grandma House By Bird ID Tweetie Bird House ID # By
-------- -------------- -------- -------- ------------------ -------- --------
1 Blue House 3 1 Cage 1 3
1 Blue House 3 2 Tree House 1 4
As always, I hope complete code samples help solve real problems.
PL/pgSQL Transactions
There are many nuances that I show students about PL/pgSQL because first I teach them how to use PL/SQL. These are some of the differences:
- PL/SQL declares the function or procedure and then uses the IS keyword; whereas, PL/pgSQL uses the AS keyword.
- PL/SQL uses the RETURN keyword for functions declarations, like:
RETURN [data_type} IS
Whereas, PL/pgSQL uses the plural RETURNS keyword in the function declaration, like:
RETURNS [data_type] AS
- PL/SQL considers everything after the function or procedure header as the implicit declaration section; whereas, PL/pgSQL requires you block the code with something like $$ (double dollar symbols) and explicitly use the DECLARE keyword.
- PL/SQL supports local functions (inside the DECLARE block of a function or procedure); whereas, PL/pgSQL doesn’t.
- PL/SQL puts the variable modes (IN, INOUT, OUT) between the parameter name and type; whereas, PL/pgSQL puts them before the variable name.
- PL/SQL declares cursors like:
CURSOR cursor_name (parameter_list) IS
Whereas, PL/pgSQL declares them like
cursor_name CURSOR (parameter_list) FOR
- PL/SQL terminates and runs the block by using an END keyword, an optional module name, a semicolon to terminate the END; statement, and a forward slash to dispatch the program to PL/SQL statement engine:
END [module_name]; /
Whereas, PL/pgSQL terminates and runs the block by using an END keyword, a semicolon to terminate the END; statement, two dollar signs to end the PL/pgSQL block, and a semicolon to dispatch the program.
END LANGUAGE plpgsql; $$;
After all that basic syntax discussion, we try to create a sample set of tables, a function, a procedure, and a test case in PL/pgSQL. They’ve already done a virtually equivalent set of tasks in PL/SQL.
Here are the steps:
- Create the grandma and tweetie_bird tables:
/* Conditionally drop grandma table and grandma_s sequence. */ DROP TABLE IF EXISTS grandma CASCADE; /* Create the table. */ CREATE TABLE GRANDMA ( grandma_id SERIAL , grandma_house VARCHAR(30) NOT NULL , PRIMARY KEY (grandma_id) ); /* Conditionally drop a table and sequence. */ DROP TABLE IF EXISTS tweetie_bird CASCADE; /* Create the table with primary and foreign key out-of-line constraints. */ SELECT 'CREATE TABLE tweetie_bird' AS command; CREATE TABLE TWEETIE_BIRD ( tweetie_bird_id SERIAL , tweetie_bird_house VARCHAR(30) NOT NULL , grandma_id INTEGER NOT NULL , PRIMARY KEY (tweetie_bird_id) , CONSTRAINT tweetie_bird_fk FOREIGN KEY (grandma_id) REFERENCES grandma (grandma_id) );
- Create a get_grandma_id function that returns a number, which should be a valid primary key value from the grandma_id column of the grandma table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
CREATE OR REPLACE FUNCTION get_grandma_id ( IN pv_grandma_house VARCHAR ) RETURNS INTEGER AS $$ /* Required for PL/pgSQL programs. */ DECLARE /* Local return variable. */ lv_retval INTEGER := 0; -- Default value is 0. /* Use a cursor, which will not raise an exception at runtime. */ find_grandma_id CURSOR ( cv_grandma_house VARCHAR ) FOR SELECT grandma_id FROM grandma WHERE grandma_house = cv_grandma_house; BEGIN /* Assign a value when a row exists. */ FOR i IN find_grandma_id(pv_grandma_house) LOOP lv_retval := i.grandma_id; END LOOP; /* Return 0 when no row found and the ID # when row found. */ RETURN lv_retval; END; $$ LANGUAGE plpgsql;
- Create a Warner_brother procedure that writes data across two tables as a transaction. You con’t include any of the following in your functions or procedures because all PostgreSQL PL/pgSQL functions and procedures are transaction by default:
- SET TRANSACTION
- START TRANSACTION
- SAVEPOINT
- COMMIT
A ROLLBACK should be placed in your exception handler as qualified on lines #33 thru #36. The warner_brother procedure inserts rows into the grandma and tweetie_bird tables.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
/* Create or replace procedure warner_brother. */ CREATE OR REPLACE PROCEDURE warner_brother ( pv_grandma_house VARCHAR , pv_tweetie_bird_house VARCHAR ) AS $$ /* Required for PL/pgSQL programs. */ DECLARE /* Declare a local variable for an existing grandma_id. */ lv_grandma_id INTEGER; BEGIN /* Check for existing grandma row. */ lv_grandma_id := get_grandma_id(pv_grandma_house); IF lv_grandma_id = 0 THEN /* Insert grandma. */ INSERT INTO grandma ( grandma_house ) VALUES ( pv_grandma_house ) RETURNING grandma_id INTO lv_grandma_id; END IF; /* Insert tweetie bird. */ INSERT INTO tweetie_bird ( tweetie_bird_house , grandma_id ) VALUES ( pv_tweetie_bird_house , lv_grandma_id ); EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE NOTICE '[%] [%]', SQLERRM, SQLSTATE; END; $$ LANGUAGE plpgsql;
You should take note of the RETURNING-INTO statement on line #22. The alternative to this clause isn’t pretty if you know that PostgreSQL uses a table name, column name, and the literal seq value separated by underscores (that is, snake case), like:
/* Assign current value to local variable. */ lv_grandma_id := CURRVAL('grandma_grandma_id_seq');
It would be even uglier if you had to look up the sequence name, like:
/* Assign current value to local variable. */ lv_grandma_id := CURRVAL(pg_get_serial_sequence('grandma','grandma_id'));
- You can test the combination of these two stored procedures with the following DO-block:
/* Test the warner_brother procedure. */ DO $$ BEGIN /* Insert the yellow house. */ CALL warner_brother( 'Yellow House', 'Cage'); CALL warner_brother( 'Yellow House', 'Tree House'); /* Insert the red house. */ CALL warner_brother( 'Red House', 'Cage'); CALL warner_brother( 'Red House', 'Tree House'); END; $$ LANGUAGE plpgsql;
Then, query the results:
SELECT * FROM grandma g INNER JOIN tweetie_bird tb ON. g.grandma_id = tb.grandma_id;
It should return:
grandma_id | grandma_house | tweetie_bird_id | tweetie_bird_house | grandma_id ------------+---------------+-----------------+--------------------+------------ 1 | Red House | 1 | Cage | 1 1 | Red House | 2 | Tree House | 1 2 | Yellow House | 3 | Cage | 2 2 | Yellow House | 4 | Tree House | 2 (4 rows)
As always, I hope writing a clear and simple examples helps those looking for sample code.
Oracle Partitioned Tables
Oracle Partitioned Tables
Learning Outcomes
- Learn about List Partitioning.
- Learn about Range Partitioning.
- Learn about Hash Partitioning.
- Learn about Composite Partitioning.
Lesson Material
Partitioning is the process of breaking up a data source into a series of data sources. Partitioned tables are faster to access and transact against. Partitioning data becomes necessary as the amount of data grows in any table. It speeds the search to find rows and insert, update, or delete rows.
Oracle Database 21c supports four types of table partitioning: list, range, hash, and composite partitioning.
List Partitioning
A list partition works by identifying a column that contains a value, such as a STATE column in an ADDRESS table. Partitioning clauses follow the list of columns and constraints.
A list partition could use a STATE column, like the following (the complete example is avoided to conserve space, and the three dots represent the balance of partitions not shown):
CREATE TABLE franchise ( franchise_id NUMBER CONSTRAINT pk_franchise PRIMARY KEY , franchise_name VARCHAR(20) , city VARCHAR(20) , state VARCHAR(20)) PARTITION BY LIST(state) ( PARTITION offshore VALUES('Alaska', 'Hawaii') , PARTITION west VALUES('California', 'Oregon', 'Washington') , PARTITION desert VALUES ('Arizona','New Mexico') , PARTITION rockies VALUES ('Colorado', 'Idaho', 'Montana', 'Wyoming') , ... ); |
This can be used with other values such as ZIP codes with great effect, but the maintenance of list partitioning can be considered costly. Cost occurs when the list of values changes over time. Infrequent change means low cost, while frequent change means high costs. In the latter case, you should consider other partitioning strategies. Although an Oracle database supports partitioning on a variable-length string, MySQL performs list partitioning only on integer columns.
Range Partitioning
Range partitioning is very helpful on any column that contains a continuous metric, such as dates or time. It works by stating a minimum set that is less than a certain value, and then a group of sets of higher values until you reach the top most set of values. This type of partition helps you improve performance by letting you search ranges rather than complete data sets. Range partitioning is also available in MySQL.
A range example based on dates could look like this:
PARTITION BY RANGE(rental_date) ( PARTITION rental_jan2011 VALUES LESS THAN TO_DATE('31-JAN-11','DD-MON-YY') , PARTITION rental_feb2011 VALUES LESS THAN TO_DATE('28-FEB-11','DD-MON-YY') , PARTITION rental_mar2011 VALUES LESS THAN TO_DATE('31-MAR-11','DD-MON-YY') , ... ); |
The problem with this type of partitioning, however, is that the new months require constant management. Many North American businesses simply add partitions for all months in the year as an annual maintenance task during the holidays in November or December. Companies that opt for bigger range increments reap search and access benefits from range partitioning, while minimizing ongoing maintenance expenses.
Hash Partitioning
Hash partitioning is much easier to implement than list or range partitioning. Many DBAs favor it because it avoids the manual maintenance of list and range partitioning. Oracle Database 21c documentation recommends that you implement a hash for the following reasons:
- There is no concrete knowledge about how much data maps to a partitioning range.
- The sizes of partitions are unknown at the outset and difficult to balance as data is added to the database.
- A range partition might cluster data in an ineffective way.
This next statement creates eight partitions and stores them respectively in one of the eight tablespaces. The hash partition manages nodes and attempts to balance the distribution of rows across the nodes.
PARTITION BY HASH(store) PARTITIONS 8 STORE IN (tablespace1, tablespace2, tablespace3, tablespace4 ,tablespace5, tablespace6, tablespace7, tablespace8); |
As you can imagine the maintenance for this type of partitioning is low. Some DBAs choose this method to get an initial sizing before adopting a list or range partitioning plan. Maximizing the physical resources of the machine ultimately rests with the DBAs who manage the system. Developers need to stand ready to assist DBAs with analysis and syntax support.
Composite Partitioning
Composite partitioning requires a partition and subpartition. The composites are combinations of two types of partitioning—typically, list and range partitioning, or range and hash composite partitioning. Which of these you should choose depends on a few considerations. List and range composite partitioning is done for historical information and is well suited for data warehouses. This method lets you partition on unordered or unrelated column values.
A composite partition like this uses the range as the partition and the list as the subpartition, like the following:
PARTITION BY RANGE (rental_date) SUBPARTITION BY LIST (state) (PARTITION FQ1_1999 VALUES LESS THAN (TO_DATE('1-APR-2011','DD-MON-YYYY')) (SUBPARTITION offshore VALUES('Alaska', 'Hawaii') , SUBPARTITION west VALUES('California', 'Oregon', 'Washington') , SUBPARTITION desert VALUES ('Arizona','New Mexico') , SUBPARTITION rockies VALUES ('Colorado', 'Idaho', 'Montana', 'Wyoming') , ... ) ,(PARTITION FQ2_1999 VALUES LESS THAN (TO_DATE('1-APR-2011','DD-MON-YYYY')) (SUBPARTITION offshore VALUES('Alaska', 'Hawaii') , SUBPARTITION west VALUES('California', 'Oregon', 'Washington') , SUBPARTITION desert VALUES ('Arizona','New Mexico') , SUBPARTITION rockies VALUES ('Colorado', 'Idaho', 'Montana', 'Wyoming') , ... ) , ... ) |
Range and hash composite partitioning is done for historical information when you also need to stripe data. Striping is the process of creating an attribute in a table that acts as a natural subtype or separator of data. Users typically view data sets of one subtype, which means organizing the data by stripes (subtypes) can speed access based on user access patterns.
Range is typically the partition and the hash is the subpartition in this composite partitioning schema. The syntax for this type of partition is shown next:
PARTITION BY RANGE (rental_date) SUBPARTITION BY HASH(store) SUBPARTITIONS 8 STORE IN (tablespace1, tablespace2, tablespace3 ,tablespace4, tablespace5, tablespace6 ,tablespace7, tablespace8) ( PARTITION rental_jan2011 VALUES LESS THAN TO_DATE('31-JAN-11','DD-MON-YY') , PARTITION rental_feb2011 VALUES LESS THAN TO_DATE('28-FEB-11','DD-MON-YY') , PARTITION rental_mar2011 VALUES LESS THAN TO_DATE('31-MAR-11','DD-MON-YY') , ... ) |
PL/SQL Table Function
An Oracle example was requested as a comparison against the quick tutorial I wrote on how to do this in PostgreSQL’s PL/pgSQL. Unfortunately, there are many more moving parts to deliver this type of solution in Oracle’s PL/SQL.
The functions is same and simple. It returns the list of conquistadors that were originally German. It does that by filtering on the lang column in the table. For example, you use ‘de‘ for German. The additional moving parts are the required User-Defined Types (UDTs); one is a record structure and the other is a list (or Oracle parlance table).
The drops are unconditional and as such will trigger errors the first time they’re run but including PL/SQL blocks to make them conditional would have made the code much larger. It’s already larger because Oracle doesn’t support comma-delimited lists in the VALUES clause.
I’ll stage this with the same conquistador table used in the last post. Then, connect to the psql shell and run the following script file:
/* Drop the conquistador table. */ DROP TABLE conquistador; /* Create the conquistador table. */ CREATE TABLE conquistador ( conquistador_id NUMBER , conquistador VARCHAR(30) , actual_name VARCHAR(30) , nationality VARCHAR(30) , lang VARCHAR(2)); /* Drop the conquistador sequence. */ DROP SEQUENCE conquistador_seq; /* Create the conquistador_seq with a 1001 start value. */ CREATE SEQUENCE conquistador_seq START WITH 1001; /* Insert 9 rows into the table. */ INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Juan de Fuca','Ioánnis Fokás','Greek','el'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Nicolás de Federmán','Nikolaus Federmann','German','de'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Sebastián Caboto','Sebastiano Caboto','Venetian','it'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Jorge de la Espira','Georg von Speyer','German','de'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Eusebio Francisco Kino','Eusebius Franz Kühn','Italian','it'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Wenceslao Linck','Wenceslaus Linck','Bohemian','cs'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Fernando Consag','Ferdinand Konšcak','Croatian','sr'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Américo Vespucio','Amerigo Vespucci','Italian','it'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Alejo García','Aleixo Garcia','Portuguese','pt'); |
While unnecessary in PL/pgSQL, you must create User-Defined Types (UDTs) to write a table function. You must also create a local procedure to avoid allocating memory before assigning values to the list. These are the UDTs required:
/* Drop the dependency before the dependent type. */ DROP TYPE conquistador_table; DROP TYPE conquistador_struct; /* Create the UDT for a record structure accessible in SQL. */ CREATE OR REPLACE TYPE conquistador_struct IS OBJECT ( conquistador VARCHAR(30) , actual_name VARCHAR(30) , nationality VARCHAR(30)); / /* Create a list of the UDT. */ CREATE OR REPLACE TYPE conquistador_table IS TABLE OF conquistador_struct; / |
Drop any existing function or procedure of the same name before you try to build it. Oracle’s OR REPLACE fails when you try to use it for a function when there is already a procedure using the same name, and vice versa.
/* Drop the function to avoid any conflict with a procedure of the same name. */ DROP FUNCTION getConquistador; |
Now, you can build another script file to create the getConquistador function, like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | /* Create the function. */ CREATE OR REPLACE FUNCTION getConquistador (pv_lang IN VARCHAR) RETURN conquistador_table IS /* Declare a return variable. */ lv_retval CONQUISTADOR_TABLE := conquistador_table(); /* Declare a dynamic cursor. */ CURSOR get_conquistador ( cv_lang VARCHAR2 ) IS SELECT c.conquistador , c.actual_name , c.nationality FROM conquistador c WHERE c.lang = cv_lang; /* Local procedure to add to the song. */ PROCEDURE ADD ( pv_input CONQUISTADOR_STRUCT ) IS BEGIN lv_retval.EXTEND; lv_retval(lv_retval.COUNT) := pv_input; END ADD; BEGIN /* Read through the cursor and assign to the UDT table. */ FOR i IN get_conquistador(pv_lang) LOOP add(conquistador_struct( i.conquistador , i.actual_name , i.nationality )); END LOOP; /* Return collection. */ RETURN lv_retval; END; / |
While there is some white space for readability, the Oracle version is basically twice as long as the PL/pgSQL version. It also requires you to add UDTs to the data dictionary to make it work. PL/pgSQL actually doesn’t let you add references to type definitions and requires you use enumerated descriptions with column definitions.
Then, you can test it with the following syntax. The TABLE function is required to convert the list to a SQL consumable result set:
COL conquistador FORMAT A21 COL actual_name FORMAT A21 COL nationality FORMAT A12 SELECT * FROM TABLE(getConquistador('de')); |
It will return the following:
CONQUISTADOR ACTUAL_NAME NATIONALITY --------------------- --------------------- ------------ Nicolás de Federmán Nikolaus Federmann German Jorge de la Espira Georg von Speyer German 2 rows selected. |
As always, I hope this helps with a technique that’s useful.
Subscribe via RSS