Method and apparatus for reorganizing an active DBMS table6122640Abstract Reorganization of database tables is performed while the tables being reorganized are available for normal OLTP activity. A trigger is setup to record OLTP activity on a source table to be reorganized. The source table is momentarily locked to establish an SCN checkpoint and determine the location of data blocks allocated for the source table. A copy of the table is created (new table) and blocks untouched/modified since the timestamp are unloaded from the source table and inserted into the new table. Transactions occurring since the timestamp are performed on the new table until no more transactions since the timestamp remain in the trigger record. The new table is switched with the source table (renamed as the source table), and the original source table is dropped. Claims What is claimed is: Description BACKGROUND OF THE INVENTION
TABLE 1
______________________________________
Description of User definable Reorg Parameters. The input is
made available to the reorg process.
______________________________________
Login User's login Name
Password User's Password
Sid Oracle Sid
Home Oracle Home
doFailurePrediction
do failure prediction flag
FirstLock.Wait Initial Lock Wait in seconds
FirstLoc.Retry Initial Lock No of Retries
Table.Name Table Name
Table.Owner Table Owner
Table.ChangeDDL
New Table modifications to apply
WorkSpace Tablespace name for temporary storage
ExportDirectory
File System Directory names(s) to use
for file storage
Tablespaces New table storage informations
TransactionLock.Wait
Lock parameters while applying
Transactions
TransactionLock.Retry
WindowTimeOut Time allowed to keep the table locked
Switch Switch Strategy information
Threshold.Tries
No times to try switching to new table
Threshold.Exceed
Indicator of action if Threshold.Tries
is exceeded.
______________________________________
Included in step 100 is an initialization process (See FIG. 2) that connects to a database (Oracle, in this example, at step 200), performs preliminary set up (step 210), and invokes a failure prediction process 250 that performs pre-organization checks that assure the database is in condition to be reorganized. At Step 105, the various initializations as required are performed. For example, a log file for storing transactions, hereinafter referred to as a transaction table, is created. Table 2 provides an example definition of a translation table which will be described in greater detail below.
TABLE 2
______________________________________
CREATE .TABLE <c.owner>.TSREORG$LOG (.backslash.
ROW$$ rowid.backslash.
dmltype char(1) . .backslash.
updtime date) <storage parameters>
______________________________________
At Step 110, a trigger is created on the source table that logs any activity (including OLTP information) performed on the source table. The trigger enters log information, such as, for example, rowid, type of transaction, and current time stamp. The transaction table will include an identifier for the type of transaction, including references such as "I" or insert, "U" for update, and "D" for delete. Other methods for identifying the transactions occuring to the source table may also be utilized (for example, number codes identifying the transaction). Table 3 illustrates an example programming structure for implementing a trigger designed to operate in conjunction with the transaction table.
TABLE 3
______________________________________
CREATE OR REPLACE TRIGGER <c.owner>.TSREORG$TRIG
after insert or update or
delete on <c.owner.c.table>.backslash.
referencing old as old.backslash.
for each row declare dmltype char; .backslash.
begin if inserting then
dmltype: =`I`;.backslash.
elsif updating then dmltype:=`U`;.backslash.
elsif deleting then dmltype:=`D`;.backslash.
end if; .backslash.
insert into <c.owner>.TSREORG$LOG
(row$$, dmltype)
______________________________________
FIG. 3 illustrates one embodiment of the present invention, where creating a transaction table and a trigger are combined as shown in step 110. In FIG. 3, the transaction table is created (step 300) and stored in a User Defined Temporary Tablespace 320. The User Defined Temporary Tablespace 320 provides enough storage space to hold any anticipated additions and updates to the source table. Once the trigger and transaction table have been set up, the source table is locked and a checkpoint is created (Step 115). The lock on the source table may be acquired via any process that prevents updates from being performed on the table. Generally speaking, an amount of wait time and a number of tries to acquire the exclusive lock will be applied if a first attempt at acquiring the lock is not successful. FIG. 4 illustrates a detailed process for acquiring the exclusive lock and acquiring the checkpoint. At step 410, an exclusive lock is requested. If the lock is not obtained, a retry process 420 is invoked. Once the source table is locked, the SCN number for a checkpoint is obtained (steps 430 and 440). The checkpoint is any identifier or other method (such as a timestamp, a combination of a checkpoint and another reference, or an external reference, for example) that uniquely identifies a point in time or a state of the database. For example, in an Oracle database system, an SCN is applied to each transaction that occurs within the database. Since the Oracle system guarantees the uniqueness and ever-increasing quality of the SCN, it is well suited for use as a checkpoint. Thus, at Step 115, the source table is locked and an SCN or other similar identifier is obtained as a checkpoint (also referred to as CHECKPOINT.sub.-- SCN, or ReorgScn). Table 4 provides a listing and description of variables utilized in programming the reorganization process, including ReorgScn.
TABLE 4
______________________________________
Description of Application Variables used during the reorg
process
______________________________________
ReorgScn Reorg's defined System Change Number
redoFiles Application defined redo information
files
undoFiles Application defined undo information
files
ddlFiles Application defined DDL information
files
insertStmt Insert Statement DDL for Table Load
______________________________________
At Step 120, while the source table is locked, a segment header of the source table is read and a list of blocks (also referred to as an extent list) is created. The segment header of the source table is a file that stores identifiers that identify data blocks allocated for use by the source table. The extent list identifies the blocks from which source table data will be unloaded from. In Oracle database management systems, the segment header identifies extents (contiguous blocks of data) allocated either when the table was created or during updates of the table that required additional space over and above that which was already allocated for the table. Therefore, in an Oracle system, the extent list created in Step 120 is basically a list that includes each extent allocated for the source table, each of those extents identifying a contiguous set of data blocks storing table data. In the context of the present invention, the segment header of the source table is broadly defined as any file or storage device that maintains a list of where the data stored in the source table is located. For example, a database management system may allocate dataspace based on pages. In this case, the extent list created at Step 120 would include page information and other data required to identify where data of the source table is stored. Therefore, any number of database management systems and their individual structures may be accommodated using the teachings of the present invention. At Step 125, the source table is unlocked. Before unlocking the source table, the rows that have been generated in the transaction table prior to the checkpoint established in Step 115, are deleted (step 450, FIG. 4, for example). The process of creating the trigger, locking the table, establishing a checkpoint, and then deleting transactions logged in the transaction table prior to establishment of the checkpoint (since the table is locked prior to establishing the checkpoint, this process may be performed by deleting all entries in the transaction table while the source table is locked), assures that the transaction table only maintains entries that have occurred since the table was locked and checkpoint established. Continuing with reference to FIG. 1B, at step 130, a copy of the source table is created (hereinafter referred to as the new table). The new table is based on the source table as originally created and includes all applicable user requested DDL modifications to the table. FIG. 5 is a flow chart illustrating the creation of a copy of the source table. At step 500, the Data Definition Language (DDL) scheme defining the source table is retrieved. User requested DDL modifications are applied to the DDL (510), and a new table object including indexes is created (520). These processes are logged in redo and undo files created at initialization (See step 105, FIG. 1A). An insert statement for loading the new table is created based on the DDL of the new table (530) which is stored for later reference (540). Eventually, row data will be unloaded from the source table and loaded into the new table. At step 135, a mapping table is created to map rowids of the source table to rowids of the rows inserted into the new table. Regardless of whether the DBMS makes rowids available, the mapping table is setup to contain complete information regarding where rows are unloaded from the source table and where they are stored in the new table. At Step 140, a data store (unloaded block ranges) is created to store a range of file numbers and block numbers of blocks unloaded from the source table. The present invention creates a record of the file/block numbers unloaded in the data store. Because Oracle allocates blocks in contiguous ranges, it is likely that the unload process will be unloading blocks in contiguous ranges, hence in an Oracle system, it will be sufficient to store ranges of file/block numbers combinations. This has an advantage of minimizing storage requirements as opposed to storing every file/block number unloaded. However, in another DBMS, storage of each individual file/block number combinations or other methods of tracking unloaded blocks may be more efficient. At Step 145, data blocks are unloaded from the source table and the data contained therein is loaded into blocks of the new table. The unload process comprises reading data blocks from the extent list created at step 120. Rows are then extracted from the unloaded blocks and loaded into the new table. To improve speed, the reorganization process bypasses the DBMS SQL interface and reads data blocks and extracts row data directly from the DBMS data files. Bypassing the SQL interface of a DBMS requires knowledge regarding the structure of the DBMS files. Retrieving rows of data from data blocks stored in the DBMS files requires knowledge of the structure of the block. DBMS block and file structure may be obtained either from a specification describing the structure or by investigation of a block already created. For example, FIG. 6 illustrates the structure of an Oracle data block. Each data block of an Oracle table is made up of a block header 600, block transaction information area 610, and a table and row directory 620 containing pointers to row data 630. In Oracle, the row data itself is filled bottom up. Utilizing the above-described structural information, an Oracle DBMS file may be opened, data blocks read, and row information extracted therefrom. The extracted row information is loaded into the new table. As discussed above, similar operations may be performed on DBMS systems other than Oracle, which is presented herein as an example. The unload process will utilize the SCN number (CHECKPOINT.sub.-- SCN) established at Step 115, to determine whether a block is unloaded. Only blocks having an SCN less than CHECKPOINT.sub.-- SCN will be unloaded. Blocks having an SCN greater than CHECKPOINT.sub.-- SCN will be skipped. A skipped block list is also maintained to identify blocks that have been skipped in the unload/load process. The unload/load blocks of step 145 is shown in more detail in FIG. 7. At step 700, the extent list is read, and the SCN of each block on the list is determined (720). If a block SCN is less than CHECKPOINT.sub.-- SCN (ReorgScn), it indicates that the block has not been modified since the checkpoint was established. In this case, the block is directed to be unloaded from the source table and loaded into the new table (step 750), and the block is added to the unloaded block ranges 760. If the SCN of the block is greater than CHECKPOINT.sub.-- SCN, the block is added to the skipped block list (step 740). Each block in the extent list is applied to the same process. As each row is unloaded and inserted into the new table, the rowid of the source table is mapped to its new rowid in the new table and stored in the mapping table. The mapping table is utilized to process deletions of specific rows, if needed, in the latter half of the reorganization process. The mapping can be stored in the form of a table in the DBMS, in memory, on a file system, or any other method in which the mapping table may be maintained and later used by the reorganization process. Table 5 provides a description of lists utilized in one embodiment of the present invention.
TABLE 5
______________________________________
Description Lists used during the reorg process
______________________________________
Extent List Node in the List contains
FileNo Oracle Data File Number
BlockNo Starting Oracle Data Block
Length No. of Blocks in this Extent
Transaction Block List Node in the List contains
FileNo Oracle Data File Number
BlockNo Oracle Block Number
Count No. of transactions for this block
Transaction List Node in the List contains
rowid Oracle Row Address
type Transaction Type either I = insert U = update
D=Delete
Delete And Insert List Node in the List contains
SlotNo Slot where row is stored in an Oracle block
______________________________________
As shown in Table 5, the Extent List is utilized for identifying block information during the process of unloading the source table. The Transaction Block List identifies blocks where and what type of transactions that have taken place. The Transaction List provides a rowid and transaction type for each transaction identified in the Transaction Block List. And finally, the Delete and Insert List identifies slot numbers for each rowid identified in the Transaction List. The above lists are compiled by retrieving information from the transaction table and are utilized in the apply transactions process (See FIG. 9, blocks 910, and 920, for example). FIG. 8 illustrates separate unload and load processes (threads) utilized by the present invention. The load thread is started by a message 805 indicating a specific block to unload (see FIG. 7, step 750, for example). As each row is extracted (step 820), it is transferred into a shared row transfer area 840 and a message is sent to the load thread indicating that an unloaded row is ready for transfer (step 850). The load thread, having been previously initialized (step 855), receives the message indicating a row ready for transfer. The row in the transfer area is retrieved and the data from the row is loaded into the new table (870). The unloading of rows, transfer, and loading data into the new table is repeated by the unload and load threads for each row in the block (steps 890 and 895). For the duration of the unload and load processes, the source table is available to users for normal OLTP activity. Transactions generated on rows are recorded via a trigger into the transaction table. The second phase of the reorganization process is to process the recorded transactions (Apply Transactions Process, step 150). The apply transaction process, described hereinbelow, updates the new table according to transactions that have occurred to the source table while the reorganization has been/is taking place, and is illustrated in more detail in FIGS. 9A and 9B. The first step is to lock the source table (FIG. 9A, step 900) and create a transaction block list 920 identifying transactions organized by file/block number (step 910). The advantage of doing this is that the process can apply transactions that occurred in each block one block at a time rather than process individual transactions. If the transaction block list is empty, blocks located in the skipped block list are unloaded/loaded (step 930). If any transactions remain pending since the table was locked, the source table is unlocked to allow the transactions to occur (940), and the apply transactions process is started over. Continuing with FIG. 9B, for a given file/block found in the transaction table a determination has to be made if this file/block combination participated in the unload process (step 950). This check is made against the file/block ranges stored previously (see 760) during the unload/load block processes 750. The case where the file/block combination was not found the rows of this block would only need to be unloaded and loaded into the new table (960). This effectively processes all the transactions that have occurred for this block. In addition, the file/block combination entry would be removed from the skipped block list and added to the file/block ranges of unloaded blocks. The case where the block was found means that the rows in this file/block combination are already present in the new table. In one embodiment, the present invention utilizes a processing transaction per block process (970, FIG. 9B) to carry out transactions on the previously unloaded/loaded block. The processing transactions per block process is illustrated in FIG. 10A. First, the transaction table is utilized to determine rows having a transaction for a specified block (file number, block number combination, step 1010). Then, lists are created of slot numbers for deletions and inserts, if any (step 1020/1030). An update will be treated as a deletion and an insertion. The list will be created in such a way that only meaningful transactions need to be carried out. For example, if for a given slot an insert/delete/insert is identified, only an insert would be required. For deletions, the mapping table will be used to determine the rows to delete from the new table based on the rowid of the source table row deleted. For insertions, the block will be unloaded and loaded with the following constraint: only rows that are present in the insert list need to be unloaded. The insert list is a list of slot numbers for a given file/block number. Referring now to FIG. 10B, after the lists are created, if items are present in the delete list, they are organized (by increasing rowid or slot number, for example) (step 1050) and then deleted from the new table (step 1055). In like fashion, if items are present in the insert list, they are organized (step 1060), but inserted into the new table (step 1065). The insert process includes the unload/load processes 750 previously discussed. In an alternate embodiment, the transactions are processed by deleting all the rows from the new table that were inserted for this file/block combination and then the block is reloaded from the source table (980). After transactions have been processed, the corresponding rows in the transaction table will be deleted to prevent a transaction from being processed twice. The processing transactions per block process continues until there are no more transactions to process. Since the source table is online, it is possible that the transaction log table may be filled faster than the transactions can be processed. An additional control parameter may be utilized to control this situation. For example, after a certain threshold value (number of times the process has been carried out) either the action could be to lock the table and consume the remaining transactions or abort the reorganization. Other parameters may be utilized to control the window of opportunity available for processing transactions. This will allow for time sharing between all other users accessing the source table and the reorganization process. During the process of updating or changing data blocks, database management systems do not necessarily write all such updates or changes immediately to disk, resulting in dirty blocks (blocks updated, but not yet stored to disk). For efficiency, blocks are sometimes cached in memory, and then several blocks may be written to disk in a single disk write process rather than a separate write processes for each update or change. In Oracle, a system checkpoint assures that all dirty blocks are written to persistent storage (disk). This system checkpoint may either be invoked autonomously by the DBMS or explicitly requested by programming (e.g., the online reorganization processes of the present invention), or by the DBA. A default method to process a list of transactions (which may or may not have been written to disk) begins with first applying or invoking a system checkpoint. However, a system checkpoint is time consuming, and a check of times for occurrence of the last transaction and the last system checkpoint will reveal whether such a step is needed. A system checkpoint will not be needed if the time of the last transaction has occurred before the time of the most recent system checkpoint. For example, if the last transaction occurred at 11:00, the last system checkpoint occurred at 11:10, and the time that the list of pending transactions was processed is 11:20, an additional system checkpoint is not necessary at this point. Conversely, if the last transaction was at 11:15, or anytime at or after the last system checkpoint, an additional system checkpoint would be required to be certain all the transactions have been written to disk. Once no more entries remain in the transaction table, the skipped block list that had been kept while skipping blocks during the initial unload is checked. Any blocks processed during the apply transactions processes are/have been removed from the skipped block list. In FIG. 9A, file/block entries that remain in the skipped block list are processed by unloading/loading the rows from the corresponding file/block into the new table (step 930). Once all transactions have been processed, the new table replaces the source table (Switch Source Table Process, step 155), and a cleanup process is invoked (step 160) completing the reorganization process. This is performed by obtaining a final lock on the source table to prevent any further transactions from occurring when no more entries remain in the transaction log table and the skipped block list is empty. The reorganization process must determine whether the source table can be dropped and the new table (which is now up to date with the latest changes) can be renamed to the source. The reorganization process takes into consideration both (1) users who currently have read only access to the source table; and (2) users that have joined a queue of pending transactions waiting for the table to become unlocked. In the first case the following actions can be made (based on a control parameter NICE, step 1100, FIG. 11): (1) kill the user sessions involved in the read access allowing the table to be dropped (step 1110); or (2) loop on a time interval until all users transactions are terminated (1150). At this point, the table is switched (step 1120). Step 1120 includes creation of dependent objects (indexes, foreign keys, views, etc.), release of the new table (the name is changed to the source table name), and the source table is either dropped or renamed (saving it for backup purposes) completing the reorganization process. Since the new table is not locked, as soon as it is renamed it becomes available to users of the DBMS. In the second case, when transactions are still pending, the reorganization process releases the lock and repeats the apply transaction processes discussed above. Table 6 is a preliminary prototype design of the reorganization process discussed herein. The preliminary design is not intended to be either a compilable or executable program or program design language or contain all the features discussed herein, but is illustrative of various functions and processes implemented in an online reorganization consistent with the principles of the above described invention.
TABLE 6
__________________________________________________________________________
Online Reorg prototype design.
1. Parse and store the control file parameters.
Steps.
1. Use lex and yacc to parse the file.
Implementation. modify the fao.sub.-- acc.y and fao.sub.-- cmdline.1
files
in
The Fast Analyzer source to parse the control file.
For options supported in the control file see
orrg.sub.-- interface.txt.
On an option not specified use default
Errors.
Exit on any option defined as needed and not present in
control file.
2. Map values obtained to structure of
control.login = <login name value>
control.passwd = <login password value>
control.sid = < oracle sid value>
control.home = < oracle home value>
control.table = < table name value >
control.owner = < table owner value>
control.commitSize < commit size value>
control.iLock = < NOLOCK.vertline.TIMEOUT.vertline.FOREVER>
control.iLockWait = < No. of seconds value>
control.iLockRetry = < No. of time to try value>
control.atLock = < TIMEOUT.vertline.FOREVER value >
control.atLockwait = < No. of seconds value>
control.atLockRetry = < No. of times to retry>
control.atwindow = < Window for applying transactions secs>
control.switch = < IMMEDIATE.vertline.AFTER.sub.-- ALL.sub.-- TRANS.vert
line.NICE value>
NOTE future refrences to control structure will alias to c.
e.g. control.login = c.login
2. Connect to oracle.
Steps.
1. Use ora connect in fast analyze api pass c.login , c.passwd and c.sid
see connect.h for complete list of parameters.
Errors.
Exit if error received from ora.sub.-- connect.
3. Do setup of table specified for the online reorg.
Steps.
1. If c.iLock <> NOLOCK then Lock table using c.iLockwait, c.iLockRetry,
c.iLock.
Error. on error or timeout exit.
2. Check point system.
SQL= "alter system checkpoint global"
3. Find system change number for the checkpoint.
SQL= "select checkpoint change# from v$database"
REORG SCN=<result>
4. Create trigger and transaction table to keep an audit of all future
transactions.
Create transaction table as TSREORG$LOG
SQL= "create sequence <c.owner>.TSREORG$LOGID increment by 1";
SQL= "create table <c.owner>TSREORG$LOG ( .backslash.
logid number,
M ROW$$ varchar(255), .backslash.
dmltype char(1) .backslash.
updtime date .backslash.
)"
5. Create trigger on TABLE(c.owner,c.table)
SQL="CREATE OR REPLACE TRIGGER <c.owner>.TSREORG$TRIG
after insert or update or delete on <c.owner.c.table> .backslash.
referencing old as old .backslash.
for each row .backslash.
declare dmltype char; .backslash.
begin if inserting then dmltype:=`I`' .backslash.
elsif
updating
then dmltype
`U`;
.backslash.
elsif
deleting
then dmltype
`D`;
.backslash.
end if;
insert into <c.owner>.TSREORG$LOG
(TSREORG$LOGID.next,m.sub.-- row$$, dmltype)"
6. commit 4 and 5 this will unlock the table if locked.
Error. On error exit.
Implementation
Write a function `createSql` that will take a sql stmt and form
a new state
ment replacing c.owner with value and c.table with value.
Write a function `execSql` that will exec a sql statement.
These functions should be used in steps 1,2,3,4,5.
commit will use oci ocom.
4. Create new table and associated DDL.
Steps.
1. Create new table
SQL=`create table c.owner, c.TSREORG$TEMP
as select * from <c.owner>.<c.table>
where 1 = 2`
This should create an empty table.
2. Create a table to map old rowids and new rowids.
SQL=`create table <c.owner>.TSREORG$MAP
file#number,
block*number,
slot*number, nrow$$
varchar2(18) )"
3. Create indexes for map table.
SQL="create index <c.owner>.TSREORG$IDX.sub.-- 1 on <c.owner>.TSREORG$MAP
(1
(orow$$)`
SQL="create index <c.owner>.TSREORG$IDX.sub.-- 2 on
<c.owner>.TSREORG$MAP (.backslash.
(nrow$$)"
4. commit the above.
implementations
Again createSql and execSql can be used to create and exec the sql.
8. Unload and load rows from blocks where BLOCK SCN < REORG SCN defined
in step
Steps.
1. read extent list.
2. get columns of <c.owner.table> and create column list.
3. from column list create insert sql.
SQL= insert into <c.owner>.TSREORG$TEMP values ( i1,...in).
5. allocate memory to such that memory >= no columns*max column
size*array
size.
6. unload rows from a block into memory.
7. load rows from memory using oci into TSREORG$TEMP.
for each row to be loaded hold on to fileno,blockno,slotno of the row
SQL=declare nrowid rowid ; begin
insert into <c.owner>.TSREORG$TEMP values (i1,...in);
nrowid = DBMS.sub.-- SQL.LAST.sub.-- ROW.sub.-- ID();
insert into <c.owner>.TSREORG$MAP values (fileno,blockno,slotno);
end;
8. commit based on c.arrSize. The above sql can be done using array
inserts.
9. store in memory every block unloaded as ( fileNo,BlockNo) in
BlockHeap.
Implementation. Use modified fast unload source and tsreorg insert
source
to achieve 1,8.
Create module to efficiently insert,delete and retrieve
fileNo,BlockNo values into BlockHeap.
Implementation of BlockHeap. The BlockHeap will be constructed as a tree
using the otree api already developed. the BlockHeap Node
will contain the following elements.
BlockHeap Node FileNo
Start
Len.
The insert into a tree will be modified as following.
For a new n.FileNo,n.Block the following modifications must be made to
accomodate range values.
if n.FileNo != c.FileNo where c is the current node being examined.
then insert as per tree insert.
if n.FileNo = c.FileNo && n.Block != c.Start
if n.Block is in (c.Start+c.len) then c.len++
else insert new node as per tree insert
set new.Start=n.Block new.FileNo=n.FileNo and new.len=1
if n.FileNo = c.FileNo && n.Block = n.Start do nothing.
Search for a s.fileNo,s.Block in the BlockHeap will be conducted as
follows.
Found=False.
do until Found=TRUE OR end of tree is reached. Then Found =False.
if ( s. fileNo, s. Block == c. fileNo, c. Start) then Found=TRUE return.
if ( s.fileNo == c.fileNo && s.Block != c.Start)
if ( s.Block in ( c.start + c.len) then Found=TRUE return.
if ( end of tree ) return Found=FALSE.
else continue to next node.
9. Loop application of transactions that have occured and stored in
TSREORG$LOG.
Steps.
1. Lock <c.owner>.<c.table> in read only mode. using c.atLock,
c.atLockWait,
c.atLockRetry.
2. After Lock obtained proceed to select a list of transactions to
process.
2. LastUpdateTime=select max(updtime) from TSREORG$LOG;
3. LastCheckPointTime=select check.sub.-- point from V$THREAD;
4. if LastCheckPointTime < LastUpdateTime
apply system checkpoint.
5. Select blocks for applying transaction updates to the new table.
select FILE(mrow$$) , BLOCK(mrow$$) ,updtime
from TSREORG$LOG where updtime > LastApppliedTime
order by 1,2,3 intial LastApppliedTime is NEVER.
6. Taking a Block(file,block) at a time. Check Block Heap if
Block is present i.e already unloaded.
7. Start Window Timer based on c.atLockWindow.
8. if ( not Found ) then
8. Unload this block
9. Insert unloaded rows into new table.
10. update BlockHeap, with new block
else
select transaction list for this block.
foreach row ( File,Block,Slot) update a slot.sub.-- bit array
in the form slot bit[ slot ] = 0x01 (binary 00000001) for insert
slot bit[ slot ]= 0x03(binary 00000011) for update
slot bit[ slot ]= 0x02(binary 00000010) for delete.
9. pass the slot.sub.-- bit array to an unload function taking
file,block and slot.sub.-- bit array. Only unload the rows
that from an oracle block where for a given slot
The slot.sub.-- bit[slot] & 0x01 is true.
10. Delete from new table TSREORG$TEMP rows is any. delete criteria is
file,block,slot in TSREORG$LOG
matches file block slot
bit[slot]&0x20 is true.
delete from TSREORG$TEMP where rowid in
The present invention has been described in terms of database management systems and particularly the reorganization of tables in a DBMS. However, the teachings presented herein may also be applied to numerous tables or data storage repositories not directly linked to a DBMS system, such as spreadsheet files, work processing files and other data storage devices. The present invention may be conveniently implemented using a conventional general purpose or a specialized digital computer or microprocessor programmed according to the teachings of the present disclosure, as will be apparent to those skilled in the computer art. Appropriate software coding can readily be prepared by skilled programmers based on the teachings of the present disclosure, as will be apparent to those skilled in the software art. The invention may also be implemented by the preparation of application specific integrated circuits or by interconnecting an appropriate network of conventional component circuits, as will be readily apparent to those skilled in the art. The present invention includes a computer program product which is a storage medium (media) having instructions stored thereon/in which can be used to program a computer to perform any of the processes of the present invention. The storage medium can include, but is not limited to, any type of disk including floppy disks, optical discs, DVD, CD-ROMs, and magneto-optical disks, ROMs, RAMs, EPROMs, EEPROMS, magnetic or optical cards, or any type of media suitable for storing electronic instructions. Stored on any one of the computer readable medium (media), the present invention includes software for controlling both the hardware of the general purpose/specialized computer or microprocessor, and for enabling the computer or microprocessor to interact with a human user or other mechanism utilizing the results of the present invention. Such software may include, but is not limited to, device drivers, operating systems, and user applications. Ultimately, such computer readable media further includes software for performing the reorganization processes on at least one selected database table, as described above. Included in the programming (software) of the general/specialized computer or microprocessor are software modules for implementing the teachings of the present invention, including, but not limited to, identification and retrieval of database table structures, copying of database tables, unloading and loading data blocks, extracting row information, logging transactions, applying logged transactions, and the display, storage, or communication of results according to the processes of the present invention. Obviously, numerous modifications and variations of the present invention are possible in light of the above teachings. It is therefore to be understood that within the scope of the appended claims, the invention may be practiced otherwise than as specifically described herein.
|
Same subclass Same class Consider this |
||||||||||