Well we finally upgraded to 11g at work. New playground for me to play in. Few things that most are aware of already :

- PLS_INTEGER was the recommended approach for coding when precision was not required. More efficient and faster. 11g brings to the table SIMPLE_INTEGER which is always NOT NULL. Since it is never NULL, then it never checks for that value (or lack of), thus making it even faster than PLS_INTEGER.

- Oracle 11g virtual table columns are columns that are actually functions (create table t1 (c1 number, c2 number, c3 as (c1+c2) virtual), and similarly, virtual indexes that are based on functions REF partitioning, allowing you to partition a table based on the partition scheme of another. Allows you to partition an order_items table based off of the order_date column in an orders table.

- Oracle 11g PL/SQL will you to specify trigger firing order.

- The 11g SQL Access Advisor gives partitioning advice, including advice on the new interval partitioning. Interval partitioning is an automated version of range partitioning, where new equally-sized partitions are automatically created when needed. Both range and interval partitions can exist for a single table, and range partitioned tables can be converted to interval partitioned tables.

- New with 10r2 proxy identification in SQL*Plus, the connect command has been enhanced to allow for a proxy, to aid applications that always connect with the same user ID:

connect sapr3[scott]/tiger

- Fine Grained Dependancy Tracking (FGDT)
This means that when you add a column to a table, or a cursor to a package spec, you don't invalidate objects that are dependant on them.

Link: http://momendba.blogspot.com/2009/07/who-says-full-table-scans-are-evil.html

Really good article that demonstrates how full table scans can be better than indexed scans... but as you read on, the real truth emerges as to why. Lesson to be learned... if your statistics are up to date, then the optimizer will do its job in almost all cases... if they are out of wack, thats when you find yourself using optimization hints. So think twice before you apply the optimization hint and promote that to production. Work with your DBAs to identify the root cause.

Link: http://www.myoracleportal.net/blog1.php/2009/02/01/plsqlftp

Author Comments : Important issue identified regarding the structure of your remote FTP servers banners. Although with a true FTP client tool it may not be required, this FTP process follows best practice and expects that the server codes precede each line being returned by the remote FTP Server as it authenticates to the remote server. Failure to include the required prefix could cause you to be unable to access the remote server or failure to authenticate. See for more info => TCP Banners and then contact the respective server administrator.

Additional information on server codes can also be found here.

It might be possible to enhance the PLSQL solution to work past the issue, however I would not encourage that and instead stay with best practice.

On the topic of analytical functions, I will be the first to say that they are not my strength. However that said, reading one article after another I am increasingly aware of what I am missing.

I stumbled upon an excellent article demonstrating significant performance gains across the board from timing to resource utilization, all due to the use of analytical functions.

I encourage you take some time and review the posting.

Analytic Functions: A Savior by ASIF MOMEN

Link: http://www.oracle-developer.net/display.php?id=510

"Oracle has supported stored expressions for many years, in views and function-based indexes. Most commonly, views enable us to store and modularise computations and expressions based on their underlying tables' columns. In more recent versions (since around the 8i timeframe), we have been able to index expressions using function-based indexes. Now, with the release of 11g, Oracle enables us to store expressions directly in the base tables themselves as virtual columns.

As we will see in this article, virtual columns are more flexible than any of their prior alternatives. We will examine their basic usage and also consider some of the performance aspects of the new feature."