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 <sinardyxing_at_bcsis.com> 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: 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).
>
>
> __________________________________________________
> 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: ashraf_salaymeh_at_yahoo.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 Wed Sep 19 2001 - 00:50:20 CDT
