Things I miss about Oracle 10g

Due to a recent job change, I have had to go from 10gR1 to 8i. While it hasn't been a horrible transition, there are some things that I wish I was ignorant about. Here are some things that I miss about Oracle 10gR1:

1) Dual row generation trick
I don't use this one much, but when I need it, its one of the best ways to create rows on the fly.

SELECT ROWNUM rn
FROM DUAL
CONNECT BY ROWNUM <= :p_num_rows

2) With clause
I love the modularity of this syntax. It's great when you want to get a filtered list from a master table and then get the child records of those master records. Complex queries can be written in a cascading style instead of inside out. Queries can be better self-documented. It even opens a new path of materializing a reused query to temp for the sake of performance!

WITH info AS (
  SELECT o.ORDER_ID, o.CUSTOMER_NAME
  FROM ORDERS o
  WHERE o.ORDER_TOTAL > 1000
), details AS (
  SELECT od.ORDER_ID, od.PRODUCT, od.QUANTITY, od.LINE_TOTAL
  FROM ORDER_DETAILS od
  WHERE od.ORDER_ID IN (
    SELECT i.ORDER_ID
    FROM INFO i
  )
)
SELECT
  i.ORDER_ID,
  i.CUSTOMER_NAME,
  NULL PRODUCT,
  NULL QUANTITY,
  NULL LINE_TOTAL
FROM INFO i
  UNION ALL
SELECT
  d.ORDER_ID,
  NULL CUSTOMER_NAME,
  d.PRODUCT,
  d.QUANTITY,
  d.LINE_TOTAL
FROM DETAILS d

3. SQL and PL/SQL having the same parser.
This is one of the most annoying changes. Write a query that works from TOAD/SQL*PLUS and then, when you put it in your PL/SQL, it bombs. Why? Because PL/SQL doesn't recognize the syntax. Its easy to get around, just open it dynamically with "execute immediate" or "open {cursor} for" syntax, but I prefer to avoid as much hard parsing as possible.


4. User defined aggregates (especially stragg).
When you want to return a list of information and a comma separated list of some detail record (e.g. a user lists and a list of each user's interests), the fastest and best way I've found to return that is via stragg. Without it, I've had to resort all sorts of tricks depending on the situation. A cursor column, a query for details for each record, a cartesian of sorts (duplicate the master record for each child), or hard code a max number of details to display.


5. Flashback
This feature is just too cool. Its nice to be able to "see" what modifications were made to a table. Just compare the table to a flashback at a known time and see if it did what you expected. Its like having access to any data in your db as far back as your undo space can hold


6. PL/SQL tables indexed by VARCHAR2
Trying to figure out how to assign a number to each row/group you want to process when it has no uniquely identifying number attribute can be awfully painful. Usually its a choice between using a simple algorithm that will be slower, easier to read, and harder to screw up and a more complex "correct" algorithm that will be faster, but harder to read and easier to screw up.


7. AWR
Ok, its available in 8i via Statspack, but AWR is much cleaner. Instead of having to have the correct scripts in your oracle home and then running the report script, you just use the awr package to run both the snapshots AND the report. Very cool. One of the easier ways to get performance info without having to do any plate switching. Makes me wonder why they don't build a trace analyzing package into the database to make it easy to tkprof a session's work...


8. Transparent PL/SQL optimizations
While it may just feel like 10g is faster, I think it might actually be faster with some of the optimizations they did. One known example is the transparent 100 row bulk fetch for implicit cursor for loops.


9. Returning Clause
This clause allows you to insert a record and simultaneously query back what you just inserted. Why is it useful? The most common use for me is to insert a record with a sequence and get the new id back in one statement.

INSERT INTO T(
  ID,
  NAME
) VALUES (
  T_SEQ.NEXTVAL,
  'John Doe'
)
RETURNING ID
INTO v_id;

10. Inserting using a %ROWTYPE PL/SQL variable
This just looks cleaner to me. Have a pl/sql %ROWTYPE variable, set the fields there and then insert all the values. I don't use it when I need to get a sequence as I find a sequence query + insert is less performant on scale (e.g. the more inserts you do that way, the worse it gets)

DECLARE
  v_t T%ROWTYPE
BEGIN
  v.NAME := 'Tom';
  INSERT INTO T VALUES v_t;
END;

11. Merge
Another tool that's perfect for what it was designed. Want to ensure that a record exists a certain way in a table, but don't know whether to insert or delete it? Then merge it!

MERGE INTO T old_t
USING (
  SELECT 12 ID, 'John Doe' NAME
  FROM DUAL
) new_t
ON (
  old_t.ID = new_t.ID
)
WHEN MATCHED
THEN UPDATE
SET
  old_t.NAME = new_t.NAME
WHEN NOT MATCHED
THEN INSERT(
  ID,
  NAME
) VALUES (
  new_t.ID,
  new_t.NAME
)

The examples are not tested and are merely meant to show the syntax. If I can come up with one more, then I may have to make a 12 days of XMas song out of it :-).