problems with big table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: problems with big table

Hybrid View

  1. #1
    Join Date
    Mar 2002
    Posts
    56

    problems with big table

    I have Oracle 10g Standard edition and a table that should accomodate 3 mil records per month. Because partitioning option is not supported in Oracle Standard, the table is not partitioned. I need to do maintenance on this table. Every time I delete(not truncate) a month's worth of data, the insert becomes very slow(. I'm in the testing phase, so I truncate the table with drop storage option and everything works fine afterwards. I'm just worried that as soon as the database will be in production, I won't have the option of truncating. How can I tune the table maintenance in order to have fast inserts?
    Thank you

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Do you delete 3 M rows when the end users insert rows in the table or in a maintenance window?

    Tamil

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You might like to experiment with the old partitioning method of using views.

    The technique is described in the 8.1.7 documentation here ... http://download-west.oracle.com/docs.../sql.htm#15707

    You'd have to test how well it works in 10g -- let us know how you get on if you do go down this route though.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Mar 2002
    Posts
    56
    Ideally I would like to delete the rows while the users insert data into the table. Right now, I'm deleting and only after I finish the users start inserting and the insert is really slow.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    The slowness of insert may be due to inadequate number of freelists.

    Tamil

  6. #6
    Join Date
    Mar 2002
    Posts
    56
    I was thinking maybe the slownes is related to the high watermark, the space under the highwatermark is not realeased with delete but it is with truncate drop storage.
    If freelists are at fault, haw do I solve the problem?

  7. #7
    Join Date
    Aug 2001
    Posts
    267
    You can also try re create indexes on that table after a big delete. That will improve a lot. And also diable indexes for large inserts and enable back. And did you tried with nologging option on table or tablespace..
    Raghu

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Can you post the table definition here?

    Tamil

  9. #9
    Join Date
    Mar 2002
    Posts
    56
    Here is the table definition and the tablespace in which it resides
    :

    CREATE TABLE HRCCOL_MASTER.tbl_WB_322
    (wb_322_id NUMBER(38,0) CONSTRAINT PK_W322_ID PRIMARY KEY,
    parser_information_id NUMBER(38,0) CONSTRAINT CN_W322_PARSER_ID NOT NULL,
    group_cont_num VARCHAR2(9) CONSTRAINT CN_W322_GROUP_COUNT NOT NULL,
    transaction_cont_num VARCHAR2(9) CONSTRAINT CN_W322_TRANS_NUM NOT NULL,
    terminal_id NUMBER(3,0) CONSTRAINT CN_W322_TERM_ID NOT NULL,
    port_name VARCHAR2(4) CONSTRAINT CN_W322_PORT_NAME NOT NULL,
    shipment_status VARCHAR2(2) CONSTRAINT CN_W322_SHIP_STAT NOT NULL,
    activity_date DATE CONSTRAINT CN_W322_ACT_DATE NOT NULL,
    equipment_initial VARCHAR2(4) CONSTRAINT CN_W322_EQ_INIT NOT NULL,
    equipment_number NUMBER(10,0) CONSTRAINT CN_W322_EQ_NUM NOT NULL,
    equipment_type VARCHAR2(4),
    equipment_status VARCHAR2(2),
    chassis_initial VARCHAR2(4),
    chassis_number VARCHAR2(10),
    vessel_code VARCHAR2(8),
    vessel_name VARCHAR2(28),
    country_code VARCHAR2(3),
    voyage_number VARCHAR2(10),
    movement_type VARCHAR2(2),
    movement_mode CHAR(1),
    length NUMBER(4,0),
    loaded CHAR(1),
    railroad VARCHAR2(4),
    check_digit NUMBER(1,0),
    calc_check_digit NUMBER(1,0),
    elim_check_digit NUMBER(1),
    hazmat CHAR(1),
    insert_date DATE,
    u_insert_date DATE )
    TABLESPACE HRCCOL_DATA_L
    STORAGE (
    INITIAL 160M
    NEXT 160M
    MAXEXTENTS 1000
    )
    /

    The tablespace is defined as follows:

    CREATE TABLESPACE "HRCCOL_DATA_L" LOGGING DATAFILE '&vDATAFILES\HRCCOL_data01L.dbf' SIZE 204808K REUSE AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 160M;

  10. #10
    Join Date
    Feb 2001
    Posts
    295
    About the partitioning itself, the "old method" suggested by slimdave should work. However, you may need to recompile the view after maintenance operations on the base tables.

    About the freelists issue, it probably would not be a problem on tablespaces with automatic segment space management (which is a default on 10g).
    An ounce of action is worth a ton of theory.
    —Friedrich Engels

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