CODEWITHSUNDEEP
oracle-databasesearchindexinginteger
Ketil Duna
Ketil Duna

Reputation: 35

Oracle INT column, strategy for fast searching

I have a wide table with millions of records. There is a column of type integer I need to examine for certain conditions. The value can be 0..6 or NULL. only about 1% of the records have a non-null, non-zero value in this column. Searching for a "6" value can take 90 seconds(!) There is no index on the column/field, so I'm guaranteed a table scan. How should I improve search performance ? Should I make sure to null the column when out of range, or should I make sure the column is not null and update to a 0 ? Can an integer field with so low cardinality benefit from an index ?

Upvotes: 0

Views: 77

Answers (2)

Non Plus Ultra
Non Plus Ultra

Reputation: 906

If you want to query on null values, try using a function-based index:

CREATE INDEX quirky_column_idx ON wide_table (case quirky_column when null then -1 else quirky_column end);

If you want to utilize the index, you have to rewrite your query exactly like the function in the index, for instance: select count(*) from wide_table where -1 = case quirky_column when null then -1 else quirky_column end ;

Now you can even query null values with use of an index. Knowing that the data is heavily skewed, it probably will not make much of a difference unless you include other columns in your where clause.

Upvotes: 0

Boneist
Boneist

Reputation: 23588

Sounds like that column is an ideal candidate for an index. Even if there are only a handful of distinct values, since Oracle indexes only store values that aren't null (or, in the case of indexes on multiple columns, where all the column values aren't null) you're going to automatically reduce the search down to 1% of your table.

Upvotes: 2

Related Questions