Query Behaviour

[Altavista]

I have a select statement which makes use of analytic
functions. If i execute this query the response time
is 20ms. However if i apply an outer Select to this
Result set the response time is 6 minutes.

My inner subquery is as below .
This executes in 20 milliseconds.
ResultSet=
SELECT '2' AS DATA_CD, id.ISSUE_SYM_ID AS ISSUE_SYM_ID,
eft.EVENT_FL_TYPE_NM AS EVENT_FL_TYPE_NM,
eft.EVENT_FL_TYPE_ID AS EVENT_FL_TYPE_ID,
COUNT(*) OVER(PARTITION BY id.ISSUE_SYM_ID,eft.EVENT_FL_TYPE_NM) AS INSTC_CT,
MAX(ef.RANK) OVER(PARTITION BY id.ISSUE_SYM_ID, eft.EVENT_FL_TYPE_NM, ns.NEWS_STORY_ID) AS INSTC_ID,
ws.WIRE_SRC_TX AS NEWS_SRC_NM,
MIN(nsner.NEWS_RFRNC_ID) OVER(PARTITION BY id.ISSUE_SYM_ID,eft.EVENT_FL_TYPE_NM,ns.NEWS_STORY_ID) AS NEWS_RFRNC_ID,
DECODE(nsner.NEWS_RFRNC_ID,MIN(nsner.NEWS_RFRNC_ID) OVER(PARTITION BY id.ISSUE_SYM_ID, eft.EVENT_FL_TYPE_NM, ns.NEWS_STORY_ID),nsner.SNTNC_NT,TO_CHAR(NULL)) AS SNTNC_NT,
ns.STORY_HDLN_TX AS STORY_HDLN_TX,
ns.STORY_TS AS STORY_TS
FROM
EVENT_FL_INSTC_DAILY ef,
EVENT_FL_RULE efr,
EVENT_FL_TYPE eft,
ISSUE_DETAIL id,
NEWS_STORY_NEWS_EVENT_REFERENC nsner,
NEWS_STORY ns,
WIRE_SRC ws
WHERE
ef.ISSUE_ID = 70073987 AND
ef.trd_dt BETWEEN '31-DEC-2002' AND '31-DEC-2002'AND efr.EVENT_FL_RULE_ID = ef.EVENT_FL_RULE_ID AND
eft.EVENT_FL_TYPE_ID = efr.EVENT_FL_TYPE_ID AND
id.ISSUE_ID = ef.ISSUE_ID AND
id.EFCTV_DT <= ef.TRD_DT AND
id.XPRTN_DT > ef.TRD_DT AND
nsner.NEWS_RFRNC_ID= ef.NEWS_RFRNC_ID AND
ns.NEWS_STORY_ID= nsner.NEWS_STORY_ID AND
ws.WIRE_SRC_CD= ns.WIRE_SRC_CD


However if i apply a Select * from (Result Set)then
it takes 6 minutes...What could be the probable reason
for this behaviour.


Any help would be greatly appreciated.

 

[Turkbear]

Have you run an Explain Plan on it?..

What results?

 

[Altavista]

Thanks for your prompt response.
Getting hang of the problem as the execution plan is
different.


Thanks once again.