Home » RDBMS Server » Performance Tuning » Index not being used in Oracle 10g (Oracle 10g)
Index not being used in Oracle 10g [message #347640] Fri, 12 September 2008 08:53 Go to next message
Karena_Russell
Messages: 1
Registered: September 2008
Junior Member
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
Re: Index not being used in Oracle 10g [message #347641 is a reply to message #347640] Fri, 12 September 2008 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck

Then provide the requested and usual information.

Regards
Michel
Re: Index not being used in Oracle 10g [message #347642 is a reply to message #347640] Fri, 12 September 2008 09:12 Go to previous message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>Any ideas why the cost based optimer is chosing to do a table scan and not use the index.?
On line 42 an implicit datatype conversion precludes index use which is intuitively obvious to most folks seeing posted SQL.
Previous Topic: TKPROF - High scattered read
Next Topic: New predicate increasing response time from 30 sec to 6 min
Goto Forum:
  


Current Time: Fri Dec 09 09:52:23 CST 2016

Total time taken to generate the page: 0.20721 seconds