Re: NULLS FIRST option in CREATE INDEX?

Part of my point is that by using this method, whether it is 'AAA' or some other value, is not always a good thing to do. One ends up making artificial values mean things that they don't if taken for face value. This can lead to problems down the road. The same is true when introducing artificial keys, which people seem to do all the time. SQL Server is particularly guilty of this with its IDENTITY column. If at all possible, I like to stay away from artificial values. I've just been snake bit too many times.

Just my 3.14159265 cents worth,
Brian

Jan wrote:
>
> 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"

-- 
===================================================================

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"