Home » RDBMS Server » Performance Tuning » indexing nulls (oracle 11.2.0.4 on Linux)
indexing nulls [message #635872] Fri, 10 April 2015 05:36 Go to next message
orausern
Messages: 819
Registered: December 2005
Senior Member
Hi Experts,

I have a question on indexing the nulls. I have a table where a column is nullable and the query is find the data where the column "IS NOT NULL'.

I was reading on this topic and saw this reference:
http://www.dba-oracle.com/oracle_tips_null_idx.htm
where it is said:
Quote:

Note: Starting in Oracle 11g, there is new "create index" syntax that allows NULL values to be included in the index:

create index
emp_ename_idx
on
emp
(ename asc, 1)
;

Here, the "1" tells Oracle that to index on NULL values within the tables.



I find this to be useful but when I refer to the oracle documentation of Oracle 11g, I don't see it documented. Can someone help to confirm if this is a valid fact that by adding '1' oracle will allow the null value to be indexed?

Re: indexing nulls [message #635875 is a reply to message #635872] Fri, 10 April 2015 06:03 Go to previous messageGo to next message
gazzag
Messages: 993
Registered: November 2010
Location: Bristol, UK
Senior Member
Test it with an Explain Plan.
Re: indexing nulls [message #635877 is a reply to message #635872] Fri, 10 April 2015 06:37 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3131
Registered: May 2013
Location: World Wide on the Web
Senior Member
Here is a better write up, demo and explanation by Thomas Kyte.
Re: indexing nulls [message #635890 is a reply to message #635872] Fri, 10 April 2015 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I was reading on this topic and saw this reference:


Forget this site, it is b...

Re: indexing nulls [message #635891 is a reply to message #635890] Fri, 10 April 2015 10:30 Go to previous messageGo to next message
gazzag
Messages: 993
Registered: November 2010
Location: Bristol, UK
Senior Member
+10 Smile
Re: indexing nulls [message #635892 is a reply to message #635891] Fri, 10 April 2015 10:38 Go to previous messageGo to next message
orausern
Messages: 819
Registered: December 2005
Senior Member
Thank you experts! Thanks a lot Lalit!! That blog from Tom Kyte cleared up everything. I was able to test as well and my question is resolved.

Thank you!
OrauserN
Re: indexing nulls [message #635896 is a reply to message #635892] Fri, 10 April 2015 11:24 Go to previous message
Lalit Kumar B
Messages: 3131
Registered: May 2013
Location: World Wide on the Web
Senior Member
Sooner or later you will understand that not everything that comes up on top Google search is actually good. Burleson's site is one such example.

And, I have a strong feeling that the content is just copy pasted without any citation. I find that site useless and just a means to advertise and be on top of search engines. Simple copy pasting content, exaggerated output, guicy title, and there you go... Bu**sh**
Previous Topic: Index skip scan
Next Topic: INDEX FULL SCAN (MIN/MAX)
Goto Forum:
  


Current Time: Mon Oct 22 03:59:50 CDT 2018