getting corelated values in the inline view of the sub-query

From: Wizkid <hariharan.sam_at_wipro.com>
Date: 29 Oct 2004 09:40:39 -0700
Message-ID: <54626635.0410290840.1c51c568@posting.google.com>

All,

I have a big table say, tablea with columns cola (varchar),colb
(date),colc (date),cold(number). The first three columns are part of
the PK. I have the typical "most recent occurence" query requirements on this table.

I have already exercised options a) making use of analytical functions such as rank,row_number,first_value,last_value either in the correlated sub-query or in the in-line view b) to make use of the typical max&group by based correlated query and am getting varying response times.

I am currently pursuing INDEX_DESC, FIRST_ROWS hints. All the examples i have seen don't have the main query passing multiple values to the correlated sub-query as Oracle doesn't allow the foll. Do you have alternatives?

SELECT

cola,
colb,
colc,
cold
FROM 	tablea TA 

WHERE
TA.cola IN
('a','b','c','d','e')

AND (colb,colc) =

          (select colb,colc from 
		(
			SELECT	/*+ FIRST_ROWS */ TA1.cola,TA1.colb,TA1.colc				FROM tablea
TA1
			WHERE 
                        TA1.cola=TA.cola and
                        TA1.colb<=SYSDATE and
                        TA1.colb<=SYSDATE and
			ORDER BY TA1.cola DESC,TA1.colb desc,TA1.colc desc
                 ) TAIN
            WHERE ROWNUM<2
            )

• Oracle doesn't allow TA.cola to be referenced in the inline view of the correlated sub-query.

Rgds
Hari Received on Fri Oct 29 2004 - 11:40:39 CDT