Related Entries

Useful SQL*Plus headers
Dino eats frog
Oracle with Active Record
When is he free?
Gentle dive into analytic functions

« Canada launches WebPot
» Stored procedures for security

PL/SQL grows a bit more

New features in Oracle 9i for PL/SQL

New Oracle 9i Features: “Native Compilation, CASE, and Dynamic Bulk Binding.”. Native compilation and bulk binding, I like. I think adding new syntax like CASE and FORALL is not entirely useful - IF-ELSIF and FOR would’ve been enough and the language would have continued to be small and simple.

Native code compilation should result in good speed increase, especially for batch processing. When I was working on a data warehousing system, I needed to do some nightly processing that involves search and updates of several million records. How much ever I tuned this, it was not very speedy. Finally I solved it by getting a huge hard disk, downloading data to flat files, then using standard unix shell tools to manipulate these and use SQL*Loader to upload the data back. Ugly and inelegant, but the speed was impressive. You still can’t do good text processing in PL/SQL, but native compilation might just be the catalyst for your performance requirements.

Bulk binding looks like

SELECT employee_id
     BULK COLLECT INTO employee_ids
     FROM employees
    WHERE salary < 3000;
Not pretty, but is should improve performance quite a bit.