INDEX performance ORACLE

SachinP-Oracle

I have one table called account. The column cust_Ac_no is having NUMERIC DATATYPE. When I am using with single quotes, it is giving faster result but when used WITHOUT single quote it is taking long time. Having same index. Can you please check why it is taking more time with single Quote?

SELECT Customer_Id,Branch_Code FROM Account WHERE Cust_Ac_No = '15965766'; --- This query is giving result in 0.20 seconds

SELECT Customer_Id,Branch_Code FROM Account WHERE Cust_Ac_No = 15965766; -- This query is giving result in 30 seconds

Structure of Account table:

BRANCH_CODE         VARCHAR2(12),

CUST_AC_NO          NUMBER(20) CONSTRAINT Cust_ACNO_nn NOT NULL --- CHECK type constraint

Having 2 indexes and one primary key

CREATE UNIQUE INDEX Account_Ac_Ix ON Account(Cust_Ac_No) REVERSE Tablespace THUNDER parallel 26 Nologging';

ALTER TABLE Account Add CONSTRAINT Account_Pk Primary Key(Cust_Ac_No) Novalidate';

create INDEX ACCOUNT_CUST_IX on ACCOUNT (CUSTOMER_ID) reverse tablespace THUNDER parallel 26 nologging;

Billy Verreynne

You are mistaken by your diagnosis that specifying the account number as a string literal as oppose to a number, is faster.

Likely your are seeing faster performance because the SQL query finds the row in the db buffer cache, as oppose to having to read the row from disk.

Examine the SQL execution plans of both queries, and the amount of LIO and PIO (logical and physical I/O) calls work done, for comparison.

SachinP-Oracle

Explain_Plan.docx

Thanks Billy for your reply and I have attached the explain for both queries. And In fact , the SELECT INTO I am using the my PLSQL procedure and more than 100 sessions are hitting the DB at same and all 100 sessions taking huge time in this SELECT INTO.

SELECT Customer_Id,Branch_Code INTO v_cust,v_branch FROM Account WHERE Cust_Ac_No = 15965766

Billy Verreynne

FTS is unexpected. The index is not used with the number value specified.

Please use DBMS_METADATA.get_ddl(:OBJECTTYPE,:OBJECT,:SCHEMA) to show the DDLs for table ACCOUNT and index ACCOUNT_AC_IX in the THUNDER schema (you can remove the storage clauses).

Also state your 4 digit Oracle version number.

SachinP-Oracle

This table is having 128 hash type partitions: Oracle version is 21.0.0.0.0

 CREATE TABLE "THUNDER"."ACCOUNT" 

  ( "BRANCH_CODE" VARCHAR2(12), 

"CUST_AC_NO" VARCHAR2(20) CONSTRAINT "CUST_ACNO_NN" NOT NULL ENABLE, 

"AC_DESC" VARCHAR2(105), 

"CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "ACCOUNT_CUST_ID_NN" NOT NULL ENABLE, 

"CCY" VARCHAR2(3), 

"AC_STAT_NO_DR" CHAR(1), 

"AC_STAT_NO_CR" CHAR(1), 

"AC_STAT_BLOCK" CHAR(1), 

"AC_STAT_STOP_PAY" CHAR(1), 

"AC_STAT_DORMANT" CHAR(1), 

"JOINT_AC_INDICATOR" CHAR(1), 

"AC_OPEN_DATE" DATE, 

"CHEQUE_BOOK_FACILITY" CHAR(1), 

"ATM_FACILITY" CHAR(1), 

"PASSBOOK_FACILITY" CHAR(1), 

"AC_STMT_TYPE" CHAR(1), 

"AC_STAT_FROZEN" CHAR(1), 

"NOMINEE1" VARCHAR2(105), 

"NOMINEE2" VARCHAR2(105), 

"RECORD_STAT" CHAR(1), 

"AUTH_STAT" CHAR(1), 

"MOD_NO" NUMBER, 

"CURR_BALANCE" NUMBER, 

"BLOCKED_AMOUNT" NUMBER, 

"THREAD_NAME" VARCHAR2(30), 

CONSTRAINT "ACCOUNT_CUST_FK" FOREIGN KEY ("CUSTOMER_ID")

 REFERENCES "THUNDER"."CUSTOMER" ("CUSTOMER_ID") DEFERRABLE ENABLE NOVALIDATE

  ) PCTFREE 10 PCTUSED 40 INITRANS 32 MAXTRANS 255 

 ROW STORE COMPRESS ADVANCED 

 STORAGE(INITIAL 8388608 NEXT 8388608

 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

 TABLESPACE "THUNDER" 

 PARTITION BY HASH ("CUST_AC_NO") 

 

 CREATE UNIQUE INDEX "THUNDER"."ACCOUNT_AC_IX" ON "THUNDER"."ACCOUNT" ("CUST_AC_NO") REVERSE 

 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 

 STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

 TABLESPACE "THUNDER" 

 PARALLEL 26 

ALTER TABLE "THUNDER"."ACCOUNT" ADD CONSTRAINT "ACCOUNT_PK" PRIMARY KEY ("CUST_AC_NO")

 USING INDEX "THUNDER"."ACCOUNT_AC_IX" ENABLE NOVALIDATE

Billy Verreynne

The column CUST_AC_NO is a string. The index is thus on a string value.

The index is used when you use a string literal.

The index is not used when the value supplied is a number. There is thus an implicit data type conversion that results in the number value not being able to be used for searching the string-based index.

A basic rule to adhere to in SQL and PL/SQL is to use explicit data type conversion. In your case:

select * from account where cust_ac_no = to_char(15965766)

SachinP-Oracle

Sorry my mistake, I have changed the data type of cust_ac_no to Varchar2 when I got the issue. So I have sent you the update one. let me check again and will come back. Thank you very much for your analysis

Mohamed Houri

Never consider execution plan without the predicate part

Can you copy past the two execution plans with their corresponding predicate part?

I suspect that the cust_ac_no is really a string and you are comparing string to number

Since the implicit conversion hierarchy is always from

VARCHAR → NUMBER
VARCHAR → DATE

https://www.red-gate.com/simple-talk/sql/oracle/oracle-data-type-implicit-conversion-hierarchy/

Then when you use cust_ac_no= 15965766 Oracle is applying the implicit conversion on the column which is the worst scenario.

Post here the predicate part of the execution plan and we will, very probably, see an implicit conversion like

to_number(cust_ac_no)

Best Regards

Mohamed Houri