--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...> wrote:

>
> > YES , Absolutely 24X7 , DB
> >
> > first i can understand what table BUT i have multiple index for this
> > table then can not understand what index(s) of this table have
> > problem...
> > then how use ALTER INDEX ... ACTIVE; COMMIT ?
> >
> > one note i point to it : i have *always* problem with one table of over
> > thousand of tables in my db only one table
> > also other note this table have used in one *heavy load* recursive
> > stored procedure (get over 50 sec to finish) one other *hungry*
> > user work with this table ...
> >
> >
>
> why don't you just show use the table structure for this table, including
> index declarations and the recursive procedure for it.
> That may shed some light on possible issues
> Alan
>

Hi

CREATE TABLE ACC$PREDOC (
NID DM_ID NOT NULL /* DM_ID = INTEGER */,
DOC_LINK DM_ID NOT NULL /* DM_ID = INTEGER */,
NAME DM_NAME_L2 NOT NULL /* DM_NAME_L2 = VARCHAR(50) */,
BED DM_MONEY_BIG DEFAULT 0 /* DM_MONEY_BIG = BIGINT */,
BES DM_MONEY_BIG DEFAULT 0 /* DM_MONEY_BIG = BIGINT */,
TREE_NUM_BASE DM_ID /* DM_ID = INTEGER */,
ROW DM_ID /* DM_ID = INTEGER */,
TREE_NUM_2 DM_ID /* DM_ID = INTEGER */,
TREE_NUM_3 DM_ID /* DM_ID = INTEGER */,
CMMNT DM_COMMENT_120 /* DM_COMMENT_120 = VARCHAR(120) */,
PRIORITY_ID DM_ID /* DM_ID = INTEGER */,
OBJECT DM_ID /* DM_ID = INTEGER */,
OBJEC_ID DM_ID /* DM_ID = INTEGER */,
COST_ID DM_ID /* DM_ID = INTEGER */,
MANAGE_ID DM_ID /* DM_ID = INTEGER */,
SPECIAL_ID DM_ID /* DM_ID = INTEGER */,
PAYTYPE_ID DM_ID /* DM_ID = INTEGER */,
PAYTYPE_DATE DM_PDATE /* DM_PDATE = TIMESTAMP */,
INV_STUFF DM_ID /* DM_ID = INTEGER */,
ARTICLE DM_ID DEFAULT 1 NOT NULL /* DM_ID = INTEGER */,
PRJ_CODE DM_ID /* DM_ID = INTEGER */,
OLD_TREE_BASE DM_ID DEFAULT -1 /* DM_ID = INTEGER */,
SUBSYS DM_ID /* DM_ID = INTEGER */,
GTR_NID DM_ID /* DM_ID = INTEGER */,
INSERT_BY DM_ID /* DM_ID = INTEGER */,
INSERT_TIME DM_PDATE /* DM_PDATE = TIMESTAMP */,
UPDATE_BY DM_ID /* DM_ID = INTEGER */,
LAST_UPDATE_TIME DM_PDATE /* DM_PDATE = TIMESTAMP */,
UPDATE_COUNT DM_ID /* DM_ID = INTEGER */,
PDATE DM_PDATE /* DM_PDATE = TIMESTAMP */,
TREE_BASE_R3 DM_ID /* DM_ID = INTEGER */
);




/* Check constraints definition */

ALTER TABLE ACC$PREDOC ADD CONSTRAINT ACC$PREDOC$BES_BES_HAVE_VALUE CHECK((BED IS NULL) OR (BED = 0) OR (BES=0) OR (BES IS NULL));
ALTER TABLE ACC$PREDOC ADD CHECK(ARTICLE > 0);
ALTER TABLE ACC$PREDOC ADD CHECK(ARTICLE > 0);


/******************************************************************************/
/**** Unique Constraints ****/
/******************************************************************************/

ALTER TABLE ACC$PREDOC ADD CONSTRAINT UNQ1_ACC$PREDOC_ROW UNIQUE (ROW)
USING INDEX UNQ1_ACC$PREDOC;


/******************************************************************************/
/**** Primary Keys ****/
/******************************************************************************/

ALTER TABLE ACC$PREDOC ADD CONSTRAINT PK_ACC$PREDOC PRIMARY KEY (NID);


/******************************************************************************/
/**** Foreign Keys ****/
/******************************************************************************/

ALTER TABLE ACC$PREDOC ADD CONSTRAINT FK_ACC$PD_DOC_LINK FOREIGN KEY (DOC_LINK) REFERENCES ACC$DOC (NID);
ALTER TABLE ACC$PREDOC ADD CONSTRAINT FK_ACC$PD_PAYTYPE_ID FOREIGN KEY (PAYTYPE_ID) REFERENCES ZMAZ$PAYTYPE (NID);
ALTER TABLE ACC$PREDOC ADD CONSTRAINT FK_ACC$PREDOC_1 FOREIGN KEY (TREE_BASE_R3) REFERENCES ACC$TREE$BASE$R3 (NID);


/******************************************************************************/
/**** Indices ****/
/******************************************************************************/

CREATE INDEX ACC$PREDOC_IDX3 ON ACC$PREDOC (DOC_LINK, NID);


/* Trigger: ACC$PREDOC_BU0 */
CREATE OR ALTER TRIGGER ACC$PREDOC_BU0 FOR ACC$PREDOC
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE A DM_ID;
DECLARE VARIABLE C DM_ID;
DECLARE VARIABLE DOC_STATE DM_ID;
DECLARE VARIABLE Z_UPDATE_BY INTEGER;
BEGIN

EXECUTE PROCEDURE FUN_GET_CUR_USERID RETURNING_VALUES(:Z_UPDATE_BY);
NEW.INSERT_BY = OLD.INSERT_BY;
NEW.INSERT_TIME = OLD.INSERT_TIME;
NEW.UPDATE_BY = Z_UPDATE_BY;
NEW.LAST_UPDATE_TIME = CURRENT_TIMESTAMP;
NEW.UPDATE_COUNT = OLD.UPDATE_COUNT + 1;

-- NO CHANGE.
SELECT D.SSTATE FROM ACC$DOC D WHERE (OLD.DOC_LINK = D.NID) INTO :DOC_STATE;
IF (DOC_STATE <> 1) THEN
EXCEPTION EXP_DEFAULT 'تنها در صورتي امكان تغير در اقلام سند وجود دارد كه سند در حالت ناقص باشد';
--CHK.
IF ((ABS(NEW.BED) > 0) AND (ABS(NEW.BES) > 0)) THEN
EXCEPTION EXP_ACC_BED_BES ;
-- NULL.
IF (NEW.NAME IS NULL) THEN
EXCEPTION EXP_ACC_DOC_NAME ;
A = 0;

SELECT COUNT(*) FROM ACC$TREE$BASE$R3 T WHERE (NEW.TREE_BASE_R3 = T.NID) INTO A;
IF ((NEW.TREE_BASE_R3 IS NULL) OR (A < 1)) THEN
EXCEPTION EXP_ACC_DOC_TREE ;
C = 0;
SELECT COUNT(*) FROM ACC$PREDOC T1
WHERE (NEW.ROW = T1.ROW) AND (NEW.DOC_LINK = T1.DOC_LINK) INTO C;
IF (C > 0) THEN
EXCEPTION EXP_ACC_DOC_ROW ;
END