Speed of query with REF?

From: <si_bendovi_at_hotmail.com>
Date: Tue, 16 Mar 1999 18:46:27 GMT
Message-ID: <7cm8tv$kjp$1@nnrp1.dejanews.com>

I need to improve speed of master/detail join iteration. I did:

SQL> select * from ( select deref( i) i, a from ii) ii1 where ii1.i.a = 2;

I(A, B)

TA(2, 2)

real: 142384
As you can see, it took 142 seconds.
Similar query using join on Foreign Key tooks 270 milliseconds. Amount of data in tables is the same.

SQL> select ta( f.id, f.a), ff.a from f, ff where ff.f = f.id and f.id = 2;

TA(F.ID,F.A)(A, B)

TA(2, 2)

real: 270

Here is structure of tables i, ii

SQL> desc ta

 Name                            Null?    Type


 ------------------------------- -------- ----


 A                                        NUMBER
 B                                        NUMBER

SQL> desc i
 Name                            Null?    Type


 ------------------------------- -------- ----


 A                                        NUMBER
 B                                        NUMBER

SQL> desc ii
 Name                            Null?    Type


 ------------------------------- -------- ----


 I                                        REF OF TA
 A                                        NUMBER

When I start this long query using reference, memory usage of server grows from 70MB to 170MB. I mean size, not resident size. When query starts, Oracle session process working 95% of processor time and memory is allocating this additional 100MB memory. This take 1 minute. After that Oracle session process drop to 5% and disk is working. Machine has 128MB physical memory. When query finish, size of Oracle in memory drop to 70MB. Table I has 20000 rows and table II has 40000 rows. There are 2 rows in II for one in I. REF column in table II is created with scope and rowid. I tried to improve speed of this query with index, but I have:

SQL> create index ii$i$a on ii( i.a);
create index ii$i$a on ii( i.a)

ERROR at line 1:
ORA-22808: REF dereferencing not allowed

What I missed out?

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Mar 16 1999 - 12:46:27 CST