Sqlplus tunning

From: Sinardy <sinardyxing_at_bcsis.com>
Date: Tue, 18 Sep 2001 19:11:21 -0700
Message-ID: <F001.0039220C.20010918184017@fatcity.com>

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_at_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_at_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). Received on Tue Sep 18 2001 - 21:11:21 CDT