June 25, 2009 at 12:31 pm
I have 1 million rows in a table and got 20 indexes on it but now i came to know that i need to remove 90% of the rows from the table can i know whats the best way to do is .....its a bit urgent
i thought of moving that 10% rows to other table and rename it but he said u cant get the indexes same name and thats not the right way can you please help me out
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
June 25, 2009 at 12:48 pm
Unless there is a requirement that the index names stay the same (Application Supported indexes like in PeopleSoft, used in index hints in queries) there is no real reason that the indexes need to have the same name in the new table. If they do, there is an alternative.
1) Script the table CREATE without indexes, call it table_new
2) Script the DROP INDEX statements on table
3) Script the indexes as CREATE INDEX on table (keep the same names, etc)
4) Create table_new with no indexes
5) Move the 10% of the rows that need to be kept from table to table_new
6) Drop indexes on table using the scripts from #2
7) Rename table to table_old
8) Rename table_new to table
9) Create the indexes using the scripts from #3
June 25, 2009 at 4:09 pm
Do you have any special requirements? Such as...
- Are you trying to save the 90 percent of the rows you need to get rid of?
- Do you need to keep the system up while you are doing this?
- Does this have to get done in a specific time frame?
If not, why not just...
- Backup the database -- just in case.
- Get everyone out of the system
- Script the indexes (as create) on the existing table
- Drop all indexes on that table
- Delete the rows that you don't want in there
- Add the indexes back
June 26, 2009 at 12:21 am
DBA with OCD (6/25/2009)
Do you have any special requirements? Such as...- Are you trying to save the 90 percent of the rows you need to get rid of?
- Do you need to keep the system up while you are doing this?
- Does this have to get done in a specific time frame?
If not, why not just...
- Backup the database -- just in case.
- Get everyone out of the system
- Script the indexes (as create) on the existing table
- Drop all indexes on that table
- Delete the rows that you don't want in there
- Add the indexes back
Time consuming. Also, you are deleting 90% (900,000) of 1,000,000 rows of data. This has the potential to greatly increase the size of the transaction log file. You only want to keep 10% of the data. If you can handle a short period of down time, creating a new table, moving the data to be kept, then "swapping" table names will be faster. Plus, you can retain the old table for a period of time in case someone comes back and needs some of the data that was "deleted".
June 26, 2009 at 12:38 am
Gotch u Thank You Guys...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
June 26, 2009 at 1:34 am
Maybe something like this?
SELECT * INTO #temp FROM dbo.SourceTable WHERE
TRUNCATE TABLE dbo.SourceTable
INSERT INTO dbo.SourceTable SELECT * FROM #temp
Best Regards,
Chris Büttner
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply