Help Joining Large Tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Help Joining Large Tables

  1. #1
    Join Date
    Oct 2000
    Posts
    9

    Lightbulb

    I have several large tables I need to pull data from using a passthrough SQL query in Access. From this I am going to populate an Access table for another application.

    Here are my tables and their fields

    Orders (VERY LARGE TABLE)
    ---------------------------------
    Item
    Revision
    Location
    Order Date
    Quantity
    etc.

    Items (LARGE TABLE)
    --------------------------------
    Item
    Revision
    Description

    Items_CustomFields
    -------------------------
    Item
    Revision
    CustomField
    Vendor
    Vendor_Location
    (Used to determine if the Part is included in this particular report, and value must be in the resultset)

    Locations
    -----------
    Location
    CustomField (Yes/No)
    (Determines if the location is included in the report)


    Vendors
    -----------------------
    Vendor
    Vendor_Loc
    VendorName


    Inventory (VERY LARGE TABLE)
    --------------------
    Item
    Revision
    Location
    QTY_On_Hand


    I have the bulk of the query (using group by to get calculations) combining Orders, Items, Items_CustomFields, and Locations. This report runs within 20-30 minutes depending on server load. However, whenever I try and add either Inventory or Vendors I get the following Error:

    ORA-01652: unable to extend temp segment by 5394 in tablespace SYSTEM

    I have no control over the Oracle database, I can only send passthrough queries to retrieve READONLY data. So giving more temp space on the server is out of the question.

    So, I tried the following technique:

    SELECT
    a.*,
    b.QTY_On_Hand FROM (SELECT ..... [the main query] ...) a, Inventory b
    WHERE
    a.Item=b.Item and a.Revision=b.Revision and a.Location=b.Location
    Order by a.Item;

    Using this technique I receive the following error:

    ORA-01722: invalid number

    I assumed this was because of the * so I specifically typed out all of the a.FIELDNAMES from the main query. But that too did not work stating that there was an invalid field name. I double checked all the spellings of the field names and their respective tables, but none of them are incorrect.

    Can anybody suggest a better way to join these LARGE tables, or to be able to join them at all in this case!!! Any help would be greatly appreciated.

    Thanks in advance

  2. #2
    Join Date
    May 2000
    Location
    Richardson, Texas, USA
    Posts
    39
    The account that accesses the Oracle database has SYSTEM tablespace as the temporary tablespace which is never a good idea. Please ask the database administrator to assign a temporary tablespace other than SYSTEM which is large enough to take care of the sorting associated with the query.

    Regarding the last problem - please paste the exact query and error message so that we can take a look at that. There is definitely something wrong on your side otherwise the query would not fail.

    Thanks.

    Syed

Posting Permissions

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


Click Here to Expand Forum to Full Width