try nested query in place of sort join method... ----- Original Message ----- Date: Wednesday, September 19, 2001 11:10 am > Try to use Index for big table ITEM > To avoid full table scan. > > Create index item_index on item(no); > > This will speed the process... > > --- Sinardy <[EMAIL PROTECTED]> wrote: > > Hi, > > > > I have 2 big tables, ITEM (is about 1 million rows) > > and RTNITEM (is about > > 20K rows) > > > > When I do: > > > > SELECT ITEM.no, > > NVL(SUM(ITEM.CUSTSOLD), 0), > > NVL(SUM(RTNITEM.CUSTRTN) > > > > FROM ITEM, RTNITEM > > > > WHERE ITEM.no=RTNITEM.no > > > > GROUP BY ITEM.no; > > > > > > Time to execute above query is to long. > > > > I tried > > > > CREATE OR REPLACE VIEW proc_view_itemsold AS > > SELECT no, > > NVL(SUM(custsold, 0)) AS sold > > FROM item > > GROUP BY no; > > > > CREATE OR REPLACE VIEW proc_view_itemrtn AS > > SELECT no, > > NVL(SUM(custrtn, 0)) as return > > FROM rtnitem > > GROUP BY no; > > > > SELECT i.no, > > i.sold > > r.return > > FROM proc_view_itemsold, proc_view_itemrtn > > WHERE i.no = r.no; > > > > DROP VIEW proc_view_itemsold; > > DROP VIEW proc_view_itemrtn; > > > > > > The result is the same, it took more than 25 > > minutes. > > > > Do I have to create a temporary tables instead of > > view to prevent these two > > giant tables producing a cardinality product ? > > In this situation is that possible using inner query > > with where clause again > > to prevent those giant tables combined? > > > > > > > > Thank you, > > > > > > Sinardy > > > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: Sinardy > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- (858) 538-5051 FAX: > > (858) 538-5051 > > San Diego, California -- Public Internet > > access / Mailing Lists > > > ------------------------------------------------------------------- > - > > To REMOVE yourself from this mailing list, send an > > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > > ORACLE-L > > (or the name of mailing list you want to be removed > > from). You may > > also send the HELP command for other information > > (like subscribing). > > > __________________________________________________ > Terrorist Attacks on U.S. - How can you help? > Donate cash, emergency relief information > http://dailynews.yahoo.com/fc/US/Emergency_Information/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: ASHRAF SALAYMEH > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > ------------------------------------------------------------------- > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). >
---------------------------------------------------------------------------------------------------------------------- Information transmitted by this E-MAIL is proprietary to Wipro and/or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at mailto:[EMAIL PROTECTED] and delete this mail from your records. ----------------------------------------------------------------------------------------------------------------------
