RE: Sqlplus tunning

Doh!.

I guess I should have looked at what the table names suggest the restriction is.

Sorry!

Iain Nicoll

-----Original Message-----
Sent: 19 September 2001 18:30
To: 'ORACLE-L_at_fatcity.com'

I thought that hash_join was supposed to be best where you had one table much smaller than the other. As there are no restrictions on the data being brought back what is wrong with doing a full table scan of each?.

-----Original Message-----
Sent: 19 September 2001 07:25
To: Multiple recipients of list ORACLE-L

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: iain.nicoll_at_calanais.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).