Oracle Indexing Join on Not Null-CodePudding

We are doing SQL Tuning/Indexing on Oracle Database 11g. Currently there is no index on CustomerTransaction ProductId, so we want to add one to help with Join tuning performance. However, DBA noticed 95% of the ProductIds in CustomerTransaction are Null. Does Oracle have an option to index on not-null rows only ? What is alternative way to index, or should we apply the index in this 90% distribution/statistics scenario?

select ct.customerId, pr.ProductName
from dbo.CustomerTransaction ct
inner join dbo.Product pr 
    on ct.ProductId = pr.ProductId


CREATE TABLE [dbo].[CustomerTransaction](
    [CustomerTransactionId] [int] NOT NULL, // this is the primary key
    [ProductId] [int] NULL,
    [SalesDate] [datetime] NOT NULL, 
    ...
   )

ProductId, Count Distribution etc sample list

NULL,34065306
2,127444
3,103996
5,96280
6,78247
366,66744
9,58251
4,48056
10,29841
155,27353
8,22143
1052,20885
16,18298
23204,17242
21,16413
26,15084
11,15061
23205,14161
168,14086
7,14022
738,13294
115,12385
13,12119
18,11844
23208,11610

CodePudding user response：

For single column B-tree indexes, Oracle will only index the rows where the indexed column is not null.

5% of a table can sometimes still be slower to read by an index rather than a full table scan, it will depend on how those rows are spread out.