Re: 9i R2 database vs. 9i R2 Forms Developer PL/SQL compatibility

Yong Huang wrote:
>
> Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3F44B530.20A8_at_yahoo.com>...
> > Sybrand Bakker wrote:
> > >
> > > On Wed, 20 Aug 2003 17:02:58 GMT, "Maximus" <asdfasdasd_at_eqeqweqwe.com>
> > > wrote:
> > >
> > > >it compiles and works as documented. However, in Forms Developer I get a
> > > >compile error "no function 'VARCHAR2' exists in this scope."
> > >
> > > The type definition doesn't make sense. Why you would index a table by
> > > a varchar2?
> > >
> > > Sybrand Bakker, Senior Oracle DBA
> > >
> > > To reply remove -verwijderdit from my e-mail address
> >
> > For functionality purposes, but also for sparse data it can actually be
> > faster to do so than a numeric index.
>
> Connor,
>
> Can you elaborate? Are you aware of any benchmark? Someone once
> claimed on an Oracle mailing list that comparing varchars (as in where
> empno =
> '1234') is faster than comparing numbers (as in where empno = 1234) if
> the
> datatype is defined as varchar and number, respectively. His reasoning
> is that numbers are not stored as simple numbers and conversion has to
> be done. Could that be the same reason?
>
> Yong Huang

I've got a very basic demo of associative arrays at http://www.oracledba.co.uk/tips/9i_assoc_arrays.htm

So lets say you want to index by a sparse set of numbers ie big gaps between each index. If you index-by-varchar2 as opposed to what would feel more natural (ie index-by-binary-int) then cycling through the array using the ".next" attribute runs faster with the varchar indexed array.

The results are reversed if the indexes are contiguous. (ie numeric indexes win out). I would guess that internally the two implementations are done with different aims in mind.

cheers
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"