Home » RDBMS Server » Performance Tuning » performance tuning
performance tuning [message #220773] Thu, 22 February 2007 01:39 Go to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

Here is the table scripts which I ranned the below updated view querry.
update
(
select a.auto_approve_flag ,
a.auto_approve_window ,
a.auto_approve_flag_sys ,
a.use_system_logic ,
a.sys_last_modified_date ,
a.sys_ent_state,
a.item_id,
a.location_id
from mst_do_approve a , mst_item b , mst_location c
where a.location_id = c.location_id
and a.item_id = b.item_id
and c.location_category in ('RETAIL','DIRECT','INSURANCE','DHRP','PRDC')
and b.category in ('SPARES','ACCESSORIES')
and b.scenario_id = 0
and ( nvl(b.is_bod_initialized,'0') = '0' or nvl(c.is_rp_initialized,'0') = '0')
and a.sys_ent_state = 'ACTIVE' )
set auto_approve_flag = 1 , auto_approve_window = 7 , auto_approve_flag_sys = 0 , use_system_logic = 0 , sys_last_modified_date = sysdate where sys_ent_state = 'ACTIVE'

When I tried to run the above querry. Its giving an error as
ORA:1779: Oracle cannot modify a column which maps to a non key preserved table.




1.1900 records
CREATE TABLE MST_ITEM
(
SCENARIO_ID INTEGER DEFAULT 0 NOT NULL,
IS_VIRTUAL CHAR(1 BYTE) DEFAULT 0,
UOM VARCHAR2(10 BYTE),
CATEGORY VARCHAR2(40 BYTE),
CURRENCY_ID VARCHAR2(40 BYTE),
DESCRIPTION VARCHAR2(200 BYTE) NOT NULL,
EFF_END_DATE DATE,
EFF_START_DATE DATE,
ITEM_ID VARCHAR2(40 BYTE) NOT NULL,
ITEM_LEVEL VARCHAR2(40 BYTE),
LIFE_CYC_STG VARCHAR2(40 BYTE),
LOT_SERIAL_MANAGED VARCHAR2(1 BYTE),
MFR_ITEM_DESC VARCHAR2(200 BYTE),
MFR_ITEM_ID VARCHAR2(40 BYTE),
NAME VARCHAR2(50 BYTE) NOT NULL,
QUANTITY_MULTIPLE NUMBER(15,4),
STATUS VARCHAR2(40 BYTE),
SUB_CATEGORY VARCHAR2(40 BYTE),
LIST_PRICE NUMBER(38,4),
UNIT_COST NUMBER(38,4),
ORG_ID VARCHAR2(40 BYTE),
MFGR_ID VARCHAR2(40 BYTE),
MIN_QTY NUMBER(38,4),
ACC_UOM VARCHAR2(10 BYTE),
ALT_UOM1 VARCHAR2(10 BYTE),
ALT_UOM2 VARCHAR2(10 BYTE),
ALT_UOM3 VARCHAR2(10 BYTE),
ALT_CONV_FACT1 NUMBER(38,4),
ALT_CONV_FACT2 NUMBER(38,4),
ALT_CONV_FACT3 NUMBER(38,4),
IS_SALEABLE VARCHAR2(24 BYTE),
IS_PURCHASEABLE VARCHAR2(24 BYTE),
IS_MANUAL_REPLENISHMENT VARCHAR2(24 BYTE),
PSOFT_ITEM_ID VARCHAR2(40 BYTE),
PRIMARY_VENDOR VARCHAR2(40 BYTE) NOT NULL,
RETURN_ITEM_ID VARCHAR2(40 BYTE),
REFURB_ITEM_ID VARCHAR2(40 BYTE),
IS_CUSTOMER_COLLABORATED CHAR(1 BYTE) DEFAULT 0,
IS_SUPPLIER_COLLABORATED CHAR(1 BYTE) DEFAULT 0,
PLANNED_BY_I2 CHAR(1 BYTE),
PO_VENDOR VARCHAR2(40 BYTE),
IS_BOD_INITIALIZED CHAR(1 BYTE),
CONV_TO_EA NUMBER(15,4),
LEVELID VARCHAR2(200 BYTE) NOT NULL,
SYS_TARGET_ID VARCHAR2(25 BYTE),
SYS_AUTH_ID VARCHAR2(25 BYTE),
SYS_SOURCE VARCHAR2(25 BYTE) DEFAULT 'BackEnd',
SYS_CREATED_BY VARCHAR2(70 BYTE),
SYS_CREATION_DATE DATE,
SYS_ENT_STATE VARCHAR2(25 BYTE),
SYS_LAST_MODIFIED_BY VARCHAR2(70 BYTE),
SYS_LAST_MODIFIED_DATE DATE,
SYS_NC_TYPE VARCHAR2(25 BYTE),
UPDATE_ID VARCHAR2(100 BYTE)
)



CREATE INDEX CATEGORY ON MST_ITEM
(CATEGORY)



CREATE INDEX IDX_288 ON MST_ITEM
(SCENARIO_ID)



CREATE INDEX IDX_289 ON MST_ITEM
(ORG_ID)



CREATE INDEX IDX_290 ON MST_ITEM
(ACC_UOM)



CREATE INDEX IDX_291 ON MST_ITEM
(UOM)
LOGGING



CREATE INDEX IDX_292 ON MST_ITEM
(PRIMARY_VENDOR)



CREATE INDEX IS_BOD_INIT ON MST_ITEM
(IS_BOD_INITIALIZED)




CREATE INDEX IS_SUP_COLLAB ON MST_ITEM
(IS_SUPPLIER_COLLABORATED)



CREATE INDEX MST_ITEM_I0 ON MST_ITEM
(PRIMARY_VENDOR, SCENARIO_ID)



CREATE INDEX MST_ITEM_I1 ON MST_ITEM
(ORG_ID, SCENARIO_ID)



CREATE INDEX MST_ITEM_I2 ON MST_ITEM
(ACC_UOM, SCENARIO_ID)


CREATE INDEX MST_ITEM_I3 ON MST_ITEM
(UOM, SCENARIO_ID)



CREATE UNIQUE INDEX MST_ITEM_PK ON MST_ITEM
(SCENARIO_ID, ITEM_ID)



ALTER TABLE MST_ITEM ADD (
CONSTRAINT MST_ITEM_C0 CHECK ( IS_VIRTUAL IN ('0', '1' )));

ALTER TABLE MST_ITEM ADD (
CONSTRAINT MST_ITEM_C1 CHECK ( IS_CUSTOMER_COLLABORATED IN ('1', '0' )));

ALTER TABLE MST_ITEM ADD (
CONSTRAINT MST_ITEM_C2 CHECK ( IS_SUPPLIER_COLLABORATED IN ('1', '0' )));

ALTER TABLE MST_ITEM ADD (
CONSTRAINT MST_ITEM_C3 CHECK ( SYS_ENT_STATE IN ('ACTIVE', 'INACTIVE', 'NEW', 'DELETED' )));

ALTER TABLE MST_ITEM ADD (
CONSTRAINT MST_ITEM_PK PRIMARY KEY (SCENARIO_ID, ITEM_ID))





2.MST_LOCATION(1500 records)


CREATE TABLE MST_LOCATION
(
SCENARIO_ID INTEGER DEFAULT 0 NOT NULL,
ADDRESS_ID VARCHAR2(40 BYTE),
CALENDAR_ID VARCHAR2(40 BYTE),
CATEGORY_PARENT_ID VARCHAR2(40 BYTE),
CLIMATE VARCHAR2(50 BYTE),
DFLT_STORAGE_LOCATION_ID VARCHAR2(64 BYTE),
DEMOGRAPHIC VARCHAR2(50 BYTE),
DESCRIPTION VARCHAR2(255 BYTE) NOT NULL,
INVENTORY_ORGANIZATION_ID VARCHAR2(40 BYTE),
LOCATION_CATEGORY VARCHAR2(40 BYTE),
LOCATION_CODE VARCHAR2(32 BYTE),
LOCATION_ID VARCHAR2(40 BYTE) NOT NULL,
LOCATION_OWNER VARCHAR2(40 BYTE),
LONG_NAME VARCHAR2(64 BYTE),
NAME VARCHAR2(32 BYTE) NOT NULL,
OWNER_ORGANIZATION_ID VARCHAR2(40 BYTE),
PARENT_LOCATION_ID VARCHAR2(40 BYTE),
PRIORITY NUMBER(31,2),
REGION VARCHAR2(50 BYTE),
REVIEW_FENCE NUMBER(14),
SIZE_CODE VARCHAR2(50 BYTE),
STATUS VARCHAR2(9 BYTE),
TYPE VARCHAR2(40 BYTE),
UD_STORE VARCHAR2(40 BYTE),
VALUATION_CODE VARCHAR2(32 BYTE),
VOLUME_CODE VARCHAR2(50 BYTE),
WEEKLY_REVIEW_FENCE NUMBER(14),
DAILY_REVIEW_FENCE NUMBER(14),
IS_LOGICAL CHAR(1 BYTE),
PSOFT_SHIPTO_CODE VARCHAR2(40 BYTE),
I2_APPROVAL_REQUIRED CHAR(1 BYTE),
IS_RP_INITIALIZED CHAR(1 BYTE),
LEVELID VARCHAR2(200 BYTE) NOT NULL,
SYS_TARGET_ID VARCHAR2(25 BYTE),
SYS_AUTH_ID VARCHAR2(25 BYTE),
SYS_SOURCE VARCHAR2(25 BYTE) DEFAULT 'BackEnd',
SYS_CREATED_BY VARCHAR2(70 BYTE),
SYS_CREATION_DATE DATE,
SYS_ENT_STATE VARCHAR2(25 BYTE),
SYS_LAST_MODIFIED_BY VARCHAR2(70 BYTE),
SYS_LAST_MODIFIED_DATE DATE,
SYS_NC_TYPE VARCHAR2(25 BYTE)
)

CREATE BITMAP INDEX BM01_MST_LOCATION ON MST_LOCATION
(VOLUME_CODE)


CREATE BITMAP INDEX BM02_MST_LOCATION ON MST_LOCATION
(DEMOGRAPHIC)


CREATE INDEX IDX_401 ON MST_LOCATION
(SCENARIO_ID)



CREATE INDEX IDX_402 ON MST_LOCATION
(TYPE)


CREATE INDEX IDX_403 ON MST_LOCATION
(CALENDAR_ID)




CREATE INDEX IDX_404 ON MST_LOCATION
(OWNER_ORGANIZATION_ID)



CREATE INDEX IDX_405 ON MST_LOCATION
(PARENT_LOCATION_ID)



CREATE INDEX IDX_406 ON MST_LOCATION
(ADDRESS_ID)



CREATE INDEX IS_RP_INIT ON MST_LOCATION
(IS_RP_INITIALIZED)



CREATE INDEX LOC_CATEGORY ON MST_LOCATION
(LOCATION_CATEGORY)



CREATE INDEX MST_LOCATION_I0 ON MST_LOCATION
(ADDRESS_ID, SCENARIO_ID)



CREATE INDEX MST_LOCATION_I1 ON MST_LOCATION
(TYPE, SCENARIO_ID)


CREATE INDEX MST_LOCATION_I3 ON MST_LOCATION
(CALENDAR_ID, SCENARIO_ID)


CREATE INDEX MST_LOCATION_I4 ON MST_LOCATION
(OWNER_ORGANIZATION_ID, SCENARIO_ID)


CREATE UNIQUE INDEX MST_LOCATION_PK ON MST_LOCATION
(SCENARIO_ID, LOCATION_ID)


ALTER TABLE MST_LOCATION ADD (
CONSTRAINT MST_LOCATION_C0 CHECK ( SYS_ENT_STATE IN ('ACTIVE', 'INACTIVE', 'NEW', 'DELETED' )));

ALTER TABLE MST_LOCATION ADD (
CONSTRAINT MST_LOCATION_PK PRIMARY KEY (SCENARIO_ID, LOCATION_ID))




3.MST_DO_APPROVE(230000 records)



CREATE TABLE MST_DO_APPROVE
(
SCENARIO_ID INTEGER DEFAULT 0 NOT NULL,
USE_SYSTEM_LOGIC CHAR(1 BYTE) DEFAULT 0,
AUTO_APPROVE_FLAG NUMBER(9),
AUTO_APPROVE_WINDOW NUMBER(14),
ITEM_ID VARCHAR2(128 BYTE) NOT NULL,
ITEM_STATUS_ID NUMBER(38,4),
LOCATION_ID VARCHAR2(200 BYTE) NOT NULL,
SERVICE_NAME VARCHAR2(128 BYTE),
TRANS_MODE_ID VARCHAR2(128 BYTE),
TRANSAGENT_ID VARCHAR2(128 BYTE),
TRANSPORT_TYPE VARCHAR2(128 BYTE),
AUTO_APPROVE_FLAG_SYS CHAR(1 BYTE),
SYS_TARGET_ID VARCHAR2(25 BYTE),
SYS_AUTH_ID VARCHAR2(25 BYTE),
SYS_SOURCE VARCHAR2(25 BYTE) DEFAULT 'BackEnd',
SYS_CREATED_BY VARCHAR2(70 BYTE),
SYS_CREATION_DATE DATE,
SYS_ENT_STATE VARCHAR2(25 BYTE),
SYS_LAST_MODIFIED_BY VARCHAR2(70 BYTE),
SYS_LAST_MODIFIED_DATE DATE,
SYS_NC_TYPE VARCHAR2(25 BYTE)
)


CREATE INDEX IDX_253 ON MST_DO_APPROVE
(SCENARIO_ID)



CREATE INDEX IDX_254 ON MST_DO_APPROVE
(ITEM_STATUS_ID)



CREATE INDEX IDX_255 ON MST_DO_APPROVE
(ITEM_ID)



CREATE INDEX IDX_256 ON MST_DO_APPROVE
(LOCATION_ID)



CREATE INDEX MST_DO_APPROVE_I0 ON MST_DO_APPROVE
(ITEM_STATUS_ID, SCENARIO_ID)


CREATE INDEX MST_DO_APPROVE_I1 ON MST_DO_APPROVE
(ITEM_ID, LOCATION_ID, SCENARIO_ID)



CREATE UNIQUE INDEX MST_DO_APPROVE_PK ON MST_DO_APPROVE
(SCENARIO_ID, ITEM_ID, LOCATION_ID)


ALTER TABLE MST_DO_APPROVE ADD (
CONSTRAINT MST_DO_APPROVE_C0 CHECK ( USE_SYSTEM_LOGIC IN ('0', '1' )));

ALTER TABLE MST_DO_APPROVE ADD (
CONSTRAINT MST_DO_APPROVE_C1 CHECK ( SYS_ENT_STATE IN ('ACTIVE', 'INACTIVE', 'NEW', 'DELETED' )));

ALTER TABLE MST_DO_APPROVE ADD (
CONSTRAINT MST_DO_APPROVE_PK PRIMARY KEY (SCENARIO_ID, ITEM_ID, LOCATION_ID)


Can any one help in tuning this querry.

Thanks in advance

Report message to a moderator

Re: performance tuning [message #220781 is a reply to message #220773] Thu, 22 February 2007 02:02 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Hi.

1. There nothing about performance here.

2. Please read the manual about key-preserved/updatable/non-updatable views .

Report message to a moderator

Previous Topic: Questions
Next Topic: update performance
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri May 17 02:06:28 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.