How to use BIND variable for SQL statement with IN clause ?

One of the question I faced in my last interview (about 3 months back) is "How do you use Bind Variable of SQL query with dynamic IN clause in JDBC?". It's irrelvant what I answered then, however while doing SQL Tuning task for the applicaiton I currently work I stumbled on identical queries with hard parsing. The reason for hard parsing - dynamic IN value set is concatenated to the SQL statement rather than using bind variable as there is not direct mechanism to use bind variables for dynamic IN value set. After searching through AskTom (my favorite site!!) found a way to use bind variables:

SQL>   create type num as table of number;

SQL> create or replace function CUSTOM_IN_LIST(in_list IN VARCHAR2)
 RETURN NUM
 as
 built_in_datatype  dbms_utility.uncl_array;
 array_len                   binary_integer :=1;
 return_array            num := num();
 begin
   dbms_utility.comma_to_table(in_list,array_len,built_in_datatype);
   for indx in built_in_datatype.first .. built_in_datatype.last-1
 loop
  return_array.extend;
  return_array(indx) := to_number(replace(built_in_datatype(indx),'"'));
   end loop;
   return return_array;
 end;

After this all that needs to be done is replace the SQL code like below

SELECT * FROM dept WHERE deptno IN (10,20,30);

to
SELECT * FROM dept WHERE deptno IN
(select * from TABLE(CUSTOM_IN_LIST('"10","20","30"')));

This is comparitively easy as all one have to do in concatenate the function call to SQL string.

Oracle 9i New Features

It's fun to know whaz up with the new release (though 9i isn't latest) and Oracle Press book on Oracle 9i New Features is concise, simple, clear and informative. I listed things which I find more interesting (particularly the feature to set time out on FOR UPDATE NOWAIT is great!)


1. Merge Statement

Does both insert and update in just one scan on source table. ETL guys will love this!.

MERGE INTO bonuses D USING
(SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1);

2. Inserting into multiple table

Another command that can be used in ETL. Can also be used for spliting one table into multiple tables without using PARTITIONING

(a) Insert into all listed tables

INSERT ALL
INTO table_1 VALUES(col1,col2,col3)
INTO table_2 VALUES(col2,col3,col5)
SELECT col1, col2,col3,col5 FROM table_3
....

(b) Insert into first matching table

INSERT FIRST
WHEN condition1 THEN INTO table1 VALUES(...)
WHEN condition2 THEN INTO table2 VALUES(...)
ELSE INTO table3 VALUES(...)
SELECT ....

3. CASE expression

Portrays SQL as a programming language

(a) Simple CASE

SELECT cust_last_name,
CASE credit_limit
WHEN 100 THEN 'Low'
WHEN 5000 THEN 'High'
ELSE 'Medium' END
FROM customers;

(b) Search CASE

select
case when deptno > 20 then 'FINANCE' else dname end
as deptartment from dept;

4. CURSOR expression

I don't have to use anonymous block any more to test PL/SQL function/procedure that takes ref cursor as it's argument.

- This is equivalent to PL/SQL Ref CURSOR.

Assume function f takes SYS_REFCURSOR as an argument. Now you can call this function from SQL as

SELECT f(CURSOR(select * from emp)) from dual;

create or replace function f(x in SYS_REFCURSOR) return number is
salsum number := 0;
rec emp%ROWTYPE;
begin
   loop
       fetch x into rec;
      EXIT when x%NOTFOUND;
      salsum := salsum + rec.sal;
  end loop;
 close x;
 return salsum;
end;

5. Skip Scan Indexes

This is interesting feature - Just makes composite indexes more useful. If you want to use this feature then you have take side with camp that suggests the leading column of composite index must be columns with low cardinality :-(

With skip scan, Oracle can use a composite index even if leading column is empty.

Skip scan is done only when the cardinality of the leading column is low (less unique values)


6. External Tables

This one I am not sure. It's great to know that using SQL you can access DAT files stored in the OS, however I am not sure about performance and how extensively this feature can be used.

    CREATE DIRECTORY EXT_TABLES AS 'C:\Oracle\External_Tables';

-- Create the external table
-- Files must exist in the specified location

CREATE TABLE employees_ext
 (empno NUMBER(8), first_name VARCHAR2(30), last_name VARCHAR2(30))
ORGANIZATION EXTERNAL
(
   TYPE ORACLE_LOADER
   DEFAULT DIRECTORY ext_tables
   ACCESS PARAMETERS
   (
 RECORDS DELIMITED BY NEWLINE
 FIELDS TERMINATED BY ','
   )
   LOCATION ('employees1.txt','employees2.txt')
)
 PARALLEL 5
 REJECT LIMIT 200;

7. PL/SQL Enhacements

(a) Insert row into a table using PL/SQL Record (just the record variable instead of specifying all attributes)

   declare
    my_emprow emp%ROWTYPE;
    p1 SYS_REFCURSOR;
begin
   open p1 for 'select * from emp where empno=7934';
   fetch p1 into my_emprow;
   insert into emp values my_emprow;
   commit;
end;

(b) Bulk error handling

Don't stop bulk loading if an exception happens in the middle. No more UNDO of entire DML when one row fails in the bulk updates

• SAVE EXCEPTIONS
• SQL%BULK_EXCEPTIONS.COUNT
• SQL%BULK_EXCEPTIONS().error_code|error_index

8. Oracle Supplied Packages

Extra metadata in plain text & xml

• dbms_metadata.get_ddl(...)

List the MV capabilities

• dbms_mview.explain_mview('mview') & mv_capabilities_table
  

-Very handy when you don't know why your materialized view is not fast refreshable

How to setup flash-back queries?

      dbms_flashback.enable_at_time
                       .disable
                       .enable_at_system_change_number
                       .get_system_change_number

Set-up

- undo_retention must be specified
- DB must be using automated UNDO management

How to reorganize table online?

Now, it is possible to built tables online just like index rebuild.

 dbms_redefinition   -> rebuild tables online (allows DML)
                .can_redef_table()
                .start_redef_table()
                .finish_redef_table()

before start... create the duplicate table which match the table being rebuilt and disable all referential constraints. (The concepts I found very similar to fast refreshing materialized views)

9. Piped Function/Table Functions

How about IPC in oracle?... Though you don't write any lowel code for this :-(. With pipe's you can return result as they were produced instead of waiting for the whole operation to be completed.

create type dept_wages as object (deptno number, total_wages number);
/

create type dept_wages_table as table of dept_wages;
/

CREATE OR REPLACE FUNCTION "SCOTT"."COMPUTE_DEPT_WAGES"
    (dept_records IN SYS_REFCURSOR)
return dept_wages_table
pipelined
parallel_enable (partition dept_records BY ANY)
is
dept_wages_instance dept_wages := dept_wages(0,0);
dept_rec emp%ROWTYPE;
last_dept emp.deptno%type := -1;
begin
LOOP
    fetch dept_records into dept_rec;
    EXIT when dept_records%NOTFOUND;
    if dept_rec.deptno=last_dept then
          dept_wages_instance.total_wages :=
                     dept_wages_instance.total_wages + dept_rec.sal;
    elsif last_dept=-1 then
          last_dept := dept_rec.deptno;
          dept_wages_instance.deptno :=last_dept;
          dept_wages_instance.total_wages := dept_rec.sal;
    elsif dept_rec.deptno!=last_dept then
          pipe row(dept_wages_instance);
          last_dept := dept_rec.deptno;
          dept_wages_instance.deptno :=last_dept;
          dept_wages_instance.total_wages := dept_rec.sal;
    end if;
END LOOP;
pipe row(dept_wages_instance);
close dept_records;
return;
end;
/

select * from TABLE(compute_dept_wages(CURSOR(
                        select * from emp order by deptno))
                )

The catch is Pipelined function can take either a ref cursor or collection object and will return Nested Table or Varray object.

10. SQL New Features

• SELECT a.empno,a.deptno,b.dname FROM emp a RIGHT OUTER JOIN dept b ON (a.deptno=b.deptno)
  

          - FULL OUTER JOIN
       - JOIN  

• NULLIF(comm,0) - returns NULL if both are equal or value of 1st arg.

• COALESCE(comm,0) - returns first non-null value (~ nvl)

11. Schema Design

Foreign key:

• DML on child table will not lock parent table at all.
• Duration of lock on child table while updating parent table is minimized in 9i. (Lock is uptained and released once for each row)
• In general you don't have to index foreign key unless you have on delete cascade.
  

NOWAITE timeout:

Now, Java threads can't can be in struck state when the thread tries to update the record that's being updated (yet uncommited) by some other process.

SELECT * FROM EMP FOR UPDATE WAIT 3 -- (3 seconds)

CURSOR_SHARING = -- Still haven't tried this feature.

12. Data types

- TIMESTAMP
- INTERVAL

In short following are the major enhancements done in 9i for ETL:
1. External tables
2. Multi-table insert
3. Merge statement
4. Table functions (Pipelined & Parallel)
5. CDC