The PLSQL Compiler

Page 181

TY_REVIEW (T_REVIEW_DATES(SYSDATE), T_REVIEW_DATES(SYSDATE-30), T_REVIEW_DATES(SYSDATE-60), T_REVIEW_DATES(SYSDATE-90) ), TY_REVIEW (T_REVIEW_DATES(SYSDATE), T_REVIEW_DATES(SYSDATE-30), T_REVIEW_DATES(SYSDATE-60), T_REVIEW_DATES(SYSDATE-90) ), TY_REVIEW (T_REVIEW_DATES(SYSDATE), T_REVIEW_DATES(SYSDATE-30), T_REVIEW_DATES(SYSDATE-60), T_REVIEW_DATES(SYSDATE-90) ), SYSDATE, SYSDATE); -- Now, query using the in operator COLUMN review_date FORMAT A20 COLUMN salary_last_increase FORMAT A20 SQL> SELECT emp_id FROM t_emp_info WHERE promotion_date IN (review_date, salary_last_increase);     EMP_ID ----------          2 SQL> SELECT emp_id FROM t_emp_info WHERE promotion_date NOT IN (review_date, salary_last_increase); EMP_ID ----------          1 

New Operators

A number of new operators are available for use with collections in Oracle Database 10g. Table 7-3 lists these new operators and describes their use. For examples and usage, see the Oracle Database 10g SQL Reference Guideor the Oracle Database 10g PL/SQL Users Guide and Reference.

The PL/SQL Compiler

There have been changes to the PL/SQL compiler in Oracle Database 10g. This includes a new version of the PL/SQL compiler, changes to native compilation of PL/SQL, new fine-grained debugging capabilities, and new PL/SQL compiler warnings. Let's look at each of these features in more detail in the next few sections.

New PL/SQL Compiler

Oracle Database 10g comes with a new, faster PL/SQL compiler. The compiler is more efficient than the previous PL/SQL compilers, and thus generates code that runs more efficiently. In fact, Oracle reports that the new PL/SQL compiler is 50 to 75 percent faster than the Oracle9i compiler. Associated with the new compiler is a new parameter, plsql_optimize_level. When this parameter is set to 2 (the default