Index not used in a select statement, why?

I have a simple select statement that does a full table scan on a very large table even though there is a where clause in the select
statement restricting the rows.

WHERE DATA_STORE_ID IS NULL

There is an index on this column so why doesn't Oracle using this index?
gram77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gram77Author Commented:
  SELECT XREF_DATA_STORE_ID_SEQ.NEXTVAL,
          SYSTIMESTAMP CREATE_TS,
          SYSTIMESTAMP UPDATE_TS,
          'I',
          AS_OF_DT,
          'DataLoader_20100309',
          'NX',
          XREF_DATA_STORE_ID_SEQ.CURRVAL,
          VNDR_SRC_ROW_HASH_CD,
          'Y',
          XREF_ROW_HASH_CD,
          DERIVED_TICKER_ID,
          GROUP_NME,
          INDY_GROUP_NME,
          INST_CCY_CD,
          INST_CMDTY_CD,
          INST_CMDTY_DT,
          INST_CNTRY_ISS_CD,
          INST_CUSIP_ID,
          INST_DSC,
          INST_EXCH_CD,
          INST_EXPIRE_DT,
          INST_RACS_SUB_TYP_CD,
          INST_RACS_TYP_CD,
          INST_TICKER_ID,
          INST_VNDR_ID,
          INST_VNDR_SRC_ID,
          INST_XREF_TYP_CD,
          MOD_INST_LOCATOR_ID,
          OPT_CONT_SIZER_QTY,
          OPT_EXPIRE_DT,
          OPT_PUT_CALL_IND,
          OPT_STRIKE_PRC,
          PACKAGE_NME,
          PARENT_VENDOR_ID,
          PARENT_VENDOR_SRC_ID,
          REGION_ID
     FROM TT_XREF_DATA_STORE
    WHERE DATA_STORE_ID IS NULL


*********************************************************************
Index on TT_XREF_DATA_STORE.DATA_STORE_ID
*********************************************************************
CREATE UNIQUE INDEX PBSDDV.UK_TT_XREF_DS ON PBSDDV.TT_XREF_DATA_STORE
(DATA_STORE_ID);


*************
Explain Plan:
*************
Operation                                 Object                                                           COST
------------------------------ ------------------------------ ----------
SELECT STATEMENT ()                                                                       2641
 SEQUENCE ()                            XREF_DATA_STORE_ID_SEQ
   TABLE ACCESS (FULL)          TT_XREF_DATA_STORE                   2641 <--why isn't index used?

There is a huge cost associated with full table scan of TT_XREF_DATA_STORE
0
TolomirAdministratorCommented:
As far as I know an index cannot contain NULL entries.

So oracle cannot use this index.

 
0
TolomirAdministratorCommented:
As solution check this:

Whenever a SQL query asks for the open position employee slots "where ename is NULL", there will be no index entries for NULLS in emp_name_idx and Oracle would perform an unnecessary large-table full-table scan.

To get around the optimization of SQL queries that choose NULL column values, we can create a function-based index using the null value built-in SQL function to index only on the NULL columns.


http://www.dba-oracle.com/oracle_tips_null_idx.htm

Tolomir
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

TolomirAdministratorCommented:
See also the readers comment below the original suggestion.
0
Mark GeerlingsDatabase AdministratorCommented:
Yes, Tolomir is correct, "normal" Oracle indexes do not contain null values, so your query cannot use the index.  If your query was like this it would use the index
 where DATA_STORE_ID  = [a single value]

If your query was like any of these:
 where DATA_STORE_ID  in [a comma-separated list of values, or a sub-query]
 where DATA_STORE_ID  > [a single value]
 where DATA_STORE_ID  < [a single value]

 it *MAYBE* would use the index (depending on the table statistics and depending on how many values are included in the "in" list or the "<" or ">" values.
0
schwertnerCommented:
There is an undocumented function SYS_OP_MAP_NONNULL (see the code)
that works in 10g R2 but there is no guarantee that it will supported further.

Another workarount is to substitute NULL values in the DB with values like
0 or 'N' that are regular values.

SYS_OP_MAP_NONNULL is covered on the undocumented Oracle page of the library

CREATE TABLE t (
col1 VARCHAR2(1),
col2 NUMBER(5));

INSERT INTO t (col1, col2) VALUES ('Y', NULL);
INSERT INTO t (col1, col2) VALUES ('Y', 2);
INSERT INTO t (col1, col2) VALUES ('Y', NULL);
INSERT INTO t (col1, col2) VALUES ('N', 1);
INSERT INTO t (col1, col2) VALUES ('N', 2);
INSERT INTO t (col1, col2) VALUES ('N', 1);

CREATE INDEX ix_t
ON t (col2);

EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE col2 IS NULL;

SELECT * FROM TABLE(dbms_xplan.display);

CREATE INDEX ix_t_nulls
ON t (sys_op_map_nonnull(col2));

EXPLAIN PLAN FOR
SELECT *
FROM t
WHERE sys_op_map_nonnull(col2) = sys_op_map_nonnull(NULL);

SELECT * FROM TABLE(dbms_xplan.display);

Open in new window

0
Franck PachotCommented:
Hi,
Here is the right way to index null values:
CREATE UNIQUE INDEX PBSDDV.UK_TT_XREF_DS ON PBSDDV.TT_XREF_DATA_STORE
(DATA_STORE_ID,0);
The '0' can be any constant. this is because an index will not have entries for rows where all indexed columns are null. But one of them can be null. So here you will have an index entry for all DATA_STORE_ID values even if null.
Regards,
Franck.
0
Mark GeerlingsDatabase AdministratorCommented:
True, a multi-column index will include entries for records that have nulls in one (or more) index columns, as long as at least one value is non-null.

But, then the question becomes: is getting records with values into the index worth the expense of the index, or is re-writing the query to use a different index (or changing the application to avoid nulls) the better option?
0
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Since oracle is not storing nulls in index entry, therefore it skips using index.
Alternatly if this is very frequent statement upon large table, you can think of creating function based index.
ie
create index indx on tablename ( decode(column, null, 'NULL', null) )
ie if columns contain null, it will put  entry NULL in index, if it has some value, then it will consider that value as null therefore it will not put entry in index. Basically we are storing null value in index.

To validate above theory, you can use the following command.

CREATE INDEX NEW_INDEX ON YOURTABLE ( DECODE(COL1, NULL, 0, NULL));
select num_rows from user_indexes where index_name ='NEW_INDEX';
This will give you total number of nulls in table present
0
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Above is just the opposite of normal index, that stores null entry in indexes only, therfore if you use
that statement in where clause, it will use index and give you result very fast.
0
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Also you want to select like this
select * from YOURTABLE where decode(col1, null, 0, null)=0;
instead of select * from YOURTABLE where col1 is null;
0
gram77Author Commented:
virdi_ds:
create table dummy
(col1 varchar2(10));

insert into dummy values ('1');
insert into dummy values (null);
insert into dummy values (null);
insert into dummy values (null);
insert into dummy values ('1');

CREATE INDEX NEW_INDEX on dummy(col1);
CREATE INDEX NEW_INDEX1 ON dummy ( DECODE(col1, null, 0));

select num_rows from user_indexes where index_name ='NEW_INDEX';--2 ok
select num_rows from user_indexes where index_name ='NEW_INDEX1';--3 should be 5 rows not 3 rows, it is still not indexing null values correctly..
0
Mark GeerlingsDatabase AdministratorCommented:
Instead of trying to get Oracle to index null values (which it does not do by default) why not change your application to use what Oracle does do by default?  That would seem to be the options that is much more likely to be successful.
0
Devinder Singh VirdiLead Oracle DBA TeamCommented:
DECODE(col1, null, 0) Change any null values to not null and not null values to nulll. Do the following
select DECODE(col1, null, 0) from dummy;

Actually now you have two indexes one with null and another with not null values. If you want to combine into one then use Decode like this

CREATE INDEX NEW_INDEX1 ON dummy ( DECODE(col1, null, 0,col1));
select num_rows from user_indexes where index_name ='NEW_INDEX1';
0
schwertnerCommented:
NULL means "unknown value".
Thatswhy Oracle prohibit comparisons like
.......
WHERE column = NULL
This means look for columnes with the value equal to "unknown"value.
Instead Oracle uses
.......
WHERE column IS NULL
This is not comparison of values and thatswhy no index can be used at all.
The correct way is either to substitute the NULL values in the tables with
string like 'NULL', 'N' or some number if it is number column.

In many sites the undocumented function described in ID:28470078 is discussed.
0
Franck PachotCommented:
Hi,
index on (col1) has  the 2 non-null values;
index on  DECODE(col1, null, 0)) has the 3 null values only
If you want the 5 values, just index on (col1,0) or have a bitmap index
Regards,
Franck.
0
gram77Author Commented:
frankpachot:
"If you want the 5 values, just index on (col1,0) or have a bitmap index "

Bitmap indexes do not index null values. Here's how..

create table dummy
(col1 varchar2(10));

insert into dummy values ('1');
insert into dummy values (null);
insert into dummy values (null);
insert into dummy values (null);
insert into dummy values ('1');


CREATE BITMAP INDEX NEW_INDEX on dummy(col1);
select num_rows from user_indexes where index_name ='NEW_INDEX'; --2 columns indexed

Also the following are the same, both index null and not null values:
CREATE INDEX NEW_INDEX1 ON dummy ( DECODE(col1, null, 0,col1));
and
CREATE INDEX NEW_INDEX2 ON dummy ( NVL(col1,0));

select num_rows from user_indexes where index_name ='NEW_INDEX1'; --5 columns indexed
select num_rows from user_indexes where index_name ='NEW_INDEX2'; --5 columns indexed
0
Mark GeerlingsDatabase AdministratorCommented:
Why not change your application to take advantage of what Oracle does for us by default (like indexing non-null values), instead of trying to change Oracle to do something that it does not do very well or easily?

Sometimes this requires adding another varchar2(1) column to a table with a name something like "process_flag" and a trigger to make sure that it gets set to 'Y' when new records are added.  Create an index on this column, and change your query to include this condition:
and process_flag = 'Y'.  Also, change your processing step so that when you record the fact that this record now has been processed, like by filling in a date_shipped, or date_processed, etc., you also set the value of "process_flag" to null.  This keeps the index on this column small and fast even if the table grows very large.
0
gram77Author Commented:
markgeer:
Changing the architecture is something that we developers are never allowed. This suggestion will just fall into deaf ears.
0
Mark GeerlingsDatabase AdministratorCommented:
Wow!  So someone in your organization (or at a customer of your organization) is willing to pay the money for an Oracle database, but then does not want to use it the way Oracle works best?  Then you and whoever is paying for the database and/or application will have to get used to some disappointments.
0
Devinder Singh VirdiLead Oracle DBA TeamCommented:
While solving any database level performance issue, the very first question is
Is application tuned and uses the correct db resources?
0
schwertnerCommented:
Under ID:28470078 i made a sugestion, but it seems nobody is going
to check it ....
0
gram77Author Commented:
schwertner:
Thanks for your suggestion, I noted it.

But since you have said that  SYS_OP_MAP_NONNULL is an undocumented function that works in 10gR2 and there is no guarentee that it will be supported any further so i did not take this suggestion

"Being undocumented means that it should not be used in production code"
0
schwertnerCommented:
I will recommend Markgeer's comments like ID:29789178 Author:markgeer.
I also think in the same way.  many friends suggest to use a value that can be interpreted as NULL (even the string 'NULL') in order to use the indexes.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.