Archives of the TeradataForum

Message Posted: Tue, 15 Oct 2002 @ 20:36:29 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

The select from the following query runs in 2 MINUTES. The Insert for this same query, gets stuck in step 6 of the Explain (MERGE into table). The destination table is EMPTY. So, WHY? Anyone have similar problems? (BTEQ 06.00.00.00, Teradata RDBMS V2R4.1.1)

QUERY:
insert into InventoryDaraWork
(DayID, StoreNumber, ProductID, UnitsOnHand,
 LastAuditDate, LastPurchaseDate,
 AVerageCost, AverageSalesPerDay,
 EstablishDate, EstablishID, EstablishProgram,
 UpdateDate, UpdateID, UPdateProgram)
select I.DayID, I.StoreNumber, I.ProductID, I.UnitsOnHand,
       I.LastAuditDate, I.LastPurchaseDate, I.AverageCost,
       I.AverageSalesPerDay, I.EstablishDate,
       I.EstablishID, I.EstablishProgram,
       Date, User, 'RadInventoryDEL.bteq'
from InventoryDara I
where exists (select * from InvLastStoreVTemp V
               where I.StoreNumber = V.StoreNumber
                 and I.DayID = V.DayID)
  and I.DayID > (Add_Months(Date - Extract(Day from Date)+1, -37));

EXPLAIN:
-------------------------------------------------------------------------

1)   First, lock [DBId=0x03EE]."pseudo table" for write on a row hash.

2)   Next, we lock [DBId=0x03EE]."pseudo table" for read on a row hash.

3)   We lock [DBId=0x03EE].[TBId=0x1767] for write and we lock
     [DBId=0x03EE].[TBId=0x1766] for read.

4)   We do an All-AMPs RETRIEVE step from Spool 83 by way of an all-rows
     scan into Spool 90, which is redistributed by hash code to all AMPs.

5)   We do an All-AMPs JOIN step from [DBId=0x03EE].[TBId=0x1766] by
     way of an all-rows scan, which is joined to Spool 90. table
     [TBId=0x1766] and Spool 90 are joined using an inclusion merge join .
     The result goes into Spool 89, which is built locally on the AMPs.

6)   We do a MERGE into table [TBId=0x1767] from Spool 89.

7)   We Spoil the parser's dictionary cache for the table.

8)   We send out an END TRANSACTION step to all AMPs involved in
     processing the request.

TABLE:
CREATE SET TABLE SSA.inventorydarawork ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      DayID DATE FORMAT 'YYYYMMDD' NOT NULL,
      StoreNumber INTEGER NOT NULL,
      ProductID VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      UnitsOnHand INTEGER,
      LastAuditDate DATE FORMAT 'YYYYMMDD' DEFAULT NULL ,
      LastPurchaseDate DATE FORMAT 'YYYYMMDD' DEFAULT NULL ,
      LastSaleDate DATE FORMAT 'YYYYMMDD' DEFAULT NULL ,
      AverageCost DECIMAL(9,4),
      AverageSalesPerDay DECIMAL(11,2),
      EstablishDate DATE FORMAT 'YYYY-MM-DD' TITLE 'Establish Date' DEFAULT DATE ,
      EstablishID VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Establish ID',
      EstablishProgram VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Establish Program',
      UpdateDate DATE FORMAT 'YYYY-MM-DD' TITLE 'Update Date' DEFAULT DATE ,
      UpdateID VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Update ID',
      UpdateProgram VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Update Program')
PRIMARY INDEX PKInventory ( DayID ,StoreNumber );

Thanks for any help!
Dara Tressler
Speedway SuperAmerica LLC

	<Prev	Next>		<<First	<Prev	Next>	Last>>