Home » SQL & PL/SQL » SQL & PL/SQL » Test query for SORT (merged) (12c)
Test query for SORT (merged) [message #644249] Mon, 02 November 2015 00:57 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member

Hi , I am doing some test on PGA and TEMP tablespace usage .

Can someone give me a test script for a nasty sort using dba_object table ? I want blew up 2G of PGA and spill over to temporary tablespace Smile


Thank you
Re: Test query for SORT [message #644255 is a reply to message #644249] Mon, 02 November 2015 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just cross join dba_objects with itself and you will have a set of more than 5,000,000,000 rows.

[Updated on: Mon, 02 November 2015 01:31]

Report message to a moderator

Re: Test query for SORT [message #644291 is a reply to message #644255] Mon, 02 November 2015 13:07 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Mike, Tried that already . MERGE CARTITION is not a SORT . It isnt taking much PGA or TEMP though it runs for ever .

Some GROUP BY , ORDER would be helpful ..But what ever I tried comes back in few seconds :/

Thanks
Re: Test query for SORT [message #644292 is a reply to message #644291] Mon, 02 November 2015 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Who prevent you from adding an ORDER BY clause to the query?

Re: Test query for SORT [message #644293 is a reply to message #644292] Mon, 02 November 2015 13:25 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
MERGE CARTITION runs for ever so SORT happens sometime in the future(not sure when) . I was looking for some help to make a query that just do SORT SORT nothing else , runs for sometime and generate 2G+ of data that can fills up my PGA .
Re: Test query for SORT [message #644294 is a reply to message #644293] Mon, 02 November 2015 13:29 Go to previous message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you want big sort, you have to first fetch many rows which will indeed takes long time.

Note: "MERGE CARTITION" is something that does not exist.

Previous Topic: i want to upload a file in database using plsql
Next Topic: stored procedure to Inserting new rows and updating existing rows
Goto Forum:
  


Current Time: Tue Mar 19 05:28:53 CDT 2024