[EMAIL PROTECTED] wrote:
> Hi I've got a performance problem , please help me
>
> I have two tables
>
> 1 S - Sales (3'000,000 records )
> with Sales_Country ,Product_id, Sales_AmountUSD , Sales_Quantity
> and Sales_Date
>
> 2 P - Product (77,000 records )
> with Product_id, Product_Country, Product_Desc, Product_
> size, Product_family , Product_ group , Product_xxx
> .................................(manymany fields)
>
> I join both tables with
>
> insert into DESTINATION
> ( select SALES.* , PRODUCT.* {all the fields}
> FROM SALES , PRODUCT
> where SALES.Product_id = Product.Product_id
> and SALES.country = Product.country )
>
> * It is necessary to join the tables
> * I've indexed country and ID on both tables
>* the explain command tell me that i makes a full scan on the product table and a
> indexed seach on the sales one
>
> OWNER TABLENAME COLUMN_OR_INDEX STRATEGY
> > PAGECOUNT
> O D T M
> ADMIN PRODUCT
> TABLE SCAN >
> 2801
> ADMIN SALES ITXTMASTER JOIN VIA
> RANGE OF MULTIPLE INDEXED COL. 36407
> PAPROPAC
> (USED INDEX COLUMN)
> ADMIN
> RESULT IS COPIED , COSTVALUE IS > 25590
>
>
> THE PROBLEM IS THAT THIS TAKES 4 HOURS ON A PENTIUM 4 WITH 1 GB RAM , IS IT NORMAL ?
> HOW CAN I IMPROVE THIS ? ,
> THANKS !
It seems that the explain output doesn't match to your statement because
I couldn't see any column with the name PAPROPAC in those tables.
Anyway, you should try to actualize your optimizer statistics.
If this won't help feel free to send me the output of
explain
select SALES.* , PRODUCT.*
FROM SALES , PRODUCT
where SALES.Product_id = Product.Product_id
and SALES.country = Product.country
and perhaps a vtrace (http://sapdb.2scale.net/moin.cgi/VTrace)
Kind regards,
Holger
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
