Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2007
    Posts
    5

    Unanswered: Global Temporary Tables

    I've searched over the past week for an answer that will work for this, but all of the documentation I come across on the subject has not worked for one reason or another. So I present myself again before my superiors in request for a solution, so here goes...

    I am developing this in PL/SQL Developer. Oracle version 9i

    I have a user NIWH who's set Temporary Tablespace is 'TEMP'

    I have two package bodies, one for PULL and one for PUSH procedures.

    The following is my current code for a PULL procedure.

    Code:
      PROCEDURE prc_Pull_Shipment( p_CostCenterID                 IN  number,
                                   p_LastUpdated                  IN  date,
                                   p_IsWarehouse                  IN  number,
                                   p_IsFirstRequest               IN  number,
                                   cur_OutCursor_Shipment         OUT T_CURSOR,
                                   cur_OutCursor_ShipmentDetail   OUT T_CURSOR )
      IS
      BEGIN
    
              OPEN cur_OutCursor_Shipment FOR
                  SELECT shipment.GUID             AS ShipmentID,
                         shipment.shipment_number  AS ShipmentNumber,
                         shipment.cost_center_id   AS CostCenterID,
                         shipment.comments         AS Comments,
                         shipment.additional_items AS AdditionalItems,
                         shipment.destination_id   AS DestinationID,
                         shipment.date_received    AS DateReceived,
                         shipment.receipt_comments AS ReceiptComments,
                         shipment.status_id        AS StatusID,
                         shipment.last_updated_on  AS LastUpdatedOn,
                         shipment.last_updated_by  AS LastUpdatedBy,
                         shipment.created_by       AS CreatedBy,
                         shipment.created_on       AS CreatedOn,
                         shipment.shipment_id      AS NaborsID
                  FROM   NIWH_SHIPMENT shipment
                  WHERE  shipment.LAST_UPDATED_AT != p_IsWarehouse
                  AND    (shipment.LAST_UPDATED_ON > p_LastUpdated
                  OR     shipment.last_synchronization > p_LastUpdated)
                  AND    (( p_IsWarehouse = 0 AND shipment.Cost_Center_ID = p_CostCenterID )
                   OR    (P_IsWarehouse = 1 AND shipment.cost_center_id IN ( SELECT wcc.cost_center_id
                                                      FROM  NIWH_WAREHOUSE_COST_CENTER wcc,
                                                            NIWH_Warehouse w
                                                      WHERE wcc.warehouse_id = w.warehouse_ID
                                                       AND  w.Cost_Center_ID = p_CostCenterID )))
    
                  OR EXISTS (SELECT molitemdetail.mol_item_detail_id
                                FROM NIWH_SHIPMENT_DETAIL shipmentdetail,
                                     NIWH_MOL_ITEM_DETAIL molitemdetail,
                                     NIWH_SHIPMENT shipment
                                WHERE  shipmentdetail.LAST_UPDATED_AT != p_IsWarehouse
                                AND    (shipmentdetail.LAST_UPDATED_ON > p_LastUpdated              
                                OR     shipmentdetail.last_synchronization > p_LastUpdated)
                                AND    (( p_IsWarehouse = 0 AND shipment.Cost_Center_ID = p_CostCenterID )
                                 OR    (P_IsWarehouse = 1 AND shipment.cost_center_id IN ( SELECT wcc.cost_center_id
                                                                    FROM  NIWH_WAREHOUSE_COST_CENTER wcc,
                                                                          NIWH_Warehouse w
                                                                    WHERE wcc.warehouse_id = w.warehouse_ID
                                                                     AND  w.Cost_Center_ID = p_CostCenterID ))));
    
    
              OPEN cur_OutCursor_ShipmentDetail FOR
                  SELECT shipmentdetail.GUID                 AS ShipmentDetailID,
                         shipment.GUID                       AS ShipmentID,
                         shipmentdetail.inventory_staging_id AS InventoryStagingID,
                         shipmentdetail.shipment_qty         AS ShipmentQty,
                         shipmentdetail.received_qty         AS ReceivedQty,
                         shipmentdetail.uom_id               AS UOMID,
                         shipmentdetail.status_id            AS StatusID,
                         shipmentdetail.Last_Updated_On      AS LastUpdatedOn,
                         shipmentdetail.Last_Updated_By      AS LastUpdatedBy,
                         shipmentdetail.created_by           AS CreatedBy,
                         shipmentdetail.created_on           AS CreatedOn,
                         shipmentdetail.shipment_detail_id   AS NaborsID,
                         shipmentdetail.returned_qty         AS ReturnedQty,
                         shipmentdetail.Inventory_Staging_ID AS InventoryStagingID,
                         shipmentdetail.shipped_UOM_ID       AS shippedUOMID,
                         molitemdetail.GUID                  AS MolItemDetailID,
                         shipmentdetail.Unit_Cost            AS UnitCost
                  FROM   NIWH_SHIPMENT_DETAIL shipmentdetail
                  JOIN   NIWH_MOL_ITEM_DETAIL molitemdetail
                  ON     molitemdetail.mol_item_detail_ID = shipmentdetail.mol_item_detail_id
                  JOIN   NIWH_SHIPMENT shipment
                  ON     shipmentdetail.SHIPMENT_ID     = shipment.SHIPMENT_ID
                  WHERE  shipmentdetail.LAST_UPDATED_AT != p_IsWarehouse
                  AND    shipmentdetail.LAST_UPDATED_ON > p_LastUpdated
                  AND    (( p_IsWarehouse = 0 AND shipment.Cost_Center_ID = p_CostCenterID )
                   OR    (P_IsWarehouse = 1 AND shipment.cost_center_id IN ( SELECT wcc.cost_center_id
                                                      FROM  NIWH_WAREHOUSE_COST_CENTER wcc,
                                                            NIWH_Warehouse w
                                                      WHERE wcc.warehouse_id = w.warehouse_ID
                                                       AND  w.Cost_Center_ID = p_CostCenterID )));
    
    
    
      END;
    I believe it would be faster to create a global temporary table where I insert
    SHIPMENT_DETAIL_ID and SHIPMENT_ID columns from the NIWH_SHIPMENT_DETAIL table and use the temporary table to speed up the query. However I am challenged as to where in my procedure to place this and if my idea is even correct.

    Everyone in my office is an SQL Server expert, not Oracle, so again, I thank you in advance and sorry if this is a newbie question.
    Last edited by spleen_blender; 06-19-07 at 19:27.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Forgive me, but I am not convinced you are asking the right question.
    You very well may be asking the right questions, but I'd like to step back & review.

    Unlike other RDBMS where the use of temporary tables is typical & common, they are rarely needed in Oracle.
    With Oracle Readers do NOT block Writers & Writers do NOT block readers.

    Please keep in mind we (TINW) are not mind readers. We only know what you post here about your requirements & application.

    Explain what problem the temporary table is supposed to solve and why it is the "best" solution for this problem/requirement.

    What problem are your REALLY trying to solve?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    when you need improve performance, then check your selects in sql+ or in sql window in pl/sql developer.
    your selects have too many OR - causes full table scan
    i dont know your situation, but if your cursors need return small number of records, this can be bottleneck, otherwise not.

    in warehousing is frequently used hint append and nologging option on insert statement
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  4. #4
    Join Date
    Jun 2007
    Posts
    5
    Quote Originally Posted by anacedent
    Forgive me, but I am not convinced you are asking the right question.
    You very well may be asking the right questions, but I'd like to step back & review.

    Unlike other RDBMS where the use of temporary tables is typical & common, they are rarely needed in Oracle.
    With Oracle Readers do NOT block Writers & Writers do NOT block readers.

    Please keep in mind we (TINW) are not mind readers. We only know what you post here about your requirements & application.

    Explain what problem the temporary table is supposed to solve and why it is the "best" solution for this problem/requirement.

    What problem are your REALLY trying to solve?

    Some of the procedures in this package will be querying tables of only a couple hundred entries, others will query tables approaching a million. I used the example posted as one of the simpler procedures where there is the concept of a "header" and a "detail" table that both are returned. The detail table is separate because there are other procedures that call fields from the detail table for a different "header."

    The function in this procedure mentioned returns headers that only have details associated with them, so I wanted to populate a temporary table with only the index and key of the "detail" table and use that with a join to return only the headers for which details exist, where as now I am doing a sub-select. And like I aid, I am a noob when it comes to Oracle and SQL in general (I just started 2 weeks ago), so although this might make sense in my empty skull I hope you guys can give me some parallax on the subject.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I am at a loss as to why you think elapsed time will be reduced by reading any orginal table, populating a temporary table & then querying against this temporary table for the final results set.

    IMO, you seem to fixated on using temporary tables just because that is the way you've done things in the past.

    Another alternative would be to create VIEWs to present the same results set as would be contained in the temporary table(s).

    A shovel is a good tool for create a hole in the ground;
    but only when the correct end comes into contact with the earth.

    From my perspective you are using the wrong end of SQL.

    But it is your hole, & you are free to keep digging if that is what you want to do.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent
    Another alternative would be to create VIEWs to present the same results set as would be contained in the temporary table(s).
    I completely agree.
    Maybe even a MATERIALIZED VIEW that refreshes automatically

  7. #7
    Join Date
    Jun 2007
    Posts
    5
    Oh no, I'm not set on using that necessarily, I was just presenting my thoughts on it and trying to see what a better alternative is. I will try to use your method and I am sure it will be more effective. Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •