Forum Stats

  • 3,757,551 Users
  • 2,251,244 Discussions
  • 7,869,864 Comments

Discussions

INDEX performance 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;

Tagged:

Comments

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,570 Red Diamond
    edited Jun 18, 2021 6:45AM

    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
    SachinP-Oracle Member Posts: 17 Employee

    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
    Billy Verreynne Software Engineer Member Posts: 28,570 Red Diamond

    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
    SachinP-Oracle Member Posts: 17 Employee

    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
    Billy Verreynne Software Engineer Member Posts: 28,570 Red Diamond

    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)
    


    Jonathan Lewis
  • SachinP-Oracle
    SachinP-Oracle Member Posts: 17 Employee

    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
    Mohamed Houri Member Posts: 1,215 Bronze Trophy

    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
    

    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