Definition of a Table

From: Diego <dietitan_at_yahoo.com>
Date: 24 Jan 2002 09:52:49 -0800
Message-ID: <f4b7881f.0201240952.41e19753_at_posting.google.com>

Hi, we are designing a system over NT that has several tables. Our biggest concern is over 2 tables, they are the main tables. Its structure are:

create table FLOWDOC
(

  DOCUMENTID           NUMBER(10, 0)   NOT NULL ,
  FORMID               NUMBER(10, 0)   NOT NULL ,
  INUSE                NUMBER(10, 0)   NOT NULL ,
  IMAGEFILE            VARCHAR2(256)   NOT NULL ,
  PRIORITY             DATE            NOT NULL ,
  STATE                NUMBER(6, 0)    NOT NULL ,
  SUBSTATE             NUMBER(6, 0)   ,
  SLAVERIFICATION      NUMBER(6, 0)   ,
  KEYINGCENTERID       NUMBER(3, 0)   ,
  PROCESSINGDATE       VARCHAR2(10)   ,
  AMOUNT               NUMBER(20, 3)  ,
  ACCOUNTNUMBER        VARCHAR2(16)   ,
  TRANSACTIONCODE      VARCHAR2(16)   ,
  ABANUMBER            VARCHAR2(16)   ,
  SERIALNUMBER         VARCHAR2(16)   ,
  EXCEPTIONCODE        VARCHAR2(2)    ,
  CYCLECODE            NUMBER(4, 0)   ,
  IMPORTUID            NUMBER(10, 0)  ,
  VERIFIERUID          NUMBER(10, 0)   DEFAULT NULL,
  EXPORTERUID          NUMBER(10, 0)  ,
  ARCHIVERUID          NUMBER(10, 0)  ,
  PURGEDUID            NUMBER(10, 0)  ,
  IMPORTDATE           DATE           ,
  RECOGNIZEDDATE       DATE           ,
  VERIFIEDDATE         DATE           ,
  EXPORTDATE           DATE           ,
  ARCHIVEDATE          DATE           ,
  PURGEDDATE           DATE           ,
  ERRORID              NUMBER(10, 0)  ,
  LASTMODIFIED         DATE            NOT NULL ,
  VIEWPRIORITY         DATE           ,
  ITEMSEQUENCENUMBER   VARCHAR2(9)    ,
  RECOGNITIONUID       NUMBER(10, 0)  )

With several Indexes and FK. The PK is DOCUMENTID

The table FLOWFIELD is
create table FLOWFIELDS
(

  DOCUMENTID        NUMBER(10, 0)   NOT NULL ,
  FORMID            NUMBER(10, 0)   NOT NULL ,
  VERIFIERUID       NUMBER(10, 0)   DEFAULT NULL,
  PRIORITY          DATE            NOT NULL ,
  STATE             NUMBER(6, 0)    NOT NULL ,
  SUBSTATE          NUMBER(6, 0)    NOT NULL ,
  IMAGEFILE         VARCHAR2(256)   NOT NULL ,
  LEFTX             NUMBER(6, 2)   ,
  UPPERY            NUMBER(6, 2)   ,
  RIGHTX            NUMBER(6, 2)   ,
  LOWERY            NUMBER(6, 2)   ,
  PAGENUMBER        NUMBER(3, 0)   ,
  RECOGNIZEDVALUE   VARCHAR2(256)  ,
  RECOGNIZEDDATE    DATE           ,
  ACTUALVALUE       VARCHAR2(256)  ,
  ACTUALVALUEDATE   DATE           ,
  LASTMODIFIED      DATE            NOT NULL ,
  KEYINGCENTERID    NUMBER(3, 0)   ,
  FIELDCODE         NUMBER(5, 0)    NOT NULL ,
  INUSE             NUMBER(10, 0)   NOT NULL ,
  VIEWPRIORITY      DATE           ,
  EXPORTDATE        DATE           ,

  SLAVERIFICATION NUMBER(6, 0) ) Again with several Indexes and FK. The PK is DOCUMENTID, FIELDCODE

The FLOWDOC table receives 750,000 records per day and the FLOWFIELD table receives 2,250,000 per day.
Both tables are on-line tables, that means that during the inserts
(during all day several times) our users (100 users) connect to them
and perform updates and queries in several fields. Also during the date we delete all the records that were processed (we can't use truncate). The idea is to "clean" the tables after 24 hours to start over again, but we can't truncate the table because we can have at the same time, records from today and from "yesterday". I'd like to receive some advice about how to create them (storage parameters for the Table, for the indexes Indexes, type of table to use) and if it is a good idea to rebuild Indexes and how often.

Thanks a lot

Diego Received on Thu Jan 24 2002 - 18:52:49 CET