Home » SQL & PL/SQL » SQL & PL/SQL » Slow Output of Oracle
Slow Output of Oracle [message #205574] Sun, 26 November 2006 06:18 Go to next message
balajid
Messages: 3
Registered: November 2006
Location: India
Junior Member
Guys,

I am using a table called VGI with 8,00,000 records. I have written a query to find out unwanted records & the result is all records. So I deleted all of them. But when I give the query "Select * from VGI" it takes more than a minute to respond with "No rows selected" message.

What could be the reason?

Any help is greatly appreciated.

Regards,
Bala
Re: Slow Output of Oracle [message #205575 is a reply to message #205574] Sun, 26 November 2006 06:25 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Search the board for HWM (highwater mark).
You need to truncate. not delete.
Re: Slow Output of Oracle [message #205578 is a reply to message #205574] Sun, 26 November 2006 07:01 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi


sql>truncate table '&tablename' drop storage;




********************************************

SQL> set timing on
SQL> select count(*) from big_table;

  COUNT(*)
----------
    800000

Elapsed: 00:00:02.34
SQL> delete big_table;

800000 rows deleted.

Elapsed: 00:14:15.22
SQL> select count(*) from big_table;

  COUNT(*)
----------
         0

Elapsed: 00:00:02.39
SQL> truncate table big_table drop storage;

Table truncated.

Elapsed: 00:00:01.73
SQL> select count(*) from big_table;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.14
SQL>



hope this helps
Taj.


[Updated on: Sun, 26 November 2006 07:13]

Report message to a moderator

Re: Slow Output of Oracle [message #205623 is a reply to message #205578] Sun, 26 November 2006 21:07 Go to previous messageGo to next message
balajid
Messages: 3
Registered: November 2006
Location: India
Junior Member
hi,

Thanks for your response.

I can not truncate the table because I am not sure whether all the records need to be deleted or not. There is one more query which decides the records to be deleted. In the last case, all the records became candidate. But it may not be the case always.

What is the reason for the slow performance?

Thanks
Re: Slow Output of Oracle [message #205629 is a reply to message #205623] Sun, 26 November 2006 22:10 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi bala

if you cann't truncate ur table then you have to another choice

you can use import and export or alter table ... move statement to reset HVM.


hope this helps
Taj
Re: Slow Output of Oracle [message #205687 is a reply to message #205623] Mon, 27 November 2006 02:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The reason is that Oracle keeps a record of the highest block used in the table. This is the mark that full table scans need to scan up to.
Deletes do not affect the high water mark, but truncates do.
Thus, after deleting all the records, a full table scan will still read all the blocks up to the high water mark looking for data.

It is easier to re-organise a table than to export and re-import it.
ALTER TABLE <table_name> MOVE <tablespace>
where <tablespace> is the tablespace that the table is currently in. This will rebuild the table in it's current tablespace and lower the high watermark. You'll need to rebuild the indexes too, but it's still quicker than export/import
Re: Slow Output of Oracle [message #205842 is a reply to message #205687] Mon, 27 November 2006 21:14 Go to previous message
balajid
Messages: 3
Registered: November 2006
Location: India
Junior Member
Hi,

Thanks a lot for couple of solutions / workarounds. Yes, they are really helpful.

My sincere thanks once again.

Previous Topic: Oracle Advanced Message Service - JMS - OpenJMS
Next Topic: Count and place of occurence of a string
Goto Forum:
  


Current Time: Wed Dec 07 08:29:29 CST 2016

Total time taken to generate the page: 0.09706 seconds