Re: Parsing and many joins

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 14 Nov 1999 20:53:15 -0500
Message-ID: <72YvONQVEFMdre5f7RJLiNKGggl+@4ax.com>

A copy of this was sent to Ole C Meldahl <meldahl_at_pvv.org> (if that email address didn't require changing) On Mon, 15 Nov 1999 00:23:15 +0100, you wrote:

>Hi
>
>On Oracle 8.0.6.
>
>We have table containing the following columns: object_id, attribute_id
>and value columns.
>
>We need to get the full set of attribute names and values for an object
>as one row, this obviously means alot of joins. When the number of
>attributes is small the query runs reasonably, but when we move from 16
>to 32 attibutes the querytime goes skyhigh(more than quadrupled). Why?
>Getting 2 rows from the table shouldn't be worse than getting one and
>then one more?
>
>In general, what parses faster, hinted cost or rule? What can we do to
>reduce parsetime, pinning in or expanding shared pool is not an option.
>

the more tables -- the longer the parse, lots more combinations to consider.

Don't use a join. use decode. for example:

select object_id,

       max( decode( attribute_id, 1, value_col, null ) ) "Attribute 1", 
       max( decode( attribute_id, 2, value_col, null ) ) "Attribute 2", 
       max( decode( attribute_id, 3, value_col, null ) ) "Attribute 3", 
....
       max( decode( attribute_id, N, value_col, null ) ) "Attribute N"

  from attribute_table
 where object_id = X
 group by object_id
/

That will get the required information with one index range scan on the table. The above will be much faster and won't require lots of outer joins and all.

>
>
>ole c

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Nov 14 1999 - 19:53:15 CST