Oracle9i also supports databases created with multiple block sizes. One standard block size and up to four other sizes. This is useful when the same database is used for a transactional system (usually smaller blocks) and a Decision support system (larger blocks).
The update of outlines is now supported (dbms_outln_edit). Outlines are created like this: create or replace outline... They are used to specify an execution plan for queries so that you can optimize application queries without touching the code.
Multiple column indexes can now be used in queries that test only the second column of the where clause. That is thanks to a clever index travel technique called 'Skip-scanning'.
SQL: new command called MERGE that does both insert and update.
SQL: Very cool concept of an external table that can be based on a flat
file so that we can do a direct SELECT statement on flat files. The syntax
is:
create table abc
organization external (type oracle_loader access parameters ...)
PL/SQL: We can write pipelined functions that do intermediate returns.
That is useful for something like a filter function. The syntax is:
create function abc ... return ... pipelined ...
PL/SQL: (not new) if I have a function that has a REF CURSOR as a parameter
I can it passing an SQL statement:
a:= my_function(cursor(select * from products));
SQL: We can do inserts in several tables with the same insert statement (insert all) with some conditions inside (when) and control the sequence of the inserting with the clause 'first'.
SQL: Using query re-write we can redirect complex queries that use multiple large table joins onto materialized views.
PL/SQL: In 9i DBMS_LOB is no longer necessary for operations line length, insrt and substr on CLOBs. All regular varchar2 functions now work on CLOBs.
SQL: We can now outer join on both side of a where clause.
PL/SQL: CASE is born! It's kind of like a decode only much more powerful and easier to read. Check the doc.
PL/SQL: 9i can compile it. Performance improvement factor from 2 to 10.
PL/SQL: Real object features have arrived: inheritance of both attributes and methods.
We can recover accidentally deleted data using the new FLASHBACK feature of 9i. Will that mean we'll have to add 'Undo' to our applications one of these days?
There is an XML Datatype now!