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?
Do you delete 3 M rows when the end users insert rows in the table or in a maintenance window?
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.
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.
The slowness of insert may be due to inadequate number of freelists.
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?
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..
Can you post the table definition here?
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,
u_insert_date DATE )
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;
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).