From sqlgreg@pacbell.net Wed, 19 Sep 2001 03:40:24 -0700 From: Greg Moore Date: Wed, 19 Sep 2001 03:40:24 -0700 Subject: Re: Sqlplus tunning Message-ID: MIME-Version: 1.0 Content-Type: text/plain Create a single index (index_a) on the two columns in ITEM (no, custsold). Create a second index (index_b on two columns in RTNITEM (no, custrtn). See how the query does once these indexes are created. Then try adding these hints to see if it's any faster: select /*+ index_ffs(i, index_a) index_ffs(r, index_b) */ item.no ... ... from ITEM i, RTNITEM r ... ... Without the indexes you are forcing Oracle to do a full table scan, which is why the query is taking so long. In SQL*Plus, use SET AUTOTRACE ON so you can see the plan and confirm this. With these indexes you will see steps in the plan for the indexes that read, either INDEX (FULL SCAN) or INDEX (FAST FULL SCAN). If your goal is to have the first rows of the query pop up quickly for the user, you want a FULL SCAN of the indexes. If your goal is to have the entire query complete in the shortest amount of time, you want a FAST FULL SCAN of the indexes (which is what the above hints are designed to make happen). Use autotrace and if it still isn't working as fast as you want, copy the explain plan and post it to this list. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" Sent: Tuesday, September 18, 2001 7:40 PM > 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: sinardyxing@bcsis.com > > 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: ListGuru@fatcity.com (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: sqlgreg@pacbell.net 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: ListGuru@fatcity.com (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).