jcon.no: Oracle blog

I just found this article by Adrian Billington about the cost based optimizer related to using PL/SQL code. Anybody writing PLSQL should read the article.

This is the first time I seen the associate syntax for associating statistics with PLSQL functions.

Syntax for setting selectivity (density):

SQL> ASSOCIATE STATISTICS WITH FUNCTIONS fuction1 DEFAULT SELECTIVITY 0.1;

Syntax for setting cpu cost, io cost and network cost:

SQL> ASSOCIATE STATISTICS WITH FUNCTIONS function2 DEFAULT COST (10000, 1000, 0);

Syntax for removing associated statistics:

SQL> DISASSOCIATE STATISTICS FROM FUNCTIONS , ,...;