Re: Self-Join

There are two problems with the OTLT approach (as well as EAV). One is
laid out nicely by Tony.

The second issue is that this big, generic table hides crucial
information from the optimizer. If you cluster/order the data by the
lookup type you can at least minimize page/block reads and improve
data caching rates (in that common types are clustered together and
hence cached together), but you still prevent simple full table scans
of low cardinality sets. You make more involved cardinality
computations more difficult or even impossible for the optimizer to
resolve.

And every, literally every, optimizer mistake goes back to
insufficient information about data cardinality.

Scott

On Tue, Dec 6, 2011 at 9:32 AM, Oliveiros d'Azevedo Cristina
<oliveiros.cristina@marktest.pt> wrote:
> I have not.
>
> I've already skimmed through it.
>
> Indeed, it is very interesting
>
> Thanx , Scott
>
> Best,
> Oliver
>
> ----- Original Message ----- From: "Scott Swank" <scott.swank@gmail.com>
> To: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>
> Cc: "Abhinandan Raghavan" <Abhinandan.Raghavan@unige.ch>;
> <pgsql-sql@postgresql.org>
> Sent: Tuesday, December 06, 2011 5:17 PM
> Subject: Re: [SQL] Self-Join
>
>
>
> Have you read Tony Andrew's 2004 piece on this approach? It is a classic.
>
> http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html
>
> Scott
>
> On Tue, Dec 6, 2011 at 6:39 AM, Oliveiros d'Azevedo Cristina
> <oliveiros.cristina@marktest.pt> wrote:
>>
>> Howdy, Abhinandan,
>>
>> A quick and dirty solution might be this :
>>
>> SELECT *
>> FROM
>> (
>> SELECT a.name,MAX(b.value) as height
>> FROM original a
>> LEFT JOIN original b
>> ON a.name = b.name
>> AND b.attribute = 'Height'
>> GROUP BY a.name
>> ) height
>> NATURAL JOIN
>> (
>> SELECT a.name,MAX(b.value) as weigth
>> FROM original a
>> LEFT JOIN original b
>> ON a.name = b.name
>> AND b.attribute = 'Weight'
>> GROUP BY a.name
>> ) weight
>> NATURAL JOIN
>> (
>> SELECT a.name,MAX(b.value) as age
>> FROM original a
>> LEFT JOIN
>> original
>> b
>> ON a.name = b.name
>> AND b.attribute = 'Age'
>> GROUP BY a.name
>> ) age
>>
>> The thing is that it doesn't scale well if you have many more items beyond
>> three...
>>
>> Best,
>> Oliveiros
>>
>> ----- Original Message -----
>> From: Abhinandan Raghavan
>> To: pgsql-sql@postgresql.org
>> Sent: Tuesday, December 06, 2011 1:57 PM
>> Subject: [SQL] Self-Join
>>
>> Hi,
>>
>> I'm looking to frame an SQL statement in Postgres for what's explained in
>> the attached image.
>>
>> The original table is at the top and is called NAV (Short for Name,
>> Attribute, Value). I want to create a view (NWHA_View) involving values
>> from
>> within (presumably from a self join). I would've normally created a view
>> in
>> the following way:
>>
>>
>> SELECT A.NAME,
>> A.VALUE AS WEIGHT,
>> B.VALUE AS HEIGHT,
>> C.VALUE AS AGE
>>
>> FROM NAV A,
>> NAV B,
>> NAV C
>>
>> WHERE A.NAME = B.NAME
>> AND A.NAME = C.NAME
>> AND A.ATTRIBUTE = 'Weight'
>> AND B.ATTRIBUTE = 'Height'
>> AND C.ATTRIBUTE = 'Age'
>>
>>
>> The only problem when I create a view with the above select statement is
>> that when there are no entries for the field name "AGE" (in the case of
>> David), then the row does not get displayed. What's the way out in
>> Postgresql? I know the way it is addressed in Oracle but it doesn't seem
>> to
>> work in Postgresql.
>>
>> Thanks.
>>
>> Abhi
>>
>>
>> ________________________________
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql