RE: Used/Free Temporary Space

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Mon, 23 Sep 2002 04:53:22 -0800
Message-ID: <F001.004D660A.20020923045322@fatcity.com>

OLDALH Table has Total 100 Million Rows . Each SQL Run may fetch about 2 Million Rows (for Respective SOL_ID Value) from OLDALH Table

solgam is a Master Table Containing about 5 Million Rows

Thanks

-----Original Message-----
Sent: Friday, September 20, 2002 1:43 AM To: Multiple recipients of list ORACLE-L

Vivek - For the username you are executing this query under, what is the designated temporary tablespace? How many rows (approximately) are you expecting this query to retrieve?

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>

-----Original Message-----
Sent: Thursday, September 19, 2002 1:18 PM To: Multiple recipients of list ORACLE-L

On Issue of Following Query :-

select ollh_acid,decode(oldalh.b2k_type,'ACFFD',oldalh.modify_bod_date , oldalh.another_date)
from oldalh,solgam
where oldalh.acid=solgam.acid
and solgam.sol_id = CONSTANT

NOTE - NO Index Exists
acid has a Unique Value in Every Record of solgam Table sol_id is a Value for a Set of solgam.acid Values in solgam Table

Error :-

ORA-01652 "unable to extend temp segment by %s in tablespace %s"

NOTE - TEMPORARY Tablespace of Size 11 GB

Size of OLDALH Table = 6 GB
Size of solgam = 500 MB

Qs Should the Above Query Used Temporary Space ? if so , Why ?

Qs From which View (How) can we find the Total Temporary Space Used OR Free in Temporary Tablespace

Qs Will Creating an Index on oldalh.acid Reduce Usage of Temporary Space ?

Qs What index Creation is Advisable on solgam Table ?

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: VIVEK_SHARMA
INET: VIVEK_SHARMA_at_infosys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Sep 23 2002 - 07:53:22 CDT