[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Index not used in a select statement, why?

Posted on 2010-03-24
24
Medium Priority
?
366 Views
Last Modified: 2013-12-18
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?
0
Comment
Question by:gram77
  • 5
  • 5
  • 5
  • +3
24 Comments
 

Author Comment

by:gram77
ID: 28435193
  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
 
LVL 27

Expert Comment

by:Tolomir
ID: 28435311
As far as I know an index cannot contain NULL entries.

So oracle cannot use this index.

 
0
 
LVL 27

Accepted Solution

by:
Tolomir earned 668 total points
ID: 28435432
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 27

Expert Comment

by:Tolomir
ID: 28435558
See also the readers comment below the original suggestion.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 28452334
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
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 668 total points
ID: 28470078
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
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 28476786
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 28477196
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
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 28483190
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
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 28483472
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
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 28483882
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
 

Author Comment

by:gram77
ID: 29340932
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 29351732
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
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 664 total points
ID: 29358452
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
 
LVL 48

Expert Comment

by:schwertner
ID: 29361960
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
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 29366057
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
 

Author Comment

by:gram77
ID: 29771321
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 29789178
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
 

Author Comment

by:gram77
ID: 29795686
markgeer:
Changing the architecture is something that we developers are never allowed. This suggestion will just fall into deaf ears.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 29796053
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
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 29796942
While solving any database level performance issue, the very first question is
Is application tuned and uses the correct db resources?
0
 
LVL 48

Expert Comment

by:schwertner
ID: 29816345
Under ID:28470078 i made a sugestion, but it seems nobody is going
to check it ....
0
 

Author Comment

by:gram77
ID: 29866758
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
 
LVL 48

Expert Comment

by:schwertner
ID: 29934656
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question