RE: oracle equivalent of teradata's sparse index
- From: Jacques Kilchoer <Jacques.Kilchoer@xxxxxxxxx>
- To: "dd.yakkali@xxxxxxxxx" <dd.yakkali@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Aug 2010 18:01:55 -0700
Could you use a function-based index ? Something like this
create table t (n number, d date, v varchar2 (20)) nologging ;
insert /*+ append */ into t (n, d, v)
select rn as n, sysdate - rn as d, dbms_random.string ('p', 20) as v
from (select rownum as rn from dual connect by level <= 200000) ;
create index ti_1 on t (n) ;
create index ti_2 on t (case when d >= DATE '2010-01-01' then n else null end) ;
select index_name, num_rows, leaf_blocks
from user_indexes
where index_name in ('TI_1', 'TI_2')
order by 1 ;
Proof of concept:
SQL> create table t (n number, d date, v varchar2 (20)) nologging ;
Table crÚÚe.
SQL> insert /*+ append */ into t (n, d, v)
2 select rn as n, sysdate - rn as d, dbms_random.string ('p', 20) as v
3 from (select rownum as rn from dual connect by level <= 200000) ;
200000 ligne(s) crÚÚe(s).
SQL> commit ;
Validation effectuÚe.
SQL> create index ti_1 on t (n) ;
Index crÚÚ.
SQL> create index ti_2 on t (case when d >= DATE '2010-01-01' then n else null e
nd) ;
Index crÚÚ.
SQL> analyze table t compute statistics for table for all indexes ;
Table analysÚe.
SQL> select index_name, num_rows, leaf_blocks
2 from user_indexes
3 where index_name in ('TI_1', 'TI_2')
4 order by 1 ;
INDEX_NAME NUM_ROWS LEAF_BLOCKS
------------------------------ ---------- -----------
TI_1 200000 445
TI_2 213 1
SQL>
De : oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] De la
part de dd yakkali
Envoyé : lundi 2 août 2010 16:36
À : oracle-l@xxxxxxxxxxxxx
Objet : oracle equivalent of teradata's sparse index
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.
Google did not come up wth any hits.
thanks
Deen
Other related posts:
