Re: table operator on table of rowids

From: VC <boston103_at_hotmail.com>
Date: Thu, 05 Feb 2004 00:16:01 GMT
Message-ID: <5XfUb.225990$na.362669@attbi_s04>

Hello Chris,

"Chris Bateman" <chrisbateman_at_despammed.com> wrote in message news:70a27038b2c06a9b31ef965ffa52c766_at_news.teranews.com...
> "VC" <boston103_at_hotmail.com> wrote in message
> 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
>
>

Using varchar2 to store rowids is actually a cute idea. I ran some benchmarks and it turns out that you assumption abouthe functions I used being slower than conversion to varchar2 is true:

Case 1:

---

create or replace procedure p1 as

   l_RID myRIDTable := myRIDTable();
 begin

• Collect RIDs for x in ( select rowid from t1 where rownum <=1000 ) 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 for x in (select * from t1 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))) loop null; end loop; end;
  • TKPROF:

BEGIN p1; END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 2.65 2.99 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2 2.65 3.00 0 0 0 1

...

select * from t1 where

       rowid in (select DBMS_ROWID.ROWID_CREATE (
                  rowid_type,
                  object_number,
                  relative_fno,
                  block_number,
                  row_number) from table(cast(:b1 as myRIDTable)))

call     count       cpu    elapsed       disk      query    current

rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.32 0.35 0 6 0 0
Fetch 25367 1.08 1.10 0 25366 0 25366
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 25368 1.40 1.45 0 25372 0 25366

Case 2

---

create type myRIDTable2 as table of varchar2(30) /
create or replace procedure p2 as

   l_RID myRIDTable2 := myRIDTable2();
 begin

• Collect RIDs for x in ( select rowid from t1 ) loop l_RID.extend; l_RID(l_RID.count) := rowid; end loop;

     --
     for x in (select * from t1 where
       rowid in (select * from table(cast(l_RID as myRIDTable2))) )loop
         null;
       end loop;

   end;

• TKPROF:

BEGIN p2; END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.79 1.92 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2 1.79 1.92 0 0 0 1
....

select * from t1 where

       rowid in (select * from table(cast(:b1 as myRIDTable2)))

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.04 0.06 0 3 0 0
Fetch 25367 0.51 0.53 0 25366 0 25366
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 25368 0.55 0.59 0 25369 0 25366

---

The last select is almost three times faster with varchar2 than with the function calls.

The only hitch may be future de-support by Oracle of this rowid<->varchar2 conversion.

Rgds.

VC Received on Wed Feb 04 2004 - 18:16:01 CST