example of index suppressed

The conversion function to_number(), causes existing index not chosen.

use to_char function on the right side instead. i.e

WHERE CDT_ACCT_NBR = to_cahr(:1)


SQL> @chk_sqltext

933 DBLN_RPTP SELECT "CDT_ACCT_NBR","CDT_COMPANY_NAME" FROM "DBO"."CUST_DTLS"
933 DBLN_RPTP "B" WHERE TO_NUMBER("CDT_ACCT_NBR")=:1

SQL> @chk_sqlplan

933 DBLN_RPTP 0 3699 SELECT STATEMENT
933 DBLN_RPTP 0 3699 SELECT STATEMENT
933 DBLN_RPTP 1 0 1 TABLE ACCESS CUST_DTLS
933 DBLN_RPTP 1 0 1 TABLE ACCESS CUST_DTLS

SQL> @chk_indcol
Enter value for tbl: CUST_DTLS
old 1: select index_name,column_name,column_position from dba_ind_columns where table_name='&tbl'
new 1: select index_name,column_name,column_position from dba_ind_columns where table_name='CUST_DTLS'

PK_CUST_DTLS
CDT_ACCT_NBR
1