Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Posts
    1

    Unanswered: DB temp space in informix.

    Hi,

    i am creating a employeeTmp in which i am using the order by clause...

    SELECT p.*,a.action from audit_activity a, employee p where a.time_stamp >= to_date('11232009 00:00:00', '%m%d%Y %T') and a.table_name = 'employee' and trunc(a.pkey_value_1+0,0) = p.loc_id and a.pkey_name_1 = 'loc_id' order by p.loc_id into temp employeeTmp with no log;

    and in other part of the code i will using this query.

    what is happening is ..because of the order by clause it is consuming lot of DB temp space and it is failing.

    can anyone pls let me know whether there will be any difference in the DB temp space usage when we use the order by clause and with out using order by clause( taking into acccount i do not need the ordering of the particular column).

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    When you use an ORDER BY, the database must re-sort all data and for this they create a new temporary table.
    If you have problem with space on your DBSPACETEMP and don't wanna grow up them, try use the variables: PSORT_DBTEMP (to use a file system area) and PSORT_NPROCS (to speed up your sorts)
    PSORT_DBTEMP
    PSORT_NPROCS
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    I forgot...
    If this temp table have a lot of requests, you can create indexes for it.
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

Posting Permissions

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