Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

INDEX performance ORACLE

SachinP-OracleJun 18 2021

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;

Comments

Post Details

Added on Jun 18 2021
7 comments
244 views