Using Connect By in Oracle

When dealing with hierarchical data, Oracle has a Connect By clause that is very useful. I will try and demonstrate through examples in the following sections.

Hierarchical Data

In this example, our data will be structured in a tree-structure. Our example data will be a folder-structure.

 - Root Folder
    - Development
        - 3rd Party Software
        - Licenses
        - Release Docs
    - Sales
        - Clients
            - ChainsawsRus
            - Knitting Fever
        - Presentations

In a database, this folder structure could be represented by

TABLENAME: Folders
-------------------------------------------------|
| FolderID | ParentFolderID | FolderName         | 
-------------------------------------------------|
| 1        | 0              | Root Folder        |
| 2        | 1              | Development        |
| 3        | 2              | 3rd Party Software |
| 4        | 2              | Licenses           |
| 5        | 2              | Release Docs       |
| 6        | 1              | Sales              |
| 7        | 6              | Clients            |
| 8        | 7              | ChainsawsRus       |
| 9        | 7              | Knitting Fever     |
| 10       | 6              | Presentations      |
-------------------------------------------------|

This data example will serve as the primary example through the rest of the article.

Connect By

Let us just jump right into it and make a query on that data.

SELECT Level, F.* FROM Folders F 
START WITH ParentFolderID = 0 
CONNECT BY PRIOR FOLDERID = PARENTFOLDERID 
ORDER SIBLINGS BY FOLDERNAME DESC;

This returns the following result:

Oracle Connect By Example 01

Let us go through the SQL.

Level
The first line references the “Level” pseudo column. Whenever a hierarchical query is performed, this pseudo column as added to the result. The level column contains a number describing the level of the row in the query.

START WITH
The START WITH clause tells oracle how to define the root elements. If we had written:

SELECT Level, F.* FROM Folders F 
START WITH ParentFolderID = 7 
CONNECT BY PRIOR FOLDERID = PARENTFOLDERID 
ORDER SIBLINGS BY FOLDERNAME DESC;

The it would only return: Knitting Fever and ChainsawsRus

CONNECT BY and PRIOR
The CONNECT BY clause defines how to make the structure in the hierarchy. The PRIOR keyword, simply put, tells Oracle what the column should be compared to the parent in the hierarchy. If we instead had executed the following SQL:

SELECT Level, F.* FROM Folders F 
START WITH FolderID = 7 
CONNECT BY FOLDERID = PRIOR PARENTFOLDERID 
ORDER SIBLINGS BY FOLDERNAME DESC;

It would instead consider the root folder as the one with FolderID 7 (Clients) and because the PRIOR keyword was moved in front on PARENTFOLDERID instead, it reverses the query, giving us the following result:

Oracle Connect By Example 02

ORDER SIBLINGS BY
This tells Oracle how to order siblings on the same level. For the purpose of the example I made it descending as it can be seen from the result.

I hope this article has helped someone understand oracle hierarchical queries. For more information, consule the Oracle Database SQL Reference.

Mimicking a Materialized View in Oracle

I had a quite complex View in an Oracle database that performed quite badly and I needed to improve performance. I tried making a Materialized View, but didn’t like the restrictions, so I decided to mimic the behavior using a table.

Materialized Views

I decided to look at Materialized Views, but soon realized that there were a lot of restrictions for the SQL that can be used to generate a Materialized View. A Materialized View is great for replication of data and simple queries, but for complex queries there were too many restrictions – so I decided to mimic the functionality of a Materialized View…and it turned out to be quite simple. I know it is not the best solution since it is vulnerable to coding errors, but besides that, I think it is quite elegant :-)

I had six tables (Table_A, Table_B….Table_F) and one view to create a summary – let us call it “View_ABCDEF”.

Improve Performance

To improve performance, I did the following:

I create a new table named “Mimick_ABCDEF” with the same structure as View_ABCDEF and then changed by code to search in Mimick_ABCDEF instead of View_ABCDEF.

I then updated all locations that performs UPDATE, INSERT or DELETE on Table_A through Table_F to run the following two queries after the original query:

DELETE FROM Mimick_ABCDEF WHERE (col1, col2, .... coln) IN 
(SELECT * FROM Mimick_ABCDEF MINUS SELECT * FROM View_ABCDEF);

This query removes all rows present in the table but not in the view (happens on update and delete). This query does not work when some on the values are null, but should be quite simple to fix.

INSERT INTO Mimick_ABCDEF (SELECT * FROM View_ABCDEF MINUS SELECT * FROM Mimick_ABCDEF);

This query inserts all rows in the view, but not in the table, into the table.

Quite simple :-)

 

ORA-01000: Open Cursors in Oracle

During some development at the office, we encountered some problems with Oracle having too many open cursors. We kept getting ORA-01000 error at random times.

What is a cursor

Very simple explained, a cursor in Oracle is a pointer to your result. The more complex explanation is that it is a pointer/handler into the context area where it refers to a chunk of memory that has been allocated. Two types of cursors exist: Implicit cursors and Explicit cursors. For more info on cursors, ask google :)

Problem

At work we have some Java Servlets querying an Oracle database at one day we started getting this error:

ORA-01000: maximum open cursors exceeded

This would block any new connections to Oracle, but over time we would be able to reconnect. A restart of the Servlet Runner (in this case Tomcat) would also enable us to connect again.

The problem arises when too many cursors are open at one time to Oracle. In Oracle it can be defined how many cursors are allowed, but exceeding the normal amount usually indicates that there is another problem. The usual culprit is that the code does not properly close Statements and therefore the cursor remains open until it times out or the program terminates.

Solution to ORA-01000

To start with, I will present a piece of code that will create open cursors:

public static Vector<Integer> incorrect()
{
	Vector<Integer> result = new Vector<Integer>();
	Statement stmt = null;
	ResultSet rs = null;
	try
	{
		stmt = conn.createStatement();
		rs = stmt.executeQuery("SELECT myInt FROM SomeTable");
		while(rs.next())
		{
			result.add(rs.getInt("myInt"));
		}
	}
	catch(SQLException sqle)
	{
		sqle.printStackTrace();
	}
	return result;
}

As it can be seen in the code, the stmt object is never closed. One correct way of writing this method would be:

public static Vector<Integer> correct()
{
	Vector<Integer> result = new Vector<Integer>();
	Statement stmt = null;
	ResultSet rs = null;
	try
	{
		stmt = conn.createStatement();
		rs = stmt.executeQuery("SELECT myInt FROM SomeTable");
		while(rs.next())
		{
			result.add(rs.getInt("myInt"));
		}
	}
	catch(SQLException sqle)
	{
		sqle.printStackTrace();
	}
	finally
	{
		try
		{
			if(rs != null)
			{
				rs.close();
			}
			if(stmt != null)
			{
				stmt.close();
			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
	}
	return result;
}

The try-catch statement was changed to a try-catch-finally that secures the stmt object to be closed.

To detect Open Cursors before it becomes a problem,  run the following query in Oracle:

SELECT user_name, sql_text FROM v$open_cursor ORDER BY user_name, sql_text;

This will display the last run SQL in each open cursor – but be aware lots of internal Oracle users will also be shown, do not get spooked by that :-)