db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Prashanth Menon <pme...@ca.ibm.com>
Subject Re: problem with view's and optimizer
Date Mon, 16 Apr 2007 17:50:52 GMT
Hi Army,

Thnx for the reply, I did the sysinfo call and here is what it spit out:

------------------ Java Information ------------------
Java Version:    1.4.2
Java Vendor:     IBM Corporation
Java home:       C:\Progra~1\IBM\java\jre
Java classpath:  %CLASSPATH%;C:\Progra~1\IBM\eclipse\plugins\org.apac

OS name:         Windows 2003
OS architecture: x86
OS version:      5.2
Java user name:  user
Java user home:  C:\Documents and Settings\user
Java user dir:   C:\Documents and Settings\user
java.specification.name: Java Platform API Specification
java.specification.version: 1.4
--------- Derby Information --------
JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
[C:\Program Files\IBM\eclipse\plugins\org.apache.derby\derby.jar] 10.
1.2.5 - (413784)
[C:\Program Files\IBM\eclipse\plugins\org.apache.derby\derbytools.jar
] - (413784)

I can't give the query (or the DB), but I can give you a gist of how it 

I've got VIEW1 which is fairly small, it's got about 8 columns and 
left-outer-joins on 4 other smaller tables (I can run a select 
successfully on this table).  VIEW2 which selects from VIEW1 goes 
something like:

create view VIEW2 as 
select blah, blah2, ...
        (select blah, blah2...
        from view1 
        left outer join on 1
        left outer join on 2
        ) temp1,
        (select blah3, blah4...
        from view1
        left outer join 3
        left outer join 4
        ) temp2
where blah blah blah;

Now, that gets a bit complex, but I can still do a select on it and get 
back results (after a bit of time).  VIEW3 gets interesting (and causes 
the failure), it goes something like:

create view VIEW3 as
select blah1, blah2, blah3...
        case ...
        when ..
from VIEW2 
        left outer join 
        (select blah6, blah7 from VIEW2 where blah blah blah)
        on blah.....
        left outer join...
        left outer join..
where ....

So as you can see, VIEW3 gets complex (since we select from VIEW2 twice). 
However, if I create a separate view for the select in the from clause and 
do a regular left outer join, it still will not work.  I've simplified it 
by removing certain rows and it still will not work.  It seems like doing 
any manipulation on VIEW2 crashes the thing.

Any idea? What more info do you need?

Prashanth Menon
View raw message