Performance of a query 1

[dps]

Hello,

I have attempted to carry out a delete statement - it seemed to be taking forever, so I had to cancel it.

The select statement

select count(*)
from s_number_states
where nrs_sty_code in('J', 'C')

gives

COUNT(*)
---------
2,331,408

May present rollback segments handling it and cannot simply extend RBS (backup issues).


"How can I overcome the rollback issue"? or "How can I modify my delete script to improve performance"?

Also what can I use to check the performance of the sql select statement?

Your help gratly appreciated

 

[solara]

Hi,

What you should realise is that delete will always take longer time and it use a lot of resouces.

Tell your DBA to make avaliable a new Huge rollback seg that will handle what your are doing. Also ask for the rollback tablespace
intial extent and next extent to be eg 100MB max unlimited; the same size for the rollback seg.

after creating the Huge rollback , make sure you set transaction to use the newly created rollback seg.

 

[angiole]

Are you deleteing 2.5 millions of data?
If this is the entire table, then TRUNCATE TABLE x would work faster (no reovery available except for a backup).

If you are keeping a small percentage of your data, offload it to a temp table, then Truncate the original, and re-insert temp table data.

As was mentioned by solara, deletes are not noted for their performance.

 

[solara]

angiole,

Read the sql statement first.

 

[dps]

NO DBA refuses to do that.

Other option may be to do a select statemnt to copy all rows with nrs_sty_code NOT IN J and c into a temp table then truncate the table. Then rename the temp table back to original?????? How about that? Have you any other option???

 

[dps]

Gosh you guys are quick - I was expecting my response to be placed in 3rd position.

Yes so you agree with me - offload it to a temp table, then Truncate the original, and re-insert (or rename) temp table data? No other feasible option?

ps. 2.3 million unwanted data 100,000 wanted

 

[Turkbear]

Hi,
If you DBA will not perform a standard part of most job duties for that position ( that is, provide, for one time use, access to a large rollback segment ) so that you can set your transaction to use that rollback , then do :

create table temp_data as
select * from from s_number_states
where nrs_sty_code in

(put your selection for the data you want to keep here, much faster than a NOT IN statement)

Then truncate your original table,and

insert into s_number_states select * from temp_data;

This method will retain all the permissions, indexes, etc on your s_number_states table;dropping it will lose them..

hth,

 

[spenglerr]

yes turkbear, especially the constraints will be kept and this is quite important.
This is the normal way to do it then.

A good tip is valid a star.

 

[sem]

You may create materialized view on the required data. Of course this will consume some disk space but save ALL your data untouched though speeding up appropriate queries.

 

[dps]

Yes Thank you for your suggestions and it has been carried out using the method suggested by turkbear and previous.