database planning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: database planning

  1. #1
    Join Date
    Dec 2002
    Posts
    7

    Question database planning

    First, I am dba beginner.
    I have to create database.Databse will contain 2 tables.
    First table with average row size = 159 bytes, and second table with average row size = 86 bytes.
    Every day first table will be loaded with approximate 80 000 new rows per hour, and second with approximate 10 new rows per hour.
    Tables will be Index-organized tables.Is it good or bad idea?

    HD where will be database is with size allocation unit (cluster size on win2000) = 64k.


    Which db_block_size I have to use?
    Is there any way (mathematical) to calculate it?
    Which increment value for datafile extend I have to use?
    Which size of extents for my tables I have to use?
    How to calculate others storage parameters?

    Thanks

  2. #2
    31006558 Guest
    Please give more info to the reader. Then "we" can take it step by step.

    In what environment are you working on?
    Planned usage of data?(number of concurrent end_users) etc.

    All of this is important to determain what your db should look like.

    OK?

  3. #3
    Join Date
    Dec 2002
    Posts
    7
    What do you think when you asked me about environment?
    Is it hardver & software environment?

    I'm working on windows 2000 server,HD 20G,RAM 2G.
    Number of concurrent end_users will be 50

  4. #4
    31006558 Guest
    I hope I can help you through this example, but first....in very basic form.

    The environment questionroduction,datawarehousing etc.??

    You're going to have serious HD SPACE PROBLEMS. At the rate which data is allocated to the 1st table, you're going to run out of disk space in the first 7 DAYS or so. (Approc. 3 gigs+ a day)

    The 2gig memory seem to be good for 50 users. Do some reading on the SGA. You might find it necessary to change the SGA for better performance.

    The db_block_size I use is 2048,which is sufficiant for MY DB.
    Look at this SQL to get some direction on the datafile ext. You can change the values in a later stage with ORACLE STUDIO.
    -------------------------------------------------
    CREATE TABLESPACE "MM_GEO_DATA"
    DATAFILE 'D:\ORACLE\ORADATA\MM\MM_GEO_DATA.ORA'
    SIZE 52428800
    AUTOEXTEND ON NEXT 4500M
    DEFAULT STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50) ONLINE PERMANENT;


    -- Create table
    create table C_ALIGNMENT_LEFT
    (
    ID NUMBER not null,
    SURVEY_ID NUMBER,
    SEGMENT_NUM NUMBER,
    FROM_DISTANCE NUMBER,
    TO_DISTANCE NUMBER,
    FROM_KM NUMBER,
    FROM_M NUMBER,
    TO_KM NUMBER,
    TO_M NUMBER,
    AG_ALLR BLOB,
    NETWORK_ID NUMBER
    )
    tablespace MM_GEO_DATA
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
    initial 160K
    next 5680K
    minextents 1
    maxextents 505
    pctincrease 50
    );

    -- Create/Recreate primary, unique and foreign key constraints
    alter table C_ALIGNMENT_LEFT
    add constraint PKK_C_ALIGNMENT_LEFT primary key (ID)
    using index
    tablespace MM_DEO_IDX
    pctfree 10
    initrans 2
    maxtrans 255
    storage
    (
    initial 80K
    next 144K
    minextents 1
    maxextents 505
    pctincrease 50
    );

    Good luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width