From: vaibhav singh <vaibhav19solo@yahoo.com>
To: boson_oracle_dba@groups.yahoo.com
To: ocpfree@groups.yahoo.com
To: ocpfree2@groups.yahoo.com
To: oracle-metalink@groups.yahoo.com
To: oracledevelopment@groups.yahoo.com
Cc: Cert_OCP_Kangs@groups.yahoo.com
Cc: oca_dev@groups.yahoo.com
Cc: OCP-developertrack@groups.yahoo.com
Cc: OCP9i-DBA@groups.yahoo.com
Cc: ocp_dba_9i@groups.yahoo.com
Cc: OCP_Study@groups.yahoo.com
Cc: ocpfree2003@groups.yahoo.com
Cc: oracledevelopers@groups.yahoo.com
Subject: [ocpfree] Query Optimization Help Needed.
Date: Tue, 13 Jul 2004 04:40:20 -0700 (PDT)
Message-ID: <20040713114020.61706.qmail@web52906.mail.yahoo.com> (raw)
Hi all,
I need ur help regarding the optimization of following
query. If possible pls reply asap.
Thankz a ton in advance.
SELECT company_name,coupon_id,coupon_title, city_name,
category_name, NVL(person_name,' ') person_name,
NVL (locality_name,' ') locality_name,
NVL(company_address,' ') company_address,
NVL(mobile_no,' ') mob,
NVL(company_phone1,' ') ph1,
NVL(company_phone2,' ') ph2,
psid_1,discount1,
NVL(psid_2,-1) p2,
NVL(discount2,-1) d2,
NVL(psid_3,-1) p3,
NVL(discount3,-1) d3,
TO_CHAR(expiry_date,'dd-mm-yyyy')expdate,
REDEMPTION_METHOD, PICTURE_FILE_NAME,
NVL(company_info,' ') company_info
FROM TBL_MCP_COUPON a, TBL_MCP_LOCALITY, TBL_MCP_CITY,
TBL_MCP_CATEGORY
WHERE (verify='Y' OR verify='R')
AND ROWNUM <= 10 AND (SYSDATE <= EXPIRY_DATE)
AND a.city_id=4
AND a.category_id=10
AND a.category_id=TBL_MCP_CATEGORY.category_id
AND a.locality_id=TBL_MCP_LOCALITY.locality_id
AND a.city_id=TBL_MCP_CITY.city_id
--check for with and without following two lines ,
becouse these lines will be included in query
conditionally
--AND a.locality_id=25
--AND (psid_1=113 OR psid_2=113 OR psid_3=113)
AND a.coupon_id NOT IN
( SELECT a.coupon_id FROM
TBL_MCP_USERCOUPONS b,TBL_MCP_COUPON a
WHERE a.coupon_id=b.coupon_id AND a.REDEMPTION_METHOD
= 1 AND b.MIN= 1234567891)
ORDER BY LOWER(company_name);
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
------------------------ Yahoo! Groups Sponsor --------------------~-->
Yahoo! Domains - Claim yours for only $14.70
http://us.click.yahoo.com/Z1wmxD/DREIAA/yQLSAA/o7folB/TM
--------------------------------------------------------------------~->
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ocpfree/
<*> To unsubscribe from this group, send an email to:
ocpfree-unsubscribe@yahoogroups.com
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: vaibhav19solo@yahoo.com, boson_oracle_dba@groups.yahoo.com, ocpfree@groups.yahoo.com, ocpfree2@groups.yahoo.com, oracle-metalink@groups.yahoo.com, oracledevelopment@groups.yahoo.com, Cert_OCP_Kangs@groups.yahoo.com, oca_dev@groups.yahoo.com, OCP-developertrack@groups.yahoo.com, OCP9i-DBA@groups.yahoo.com, ocp_dba_9i@groups.yahoo.com, OCP_Study@groups.yahoo.com, ocpfree2003@groups.yahoo.com, oracledevelopers@groups.yahoo.com
Subject: Re: [ocpfree] Query Optimization Help Needed.
In-Reply-To: <20040713114020.61706.qmail@web52906.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox