| Deleting & Inserting data on tables having indexes [message #10798] |
Tue, 17 February 2004 18:50  |
Savita
Messages: 2
Registered: February 2004
|
Junior Member |
|
|
I am using 2 indexes on my table to speed up the speed of query exectn. Pasted below is the table structure.
The query executes in 6-7 msec but at times at specific stage the insert or delete stmt. takes time of 5-10 sec. There are 2 crore rows in my table. There are around 100 select stmt and 20-50 delete and insert stmt. in my stored proc. The stored proc is a loader which continously loads and deletes data for n no. of policies. I analysed the o/p and found that after data insertion of every 4-5 policies the next policiy for any of the insert or delete stmt. takes around 5-10 sec.
I saw the explain plan for this insert/delete stmt and found that the no. of row hit are in millions and the cost is around 5000 - 12000.
Pasted below is my delete & insert stmt.
Delete from ENTITY_NAME_T where entity_key in
(select child_entity_key from ENTITY_REL_T where parent_entity_key in ( select child_entity_key from ENTITY_REL_T where parent_entity_key = 6424831 and entity_rel_type_key in ( 7,6,9))
and entity_rel_type_key in (8)) ;
Insert into ENTITY_NAME_T values (medicalSecurityKey,nEntityType,'MedicalSecurity',1,SYSDATE,null,null);
Is there any way that I can modify this query so that the cost is less.Also do I need to change any settings in oracle db so that the speed of insert/delete is improved. Why is it so that after selected no. of loading of policies it takes more time and then speed becomes normal.
TABLE STRUCTURE
*****************
CREATE TABLE ENTITY_NAME_T (
ENTITY_KEY NUMBER NOT NULL,
ENTITY_NAME_TYPE_KEY NUMBER NOT NULL,
ENTITY_NAME VARCHAR2 (256),
VERSION NUMBER,
UPDATE_DATE DATE,
UPDATE_USER_KEY NUMBER,
VALID_FLAG INTEGER,
CONSTRAINT ENTITY_NAME_T_PK
PRIMARY KEY ( ENTITY_KEY, ENTITY_NAME_TYPE_KEY )
USING INDEX
TABLESPACE INDX PCTFREE 10
STORAGE ( INITIAL 120586240 NEXT 5242880 PCTINCREASE 0 ))
TABLESPACE USERS
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 314572800
NEXT 503365632
PCTINCREASE 50
MINEXTENTS 1
MAXEXTENTS 249
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;
ALTER TABLE ENTITY_NAME_T ADD
FOREIGN KEY (ENTITY_NAME_TYPE_KEY)
REFERENCES PCA.ENTITY_NAME_TYPE_T (ENTITY_NAME_TYPE_KEY) ;
ALTER TABLE ENTITY_NAME_T ADD
FOREIGN KEY (ENTITY_KEY)
REFERENCES PCA.ENTITY_T (ENTITY_KEY) ;
CREATE UNIQUE INDEX ENTITY_NAME_T_I3 ON
ENTITY_NAME_T(ENTITY_NAME_TYPE_KEY, ENTITY_NAME, ENTITY_KEY)
TABLESPACE INDX PCTFREE 10 STORAGE(INITIAL 1024000 NEXT 1024000 PCTINCREASE 0 )
;
Please help.
|
|
|
|
| Re: Deleting & Inserting data on tables having indexes [message #10826 is a reply to message #10798] |
Wed, 18 February 2004 23:10  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
What about indexes on your entity_rel_t table? Are your tables, indexes, and indexed columns analyzed? Please try the following and if it does not improve things, then please post your complete procedure.
CREATE INDEX entity_rel_t_idx
ON entity_rel_t (child_entity_key, parent_entity_key, entity_rel_type_key)
/
ANALYZE TABLE entity_name_t
COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS
/
ANALYZE TABLE entity_rel_t
COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS
/
DELETE FROM entity_name_t e
WHERE e.entity_key =
(SELECT t1.child_entity_key
FROM entity_rel_t t1, entity_rel_t t2
WHERE t1.parent_entity_key = t2.child_entity_key
AND t2.parent_entity_key = 6424831
AND t2.entity_rel_type_key IN (7, 6, 9)
AND t1.entity_rel_type_key = 8
AND t1.child_entity_key = e.entity_key)
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
