Re: What does it mean when CREATE INDEX

From: Taral Desai <taral.desai_at_gmail.com>
Date: Tue, 9 Apr 2013 08:44:36 -0500
Message-ID: <CAO4+9HWiHayx1JrRJA73BOjybZ6AfX6LnUmWHEXtG4OBUODzaA_at_mail.gmail.com>

Adding to David, internally it will create function based index, basically we are making sure all rows(rowid) is there in index

On Fri, Mar 29, 2013 at 5:26 PM, David Fitzjarrell <oratune_at_yahoo.com>wrote:

> Yes, it applies to 11g. The addition of the 1 in the column list ensures
> that NULL values for the key column will be indexed. Normally a b-tree
> index will not contain entirely NULL keys so this is a way to 'fix' that.
> I blogged on this sometime back:
>
> http://dfitzjarrell.wordpress.com/2008/04/09/a-tale-of-two-indexes/
> David Fitzjarrell
>
>
>
> ________________________________
> From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
> To: ORACLE-L <oracle-l_at_freelists.org>
> Sent: Friday, March 29, 2013 4:05 PM
> Subject: What does it mean when CREATE INDEX
>
> Hi Friends,
> I saw a command like this:
>
> CREATE INDEX emp_ename ON emp(ename desc, 1);
>
> The benefit pointed of this solution is that when we use in the where
> clause
>
> --> where name IS NULL
>
> sometimes the optimizer does not use the index.
>
> In the test that person did, using ... emp(name,desc,1) the optimizer
> used the index.
>
> Does it apply to Oracle 11g ?
>
> Did anybody have any experience using this resource ?
>
> I will test it too.
>
> Regards
> Eriovaldo
>
>
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 

Thanks & Regards,
Taral Desai


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 09 2013 - 15:44:36 CEST