Re: oracle equivalent of teradata's sparse index
- From: Ilmar Kerm <ilmar.kerm@xxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 3 Aug 2010 13:18:32 +0300
On Tue, Aug 3, 2010 at 5:28 AM, Jared Still <jkstill@xxxxxxxxx> wrote:
>
> On Mon, Aug 2, 2010 at 4:36 PM, dd yakkali <dd.yakkali@xxxxxxxxx> wrote:
>>
>> hello dbas,
>>
>> Some one asked me that teradata can create index for only a subset of rows
>> of a table. We have a huge table (5 billion) which is for few months of data
>> but the data for most part is accessed for yesterday. So the question is: is
>> there any way to create index only for yesterday.
>
> Sounds like a job for partitioning.
From 11.2 new features an interesting feature caught my eye...
"Unusable indexes and index partitions no longer consume space in the
database because they become segmentless."
http://download.oracle.com/docs/cd/E11882_01/server.112/e10881/chapter1.htm#FEATURENO08879
And a note from administrators guide:
"For a partitioned index where one or more of the partitions are
unusable, the optimizer does not consider the index if it cannot
determine at query compilation time if any of the index partitions can
be pruned. This is true for both partitioned and non-partitioned
tables. The only exception is when an index is explicitly specified
with the INDEX() hint."
http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/indexes002.htm#ADMIN13280
So, if you are using 11.2, then one possibility could be to partition
the index and make old partitions unusable (and force the index use
with a hint).
--
Ilmar Kerm
--
//www.freelists.org/webpage/oracle-l
Other related posts:
