I got a call from Chris Jones over at Oracle about some really cool news. Oracle 11g’s support for Database Resident Connection Pooling (pdf) is now supported in the beta version of PHP’s OCI8 Driver. Chris and I had a long conversation a couple weeks back about where the responsibility for the driver should end, and where it should begin which sparked a few interesting topics like result set caching (also available in 11g). The net of all of this are two pretty cool features in the new driver: Support for 11g’s connection pooling and support for Fast Application Notification (FAN). Due to our platform (Solaris vs. Linux) we don’t get the joyous opportunity to try out the DRCP support; however, we’re extremely excited to be beta testing 1.3.0 of the driver specifically for FAN support.

I’m not a DBA, and even though I pretend sometimes, I can’t even come close to pretending on this one. We have a primary database and a secondary database (physical standby) using DataGuard. With FAN support, we can actually fail over to the secondary database, and allow the OCI8 driver to avoid the giant timeout normally found in TCP, reconnect and pick up where it left off. I’m super excited to see this working… has anyone tried it yet?

This is one of the coolest things ever. One of our wonderfully spectacular DBAs (Hi, Brian!) pointed this out and it has saved us a couple times. I know there are better ways to implement this specific example, but it’s to illustrate a point. Here’s what we do now:

CREATE TABLE temp_1 (
user_id NUMBER(3)
);
INSERT INTO temp_1
(user_id)
SELECT user_id
FROM table1;

SELECT user_name, user_info
FROM users, temp_1
WHERE users.user_id=temp_1.user_id;

And here’s using the WITH clause:

WITH temp_1 AS (
SELECT user_id
FROM table1
)
SELECT user_name, user_info
FROM users, temp_1
WHERE users.user_id = temp_1.user_id

Oracle creates a temporary table internally and uses it in the query. You might be thinking, “not a big deal, I do things like this all the time.” Here’s the kicker: There’s NO redo generated. For those of use with physical and/or logical standby’s, we NEED this kind of functionality to keep redo down on the backup databases. YAY!

Matthew Turland wrote on his blog, “[the nested set] model is simple and intuitive, but it has a drawback: to obtain any significant portion of the hierarchy stored in a table can take up to as many queries as there are levels of depth in the hierarchy.” I like the theory that Matthew talks about and the implementation for Oracle provided by developer.com got me thinking. I wasn’t so sure that some of the analytic functions in Oracle couldn’t make this happen faster/better/cheaper, so I set out to figure it out.

One of the primary concerns with the nested set model is the requirement that N number of rows would have to be updated with each new item. This is a deal-breaker in my job and wouldn’t fly, so I’ve added an additional column to assist in the solution that identifies the level at which the item occurs.

Given the following data set:

Here’s the query I came up with:

SELECT max(ltrim(sys_connect_by_path(site_area, ' > '),' > '))
FROM (
SELECT
site_area,
site_link,
row_number() over (partition by 1 order by site_level) level_index
FROM hierarchical_data
START WITH id=8
CONNECT BY PRIOR parent_id=id
)
START WITH level_index = 1
CONNECT BY level_index = prior level_index + 1;

And the resulting data set:

Let me know what you think… and be nice.