MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for November, 2011

Gnome Menu Editing Fix

with 38 comments

Fedora 16 is clearly better than Fedora 15 but I found Menu Editing (Alacarte package) was broken in it because of a missing library dependency, and I’ve updated Fedora Bug 734442 with the work around. Here’s what’s wrong and how to fix it.

Update on Status of Bug 734442

The GNOME Desktop Bug 626220 is the one that will permanently fix this problem. It appears that the GNOME Desktop left all symbols in that point to the PyGTK library when they should have migrated to the dynamic Python bindings in the PyGObject project.

Download Site Change

It appears you can no longer download the packages from http://download.fedora.redhat.com. You must go the Fedora project web site http://archive.fedoraproject.org/pub/fedora/linux. I’ve updated the links to reflect the new site.

After installing the Menu Editing (Alacarte) package, you’ll encounter this error when trying to launch the menu editor:

MainWindow.py:19:<module>:Import Error: No module named gmenu

That error occurs because the gnome-menus-3.2.0.1-1.fc16.x86_64 is missing the /usr/lib64/python2.7/site-packages/gmenu.so library. So, I copied the version of gmenu.so from a Fedora 15 release as the root user. Naturally, at this point you’d test if it was fixed, I did. It wasn’t, and I got a new error:

MainWindow.py:19:<module>:Import Error: libgnome-menu.so.2: cannot open shared object file: No such file or directory

That error occurs because the gnome-menus-3.2.0.1-1.fc16.x86_64 is missing the /usr/lib64/libgnome-menu.so.2 symbolic link to the /usr/lib64/libgnome-menu.so.2.4.13 library. While the package meets the dependency check, the libraries fail the run time validation.

If digging in like this is all new to you, I’d recommend UNIX and Linux System Administration Handbook (4th Edition) (University of Colorado at Bolder folks) for the Linux stuff and The Quick Python Book, Second Edition for Python basics.

You can get a copy of the Fedora 15 package with the following command, which you should connect as the root user in navigate to the /tmp directory. Then, create a copy directory and change the /tmp/copy directory before running either of the next two commands.

Use this for 32-bit Installs

# wget http://archive.fedoraproject.org/pub/fedora/linux/releases/15/Fedora/i386/os/Packages/gnome-menus-3.0.1-1.fc15.i686.rpm

Use this for 64-bit Installs

# wget http://archive.fedoraproject.org/pub/fedora/linux/releases/15/Fedora/x86_64/os/Packages/gnome-menus-3.0.1-1.fc15.x86_64.rpm

That command only a copy of the RPM file, but the following converts it into an exploded directory. Assuming you created a copy directory in the /tmp directory, execute the following command from within the /tmp/copy directory. It will create a directory tree with the required files. After you copy the files, you can remove (rm) the copy directory from the /tmp directory.

Use this for 32-bit Installs

# rpm2cpio http://archive.fedoraproject.org/pub/fedora/linux/releases/15/Fedora/i386/os/Packages/gnome-menus-3.0.1-1.fc15.i686.rpm | cpio -ivd

Use this for 64-bit Installs

# rpm2cpio http://archive.fedoraproject.org/pub/fedora/linux/releases/15/Fedora/x86_64/os/Packages/gnome-menus-3.0.1-1.fc15.x86_64.rpm | cpio -ivd

You can now copy the files with these files. The target location differs between the 32-bit and 64-bit versions of the operating system.

Use this for 32-bit Installs

# cp /tmp/copy/usr/lib/libgnome-menu.so.2* /usr/lib
# cp /tmp/copy/usr/lib/python2.7/site-packages/gmenu.so /usr/lib/python2.7/site-packages

Alternatively, you can copy the following two files from any valid 32-bit Fedora 15 instance into a Fedora 16 instance, and manually create the symbolic link.

# /usr/lib/libgnome-menu.so.2.4.13
# /usr/lib/python2.7/site-packages/gmenu.so

Use this for 64-bit Installs

# cp /tmp/copy/usr/lib64/libgnome-menu.so.2* /usr/lib64
# cp /tmp/copy/usr/lib64/python2.7/site-packages/gmenu.so /usr/lib64/python2.7/site-packages

Alternatively, you can copy the following two files from any valid Fedora 64-bit 15 instance into a Fedora 16 instance, and manually create the symbolic link.

/usr/lib64/libgnome-menu.so.2.4.13
/usr/lib64/python2.7/site-packages/gmenu.so

After you copy the two files into the right directories as root, you can create the necessary symbolic link with the following command (this isn’t necessary with the wildcard instruction provided earlier in the post). You need to ensure that you’re in the /usr/lib directory when you run the ln command, as noted by Gavin’s comment:

Use this for 32-bit Installs

# ln -s /usr/lib/libgnome-menu.so.2.4.13 libgnome-menu.so.2

Use this for 64-bit Installs

# ln -s /usr/lib64/libgnome-menu.so.2.4.13 libgnome-menu.so.2

As mentioned by Darr247, don’t forget to remove the /tmp/copy directory when you’re done making the changes.

Somebody asked me to add the Red Hat Package Manager (RPM) commands that let me find these dependencies. That seemed like a good idea, here they are:


RPM Commands
Description of Options

rpm -qa search_string
Lists all installed packages that find the string in their package name. The results are typically piped through grep to filter the list.

rpm -qf file_name
Lists the package that owns a file. You need to fully qualify the location of the file with the complete path.

rpm -q package_name
Lists information about the package.

rpm -qi package_name
Lists information about the package.

rpm -qR package_name
Lists dependent libraries and commands for a package.

rpm -ql package_name
Lists files in a package.

rpm -qd package_name
Lists documentation files for a package.

rpm -qc package_name
Lists configuration files for a package.

If you want to set a menu item up manually, check this blog post. You also have the LXMenuEditor that’s available in beta as an alternative. Hope this helps those in need, as always.

Written by maclochlainn

November 24th, 2011 at 1:23 pm

Posted in Fedora,Linux,Red Hat

NVIDIA Strikes MacBook Pro

with 4 comments

The distorted video problems introduced by NVIDIA on MacBook Pro is old news because it affected early 2008 machines. Alas, those intermittent little gray lines at the bottom of my MacBook Pro early 2008 model now transcend my screen 4 out of 5 boot cycles, as shown below:

The screen shot from the MacBook Pro shows the image is fine when emailed to another machine. As Pooh Bear (my daughter’s favorite) would say, “Oh, bother …”

Now I’m urgently moving all files over to other media. That includes conference presentations, code, and several VMs.

While waiting on the USB transfers, I checked out what it might run to fix it and ran into this Apple Support page. It basically says my inaction at the annoyance during my AppleCare period, which just lapsed in July, won’t make it my problem. That’s because I’m within the extended 4 year window from date of purchase.

Living in Eastern Idaho has a number of perks, like the environment and community. It does have some downsides, like a 250 miles drive to the nearest Apple Store.
It looks like I’ll be making a little drive to Salt Lake City next week.

Actually got this fixed at the University’s help desk. Apple sent the exact same logic board that it had previously, and guess what the bars are hemming me in now intermittently. Ouch!

A little more research and it appears that this may be related to the Black Vertical Lines on MacBook Pro 15″ Display and from MacRumor’s archive. It’s odd there’s no recall on the monitor. Maybe that’s because Apple would pay for it, and NVIDIA paid for the other?

As with every purchase in technology, there’s an upside and downside. Clearly, the failure drove me to opt for the less expensive 13″ MacBook Pro upgrade with a 2.8 GHz CPU, 8 GB of memory, and 750 MB harddisk. I’ll probably avoid ever purchasing the leading, and pricier, Apple technology again.

Written by maclochlainn

November 14th, 2011 at 11:24 pm

MySQL and Java Tutorial

with 2 comments

This demonstrates how to create an Java infrastructure for reading and writing large text files to a MySQL database. The example provides:

  • A FileIO.jar library that lets you enter MySQL connection parameters through a JOptionPane, and a customized JFileChooser to filter and read source files from the file system.
  • A mysql-connector-java-3.1.14-bin.jar file, which is MySQL’s library for JDBC communication with the MySQL Databases.

The steps to compiling and testing this code are qualified below:

  1. Download and install the Java Software Development Kit (JSDK) for Java 6.
  2. Create a C:\JavaTest folder on Windows, or a /JavaTest directory from some mount point of your choice.
  3. Download and position the mysql-connector-java-3.1.14-bin.jar and FileIO.jar files in the JavaTest directory.
  4. Create a batch file to source your environment path (%PATH% on Windows and $PATH on Linux or Mac OS X) and the two Java Archive (JAR) files. A sample batch file is noted below:
set PATH=C:\Program Files\Java\jdk1.6.0_07\bin;%PATH%
set CLASSPATH=C:\JavaDev\Java6\mysql-connector-java-3.1.14-bin.jar;C:\JavaDev\Java6\FileIO.jar;.

You can run this file by simply typing the files first name. On Linux or Mac OS X, you first need to grant it privileges with the chmod command as 755.

  1. Copy the WriteReadCLOBMysql.java code from the bottom of this posting and also put it into the JavaTest directory.
  2. Compile the WriteReadCLOBMysql.java source code with the javac utility, as shown below:
javac WriteReadCLOBMysql.java

After you compile it, you should run it as follows:

java WriteReadCLOBMysql
  1. Before running the code, you’ll need to seed (INSERT) a row that meets the desired hard coded criteria. It requires an ITEM_TITLE value of 'The Lord of the Rings - Fellowship of the Ring' and an ITEM_SUBTITLE of 'Widescreen Edition' in the ITEM table.
  2. When it runs, you’ll see the following tabbed JOptionPane.

You need to enter the following values before clicking the OK button:

  • Host: The localhost key word, or hostname of your physical machine running the database.
  • Port: The port that the MySQL Listener is running on (the default value is 3306).
  • Database: The Oracle TNS Alias, which is sampledb for the full database sample database.
  • UserID: The user name with permissions to the database entered that can access an ITEM table.
  • Password: The password for the user’s account.

In the JFileChooser, select a file to upload to the database.

You should see what you uploaded displayed in a JFrame.

Written by maclochlainn

November 14th, 2011 at 4:49 pm

Posted in Java,LAMP,MAMP,MySQL

Excel-Oracle ODBC Driver

with 3 comments

Somebody was trying to query Oracle via Microsoft Excel 2007, and didn’t have an Oracle ODBC Data Source that enable them to choose Oracle. I’d referred them to this post on adding MySQL’s ODBC driver to Windows 7 but that didn’t help enough. Posting this screen shot may not have helped either.

It appears I assumed too, like everyone would know that it’s on Windows 7 when Oracle 11g (or another Oracle database) is installed locally. The person who posed the question doesn’t have Oracle installed locally and can’t install it.

For those who don’t have an Oracle instance running locally, you can download the Oracle Data Access Components for Windows from the Oracle web site. It’s in the driver sections, as shown in the screen capture below:

This driver contains the necessary OLE DB and ODBC drivers, as you can see in this screen shot.

Once you’ve downloaded it, you can return to this post where you set up an Excel query against a remote Oracle database. You should take note that Microsoft’s future direction adopts Oracle ODBC, like the approach they’ve chosen with MySQL’s ODBC driver. As always, I hope this helps.

Written by maclochlainn

November 13th, 2011 at 12:59 am

Oracle SQL Tuning Example

with one comment

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.

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

Written by maclochlainn

November 10th, 2011 at 12:14 am

Posted in Oracle,Oracle XE,sql