RE: index on null column
Title: RE: index on null column Here is an idea ... Create a function that returns NULL if the value is null or any other acceptable value that can be ignored. And the function should return valid values otherwise. Then create a function based index on this and see if that helps you. To put in other words, you are merely indexing a subset of rows that match your criteria, while ignoring others that you don't want to deal with. Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 27, 2003 9:34 PM To: Multiple recipients of list ORACLE-L Subject: RE: index on null column AK Yes, it will benefit the non-null records. Just remember that the null values aren't indexes. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 5:19 PM To: Multiple recipients of list ORACLE-L I have a table with column A. there are 100 records in table out of which 500o records has not null in column A rest are null. if i created an index on A, will it benefit . thanks, ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: index on null column
It means if I do a query with where clause with A=:x its going to go for index . Does oracle go to table if :x is null. It looks like it does . ??? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, March 27, 2003 6:33 PM AK Yes, it will benefit the non-null records. Just remember that the null values aren't indexes. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 5:19 PM To: Multiple recipients of list ORACLE-L I have a table with column A. there are 100 records in table out of which 500o records has not null in column A rest are null. if i created an index on A, will it benefit . thanks, ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: index on null column
AK, I'd still do some testing with a bitmap index on that column. You have 1,000,000 records. 500 records have value in that column. That means the absolute most distinct values in that column is 501. This is at least one criteria for bitmap candidacy. What I see in testing is select * from table where A = 'some value' can use the bitmap index. Also I see select * from table where A is null also uses the bitmap index. I'm definitely not saying this as 'end all', just saying it might be worth playing with. Darrell Landrum Database Administrator Zale Corporation [EMAIL PROTECTED] 03/28/03 09:59AM It means if I do a query with where clause with A=:x its going to go for index . Does oracle go to table if :x is null. It looks like it does . ??? -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, March 27, 2003 6:33 PM AK Yes, it will benefit the non-null records. Just remember that the null values aren't indexes. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 5:19 PM To: Multiple recipients of list ORACLE-L I have a table with column A. there are 100 records in table out of which 500o records has not null in column A rest are null. if i created an index on A, will it benefit . thanks, ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
index on null column
I have a table with column A. there are 100 records in table out of which 500o records has not null in column A rest are null. if i created an index on A, will it benefit . thanks, ak
Re: index on null column
Hi AK, It depends ... If you frequently perform searches for not null values on column A and the cardinality of values is such that the optimizer is turned on by the index, then yes, potentially an index could be useful. Also, with so many nulls, such an index would be relatively small. Cheers Richard - Original Message - Date: Friday, March 28, 2003 10:18 am I have a table with column A. there are 100 records in table out of which 500o records has not null in column A rest are null. if i created an index on A, will it benefit . thanks, ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: index on null column
ak, This really depends as much on how you're going to access the data as much as the content. Will your typical query use this column in a where clause? And will it be looking for records where there is data? I did a little testing with a query which was looking for one unique value from column A and it ran much much better with an index than without. That's just one small test though. It really depends on your data and how it will be queried. If it will be looking for records that you know are null, you might play with a bitmap index. Have fun! [EMAIL PROTECTED] 03/27/03 05:18PM I have a table with column A. there are 100 records in table out of which 500o records has not null in column A rest are null. if i created an index on A, will it benefit . thanks, ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: index on null column
AK Yes, it will benefit the non-null records. Just remember that the null values aren't indexes. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 5:19 PM To: Multiple recipients of list ORACLE-L I have a table with column A. there are 100 records in table out of which 500o records has not null in column A rest are null. if i created an index on A, will it benefit . thanks, ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: index on null column
Queries against the non-null values should benefit
greatly. Queries on the null values will not use the index since null
values are not indexed (at least in single column indexes). It is an old
trick to create an index on something like STATUS where the vast majority of
records have a NULL (meaning perhaps"PROCESSED") and a small percentage
ofthe records have other non-null values for STATUS ("PENDING","APPROVED",
etc.).
Don Granaman
certified OraSaurus
- Original Message -
From:
AK
To: Multiple recipients of list ORACLE-L
Sent: Thursday, March 27, 2003 5:18
PM
Subject: index on null column
I have a table with column A. there are 100
records in table out of which 500o records has not null in column A rest are
null.
if i created an index on A, will it benefit
.
thanks,
ak
