Home » RDBMS Server » Performance Tuning » optimization of a query
optimization of a query [message #320085] |
Wed, 14 May 2008 01:23 |
user71408
Messages: 585 Registered: November 2007 Location: NE
|
Senior Member |
|
|
Hi all,
Please give me ideas to optimize the following query,,
SELECT req.bank_code bankcode,
req.branch_code branchcode,
req.running_serial_number rrrnumber,
usr.email_id emailid,
br.alert_email_address alertemail,
mtmpl.template_id templateid,
mtmpl.template_directory templatefile
FROM
TF_PAR_BRANCH_PERSONALIZATION br,
TF_PRF_USERPROFILE usr,
TF_M_WORK_FLOW wf,
TF_M_MAIL_TEMPLATE_DETAIL mtmpl ,
TF_IMP_LC_REQUEST req,
TF_TMP_LC_AMEND tmpamd
WHERE
tmpamd.branch_code = req.branch_code
AND tmpamd.bank_code = req.bank_code
AND tmpamd.new_value = req.running_serial_number
AND mtmpl.functionality_code = wf.functionality_code
AND req.bank_code = wf.bank_code AND
br.bank_code = req.bank_code AND
req.branch_code = br.branch_code AND
wf.functionality_code= i_funcCode AND
usr.deleted_indicator = 'N' AND
((wf.OPERATOR = 'Y' AND req.bank_code = usr.bank_code AND
req.branch_code = usr.branch_code AND usr.usertype_code = 'OPR') OR
(wf.customer_operator = 'Y' AND req.anser_user_number IS
NULL AND req.bank_code = usr.bank_code AND req.branch_code =
usr.branch_code AND usr.user_id = req.created_by AND usr.customer_id =
req.customer_id AND usr.usertype_code = 'CGO' and
req.MIGRATED_TRANSACTION is null) OR
(wf.customer_operator = 'Y' AND req.anser_user_number IS
NOT NULL AND req.ANSER_USER_NUMBER = usr.ANSER_USER_NUMBER AND
req.bank_code = usr.bank_code AND req.branch_code = usr.branch_code AND
usr.user_id = req.created_by AND usr.customer_id = req.customer_id AND
usr.usertype_code = 'CGO' and req.MIGRATED_TRANSACTION is null) OR
(wf.customer_manager = 'Y' AND
usr.lcopen_accept_available IS NOT NULL AND
usr.lcopen_accept_available = 'Y'
AND req.anser_user_number IS NOT NULL AND req.bank_code
= usr.bank_code
AND req.branch_code = usr.branch_code AND
usr.customer_id = req.customer_id AND usr.usertype_code = 'CGO'and
req.MIGRATED_TRANSACTION is null) OR
(wf.customer_manager = 'Y' AND req.bank_code =
usr.bank_code AND req.branch_code = usr.branch_code AND usr.customer_id
= req.customer_id AND usr.usertype_code = 'CGM' and
req.MIGRATED_TRANSACTION is null ) OR
usr.customer_id = req.customer_id AND usr.usertype_code IN ( 'CST',
'CGM','CGO')) OR
(wf.bpo = 'Y' AND req.BPO_BIC_CODE = usr.BPO_BIC_CODE AND
usr.usertype_code IN ('BPA','BPN')) OR
(wf.power_operator = 'Y' AND req.bank_code = usr.bank_code
AND usr.usertype_code IN ('PWA','PWN')) OR
(wf.oco = 'Y' AND req.bank_code = usr.bank_code AND
usr.usertype_code ='OCO'));
And the Plan table is as follows
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'SELECT STATEMENT'
, NULL, NULL, NULL, NULL, NULL, NULL, 'ALL_ROWS', NULL, 0, NULL, 4, 4, 1, 162, NULL
, NULL, NULL, NULL, NULL, NULL, 4, NULL, NULL, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'FILTER'
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 0, 1, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '"SYS_ALIAS_2"."USERTYPE_CODE"=''ASP'' OR "SYS_ALIAS_2"."USERTYPE_CODE"=''BPA'' OR "SYS_ALIAS_2"."USERTYPE_CODE"=''BPN'' OR ("SYS_ALIAS_2"."USERTYPE_CODE"=''CGM'' OR "SYS_ALIAS_2"."USERTYPE_CODE"=''CGO'' OR "SYS_ALIAS_2"."USERTYPE_CODE"=''OCO'' OR "SYS_ALIAS_2"."USERTYPE_CODE"=''OPR'' OR "SYS_ALIAS_2"."USERTYPE_CODE"=''PWA'' OR "SYS_ALIAS_2"."USERTYPE_CODE"=''PWN'') AND "B"."DELETED_INDICATOR"=''N'' AND "C"."DELETED_INDICATOR"=''N''');
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'NESTED LOOPS'
, 'OUTER', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, 1, 1, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'NESTED LOOPS'
, 'OUTER', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 3, 2, 1, 3, 1, 125, NULL, NULL
, NULL, NULL, NULL, NULL, 3, NULL, NULL, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'NESTED LOOPS'
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4, 3, 1, 2, 1, 113, NULL, NULL, NULL
, NULL, NULL, NULL, 2, NULL, NULL, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'TABLE ACCESS'
, 'BY INDEX ROWID', NULL, 'SUNITHA', 'TF_PAR_SYSCONFIGURATION', 6, NULL, 'ANALYZED'
, NULL, 5, 4, 1, 1, 1, 16, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'INDEX'
, 'UNIQUE SCAN', NULL, 'SUNITHA', 'PK_SYSPAR_PARTYP', NULL, 'UNIQUE', 'ANALYZED', 1
, 6, 5, 1, NULL, 19, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '"D"."PARAMETER_TYPE"=''APPLICATION_DT'''
, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'TABLE ACCESS'
, 'BY INDEX ROWID', NULL, 'SUNITHA', 'TF_PRF_USERPROFILE', 3, NULL, 'ANALYZED', NULL
, 7, 4, 2, 1, 1, 97, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, '"SYS_ALIAS_2"."DELETED_INDICATOR"=''N''');
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'INDEX'
, 'UNIQUE SCAN', NULL, 'SUNITHA', 'PK_USR_USRID', NULL, 'UNIQUE', 'ANALYZED', 1, 8
, 7, 1, NULL, 82, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '"SYS_ALIAS_2"."USER_ID"=''G6565PO1'''
, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'TABLE ACCESS'
, 'BY INDEX ROWID', NULL, 'SUNITHA', 'TF_PAR_BRANCH_PERSONALIZATION', 5, NULL, 'ANALYZED'
, NULL, 9, 3, 2, 1, 1, 12, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'INDEX'
, 'UNIQUE SCAN', NULL, 'SUNITHA', 'PK_BRPERSON_BRCDBNKCD', NULL, 'UNIQUE', 'ANALYZED'
, 2, 10, 9, 1, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '"SYS_ALIAS_2"."BRANCH_CODE"="C"."BRANCH_CODE"(+) AND "SYS_ALIAS_2"."BANK_CODE"="C"."BANK_CODE"(+)'
, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'TABLE ACCESS'
, 'BY INDEX ROWID', NULL, 'SUNITHA', 'TF_PAR_BANK_PERSONALIZATION', 4, NULL, 'ANALYZED'
, NULL, 11, 2, 2, 1, 1, 37, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'INDEX'
, 'UNIQUE SCAN', NULL, 'SUNITHA', 'PK_BNKPERSON_BANKCODE', NULL, 'UNIQUE', 'ANALYZED'
, 1, 12, 11, 1, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '"SYS_ALIAS_2"."BANK_CODE"="B"."BANK_CODE"(+)'
, NULL);
commit;
Thank u
|
|
|
optimization of a query [message #320087 is a reply to message #320085] |
Wed, 14 May 2008 01:24 |
user71408
Messages: 585 Registered: November 2007 Location: NE
|
Senior Member |
|
|
Hi all,
Please give me ideas to optimize the following query,,
SELECT req.bank_code bankcode,
req.branch_code branchcode,
req.running_serial_number rrrnumber,
usr.email_id emailid,
br.alert_email_address alertemail,
mtmpl.template_id templateid,
mtmpl.template_directory templatefile
FROM
TF_PAR_BRANCH_PERSONALIZATION br,
TF_PRF_USERPROFILE usr,
TF_M_WORK_FLOW wf,
TF_M_MAIL_TEMPLATE_DETAIL mtmpl ,
TF_IMP_LC_REQUEST req,
TF_TMP_LC_AMEND tmpamd
WHERE
tmpamd.branch_code = req.branch_code
AND tmpamd.bank_code = req.bank_code
AND tmpamd.new_value = req.running_serial_number
AND mtmpl.functionality_code = wf.functionality_code
AND req.bank_code = wf.bank_code AND
br.bank_code = req.bank_code AND
req.branch_code = br.branch_code AND
wf.functionality_code= i_funcCode AND
usr.deleted_indicator = 'N' AND
((wf.OPERATOR = 'Y' AND req.bank_code = usr.bank_code AND
req.branch_code = usr.branch_code AND usr.usertype_code = 'OPR') OR
(wf.customer_operator = 'Y' AND req.anser_user_number IS
NULL AND req.bank_code = usr.bank_code AND req.branch_code =
usr.branch_code AND usr.user_id = req.created_by AND usr.customer_id =
req.customer_id AND usr.usertype_code = 'CGO' and
req.MIGRATED_TRANSACTION is null) OR
(wf.customer_operator = 'Y' AND req.anser_user_number IS
NOT NULL AND req.ANSER_USER_NUMBER = usr.ANSER_USER_NUMBER AND
req.bank_code = usr.bank_code AND req.branch_code = usr.branch_code AND
usr.user_id = req.created_by AND usr.customer_id = req.customer_id AND
usr.usertype_code = 'CGO' and req.MIGRATED_TRANSACTION is null) OR
(wf.customer_manager = 'Y' AND
usr.lcopen_accept_available IS NOT NULL AND
usr.lcopen_accept_available = 'Y'
AND req.anser_user_number IS NOT NULL AND req.bank_code
= usr.bank_code
AND req.branch_code = usr.branch_code AND
usr.customer_id = req.customer_id AND usr.usertype_code = 'CGO'and
req.MIGRATED_TRANSACTION is null) OR
(wf.customer_manager = 'Y' AND req.bank_code =
usr.bank_code AND req.branch_code = usr.branch_code AND usr.customer_id
= req.customer_id AND usr.usertype_code = 'CGM' and
req.MIGRATED_TRANSACTION is null ) OR
usr.customer_id = req.customer_id AND usr.usertype_code IN ( 'CST',
'CGM','CGO')) OR
(wf.bpo = 'Y' AND req.BPO_BIC_CODE = usr.BPO_BIC_CODE AND
usr.usertype_code IN ('BPA','BPN')) OR
(wf.power_operator = 'Y' AND req.bank_code = usr.bank_code
AND usr.usertype_code IN ('PWA','PWN')) OR
(wf.oco = 'Y' AND req.bank_code = usr.bank_code AND
usr.usertype_code ='OCO'));
Thank u
|
|
|
Re: optimization of a query [message #320088 is a reply to message #320087] |
Wed, 14 May 2008 01:25 |
user71408
Messages: 585 Registered: November 2007 Location: NE
|
Senior Member |
|
|
And the Plan table is as follows
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'SELECT STATEMENT'
, NULL, NULL, NULL, NULL, NULL, NULL, 'ALL_ROWS', NULL, 0, NULL, 4, 4, 1, 162, NULL
, NULL, NULL, NULL, NULL, NULL, 4, NULL, NULL, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE,
OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START,
PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'FILTER'
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 0, 1, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '"SYS_ALIAS_2"."USERTYPE_CODE"=''ASP'' OR "SYS_ALIAS_2"."USERTYPE_CODE"=''BPA'' OR "SYS_ALIAS_2"."USERTYPE_CODE"=''BPN'' OR ("SYS_ALIAS_2"."USERTYPE_CODE"=''CGM'' OR "SYS_ALIAS_2"."USERTYPE_CODE"=''CGO'' OR "SYS_ALIAS_2"."USERTYPE_CODE"=''OCO'' OR "SYS_ALIAS_2"."USERTYPE_CODE"=''OPR'' OR "SYS_ALIAS_2"."USERTYPE_CODE"=''PWA'' OR "SYS_ALIAS_2"."USERTYPE_CODE"=''PWN'') AND "B"."DELETED_INDICATOR"=''N'' AND "C"."DELETED_INDICATOR"=''N''');
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'NESTED LOOPS'
, 'OUTER', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, 1, 1,
NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE,
OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START,
PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'NESTED LOOPS'
, 'OUTER', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 3, 2, 1,
3, 1, 125, NULL, NULL
, NULL, NULL, NULL, NULL, 3, NULL, NULL, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS,
OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE,
OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START,
PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'NESTED LOOPS'
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4, 3, 1, 2, 1, 113, NULL, NULL, NULL
, NULL, NULL, NULL, 2, NULL, NULL, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS,
OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE,
OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS
AM'), NULL, 'TABLE ACCESS'
, 'BY INDEX ROWID', NULL, 'SUNITHA', 'TF_PAR_SYSCONFIGURATION',
6, NULL, 'ANALYZED'
, NULL, 5, 4, 1, 1, 1, 16, NULL, NULL, NULL, NULL, NULL, NULL,
1, NULL, NULL, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS,
OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START,
PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS
AM'), NULL, 'INDEX'
, 'UNIQUE SCAN', NULL, 'SUNITHA', 'PK_SYSPAR_PARTYP',
NULL, 'UNIQUE', 'ANALYZED', 1
, 6, 5, 1, NULL, 19, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, '"D"."PARAMETER_TYPE"=''APPLICATION_DT'''
, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS,
OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE,
OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START,
PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS
AM'), NULL, 'TABLE ACCESS'
, 'BY INDEX ROWID', NULL, 'SUNITHA', 'TF_PRF_USERPROFILE', 3,
NULL, 'ANALYZED', NULL
, 7, 4, 2, 1, 1, 97, NULL, NULL, NULL, NULL, NULL, NULL, 1,
NULL, NULL, '"SYS_ALIAS_2"."DELETED_INDICATOR"=''N''');
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS,
OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE,
OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START,
PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'INDEX'
, 'UNIQUE SCAN', NULL, 'SUNITHA', 'PK_USR_USRID', NULL, 'UNIQUE', 'ANALYZED', 1, 8
, 7, 1, NULL, 82, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '"SYS_ALIAS_2"."USER_ID"=''G6565PO1'''
, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS,
OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE,
OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START,
PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'TABLE ACCESS'
, 'BY INDEX ROWID', NULL, 'SUNITHA', 'TF_PAR_BRANCH_PERSONALIZATION', 5, NULL, 'ANALYZED'
, NULL, 9, 3, 2, 1, 1, 12, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS,
OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE,
OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START,
PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS
AM'), NULL, 'INDEX'
, 'UNIQUE SCAN', NULL, 'SUNITHA', 'PK_BRPERSON_BRCDBNKCD', NULL, 'UNIQUE', 'ANALYZED'
, 2, 10, 9, 1, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '"SYS_ALIAS_2"."BRANCH_CODE"="C"."BRANCH_CODE"(+) AND "SYS_ALIAS_2"."BANK_CODE"="C"."BANK_CODE"(+)'
, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS,
OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE,
OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START,
PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'TABLE ACCESS'
, 'BY INDEX ROWID', NULL, 'SUNITHA', 'TF_PAR_BANK_PERSONALIZATION', 4, NULL, 'ANALYZED'
, NULL, 11, 2, 2, 1, 1, 37, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL);
INSERT INTO PLAN_TABLE ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID,
DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES,
FILTER_PREDICATES ) VALUES (
NULL, TO_Date( '02/06/2008 11:23:55 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 'INDEX'
, 'UNIQUE SCAN', NULL, 'SUNITHA', 'PK_BNKPERSON_BANKCODE', NULL, 'UNIQUE', 'ANALYZED'
, 1, 12, 11, 1, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '"SYS_ALIAS_2"."BANK_CODE"="B"."BANK_CODE"(+)'
, NULL);
commit;
[Updated on: Wed, 14 May 2008 01:30] Report message to a moderator
|
|
|
Re: optimization of a query [message #320098 is a reply to message #320088] |
Wed, 14 May 2008 01:42 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Posted twice.
Twice as bad.
I think you should read about execution, what is it, what is plan table, what is the output and so on, this should be your first step, there it seems you even don't understand what you post.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Fri Dec 06 14:04:26 CST 2024
|