2 Replies Latest reply on May 10, 2013 8:18 PM by damorgan

    sql storage

    972227
      Hello Guys, when ever a sql is executed it uses the temporary tablespace storage to do the sorting right?
      very often we face the problem that the temp segment is unable to extend because the query needs more space to do the sorting.
      My question is, is there any method to get an estimate on how much storage a query needs to execute. can the information of explain plan in the filed of "BYTES" or size of the table being used in the query be of any help to determine how much storage the query needs to do the sorting in the temporary tablespace?
        • 1. Re: sql storage
          L-MachineGun
          969224 wrote:
          Hello Guys, . . . E t c . . .
          . . . any help to determine how much storage the query needs to do the sorting in the temporary tablespace?
          That information would be useless because you do not know at any given time what will be executing and also using the temp tablespace.

          :p
          1 person found this helpful
          • 2. Re: sql storage
            damorgan
            when ever a sql is executed it uses the temporary tablespace storage to do the sorting right? 
            Wrong. It only uses temp if it is unable to perform the sort in memory.

            You can not estimate, in advance, the swap space required but you can write better queries, create sorted hash clusters, or use the right indexing, to minimize the number of sorts and the size of what is being sorted.

            Without a version number, DML, and Explain Plans it is impossible to give anything other than very general advice ... but suffice it to say your basic assumption is incorrect.
            1 person found this helpful