Re: NULLS FIRST option in CREATE INDEX?

That was just demonstration that it is possible. I didn`t mean that you should put 'AAA' there.

If you don`t care about storage, you can put 1 extra column into your index, e.g.:

CREATE TABLE my_table(

        col1 NUMBER NOT NULL,
        col2 NUMBER);

CREATE INDEX my_index ON my_table(col1,NVL2(col2,1,0),col2);

INSERT INTO MY_TABLE ( COL1, COL2 ) VALUES ( 1, 1);
INSERT INTO MY_TABLE ( COL1, COL2 ) VALUES ( 1, NULL);
INSERT INTO MY_TABLE ( COL1, COL2 ) VALUES ( 1, 2);
INSERT INTO MY_TABLE ( COL1, COL2 ) VALUES ( 2, NULL); COMMIT; And your query:

SELECT /*+ index(m my_index) */ *
  FROM MY_TABLE m
    WHERE col1=1
     ORDER BY col1, NVL2(col2,1,0),col2

will be ordered just by using the index (INDEX RANGE SCAN).

But you have to:

ALTER SESSION(SYSTEM)
        QUERY_REWRITE_ENABLED=TRUE;
and the same for

        QUERY_REWRITE_INTEGRITY=TRUSTED And the shcema has to have:

GRANT query rewrite TO my_schema

Jan

Brian Peasland <oracle_dba_at_remove_spam.peasland.com> wrote in message news:<3EEF4DC2.EE90185D_at_remove_spam.peasland.com>...
> Provided COL2 can never contain 'AAA'. But what happens if requirements
> change in the future and 'AAA' is an allowable value?
>
> Cheers,
> Brian
>
> Jan wrote:
> >
> > Look for Function-Based Index,
> >
> > you can do something like:
> >
> > create index my_index on my_table(col1,NVL(col2,'AAA'));
> >
> > Jan
>
> --
> ===================================================================
>
> Brian Peasland
> oracle_dba_at_remove_spam.peasland.com
>
> Remove the "remove_spam." from the email address to email me.
>
>
> "I can give it to you cheap, quick, and good. Now pick two out of
> the three"
Received on Wed Jun 18 2003 - 01:18:06 CDT