RE: oracle-l Digest V7 #223

Hi Deen

> ome 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.

In a recent post
(http://antognini.ch/2009/11/zero-size-unusable-indexes-and-the-query-op
timizer/), covering a new 11gR2 feature, I pointed that "This is really
interesting because it allows us to selectively remove unnecessary index
partitions. For example, in case some indexes are only used for the
"current" partition(s), the index partitions of the older ones could be
set unusable. As a result, lot of space might be released."

So, this might be an option for you. Here an example that I plan to put
in a follow up post in my blog:

CREATE TABLE t AS
SELECT rownum AS id, sysdate-mod(rownum,100) AS tim, rpad('*',50,'*') AS
pad
FROM dual
CONNECT BY level <= 1000;

CREATE INDEX i ON t (tim)
GLOBAL PARTITION BY RANGE (tim) (
  PARTITION i_201001 VALUES LESS THAN
(to_date('2010-02-01','YYYY-MM-DD')),
  PARTITION i_201002 VALUES LESS THAN
(to_date('2010-03-01','YYYY-MM-DD')),
  PARTITION i_201003 VALUES LESS THAN
(to_date('2010-04-01','YYYY-MM-DD')),
  PARTITION i_201004 VALUES LESS THAN
(to_date('2010-05-01','YYYY-MM-DD')),
  PARTITION i_201005 VALUES LESS THAN
(to_date('2010-06-01','YYYY-MM-DD')),
  PARTITION i_201006 VALUES LESS THAN
(to_date('2010-07-01','YYYY-MM-DD')),
  PARTITION i_201007 VALUES LESS THAN
(to_date('2010-08-01','YYYY-MM-DD')),
  PARTITION i_201008 VALUES LESS THAN
(to_date('2010-09-01','YYYY-MM-DD')),
  PARTITION i_201009 VALUES LESS THAN
(to_date('2010-10-01','YYYY-MM-DD')),
  PARTITION i_201010 VALUES LESS THAN
(to_date('2010-11-01','YYYY-MM-DD')),
  PARTITION i_201011 VALUES LESS THAN
(to_date('2010-12-01','YYYY-MM-DD')),
  PARTITION i_201012 VALUES LESS THAN
(to_date('2011-01-01','YYYY-MM-DD')),
  PARTITION i_maxvalue VALUES LESS THAN (MAXVALUE)
)
UNUSABLE;

ALTER INDEX i REBUILD PARTITION i_201008;



HTH
Chris Antognini

Troubleshooting Oracle Performance, Apress 2008
http://top.antognini.ch



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


Other related posts:

  • » RE: oracle-l Digest V7 #223 - Christian Antognini