Vivek - The first check is to ensure the username you are running the query
under has the correct temporary tablespace defined. This is not the default
in Oracle and is easy to overlook.
Once you've eliminated this, if you still have the error, you should run
EXPLAIN PLAN on your query and post the results here.
Yes, indexes often cure performance problems for all sorts of query
issues. I can't say definitely in your situation without more information.
�
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
-----Original Message-----
Sent: Monday, September 23, 2002 7:53 AM
To: Multiple recipients of list ORACLE-L
Hi
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.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
-----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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
