hi
try this

you can remove third table because it is not using

SELECT suc.spo_id,euc.DESCRIPTION euc_description bulk collect
INTO tbl_spo_id_euc, tbl_euc_dec
FROM END_USER_CATEGORY euc,
SUPPLY_POINT_EUC suc
WHERE suc.euc_number = euc.euc_number
AND suc.EUC_EFFECTIVE_DATE = (SELECT MAX(euc_effective_Date) FROM
SUPPLY_POINT_EUC WHERE
euc_effective_date<= C_REFRESH_TO_DATE
AND spo_id = suc.spo_id)
AND euc.effective_date = suc.euc_effective_date;


dhanya

-----Original Message-----
From: sanjay
[mailto:oracledba-ezmlmshield-x58471623.[Email address protected]
Sent: Wednesday, August 18, 2004 10:00 AM
To: LazyDBA Discussion
Subject: RE: Query Performance -- requires urgent inputs



See the query

SELECT suc.spo_id,euc.DESCRIPTION euc_description bulk collect INTO
tbl_spo_id_euc,
tbl_euc_dec
FROM END_USER_CATEGORY euc,
SUPPLY_POINT_EUC suc,
TMP_SPO_ID
WHERE suc.spo_id = TMP_SPO_ID.spo_id
AND suc.euc_number = euc.euc_number
AND suc.EUC_EFFECTIVE_DATE = (SELECT MAX(euc_effective_Date) FROM
SUPPLY_POINT_EUC WHERE euc_effective_date
<= C_REFRESH_TO_DATE AND spo_id = suc.spo_id)
AND euc.effective_date = suc.euc_effective_date;

Here from SUPPLY_POINT_EUC table I need to pick one record for the max
(euc_effective_Date) and for this record I need to
pick other column values... As I have used correlated subquery... the
Process Simply hangs in a smaller table size ( around 300K records it works
but as I move up does not do any thing..
What I want to know is...
Any one I can go to the exact record on this table which matches this
criteria... I have tried with Dense_rank()
over ( partition spo_id order by euc_effective_date ) and then setting the
value to 1
but no gain in performance



-----Original Message-----
From: Dhanya
[mailto:oracledba-ezmlmshield-x61363820.[Email address protected]
Sent: Wednesday, August 18, 2004 9:47 AM
To: LazyDBA Discussion
Subject: RE: Query Performance -- requires urgent inputs


hi Sanjay

actually wht is your requirement

whether to find record where the max date for a group number is <=against a
supplied date
or to find max date which is less than a supplied date

select grp_id,max(date) from ..
group by grp_id
having max(date)<given date


any way give your query then only we can give better solution


dhanya

-----Original Message-----
From: sanjay
[mailto:oracledba-ezmlmshield-x94453641.[Email address protected]
Sent: Wednesday, August 18, 2004 8:54 AM
To: LazyDBA Discussion
Subject: Query Performance -- requires urgent inputs



Hi,
Kindly provide some inputs on this situation
Table:
grp_id e_number date
1 123 12-10-2003
1 124 11-10-2003
1 134 10-10-2004
2 11 11-11-2002
2 14 12-11-2003


grp_id,e_number,date is primary key

This table contains around 170 Million recs... I need to pick record where
the max date for a group number is <=against a supplied date
this record needs to be joined with another table on the basis of grp_id..
If I use a correlated query the process simply hangs... Is there any better
way of doing it..
Pls give your valuable inputs..

Thanks and Regards



Confidentiality Notice

The information contained in this electronic message and any attachments to
this message are intended
for the exclusive use of the addressee(s) and may contain confidential or
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or
[Email address protected] immediately
and destroy all copies of this message and any attachments.


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html





Confidentiality Notice

The information contained in this electronic message and any attachments to
this message are intended
for the exclusive use of the addressee(s) and may contain confidential or
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or
[Email address protected] immediately
and destroy all copies of this message and any attachments.


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html


Oracle LazyDBA home page