MacLochlainns Weblog

Somebody wanted a post on SQL statement tuning, so here it is. SQL statement tuning requires that you understand table definitions, constraints, and indexes plus how they interact through joins before you can begin tuning. Oracle presents some of the most advanced tools for tuning SQL statements.

This example works through a demonstration of tuning one statement with multiple joins. The key elements of tuning any SQL statements is understanding the table structures and data. The key worry about tuning is that optimizing for a query can harm the effectiveness of OLTP processing by slowing the performance of INSERT and UPDATE statements. Any DML statement that adds or modifies data in tables also modifies indexes built to support queries against the table.

Oracle

You have two approaches in an Oracle database. The old way and the new way. They’re really not that different but the DBMS_XPLAN package provides a richer set of analytical data for less work.

The old way and the new way both use a single way for generating the SQL execution plan. You do that with the EXPLAIN PLAN statement.

The EXPLAIN PLAN statement is followed by a SET operator. You use the SET operator to assign a value to the STATEMENT_ID column. This is a column that uniquely identifies statements in the PLAN_TABLE. You must ensure that all STATEMENT_ID values are unique, or you’ll get incorrect results when you analyze plans. There’s no constraint that magically does this for you. After you set the statement identifier, you use the FOR keyword before the statement that you want to analyze.

An example query follows below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

EXPLAIN PLAN
SET STATEMENT_ID = '&input'
FOR
SELECT   DISTINCT
         r.rental_id
,        c.contact_id
,        tu.check_out_date AS check_out_date
,        tu.return_date AS return_date
,        3 AS created_by
,        TRUNC(SYSDATE) AS creation_date
,        3 AS last_updated_by
,        TRUNC(SYSDATE) AS last_update_date
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN transaction_upload tu
ON       c.first_name = tu.first_name
AND      NVL(c.middle_name,'x') = NVL(tu.middle_name,'x')
AND      c.last_name = tu.last_name
AND      tu.account_number = m.account_number LEFT JOIN rental r
ON       c.contact_id = r.customer_id
AND      tu.check_out_date = r.check_out_date
AND      tu.return_date = r.return_date;

After you explain a statement, you need to analyze it. Oracle uses hierarchical queries to analyze them. The following is a simple script that lets you display the execution plan by nesting the levels of the explained plan.

Here’s where you chose to follow the old or new way. Click on the link below to see the old way if you’re interested but I’d suggest you use the new way.

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
42
43
44
45

-- Clear any reporting breaks, columns, or computing.
CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
 
-- Set environment to capture explained plan.
SET FEEDBACK OFF
SET NULL ""
SET LINESIZE 120
SET PAGESIZE 9999
SET PAUSE OFF
SET VERIFY OFF
 
-- Override default substitution variable prompt.
ACCEPT input1  PROMPT "What do you want as a statement ID? "
 
-- Format the Query Plan based on a 120 character length.
COL c1   FORMAT A80     HEADING 'Query Plan'
 
-- Spool it to file.
SPOOL explain_it.log
 
-- Create the hierarchy of the join plan.
SELECT   LPAD(' ',2*(LEVEL-1))
||       operation||' '
||       options||' '
||       object_name||' '
||       DECODE(id,0,'Cost = '||POSITION) "Query Plan"
FROM     plan_table
START 
WITH     id = 0
AND      statement_id = '&input1'
CONNECT
BY PRIOR id = parent_id
AND      statement_id = '&input1';
 
-- Reset operating environment to default.
SET FEEDBACK ON
SET NULL "<Null>"
SET LINESIZE 80
SET PAGESIZE 999
SET VERIFY ON
 
-- Close output file.
SPOOL OFF

1
2

@explain_it.sql
What do you want AS a statement ID? Query1

1
2
3
4
5
6
7
8
9
10
11
12

Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT   Cost = 34
  HASH UNIQUE
    HASH JOIN OUTER
      VIEW
        NESTED LOOPS
          HASH JOIN
            TABLE ACCESS FULL MEMBER
            TABLE ACCESS FULL CONTACT
          INDEX RANGE SCAN IMPORT_DATE_RANGE
      TABLE ACCESS FULL RENTAL

Oracle’s New Way

Oracle’s new way uses the DBMS_XPLAN package. In this post, you’ll examine only how to display results and check the impact of small changes that lead to improved query performance.

Assuming you entered Query1 as the &input response to the EXPLAIN PLAN statement above, then you’d call it with the following syntax:

1
2
3
4
5
6
7
8

-- Expand the width of displayed output.
SET LINESIZE 120
 
-- Query using the TABLE function that returns a user-defined object type (UDT)
-- into a SQL aggregate table, which is a fancy word for normal SQL output
-- (unfortunately, it's the technically accurate word).
SELECT *
FROM   TABLE(dbms_xplan.display(NULL,'Query1'));

The query using the DISPLAY function of the DBMS_XPLAN returns a table of data. It should generate an output stream like the following:

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 3289798709
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |   229 | 24274 |    50   (8)| 00:00:01 |
|   1 |  HASH UNIQUE                   |                    |   229 | 24274 |    50   (8)| 00:00:01 |
|*  2 |   HASH JOIN OUTER              |                    |   229 | 24274 |    49   (7)| 00:00:01 |
|   3 |    VIEW                        |                    |     4 |   248 |    37   (6)| 00:00:01 |
|*  4 |     HASH JOIN                  |                    |     4 |   572 |    37   (6)| 00:00:01 |
|*  5 |      HASH JOIN                 |                    |    15 |  1230 |     7  (15)| 00:00:01 |
|   6 |       TABLE ACCESS FULL        | MEMBER             |     9 |   180 |     3   (0)| 00:00:01 |
|   7 |       TABLE ACCESS FULL        | CONTACT            |    15 |   930 |     3   (0)| 00:00:01 |
|   8 |      EXTERNAL TABLE ACCESS FULL| TRANSACTION_UPLOAD |  8168 |   486K|    30   (4)| 00:00:01 |
|   9 |    TABLE ACCESS FULL           | RENTAL             |  4689 |   201K|    11   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("TU"."RETURN_DATE"="R"."RETURN_DATE"(+) AND
              "TU"."CHECK_OUT_DATE"="R"."CHECK_OUT_DATE"(+) AND "C"."CONTACT_ID"="R"."CUSTOMER_ID"(+))
   4 - access("C"."FIRST_NAME"="TU"."FIRST_NAME" AND
              NVL("C"."MIDDLE_NAME",'x')=NVL("TU"."MIDDLE_NAME",'x') AND "C"."LAST_NAME"="TU"."LAST_NAME"
              AND "TU"."ACCOUNT_NUMBER"="M"."ACCOUNT_NUMBER")
   5 - access("C"."MEMBER_ID"="M"."MEMBER_ID")
 
Note
-----
   - dynamic sampling used for this statement
 
30 rows selected.

The first thing to do is move the data immediately from an external table into a production table. You do that with the following command:

1
2

-- Create a new table from an existing one.
CREATE TABLE transaction_uploaded AS SELECT * FROM transaction_upload;

Truncating the PLAN_TABLE table, is the easiest way to start over by reusing the statement identifiers, like Query1. Then, you can edit your EXPLAIN PLAN script and change TRANSACTION_UPLOAD table reference to TRANSACTION_UPLOADED. Now, the query using the DISPLAY function of the DBMS_XPLAN returns a reduction in cost from 50 to 34.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 3624831533
 
--------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |   400 | 42400 |    34   (6)| 00:00:01 |
|   1 |  HASH UNIQUE           |                   |   400 | 42400 |    34   (6)| 00:00:01 |
|*  2 |   HASH JOIN OUTER      |                   |   400 | 42400 |    33   (4)| 00:00:01 |
|   3 |    VIEW                |                   |     7 |   434 |    22   (5)| 00:00:01 |
|   4 |     NESTED LOOPS       |                   |     7 |  1001 |    22   (5)| 00:00:01 |
|*  5 |      HASH JOIN         |                   |    15 |  1230 |     7  (15)| 00:00:01 |
|   6 |       TABLE ACCESS FULL| MEMBER            |     9 |   180 |     3   (0)| 00:00:01 |
|   7 |       TABLE ACCESS FULL| CONTACT           |    15 |   930 |     3   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN  | IMPORT_DATE_RANGE |     1 |    61 |     1   (0)| 00:00:01 |
|   9 |    TABLE ACCESS FULL   | RENTAL            |  4689 |   201K|    11   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("TU"."RETURN_DATE"="R"."RETURN_DATE"(+) AND
              "TU"."CHECK_OUT_DATE"="R"."CHECK_OUT_DATE"(+) AND
              "C"."CONTACT_ID"="R"."CUSTOMER_ID"(+))
   5 - access("C"."MEMBER_ID"="M"."MEMBER_ID")
   8 - access("TU"."ACCOUNT_NUMBER"="M"."ACCOUNT_NUMBER" AND
              "C"."LAST_NAME"="TU"."LAST_NAME" AND "C"."FIRST_NAME"="TU"."FIRST_NAME" AND
              NVL("C"."MIDDLE_NAME",'x')=NVL("MIDDLE_NAME",'x'))
 
Note
-----
   - dynamic sampling used for this statement
 
31 rows selected.

A downside or quirk of thought, I’m never quite sure but folks look to the bottom not the middle first. Maybe in this case you tackle it first because it’s the attractive inefficiency due to the number of rows returned by a full table scan (most expensive).

You add a unique index that maps to the natural key for the RENTAL table, like this:

1
2

CREATE UNIQUE INDEX natural_key_rental
ON rental (rental_id, customer_id, check_out_date, return_date);

You run the EXPLAIN PLAN again, using Query2, and then analyze it. You’ll see that the query uses an index fast full scan and that the cost decreases by 2.

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3402838417
 
---------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |   400 | 42400 |    32   (7)| 00:00:01 |
|   1 |  HASH UNIQUE           |                    |   400 | 42400 |    32   (7)| 00:00:01 |
|*  2 |   HASH JOIN OUTER      |                    |   400 | 42400 |    31   (4)| 00:00:01 |
|   3 |    VIEW                |                    |     7 |   434 |    22   (5)| 00:00:01 |
|   4 |     NESTED LOOPS       |                    |     7 |  1001 |    22   (5)| 00:00:01 |
|*  5 |      HASH JOIN         |                    |    15 |  1230 |     7  (15)| 00:00:01 |
|   6 |       TABLE ACCESS FULL| MEMBER             |     9 |   180 |     3   (0)| 00:00:01 |
|   7 |       TABLE ACCESS FULL| CONTACT            |    15 |   930 |     3   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN  | IMPORT_DATE_RANGE  |     1 |    61 |     1   (0)| 00:00:01 |
|   9 |    INDEX FAST FULL SCAN| NATURAL_KEY_RENTAL |  4689 |   201K|     9   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("TU"."RETURN_DATE"="R"."RETURN_DATE"(+) AND
              "TU"."CHECK_OUT_DATE"="R"."CHECK_OUT_DATE"(+) AND
              "C"."CONTACT_ID"="R"."CUSTOMER_ID"(+))
   5 - access("C"."MEMBER_ID"="M"."MEMBER_ID")
   8 - access("TU"."ACCOUNT_NUMBER"="M"."ACCOUNT_NUMBER" AND
              "C"."LAST_NAME"="TU"."LAST_NAME" AND "C"."FIRST_NAME"="TU"."FIRST_NAME" AND
              NVL("C"."MIDDLE_NAME",'x')=NVL("MIDDLE_NAME",'x'))
 
Note
-----
   - dynamic sampling used for this statement
 
31 rows selected.

You add a unique index that maps the surrogate and natural key for the MEMBER table, like this:

1
2

CREATE UNIQUE INDEX member_account
ON member (member_id, account_number);

You run the EXPLAIN PLAN again, using Query3, and then analyze it. You’ll see that the query uses an index range scan instead of a full table access of the MEMBER table. This decreases the cost by 2.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 656512492
 
---------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |   400 | 42400 |    30   (7)| 00:00:01 |
|   1 |  HASH UNIQUE           |                    |   400 | 42400 |    30   (7)| 00:00:01 |
|*  2 |   HASH JOIN OUTER      |                    |   400 | 42400 |    29   (4)| 00:00:01 |
|   3 |    VIEW                |                    |     7 |   434 |    19   (0)| 00:00:01 |
|   4 |     NESTED LOOPS       |                    |     7 |  1001 |    19   (0)| 00:00:01 |
|   5 |      NESTED LOOPS      |                    |    15 |  1230 |     4   (0)| 00:00:01 |
|   6 |       TABLE ACCESS FULL| CONTACT            |    15 |   930 |     3   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN | MEMBER_ACCOUNT     |     1 |    20 |     1   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN  | IMPORT_DATE_RANGE  |     1 |    61 |     1   (0)| 00:00:01 |
|   9 |    INDEX FAST FULL SCAN| NATURAL_KEY_RENTAL |  4689 |   201K|     9   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("TU"."RETURN_DATE"="R"."RETURN_DATE"(+) AND
              "TU"."CHECK_OUT_DATE"="R"."CHECK_OUT_DATE"(+) AND
              "C"."CONTACT_ID"="R"."CUSTOMER_ID"(+))
   7 - access("C"."MEMBER_ID"="M"."MEMBER_ID")
   8 - access("TU"."ACCOUNT_NUMBER"="M"."ACCOUNT_NUMBER" AND
              "C"."LAST_NAME"="TU"."LAST_NAME" AND "C"."FIRST_NAME"="TU"."FIRST_NAME" AND
              NVL("C"."MIDDLE_NAME",'x')=NVL("MIDDLE_NAME",'x'))
 
Note
-----
   - dynamic sampling used for this statement
 
31 rows selected.

You add a unique index that maps the surrogate, foreign key to the MEMBER table, and the natural key for the CONTACT table, like this:

1
2

CREATE UNIQUE INDEX contact_member
ON contact (contact_id, member_id, last_name, first_name, NVL(middle_name,'x'));

You run the EXPLAIN PLAN again, using Query3, and then analyze it. You’ll see that the query uses an index full scan instead of a full table access of the CONTACT table. This decreases the cost by another 2.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 1185696375
 
---------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |   400 | 42400 |    28   (8)| 00:00:01 |
|   1 |  HASH UNIQUE           |                    |   400 | 42400 |    28   (8)| 00:00:01 |
|*  2 |   HASH JOIN OUTER      |                    |   400 | 42400 |    27   (4)| 00:00:01 |
|   3 |    VIEW                |                    |     7 |   434 |    17   (0)| 00:00:01 |
|   4 |     NESTED LOOPS       |                    |     7 |  1001 |    17   (0)| 00:00:01 |
|   5 |      NESTED LOOPS      |                    |    15 |  1230 |     2   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN  | CONTACT_MEMBER     |    15 |   930 |     1   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN | MEMBER_ACCOUNT     |     1 |    20 |     1   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN  | IMPORT_DATE_RANGE  |     1 |    61 |     1   (0)| 00:00:01 |
|   9 |    INDEX FAST FULL SCAN| NATURAL_KEY_RENTAL |  4689 |   201K|     9   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("TU"."RETURN_DATE"="R"."RETURN_DATE"(+) AND
              "TU"."CHECK_OUT_DATE"="R"."CHECK_OUT_DATE"(+) AND
              "C"."CONTACT_ID"="R"."CUSTOMER_ID"(+))
   7 - access("C"."MEMBER_ID"="M"."MEMBER_ID")
   8 - access("TU"."ACCOUNT_NUMBER"="M"."ACCOUNT_NUMBER" AND
              "C"."LAST_NAME"="TU"."LAST_NAME" AND "C"."FIRST_NAME"="TU"."FIRST_NAME" AND
              NVL("MIDDLE_NAME",'x')=NVL("MIDDLE_NAME",'x'))
 
Note
-----
   - dynamic sampling used for this statement
 
31 rows selected.

The question evolves after you tune the first query. Then, you must check what impacts may exist on other queries, like the statement that you use to merge data into the TRANSACTION table. You may have to suppress indexes in a subsequent query, or after you tune it, return and suppress an index here. You suppress an index by adding a '0' to a NUMBER or DATE column, and by concatenating a null string to a character data type.

Examples of Suppressing Indexes

The following is an example of suppressing an index built on a string. It extends the tuning statement earlier in the blog post, and references line #17. The concatenation of a null string suppresses the index.

17

AND      c.last_name = tu.last_name||''

The following is an example of suppressing an index built on a date. It extends the tuning statement earlier in the blog post, and references line #20. Adding a zero leaves the value intact and suppresses the index.

20

AND      tu.check_out_date = r.check_out_date+0

After the installation of Oracle 11g XE on Windows, I anticipated problems installing on Fedora. It’s terrific to report it was by far simpler to install on Fedora. This post provides the steps, and builds on the Fedora configuration published earlier this week.

1. After you download the software from the Oracle web site, you’ll need to expand the compressed file. When you double click on the download item you will see the following screen. Click Extract at the top of the display.

2. The Extract displays the following dialog. Click the Create Folder button and you’ll get an entry point for a new directory in your user’s directory. For the example, Oracle11gXE

3. After creating the directory, click the Extract button.

4. The extract process completes and shows the following dialog.

5. The Disk1 directory will show the following contents.

6. You need to drop down into a Terminal session, which you can launch by clicking on Applications, System Tools, and Terminal in the menu.

sudo rpm -iv oracle-xe-11.2.0-1.0.x86_64.rpm

[sudo] password for mclaughlinm:
Preparing packages for installation...
oracle-xe-11.2.0-1.0
Executing post-install steps...
You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.

7. This step requires that you assume the role of the root user, which can be done with this syntax:

sudo sh

/etc/init.d/oracle-xe configure

Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express 
Edition.  The following questions will determine whether the database should 
be starting upon system boot, the ports it will use, and the passwords that 
will be used for database accounts.  Press <Enter> to accept the defaults. 
Ctrl-C will abort.
 
Specify the HTTP port that will be used for Oracle Application Express [8080]:
 
Specify a port that will be used for the database listener [1521]:
 
Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of 
different passwords for each database account.  This can be done after 
initial configuration:
Confirm the password:
 
Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:
 
Starting Oracle Net Listener...Done
Configuring database...
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

8. This step requires that you exit the root shell by typing the exit command. This should put you back into your administration account with sudoer privileges. You use vi to edit and add the following to the .bashrc file.

. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
 
ORACLE_HOSTNAME=localhost.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe; export ORACLE_HOME
ORACLE_SID=XE; export ORACLE_SID
NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`; export NLS_LANG
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
 
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
 
if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
fi

sqlplus system

9. This step is optional. You can configure an oracleenv.sh file in the oracle user’s home directory. While you could put this in the .bashrc file, the oracle account isn’t bootable. That’s why I recommend putting it in an environment file.

# .bashrc
 
# Source global definitions
if [ -f /etc/bashrc ]; then
  . /etc/bashrc
fi
 
# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
 
# User specific aliases and functions
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
 
# Wrap sqlplus with rlwrap to edit prior lines with the
# up, down, left and right keys.
sqlplus()
{
  if [ "$RLWRAP" = "0" ]; then
    sqlplus "$@"
  else
    rlwrap sqlplus "$@"
  fi
}
 
# Set vi as a command line editor.
set -o vi

If you want to use the menu choices added by the installation, you need to put your privileged sudoer in the dba group. I wrote a blog post on it here.

My new Oracle Database 11g & MySQL 5.6 Developer Handbook will be available at Oracle Open World 2011 (OOW2011). It’s a great book to compare and contrast approaches in Oracle and MySQL. It covers Oracle SQL & PL/SQL and MySQL SQL & SQL/PSM (Persistent Stored Modules – functions and procedures). Unfortunately, it seems like the book won’t be available on amazon.com until much later in the month, and not available from amazon.de until November. You can read about it at McGraw Hill Professional’s web site. They’ve made a special effort to get copies to OOW2011. Here’s the source code for the book because I don’t know when it’ll be on the publisher’s web site.

I’ll also be at OOW2011. They’ve scheduled me in the bookstore (probably 2^nd floor of Moscone North, as usual) from 10 to 10:30 A.M. on Monday and Wednesday for a book signing. If you’re at OOW2011 and you like to stop by and say hello, I look forward to meeting you. Many folks leave comments on the posts but only a few suggest what they’d like me to write on when I’ve got a chance, and you can do that if you stop by to chat.

It’s also interesting to know how many folks use both Oracle and MySQL (any updates on that are always appreciated). Last year at the Bioinformatics and Computation Biology (ACM-BCB 2010) Conference in Niagara Falls, I found it interesting to discover how many pharmaceutical companies and national labs were using both Oracle and MySQL. They appeared consistent about using Oracle for their systems governed by legal compliance rules and MySQL for actual research.

The pharmaceutical companies also had clear barriers between the researchers and professional IT staff, specifically the DBAs. It seems that the DBAs don’t want to cede any control over installed Oracle instances, and they place barriers to research by denying additional Oracle instances when their site licenses would allow them to do so at no incremental cost. On the other hand, the DBAs are fine with letting researchers host and pilot with the MySQL Community Edition databases. This book supports those trying to figure out how to write portable SQL and how to port solutions from MySQL to Oracle and vice versa.

Hope to meet a few new folks at OOW2011. The Kindle version of the book became available 11/25/2011.

As an addendum to this original post, some folks asked for the summary of content for the new book, and the location of the errata (the errors of omission and commission in the book). Below is a summary of the book from page XVIII of the Introduction, and the errata is in the second comment to this post:

Part I: Development Components

• Chapter 1, “Architectures,” explains the Oracle 11g and MySQL 5.6 development architectures and highlights the comparative aspects of both client and server environments.
• Chapter 2, “Client Interfaces,” explains and demonstrates the basics of how you use SQL*Plus and MySQL Monitor client software.
• Chapter 3, “Security,” explains the security barriers for database servers and Data Control Language (DCL) commands that let you manage user and account privileges in the database servers.
• Chapter 4, “Transactions,” explains the nature of ACID-compliant transactions and the Two-phase Commit (2PC) process demonstrated by INSERT, UPDATE, and DELETE statements.
• Chapter 5, “Constraints,” explains the five primary database-level constraints and covers the check, not null, unique, primary key, and foreign key constraints.

Part II: SQL Development

• Chapter 6, “Creating Users and Structures,” explains how you can create users, databases, tables, sequences, and indexes.
• Chapter 7, “Modifying Users and Structures,” explains how you modify users, databases, tables, sequences, and indexes.
• Chapter 8, “Inserting Data,” explains how you insert data into tables.
• Chapter 9, “Updating Data,” explains how you update data in tables.
• Chapter 10, “Deleting Data,” explains how you delete data from tables.
• Chapter 11, “Querying Data,” explains how you query data from a single table, from a join of two or more tables, and from a join of two or more queries through set operators.
• Chapter 12, “Merging Data,” explains how you import denormalized data from external tables or source files and insert or update records in normalized tables.

Part III: Stored Program Development

• Chapter 13, “PL/SQL Basics,” explains the basics of using PL/SQL to write transactional blocks of code.
• Chapter 14, “SQL/PSM Basics,” explains the basics of using SQL/PSM to write transactional blocks of code.
• Chapter 15, “Triggers,” explains how to write database triggers in Oracle and MySQL databases.

Part IV: Appendix

• Appendix, Covers the answers to the mastery questions at the end of the chapters.

I’ve been busy working on other things since APEX 4 shipped. After I got through the installations of Oracle Database 11g XE (see prior posts), figuring out the default user name and password for APEX Administration became a tedious task. The documentation didn’t really help point to what was needed but it’s always in the code.

You reset the APEX Administration Account for Oracle Database 11g XE by doing the following steps:

1. Open a command shell and change the directory to the %ORACLE_HOME%\apex directory (the default Oracle Home is c:\oraclexe\app\oracle\product\11.2.0\server). Then, connect as the SYSTEM user to the SQL*Plus utility, and run the following command that sets the temporary password to “admin“. You’ll reset the password when you connect through the web interface.

@apxxepwd admin

2. Open APEX Administration by navigating to the http://localhost:8080/apex/apex_admin, and you’ll see the following APEX Administration Login screen. Enter admin as the user name and admin as the password and click the Login button.

3. You’ll be prompted to reset the password in the following Change Password screen. Enter admin as the old password and a new password that complies with the following rules, and click the Apply Changes button.

4. After configuring the APEX Administration account, click on the Manage Workspaces icon (or button), as shown below. If re-opening the browser to go straight here, just type the following URL http://localhost:8080/apex/apex_admin.

5. The upper left box contains what you need to create a workspace. Click on the Create Workspace option as shown below.

6. The choice to add a workspace launched a provisioning workflow. You first Identify Workspace.

The dialog box for this first provisioning step is (and the other highlighted workflow tracking lists aren’t shown):

7. The dialog box for this second provisioning step is:

8. The dialog box for this third provisioning step is:

9. The dialog box for this fourth provisioning step cpmfirms what you’re doing before you perform the task:

10. The dialog box for this fifth provisioning step confirms success or reports failure:

11. After you’ve provisioned a workspace, you can create objects like you did with APEX 3. Enter the following URL: http://localhost:8080/apex and you’ll be brought to this login screen.

12. After logging in to your Basic workspace, you’ll have these four principal widgets, as shown in the screen capture.

Maybe I went through the 2-Day APEX manual too quickly but I didn’t see how to do this, all I saw was how to use the HR sample schema. As always, I hope this helps others.

Here’s the Oracle Database 11g Express Edition step-by-step installation. I did the installation on Window 7 64-bit, and did run into a problem with the Windows Registry on a fresh installation (posted earlier on the blog).

When you download the software from Oracle, you’ll need an account and you’ll need to accept the license agreement.

1. You’re prompted to accept the license agreement and can’t proceed without doing it.

2. After accepting the license agreement you can download the software.

3. Unlike, the Oracle 10g XE MSI file, you’ve downloaded a compressed (OracleXE112_Win32.zip) file, and you’ll need to explode the zip file and write it to a directory. When you write it to a directory, it looks like the following and you double click on the setup.msi file.

It’s recommended that you right click on the setup.msi program and run it as the Administrator. If you’ve not disabled Microsoft User Access Controls (UAC), I’d do that first.

4. After launching the setup.msi file, you see the Install Shield wizard screen while the file loads, like this:

After launching the setup.msi file, and loading the MSI file, you see the first dialog box of the installation. Click the Next button to start the installation.

5. The second dialog box is the license for the installation. Click the I accept the terms in the license agreement. radio button and then the Next button.

6. The third dialog box lets you accept the default location or provide an override location. If you accept the default location, click the Next button to continue.

7. The fourth dialog box lets you specify the TNS, MTS, and HTTP Ports. The default values are shown in the following screen shot but I’ve change my TNS port to 1621, which you’ll see later. Click the Next button to continue.

8. The fifth dialog box lets you enter the password for the SYS and SYSTEM users. You must enter it twice and naturally they must match each other. Click the Next button to continue.

9. The sixth dialog box lets you see the configuration options you’ve chosen. Note that this installation is using a TNS port of 1621 rather than the default of 1521. Click the Next button to continue.

10. The seventh dialog box lets you see the progress bar, take a break it can run for two or three minutes. Click the Next button to continue.

11. The last dialog box lets you see that you’ve installed the product. Click the Finish button to complete the installation.

You’ve now installed Oracle Database 11g Express Edition. You can start the command line by typing sqlplus system/password in command shell or the Get Started option in your Program Menus, as shown below.

You should see this console when you launch Get Started or manually type http://localhost:8080/apex. There’s more to the web page but I scrunched it to fit into the blog. Alas, space is always at a premium.

Hope this helps folks.

Finally, I got to install Oracle Database 11g Express Edition (XE) tonight. Unfortunately, I ran into an error during the installation. A quick Google search landed me in the forum, where it gave me the impression it wouldn’t work on Windows 7, 64-bit. However, I’m tenacious with problems like this. Here’s the screenshot for the error:

The text from the image is noted below to help folks find the solution.

The installer is unable to instantiate the file C:\Users\McLAUGH~1\AppData\Local\Temp\{078E83D7-3FCC-4A72-903B-995C7CE44681}\KEY_XE.reg. The file does not appear to exist.

The physical file isn’t missing, what failed to happen was that the installer didn’t write a Registry Key’s Data value. You can add the missing registry key’s Data value, and then click OK on the Error dialog box to continue the installation. (Replying to Andrea, I’ve provided screen shots on editing the Registry in a comment below.) It will succeed, at least it succeeded for me on Windows 7 64-bit. Rather than use abbreviations like the forum entry, the missing key is found here in the Windows Registry:

- HKEY_CLASSES_ROOT\Installer\Products\266B7A503A089BE4EAD1986A429434C1\SourceList\Media

A screenshot shows that the Data value of the key wasn’t populated by the MSI (Microsoft Software Installer), the arguments remain for a script to populate:

You need to add the message value from your Error dialog as Data value of the Name entry 1, like this screenshot shows:

Since I believe the only things that should change are your Administrator’s hostname and the key value (type in the one that shows in the your dialog box), here’s the text you’ll need to enter:

C:\Users\hostname\AppData\Local\Temp\{078E83D7-3FCC-4A72-903B-995C7CE44681}\

This fix worked for me, and I hope the same holds true for you.

It’s finally available for download, and the documentation is posted too. However, there’s no official word on the next release of the Oracle database.

I’ve posted Step-by-step Installation Instructions and ran into a registry problem on Window 7 64-bit, the instructions for fixing the registry and salvaging the in-progress installation are in this blog post.

A couple years ago I showed how to get the last day of the month and count past leap year. Somebody asked how to get the first day of the current month.

The two easiest ways I know how to do that in Oracle is to combine the ADD_MONTHS and LAST_DAY functions, like:

SELECT
  TO_CHAR(TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1),'DD-MON-YY HH24:MI') AS "One Way"
, TO_CHAR(TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-1)),'DD-MON-YY HH24:MI') AS "Another Way"
FROM  dual

It gives you a truncated datetime value, as you can see:

One Way         Another Way
--------------- ---------------
01-JUN-11 00:00 01-JUN-11 00:00

As the comments point out, the following is best for getting the first day of the month:

SELECT TO_CHAR(TRUNC(SYSDATE,'MM'),'DD-MON-YYYY HH24:MI') FROM dual;

You can also return the first day of the year like this:

SELECT TO_CHAR(TRUNC(SYSDATE,'YY'),'DD-MON-YYYY HH24:MI') FROM dual;

If you’re planning to do this in an application and Oracle Database 11g, you may want to write it as a deterministic function, like this:

1
2
3
4
5
6

CREATE OR REPLACE FUNCTION first_day
( date_in DATE ) RETURN DATE DETERMINISTIC IS
BEGIN
  RETURN TRUNC(date_in,'MM');
END;
/

Naturally, you can also do this with the EXTRACT function but it would get very complex quickly. Hope this answers the question.

It started as a smart remark, “The DELETE FROM statement always removes one or more rows from a table when any are found, except when the table isn’t a table.” That exception is true when the table isn’t a schema-level table but a nested table in an Oracle database. Then, the DELETE FROM statement may remove a row from the table or a row from collection held by a row.

Naturally, everybody would like an example. It took more than a couple steps to work one up and the Oracle documentation isn’t very helpful on the topic.

1. Create a SQL nested table collection type of 30-character variable length strings:

1
2

CREATE TYPE street_list IS TABLE OF VARCHAR2(30);
/

2. Create a SQL user-defined object type for addresses:

1
2
3
4
5
6
7

CREATE OR REPLACE TYPE address_type AS OBJECT
( address_id     NUMBER
, street_address STREET_LIST
, city           VARCHAR2(30)
, state          VARCHAR2(2)
, postal_code    VARCHAR2(10));
/

3. Create a SQL nested table of the user-defined object type for addresses:

1
2

CREATE OR REPLACE TYPE address_list AS TABLE OF address_type;
/

4. Create a schema-level table that uses the user-defined nested table of a user-defined object type, and a SEQUENCE for the table:

1
2
3
4
5
6
7
8
9
10

CREATE TABLE employee
( employee_id    NUMBER
, first_name     VARCHAR2(20)
, middle_name    VARCHAR2(20)
, last_name      VARCHAR2(20)
, home_address   ADDRESS_LIST)
  NESTED TABLE home_address STORE AS address_table
   (NESTED TABLE street_address STORE AS street_table);
 
CREATE SEQUENCE employee_s1;

5. Insert data into the 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
29
30
31
32
33
34
35
36
37
38
39
40
41

INSERT INTO employee
( employee_id
, first_name
, last_name
, home_address )
VALUES
( employee_s1.NEXTVAL
,'Sam'
,'Yosemite'
, address_list(
    address_type( 1
                , street_list('1111 Broadway','Suite 322')
                ,'Oakland'
                ,'CA'
                ,'94612')
  , address_type( 2
                , street_list('1111 Broadway','Suite 525')
                ,'Oakland'
                ,'CA'
                ,'94612')));
 
INSERT INTO employee
( employee_id
, first_name
, last_name
, home_address )
VALUES
( employee_s1.NEXTVAL
,'Bugs'
,'Bunny'
, address_list(
    address_type( 1
                , street_list('21 Rodeo Drive','Suite 1000')
                ,'Beverly Hills'
                ,'CA'
                ,'90210')
  , address_type( 2
                , street_list('21 Rodeo Drive','Suite 2010')
                ,'Beverly Hills'
                ,'CA'
                ,'90210')));

6. Create a SQL view to explode nesting into a meaningful data set:

1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE VIEW explode_employee AS
SELECT   e.employee_id
,        e.first_name || ' ' || e.last_name AS full_name
,        st.address_id
,        sa.column_value AS st_address
,        st.city
,        st.state
,        st.postal_code
FROM     employee e CROSS JOIN
         TABLE(e.home_address) st CROSS JOIN
         TABLE(street_address) sa
ORDER BY 1, 2, 3;

7. Format output and query view:

1
2
3
4
5
6
7
8
9
10
11

-- SQL*Plus formattting
COLUMN employee_id FORMAT 999 HEADING "ID|EMP"
COLUMN full_name   FORMAT A16 HEADING "Full Name"
COLUMN address_id  FORMAT 999 HEADING "ID|UDT"
COLUMN st_address  FORMAT A16 HEADING "Street Address"
COLUMN city        FORMAT A14 HEADING "City"
COLUMN state       FORMAT A5  HEADING "State"
COLUMN postal_code FORMAT A5  HEADING "Zip|Code"
 
-- Query all from view.
SELECT * FROM explode_employee;

  ID                    ID                                       Zip
 EMP Full Name         UDT Street Address   City           State Code
---- ---------------- ---- ---------------- -------------- ----- -----
   1 Sam Yosemite        1 1111 Broadway    Oakland        CA    94612
   1 Sam Yosemite        1 Suite 322        Oakland        CA    94612
   1 Sam Yosemite        2 1111 Broadway    Oakland        CA    94612
   1 Sam Yosemite        2 Suite 525        Oakland        CA    94612
   2 Bugs Bunny          1 21 Rodeo Drive   Beverly Hills  CA    90210
   2 Bugs Bunny          1 Suite 1000       Beverly Hills  CA    90210
   2 Bugs Bunny          2 21 Rodeo Drive   Beverly Hills  CA    90210
   2 Bugs Bunny          2 Suite 2010       Beverly Hills  CA    90210

8. UPDATE statement against a nested table:

1
2
3
4
5

UPDATE TABLE (SELECT e.home_address
              FROM   employee e
              WHERE  e.employee_id = 1) e
SET   e.city = 'Fremont'
WHERE e.address_id = 1;

9. UPDATE statement against a nested table and scalar nested table within it:

1
2
3
4
5
6

UPDATE TABLE (SELECT e.home_address
              FROM   employee e
              WHERE  e.employee_id = 1) e
SET   e.street_address = street_list('1111 Broadway','Suite 522')
,     e.city = 'Oakland'
WHERE e.address_id = 1;

10. DELETE statement against a nested table, which only removes the nested row:

1
2
3
4

DELETE FROM TABLE (SELECT e.home_address
                   FROM   employee e
                   WHERE  e.employee_id = 1) ha
WHERE  ha.address_id = 1;

As always, hope this helps somebody looking for syntax or approach.

While reviewing some material and explaining list partitioning in MySQL, I heard a rumor that sounded false. The rumor was that you can’t partition on anything other than the primary key column. That’s untrue, you can partition on another column provided it’s an integer column.

It appears the culprit that led to the rumor is a misunderstanding around ERROR 1503, which returns the following:

ERROR 1503 (HY000): A PRIMARY KEY must include ALL COLUMNS IN the TABLE's partitioning function

The problem can be recreated by defining a table with an inline primary key constraint instead of an INDEX on the auto incrementing column. Here’s an example of how to create the error message:

1
2
3
4
5
6
7
8
9
10
11

CREATE TABLE franchise
( franchise_id     INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, franchise_number INT UNSIGNED
, franchise_name   VARCHAR(20)
, city             VARCHAR(20)
, state            VARCHAR(20))
PARTITION BY LIST(franchise_number)
( PARTITION offshore VALUES IN (49,50)
, PARTITION west VALUES IN (34,45,48)
, PARTITION desert VALUES IN (46,47)
, PARTITION rockies VALUES IN (38,41,42,44));

The table should be defined without the inline PRIMARY KEY constraint on the auto incrementing column when you want to partition on another column. An index on the auto incrementing column doesn’t conflict with primary key rules. The correct syntax for a list partition would be:

1
2
3
4
5
6
7
8
9
10
11
12

CREATE TABLE franchise
( franchise_id     INT UNSIGNED AUTO_INCREMENT
, franchise_number INT UNSIGNED
, franchise_name   VARCHAR(20)
, city             VARCHAR(20)
, state            VARCHAR(20)
, INDEX idx (franchise_id)) engine=innodb
PARTITION BY LIST(franchise_number)
( PARTITION offshore VALUES IN (49,50)
, PARTITION west VALUES IN (34,45,48)
, PARTITION desert VALUES IN (46,47)
, PARTITION rockies VALUES IN (38,41,42,44));

The same problem can manifest itself on range partitioning. The fix is the same and here’s a code example:

1
2
3
4
5
6
7
8
9
10

CREATE TABLE ordering
( ordering_id      INT UNSIGNED AUTO_INCREMENT
, item_id          INT UNSIGNED
, rental_amount    DECIMAL(15,2)
, rental_date      DATE
, INDEX idx (ordering_id))
PARTITION BY RANGE(item_id)
( PARTITION jan2011 VALUES LESS THAN (10000)
, PARTITION feb2011 VALUES LESS THAN (20000)
, PARTITION mar2011 VALUES LESS THAN (30000));

The MySQL 5.6 Partitioning Keys, Primary Keys, and Unique Keys article is the key reference. It’s unfortunate that MySQL can’t partition on something other than an integer with PARTITION BY LIST syntax (check the changes in MySQL 5.6 online documentation). As you’ll notice in my more recent comment back to Tim, you can when you change the SQL phrase to PARTITION BY LIST COLUMNS. MySQL now has another feature that the Oracle Database 11g supports, that is list partitioning on variable-length string columns.

There are a few required changes but here’s a working example. The index must work against the auto incrementing column. The example comes from my reply to Tim, who raised a great question.

CREATE TABLE franchise
( franchise_id     INT UNSIGNED AUTO_INCREMENT
, franchise_number INT UNSIGNED
, franchise_name   VARCHAR(20)
, city             VARCHAR(20)
, state            VARCHAR(20)
, KEY idx (franchise_id)) engine=innodb
PARTITION BY LIST COLUMNS (franchise_name)
( PARTITION m VALUES IN ('McDonald','Maggiano')
, PARTITION p VALUES IN ('Pappa John','Pizza Hut')
, PARTITION t VALUES IN ('Taco Bell','Taco Time'));

While that works, the better approach creates two unique constraints. One unique constraint on the natural key of the franchise_number and franchise_name, which serves to optimize access to data sets looking data up in the table based on a natural key search. Another unique constraint on the surrogate (auto incrementing column) and the two natural key columns, which optimizes joins from foreign keys to the partitioned table’s primary key.

CREATE TABLE franchise
( franchise_id     INT UNSIGNED AUTO_INCREMENT
, franchise_number INT UNSIGNED
, franchise_name   VARCHAR(20)
, city             VARCHAR(20)
, state            VARCHAR(20)
, UNIQUE ink (franchise_number, franchise_name)
, UNIQUE enk (franchise_id, franchise_number, franchise_name)) engine=innodb
PARTITION BY LIST COLUMNS (franchise_name)
( PARTITION m VALUES IN ('McDonald','Maggiano')
, PARTITION p VALUES IN ('Pappa John','Pizza Hut')
, PARTITION t VALUES IN ('Taco Bell','Taco Time'));

Somebody asked for a composite partitioning (MySQL terminology is subpartitioning) example, so here it is:

1
2
3
4
5
6
7
8
9
10
11
12

CREATE TABLE ordering
( ordering_id      INT UNSIGNED AUTO_INCREMENT
, item_id          INT UNSIGNED
, store_id         INT UNSIGNED
, rental_amount    DECIMAL(15,2)
, rental_date      DATE
, INDEX idx (ordering_id))
PARTITION BY RANGE(item_id)
  SUBPARTITION BY HASH(store_id) SUBPARTITIONS 4
( PARTITION jan2011 VALUES LESS THAN (10000)
, PARTITION feb2011 VALUES LESS THAN (20000)
, PARTITION mar2011 VALUES LESS THAN (30000));

Hope this helps those researching ERROR 1503 on list or range partitioning.