Topic: The basis from BLOB swells
Is ABS (Automated Bank System) in which photos of clients, and also skan-copies of their passports among other things are added.
That not the basis containing Financial Data, the separate basis for BLOB is created. Thus, to deceive the client application, stacking in separate basis of photos happens in triggers through Cross-Database requests.
-- Pictures I store so: CREATE DOMAIN DOMN$BLOB_IMAGE AS BLOB SUB_TYPE 0 SEGMENT SIZE [PAGE_SIZE bases]; -- This table of photos, is in both bases CREATE TABLE TABL$R_CS_PHOTO ( CS_ID DOMN$INTEGER_ID, - the code of the client ID DOMN$INTEGER_ID, - the photo code, strictly trigger through GEN_ID NAME DOMN$PSTRING, - the remark FLAG_DELETE DOMN$BOOLEAN, - a mark on removal DATE_COMMIT DOMN$DATETIME, - date/time = TIMESTAMP PHOTO DOMN$BLOB_IMAGE - a picture ); -- Flag in a database that it is necessary to store a photo in exterior basis CREATE GENERATOR GENR$R_CS_PHOTO_EXTERNAL; -- Further in the basis goes cross-database request CREATE OR ALTER TRIGGER TRIG$R_CS_PHOTO_BIU_240 FOR TABL$R_CS_PHOTO ACTIVE BEFORE INSERT OR UPDATE POSITION 240 AS DECLARE VARIABLE P_DB_BLOB TYPE OF COLUMN TABL$R_FILIALS.DATABASE_NAME_REMOTE; DECLARE VARIABLE P_SQL_STMT TYPE OF COLUMN TABL$J_4.DOCSTR; BEGIN IF (GEN_ID (GENR$R_CS_PHOTO_EXTERNAL, 0) <> 1) THEN EXIT; SELECT FIRST 1 P.DATABASE_PATH FROM PROC $ _ DB_PATH_BLOB P INTO:P_DB_BLOB; IF (TRIM (:P_DB_BLOB) = ") THEN EXIT; IF (NEW.PHOTO IS NULL) THEN BEGIN P_SQL_STMT = ' UPDATE OR INSERT INTO TABL$R_CS_PHOTO (CS_ID, ID, NAME, FLAG_DELETE, DATE_COMMIT) ' || ' VALUES (? Q_CS_ID? Q_ID? Q_NAME? Q_FLAG_DELETE? Q_DATE_COMMIT) ' || ' MATCHING (ID) '; EXECUTE STATEMENT (:P_SQL_STMT) ( Q_CS_ID: = NEW.CS_ID ,Q_ID: = NEW.ID ,Q_NAME: = NEW.NAME ,Q_FLAG_DELETE: = NEW.FLAG_DELETE ,Q_DATE_COMMIT: = NEW.DATE_COMMIT )ON EXTERNAL DATA SOURCE:P_DB_BLOB AS USER ' SYSDBA ' PASSWORD ' masterkey '; END ELSE BEGIN P_SQL_STMT = ' UPDATE OR INSERT INTO TABL$R_CS_PHOTO (CS_ID, ID, NAME, FLAG_DELETE, DATE_COMMIT, PHOTO) ' || ' VALUES (? Q_CS_ID? Q_ID? Q_NAME? Q_FLAG_DELETE? Q_DATE_COMMIT? Q_PHOTO) ' || ' MATCHING (ID) '; EXECUTE STATEMENT (:P_SQL_STMT) ( Q_CS_ID: = NEW.CS_ID ,Q_ID: = NEW.ID ,Q_NAME: = NEW.NAME ,Q_FLAG_DELETE: = NEW.FLAG_DELETE ,Q_DATE_COMMIT: = NEW.DATE_COMMIT ,Q_PHOTO: = NEW.PHOTO )ON EXTERNAL DATA SOURCE:P_DB_BLOB AS USER ' SYSDBA ' PASSWORD ' masterkey '; NEW.PHOTO = NULL; END END
I at level of triggers intercept BLOB, I rick it in separate basis, and in the main for-NULL-jaju.
Since the photo at first gets to the basis the basis on DatabaseGrowIncrement from a config swells up a little to contain a photo, the hell with her.
Then it stores all in the exterior.
The basis all the same swells with very great speed.
Here, for a week on separation it swelled to 2,5 a basis file.
Ordinary backup-restore resulted basis in a normal state 403 MB.
I so understood, what my decision to add a photo in separate basis for this point in question (growth of a file of a DB) - does not help?