Home » SQL & PL/SQL » SQL & PL/SQL » Index on column with 90% rows Null
Index on column with 90% rows Null [message #232517] Fri, 20 April 2007 16:04 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

Having a table with 90 million rows,
in this table, column cache_dt date datatype for this column 89 million rows values are null only 1 mil is having data,

we have some query's as where cache_Dt > sysdate-5

In this case should we have an index on this column or not.

Oracle Version 9.2.0.5

Thanks

[Updated on: Fri, 20 April 2007 16:05]

Report message to a moderator

Re: Index on column with 90% rows Null [message #232518 is a reply to message #232517] Fri, 20 April 2007 16:12 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>In this case should we have an index on this column or not.
Run a couple of benchmarks, 1 with index & 1 without, and compare results. The report your findings back here.
Re: Index on column with 90% rows Null [message #232520 is a reply to message #232517] Fri, 20 April 2007 18:12 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
IMHO - it's better with index:

1. Without index - means full table scan on 90M rows.
2. With index - even when selecting ALL rows with values - you are going to select about 1%. In your case ( selecting last 5 days - it's even less).


My 2 bits of opinion.

Michael
Re: Index on column with 90% rows Null [message #232522 is a reply to message #232520] Fri, 20 April 2007 19:11 Go to previous message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
Hi,

As Michel has suggested you can create index is better then the full table scan, and if cardinality is low you can create bit map index.


Regards,
Harshad
Previous Topic: LEFT OUTER JOIN ??? -quick question
Next Topic: use index by
Goto Forum:
  


Current Time: Thu Dec 08 22:34:25 CST 2016

Total time taken to generate the page: 0.07310 seconds