Re: table operator on table of rowids

"VC" <boston103_at_hotmail.com> wrote in message news:i4ySb.59275$U%5.344787_at_attbi_s03...
> This looks a little nicer and should be more efficient:
>
> create or replace function f1( p_deptno in number) return sys_refcursor as
> l_RID myRIDTable := myRIDTable();
> l_cur sys_refcursor;
> begin
>
> -- Collect RIDs
> for x in ( select rowid from emp where deptno=p_deptno ) loop
> l_RID.extend;
> l_RID(l_RID.count) := myRIDType(
> dbms_rowid.rowid_type(x.rowid),
> dbms_rowid.rowid_object(x.rowid),
> dbms_rowid.rowid_relative_fno(x.rowid),
> dbms_rowid.rowid_block_number(x.rowid),
> dbms_rowid.rowid_row_number(x.rowid)
> );
> end loop;
>
> -- open ref cursor
> open l_cur for select empno, ename, job, deptno from emp where
> rowid in (select DBMS_ROWID.ROWID_CREATE (
> rowid_type,
> object_number,
> relative_fno,
> block_number,
> row_number) from table(cast(l_RID as myRIDTable)));
> return l_cur;
> end;
> /
>
> VC

Thanks for this suggestion and also to Jonathan for his suggestion (the link for which is http://www.jlcomp.demon.co.uk/v7arrays.html)

I've performed an admittedly crude performance test of 4 methods:

1. A 'control' test, where I build a PL/SQL table of numbers and then cast it to select by the primary key of the table.

(By using rowids instead, performance should improve a little by avoiding a table access to get the primary key value and then an index scan to get the rowid from primary key).

2. Jonathan's 'array_as_cursor' style function improves on the control test by approx 10%.

3. VC's DBMS_ROWID method was 60% slower than my control test - perhaps due to the numerous function calls involved or use of PL/SQL table of object_type?

4. Using a PL/SQL table of varchar2(18) is 20% faster than my control test. I guess this works because my varchar2 rowid value is implicitly cast to rowid type...

SQL> select * from foobar where rowid = 'AAAG4yAAGAAAAAMAAt';

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (BY USER ROWID) OF 'FOOBAR' I'm going to go with the last method because it's simple and fast.

Regards
Chris Received on Wed Feb 04 2004 - 03:24:39 CST