Hello,
Can anyone help.I have a query running against a table GL_INTERFACE, the table is indexed on fields GROUP_ID,USER_JE_SOURCE_NAME,SET_OF_BOOKS_ID & STATUS, but the query is doing a table scan instead of using the indexes. The table stats are up to date. Any ideas why the cost based optimer is chosing to do a table scan and not use the index.?
1 TABLE ACCESS FULL GL.GL_INTERFACE "INT"."STATUS"<>'PROCESSED' AND "GROUP_ID"=2644922 AND "INT"."USER_JE_SOURCE_NAME"='BoF Import' AND "INT"."SET_OF_BOOKS_ID"=362Cost : 11,356 Bytes : 289 Cardinality : 1
SELECT INT.ROWID,
DECODE(INT.segment1 || INT.segment2 || INT.segment3 || INT.segment4 || INT.segment5, '', REPLACE(ccid_cc.segment2, '.', '
') || '.' || REPLACE(ccid_cc.segment1, '.', '
') || '.' || REPLACE(ccid_cc.segment3, '.', '
') || '.' || REPLACE(ccid_cc.segment4, '.', '
') || '.' || REPLACE(ccid_cc.segment5, '.', '
'), REPLACE(INT.segment2, '.', '
') || '.' || REPLACE(INT.segment1, '.', '
') || '.' || REPLACE(INT.segment3, '.', '
') || '.' || REPLACE(INT.segment4, '.', '
') || '.' || REPLACE
(INT.segment5, '.', '
')) flexfield,
NVL(flex_cc.code_combination_id, NVL(INT.code_combination_id, -4)),
DECODE(INT.segment1 || INT.segment2 || INT.segment3 || INT.segment4 || INT.segment5, '', DECODE(ccid_cc.code_combination_id, NULL, DECODE(INT.code_combination_id, NULL, -4, -5), DECODE(SIGN(NVL(ccid_cc.start_date_active, INT.accounting_date - 1) - INT.accounting_date), 1, -1, DECODE(SIGN(NVL(ccid_cc.end_date_active, INT.accounting_date + 1) - INT.accounting_date), -1, -1, 0)) + DECODE(ccid_cc.enabled_flag, 'N', -10, 0) + DECODE(ccid_cc.summary_flag, 'Y', -100, DECODE(INT.actual_flag, 'B', DECODE
(ccid_cc.detail_budgeting_allowed_flag, 'N', -100, 0), DECODE(ccid_cc.detail_posting_allowed_flag, 'N', -100, 0)))), DECODE(flex_cc.code_combination_id, NULL, -4, DECODE(SIGN(NVL(flex_cc.start_date_active, INT.accounting_date - 1) - INT.accounting_date), 1, -1, DECODE(SIGN(NVL(flex_cc.end_date_active, INT.accounting_date + 1) - INT.accounting_date), -1, -1, 0)) + DECODE(flex_cc.enabled_flag, 'N', -10, 0) + DECODE(flex_cc.summary_flag, 'Y', -100, DECODE(INT.actual_flag, 'B', DECODE(flex_cc.detail_budgeting_allowed_flag
, 'N', -100, 0), DECODE(flex_cc.detail_posting_allowed_flag, 'N', -100, 0))))),
INT.user_je_category_name,
INT.user_je_category_name,
'UNKNOWN' period_name,
DECODE(actual_flag, 'B', DECODE(period_name, NULL, '-1', period_name), NVL(period_name, '0')) period_name2,
currency_code,
DECODE(actual_flag, 'A', actual_flag, 'B', DECODE(budget_version_id, 1181, actual_flag, 1182, actual_flag, 1183, actual_flag, 1184, actual_flag, 1185, actual_flag, 1186, actual_flag, 1187, actual_flag, 1188, actual_flag, 1189, actual_flag, 1190, actual_flag, 1191, actual_flag, 1192, actual_flag, 1193, actual_flag, 1194, actual_flag, 1195, actual_flag, 1196, actual_flag, 1197, actual_flag, 1198, actual_flag, 1199, actual_flag, 1200, actual_flag, 1201, actual_flag, 1241, actual_flag, 1281, '2'
, 1301, actual_flag, NULL, '1', '6'), 'E', DECODE(encumbrance_type_id, 1000, actual_flag, 1001, actual_flag, 1020, actual_flag, 999, actual_flag, NULL, '3', '4'), '5') actual_flag,
'0' exception_rate,
DECODE(currency_code, 'GBP', 1, 'STAT', 1, DECODE(actual_flag, 'E', -8, 'B', 1, DECODE(user_currency_conversion_type, 'User', DECODE(currency_conversion_rate, NULL, -1, currency_conversion_rate), 'Corporate', DECODE(currency_conversion_date, NULL, -2, -6), 'Spot', DECODE(currency_conversion_date, NULL, -2, -6), 'Daily/Weekly End Rate', DECODE(currency_conversion_date, NULL, -2, -6), 'YTD Average Rate', DECODE(currency_conversion_date, NULL, -2, -6), 'BDS_AVG RATE', DECODE(currency_conversion_date
, NULL, -2, -6), NULL, DECODE(currency_conversion_rate, NULL, DECODE(DECODE(NVL(TO_CHAR(entered_dr), 'X'), 'X', 1, 2), DECODE(NVL(TO_CHAR(accounted_dr), 'X'), 'X', 1, 2), DECODE(DECODE(NVL(TO_CHAR(entered_cr), 'X'), 'X', 1, 2), DECODE(NVL(TO_CHAR(accounted_cr), 'X'), 'X', 1, 2), -20, -3), -3), -9), -9))) currency_conversion_rate,
TO_NUMBER(TO_CHAR(NVL(INT.currency_conversion_date, INT.accounting_date), 'J')),
DECODE(INT.actual_flag, 'A', DECODE(INT.currency_code, 'GBP', 'User', 'STAT', 'User', NVL(INT.user_currency_conversion_type, 'User')), 'B', 'User', 'E', 'User', NVL(INT.user_currency_conversion_type, 'User')) user_currency_conversion_type,
LTRIM(RTRIM(SUBSTRB(RTRIM(SUBSTRB(INT.reference1, 1, 50)) || ' ' || INT.user_je_source_name || ' 2644932: ' || INT.actual_flag || ' ' || INT.GROUP_ID, 1, 100))),
RTRIM(SUBSTRB(NVL(RTRIM(INT.reference2), 'Journal Import ' || INT.user_je_source_name || ' 2644932:'), 1, 240)),
LTRIM(RTRIM(SUBSTRB(RTRIM(RTRIM(SUBSTRB(INT.reference4, 1, 25)) || ' ' || INT.user_je_category_name || ' ' || INT.currency_code || DECODE(INT.actual_flag, 'E', ' ' || INT.encumbrance_type_id, 'B', ' ' || INT.budget_version_id, '') || ' ' || INT.user_currency_conversion_type || ' ' || DECODE(INT.user_currency_conversion_type, NULL, '', 'User', TO_CHAR(INT.currency_conversion_rate), TO_CHAR(INT.currency_conversion_date))) || ' ' || SUBSTRB(INT.reference8, 1, 15) || INT.originating_bal_seg_value
, 1, 100))),
RTRIM(NVL(RTRIM(INT.reference5), 'Journal Import 2644932:')),
RTRIM(SUBSTRB(NVL(RTRIM(INT.reference6), 'Journal Import Created'), 1, 80)),
RTRIM(DECODE(UPPER(SUBSTRB(NVL(RTRIM(INT.reference7), 'N'), 1, 1)), 'Y', 'Y', 'N')),
DECODE(UPPER(SUBSTRB(INT.reference7, 1, 1)), 'Y', DECODE(RTRIM(reference8), NULL, '-1', RTRIM(SUBSTRB(reference8, 1, 15))), NULL),
RTRIM(UPPER(SUBSTRB(INT.reference9, 1, 1))),
RTRIM(NVL(RTRIM(INT.reference10), NVL(TO_CHAR(INT.subledger_doc_sequence_value), 'Journal Import Created'))),
INT.entered_dr,
INT.entered_cr,
TO_NUMBER(TO_CHAR(INT.accounting_date, 'J')),
TO_CHAR(INT.accounting_date, 'YYYY/MM/DD'),
INT.user_je_source_name,
NVL(INT.encumbrance_type_id, -1),
NVL(INT.budget_version_id, -1),
NULL,
INT.stat_amount,
DECODE(INT.actual_flag, 'E', DECODE(INT.currency_code, 'STAT', '1', '0'), '0'),
DECODE(INT.actual_flag, 'A', DECODE(INT.budget_version_id, NULL, DECODE(INT.encumbrance_type_id, NULL, '0', '1'), DECODE(INT.encumbrance_type_id, NULL, '2', '3')), 'B', DECODE(INT.encumbrance_type_id, NULL, '0', '4'), 'E', DECODE(INT.budget_version_id, NULL, '0', '5'), '0'),
INT.accounted_dr,
INT.accounted_cr,
NVL(INT.GROUP_ID, -1),
NVL(INT.average_journal_flag, 'N'),
INT.originating_bal_seg_value
FROM gl_interface INT,
gl_code_combinations flex_cc,
gl_code_combinations ccid_cc
WHERE INT.set_of_books_id = 362
AND INT.status != 'PROCESSED'
AND (INT.user_je_source_name, GROUP_ID) IN (('BoF Import', 2644922))
AND flex_cc.segment1 (+) = INT.segment1
AND flex_cc.segment2 (+) = INT.segment2
AND flex_cc.segment3 (+) = INT.segment3
AND flex_cc.segment4 (+) = INT.segment4
AND flex_cc.segment5 (+) = INT.segment5
AND flex_cc.chart_of_accounts_id (+) = 50778
AND flex_cc.template_id (+) IS NULL
AND ccid_cc.code_combination_id (+) = INT.code_combination_id
AND ccid_cc.chart_of_accounts_id (+) = 50778
AND ccid_cc.template_id (+) IS NULL
ORDER BY DECODE(INT.segment1 || INT.segment2 || INT.segment3 || INT.segment4 || INT.segment5, '', RPAD(ccid_cc.segment2, 30) || '.' || RPAD(ccid_cc.segment1, 30) || '.' || RPAD(ccid_cc.segment3, 30) || '.' || RPAD(ccid_cc.segment4, 30) || '.' || RPAD(ccid_cc.segment5, 30), RPAD(INT.segment2, 30) || '.' || RPAD(INT.segment1, 30) || '.' || RPAD(INT.segment3, 30) || '.' || RPAD(INT.segment4, 30) || '.' || RPAD(INT.segment5, 30)), INT.entered_dr, INT.accounted_dr, INT.entered_cr, INT.accounted_cr, INT
.accounting_date
|
] 
Blog
Search
Help
Members
Register
Login
Home
