OraFAQ Forum: Performance Tuning » Simple Query on Table with Billions of rows getting ORA-01652

Home » RDBMS Server » Performance Tuning » Simple Query on Table with Billions of rows getting ORA-01652 (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Simple Query on Table with Billions of rows getting ORA-01652 [message #690044]

Wed, 25 September 2024 13:27

wtolentino
Messages: 422
Registered: March 2005

Senior Member

A simple query on a table with billions of rows is getting an error:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

TEMP tablespace has 3 datafile and each has a maxsize of 32003mb. Currently no other session is active or is connected to the same database.

This query completes without error:

Code: [Select all] [Show/ hide]

select count(*) cnt from TRANSACTION_tab;

Please advise thank you.

This query throws an error ORA-01652:

Code: [Select all] [Show/ hide]

select * from TRANSACTION_tab describe trans_dt desc;

Report message to a moderator

Re: Simple Query on Table with Billions of rows getting ORA-01652 [message #690045 is a reply to message #690044]

Wed, 25 September 2024 13:54

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Your count(*) query can probably run by scanning an index. THe select * query will have to look at the table. It is the usual thing we all (should) know: get the execution plans to see what is happening.

Report message to a moderator

Re: Simple Query on Table with Billions of rows getting ORA-01652 [message #690046 is a reply to message #690044]

Wed, 25 September 2024 14:00

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

COUNT(*) just requires to count rows one by one, no need of (temp) space.

SELECT * ... ORDER BY ... requires to store all rows (or index values and rowid) in temp space to then sort them.

As John said, see the execution plans to know what is done.

Report message to a moderator

Re: Simple Query on Table with Billions of rows getting ORA-01652 [message #690047 is a reply to message #690046]

Wed, 25 September 2024 21:01

wtolentino
Messages: 422
Registered: March 2005

Senior Member

here's the explain plan.

Code: [Select all] [Show/ hide]

explain plan for select count(*) cnt from TRANSACTION_tab;
Plan hash value: 3371900491
 
------------------------------------------------------------------------------------------
| Id  | Operation             | Name                     | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                          |     1 |  1479K  (1)| 00:00:58 |
|   1 |  SORT AGGREGATE       |                          |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| TRANSACTION_TAB_PK       |  1290M|  1479K  (1)| 00:00:58 |
------------------------------------------------------------------------------------------

Code: [Select all] [Show/ hide]

explain plan for select * from TRANSACTION_tab order by trans_dt desc;
Plan hash value: 2017279614
 
----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                       |  1290M|   103G|       |    39M  (1)| 00:25:34 |
|   1 |  SORT ORDER BY     |                       |  1290M|   103G|   161G|    39M  (1)| 00:25:34 |
|   2 |   TABLE ACCESS FULL| TRANSACTION_TAB       |  1290M|   103G|       |  4562K  (1)| 00:02:59 |
----------------------------------------------------------------------------------------------------

note: not the actual table name

Report message to a moderator

Re: Simple Query on Table with Billions of rows getting ORA-01652 [message #690048 is a reply to message #690047]

Thu, 26 September 2024 00:47

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So you have your answer.

Report message to a moderator

Re: Simple Query on Table with Billions of rows getting ORA-01652 [message #690049 is a reply to message #690048]

Thu, 26 September 2024 06:36

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

To ask the obvious question - what is the point of ordering 1.29 billion rows?

Report message to a moderator

Re: Simple Query on Table with Billions of rows getting ORA-01652 [message #690050 is a reply to message #690049]

Thu, 26 September 2024 07:59

wtolentino
Messages: 422
Registered: March 2005

Senior Member

The order by is to get the latest rows.

Report message to a moderator

Re: Simple Query on Table with Billions of rows getting ORA-01652 [message #690051 is a reply to message #690050]

Thu, 26 September 2024 09:05

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you want only some rows have a look at row limiting clause (after ORDER BY one), for instance:
FETCH FIRST 100 ROWS ONLY

Report message to a moderator

Re: Simple Query on Table with Billions of rows getting ORA-01652 [message #690052 is a reply to message #690050]

Thu, 26 September 2024 09:12

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

if you want the latest rows why are getting all the rows?

If there's an index on trans_dt then oracle can process

Code: [Select all] [Show/ hide]

select * from (select * from TRANSACTION_tab order by trans_dt desc) 
where rownum < [whatever]

really fast, because it can ignore most of the data in the table.

Report message to a moderator

Re: Simple Query on Table with Billions of rows getting ORA-01652 [message #690069 is a reply to message #690052]

Wed, 02 October 2024 08:23

wtolentino
Messages: 422
Registered: March 2005

Senior Member

Right I can use FETCH FIRST 100 ROWS ONLY or rownum < ....

It just because I'm used to do select * from table order by .... to write the query quick.

Report message to a moderator

Re: Simple Query on Table with Billions of rows getting ORA-01652 [message #690070 is a reply to message #690069]

Wed, 02 October 2024 10:24

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

quick and dirty selects are fine on dev systems with limited data, but when you've got a system with billions of rows you need to think about the implications of every single query you write.

Report message to a moderator

Previous Topic:	Parallel Degree Limit correct num to use on Parallel Hints
Next Topic:	Create partition Index on Non-partition column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 07:57:21 CDT 2025