Index Hints

A new form of index hint appeared in 10g – and it’s becoming more common to see it in production code; instead of naming indexes in index hints, we describe them. Consider the following hint (expressed in two ways, first as it appeared in the outline section of an execution plan, then cosmetically adjusted to look more like the way you would write it in your SQL):

INDEX(@"SEL$1" "PRD"@"SEL$1" ("PRODUCTS"."PRODUCT_GROUP" "PRODUCTS"."ID"))

index(@sel$1 prd@sel$1(product_group  id))

With this syntax, Oracle is directed to use an index on the table aliased as prd in query block with the (default) name sel$1; the index has to start with the columns (product_group, id) in that order – with preference given to an exact match, otherwise using the lowest cost index that starts the right way. In passing, although this example shows the default query block names, it’s good practice to name query blocks explicitly with the /*+ qb_name() */ hint.

With this in mind, what is the correct way to hint an index with the following definition:

create index prd_case on products(
        case product_group
                when 'CLASSICAL CD' then id
        end
)
;

The correct answer is the obvious guess – you have to go back to the old syntax which (again in two versions) would be:

INDEX(@"SEL$1" "PRD"@"SEL$1" "PRD_CASE")

index(@sel$1 prd@sel$1 prd_case)

There simply is no alternative.

So how about hinting a bitmap join index, e.g:

create bitmap index pe_home_st_idx on people(st.name)
from
        states  st,
        towns   ho,
        people  pe
where
        ho.id_state     = st.id
and     pe.id_town_home = ho.id
;

Again you could use the traditional approach of supplying the index name, but in this case you can also take the newer approach of supplying the index description – except you have to remember to qualify any column names with their table name (irrespective of the aliases you used for the table in the query) e.g:

/*+ index(pe (states.name)) */

Finally, left as an exercise to the readers, how should you hint an index on a virtual column:

alter table products
        add cd_product generated always as (
                case product_group
                        when 'CLASSICAL CD' then id
                end
        ) virtual
;

create index prd_virt on products(cd_product);

If you check user_tab_columns and user_ind_columns they both contain the column name cd_product, so it seems pretty clear that we should be able to use the 10g description method – but will the optimizer recognise the index if hinted that way, and what will the outline section of the execution plan show ?