Dear all,
You are right ...
This approach is real.
The business operation data value, the method for keeping or removing data,
the opportunity to generate a "dirty repository" and not use it more ...
All things are valuable in this scenario.
All of your remarks are pertinent and for sure the business team's decision
will lead to the best technical solution I am looking for.
Thanks very much
Eriovaldo
Em seg., 3 de jan. de 2022 às 17:55, Mark W. Farnham <mwf@xxxxxxxx>
escreveu:
One other thing comes to mind. IF this is a business decision to make old
data unavailable and NOT a near term space consideration, then you could
make the older data disappear with a covering view and the appropriate
renames.
Now why might you do that? With the views in place, you could then take
your time, most important table first (where most important is defined by
your evaluation of your query requirements), and partition those objects,
probably oldest keepers first which likely avoids contention until the last
few time periods. So you copy a period at a time into the new partitioned
version of the table and then update the covering view to filter out the
completely copied oldest not-too-old period from the current base table and
connect it via union all to the new partitioned table. Eventually all the
table you want to keep is in the new period partitioned table, with which
name you replace the covering view.
This can be done gradually, possibly with a bit of short outages for data
of time periods that are not completely quiescent by business rule. But the
date range of that is probably small enough for a copy out to be quite fast.
This overall strategy WILL consume more data space along the way. It will
quickly make the old data inaccessible and you won’t incur the delete
overhead of anything. At the end of the game you can optionally archive the
original, but you should ask if the point is to permanently make no longer
legally required data actually unavailable. IF THAT IS THE CASE, then you
need to clean up (destroy) all your archived old data and paper copies.
This is sometimes an actual requirement.
A conversation of the business reasons (if any) for getting rid of the old
data is likely very useful. Space savings is a very different issue from
information management. A likely suspect is the CFO or his pet actuary or
legal representative (of both, in a meeting).
If ask these questions and they say “Don’t y’all worry your purdy head
about those things above your pay grade” that will still likely put you on
their minds that you care about what they need in addition to what they
directly ask for. Or they may immediately bring you into the business
requirements conversation. Likely that is a career enhancing moment.
Remember to listen carefully and ask if anything they say sounds like a
foreign language.
One last thing: It’s entirely possible the interim version with a cover
view is noticeably or unacceptably unperformant. Or it could be just fine.
Good luck, I think others have laid out excellent advice on technical
deletion.
mwf
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jonathan Lewis
*Sent:* Monday, January 03, 2022 8:18 AM
*To:* ORACLE-L
*Subject:* Re: Shrink table space strategy
There is a limitation on "move including" - you can't use it at the PARENT
end of referential integrity (though it's valid at the child end).
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Regards
Jonathan Lewis
On Mon, 3 Jan 2022 at 12:48, Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
wrote:
HI,
1.) The oracle version is 12.2
the ALTER TABLE ... MOVE ONLINE INCLUDING ROWS WHERE <predicates
filtering which rows you want to keep> is a good approach.
I will check it.
2.) About the integrity : the script that will delete data is organized to
respect the FK , deferred and guarantee the referential integrity of
the data on commit.
Delete will be applied only for old data .. too old...
3.) The volume is actually an important.
I think that it is the item that defines the strategy.
In this case, I can use one or more strategy during all "cleaned task".
Thanks for all the answers.
Eriovaldo
Em seg., 3 de jan. de 2022 às 06:14, Jonathan Lewis <
jlewisoracle@xxxxxxxxx> escreveu:
Since you mention partitioning I assume you are running enterprise edition.
You haven't mentioned a version - but if you're on 12.2 or above then
Sayan's suggestion is an obvious first choice - though it has limitations
(you haven't said anything about referential integrity) , see:
https://jonathanlewis.wordpress.com/2020/07/12/massive-deletes/
A significant problem with any strategy that has to take place while the
system is active is that online deletes. copies, index updates generate a
lot of undo and redo, and a lot of I/O, so you need to think about the type
and volume of I/O that might take for each table in turn and maybe do one
table with its indexes at a time.
Regards
Jonathan Lewis
On Mon, 3 Jan 2022 at 02:11, Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
wrote:
Hello,
I have big tables with millions of lines in the production environment ...
We have a request to delete old data in these tables. The filter will be
defined by the business rules, considering data and some status ...
Partition is an option but not at this moment. Business rules are complex
to define the best partition and tables are too many (+-150).
Considering that shrink table causes lock in the table, what is the best
strategy :
a.) delete a lot of lines, rebuild indexes (the maximum is possible) and
execute shrink a few times
b.) delete few lines and execute shrink many times and rebuild indexes few
times.
c.) are there other ways to do it ???
I did not find how to execute the shrink online without lock.
Regards
Eriovaldo
