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).
