9 Replies Latest reply: Nov 16, 2012 2:21 AM by Kim Berg Hansen RSS

    Create a conditional index

    Murray Sobol
      Can I create a conditional index.

      For example, if column 1 is not null, create an index on column 1, column 2 and column 3.

      If column is null, i do not want to create an index.

      In the above example, data can from 2 sources; when column 1 is populated I know the source of the data so creating an index would be beneficial.

      Thanks
        • 1. Re: Create a conditional index
          JustinCave
          You can take advantage of the fact that Oracle does not store completely NULL keys in a b-tree index and create a function-based index. Something like
          CREATE INDEX my_index_name
              ON table_name( 
                    (CASE WHEN column1 IS NOT NULL THEN column1 ELSE NULL END),
                    (CASE WHEN column1 IS NOT NULL THEN column2 ELSE NULL END),
                    (CASE WHEN column1 IS NOT NULL THEN column3 ELSE NULL END) );
          The index exists regardless of what data is inserted. But only rows where column1 is not null will have their ROWIDs stored in the index.

          Justin
          • 2. Re: Create a conditional index
            sb92075
            Murray Sobol wrote:
            Can I create a conditional index.

            For example, if column 1 is not null, create an index on column 1, column 2 and column 3.

            If column is null, i do not want to create an index.

            In the above example, data can from 2 sources; when column 1 is populated I know the source of the data so creating an index would be beneficial.

            Thanks
            What if for some rows COL1 is NULL & for other rows COL1 IS NOT NULL.

            Either INDEX should exist or not & does not depend upon value within any particular row.

            How do I ask a question on the forums?
            SQL and PL/SQL FAQ

            Handle:     Murray Sobol
            Status Level:     Newbie
            Registered:     Sep 28, 2009
            Total Posts:     94
            Total Questions:     39 (32 unresolved)
            • 3. Re: Create a conditional index
              Kim Berg Hansen
              So you want an index, but you only want the index to contain those rows where column1 is not null?

              You could do something like:
              create index idx_name on tab_name (
                 column1
               , case when column1 is not null then column2 end
               , case when column1 is not null then column3 end
              )
              /
              But for your queries to use that index, then you need to query like:
              select column1, column2, column3
                from tab_name
               where column1 = p_col1
                 and case when column1 is not null then column2 end = p_col2
                 and case when column1 is not null then column3 end = p_col3
              /
              If you are on 11.2 you can consider virtual columns for the two case expressions.
              • 4. Re: Create a conditional index
                793965
                Hi Justin..

                Can he use Function Based Index here...

                as for example:

                create index my_index_name on table_name (nvl(column1,1), column2, column3);

                In that case, he will always get benefit from the index.
                • 5. Re: Create a conditional index
                  Peter vd Zwan
                  Hi,

                  What do you care if the index is "created" or not? (contains the row with col1 is null)

                  What are you trying to solve?

                  Regards,

                  Peter
                  • 6. Re: Create a conditional index
                    Murray Sobol
                    Kim:

                    can you further explain "virtual columns"??

                    Thanks
                    • 7. Re: Create a conditional index
                      sb92075
                      Murray Sobol wrote:
                      Kim:

                      can you further explain "virtual columns"??

                      Thanks
                      UNWILLING or INCAPABLE to Read The Fine Manual yourself?

                      http://www.oracle.com/pls/db112/search?remark=quick_search&word=VIRTUAL+COLUMN&partno=
                      • 8. Re: Create a conditional index
                        Sven W.
                        It looks like you have a flawed or misleading understanding what a index is good for and how and when it is used.

                        Please explain your problem first, before asking for the implementation of a very questionable solution.

                        From a very general perspective: An index is benefical in many cases. I don't see why I should matter whether any other unindexed column is populated or not. Even if you do not know the source of the data, then an index should be "benefical".

                        The CBO will take care of the question if the index should be used during a particular query or not.
                        • 9. Re: Create a conditional index
                          Kim Berg Hansen
                          Sven W. wrote:
                          From a very general perspective: An index is benefical in many cases. I don't see why I should matter whether any other unindexed column is populated or not. Even if you do not know the source of the data, then an index should be "benefical".

                          The CBO will take care of the question if the index should be used during a particular query or not.
                          I very much agree :D

                          But one reason for wanting an index with only a subset of the rows can be if that set of rows (column1 is not null) is a small fraction of the complete set, and you know that querying the data on column2/column3 makes only sense when column1 is not null. In those cases a function based index as described by Justin and myself can make sense, as it takes up a fraction of the space. And it will allow the optimizer in some cases to fast-full-scan that index, which could potentially be "beneficial" compared to index range scan in a big index containing all rows.

                          An alternative solution might be to create two tables - one containing the data from his one source and create an index on that table, and then another table containing the data from the other source without an index. Then if need be create a view as a UNION ALL select of data from both tables.

                          As for the [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_3001.htm#i2183462]virtual columns, they can help make this easier.
                          alter table tab_name add (
                             source_a_column2 generated always as (case when column1 is not null then column2 end) virtual
                           , source_a_column3 generated always as (case when column1 is not null then column3 end) virtual
                          )
                          /
                          
                          create index idx_name on tab_name (
                             source_a_column2
                           , source_a_column3
                          )
                          /
                          
                          select *
                            from tab_name
                           where source_a_column2 = :var2
                             and source_a_column3 = :var3
                          /
                          Virtual column source_a_column2 will contain the data of column2 only when column1 is not null (=data is from source A.) All other rows the virtual column is null.
                          Likewise for virtual column source_b_column3.
                          The index therefore will only contain rows from source A (column1 is not null) and when you query the virtual columns it can use the index (either range scan or fast full index scan) and only retrieve rows from source A.


                          But Murray : please understand that you should really only care about this if column1 is null in almost all of your data! That is that only a small fraction of your data comes from "source A".
                          Otherwise just create a plain normal index on column1, column2, column3 - and let the CBO worry about the rest, just like Sven advises ;-)