OraFAQ Forum: Performance Tuning » optimization of a query

Home » RDBMS Server » Performance Tuning » optimization of a query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

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

Report message to a moderator

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: 68716
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

Report message to a moderator

Previous Topic:	Error while creating partitioned table
Next Topic:	What the statistics in Explain plan suggests? (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 01:46:32 CST 2024