Role of null in performance tuning

Question

This query is regarding performance tuning of a query. I have a table TEST1, which has 200,000 rows. the table structure is as below.

ACCOUNT_NUMBER      VARCHAR2(16)
BRANCH              VARCHAR2(10)
ACCT_NAME           VARCHAR2(100)
BALANCE             NUMBER(20,5)
BANK_ID             VARCHAR2(10)
SCHM_CODE           VARCHAR2(10)
CUST_ID             VARCHAR2(10)

And the indexes are as below.

fields              Index Name              Uniquness
ACCOUNT_NUMBER      IDX_TEST_ACCT           UNIQUE
SCHM_CODE,BRANCH    IDX_TEST_SCHM_BR        NONUNIQUE

Also I have one more table STATUS,

ACCOUNT_NUMBER      VARCHAR2(16)
STATUS              VARCHAR2(2)
ACCOUNT_NUMBER      IDX_STATUS_ACCT         UNIQUE

When I write a query joining to table tables like below, execution too much time and is costly query.

SELECT ACCOUNT_NUMBER,STATUS
FROM TEST,STATUS
where TEST.ACCOUNT_NUMBER = STATUS.ACCOUNT_NUMBER
AND TEST.BRANCH = '1000';

There is query return by product team to fetch the same details has ||null in where condition, the query is returning the same results but the performance is very good compared to my query.

SELECT ACCOUNT_NUMBER,STATUS
FROM TEST,STATUS
where TEST.ACCOUNT_NUMBER = STATUS.ACCOUNT_NUMBER
AND TEST.BRANCH||NULL = '1000';

Can anyone explain me how ||null in the where condition made that difference.

I am writing this because, I want to know how it made the difference and want to use wherever it is possible.

Answer 1

If you turn on autotrace and get the execution plans of both queries, I would guess that your query is trying to use the index IDX_TEST_SCHM_BR and the other query cannot use the index due to the clause TEST.BRANCH||NULL and cannot use the index since that clause prevents the optimizer from using the index.

Normally, the use of a function on a table column prevents Oracle from using the index, and in your case appending a null to a table column with the || operator is like invoking the function concat(TEST.BRANCH||NULL). To make your query run faster, you can

1. Add a hint to ignore the index SELECT /*+ NOINDEX(TEST1 IDX_TEST_SCHM_BR */ ACCOUNT_NUMBER, ... (Not recommended)
2. Create a new index with BRANCH as the only column (Recommended)

As @symcbean noted, if an index is not very selective, (IE: the query returns a lot of rows in the table), then a full table scan would probably be faster. In this case, since the BRANCH column is not the first column in the index, Oracle has to skip through the index to find the rows that match the join criteria. A general rule of thumb is if the query is returning more than around 20% of the rows, a full table scan is quicker. In this case due to the index definition, Oracle has to read through several index entries, skipping along until it finds the next new BRANCH value, so in this case much less than 5% probably

Also ensure your tables have current statistics gathered, and if any of your columns are not null, you should specify that in the table definition to help Oracle optimizer avoid issues like you are having.