RE: "create index offline"

Well, yeah, that's sort of the point.

It's the equivalent of:
create index blah_ind on blah(a);
alter index blah_ind unusable;

except that you don't have to wait for the index to create, if you want it initially unusable. (Possibly as part of a larger load process.)

I'm still not totally clear on when you'd need this, you can always do the index create after the data load.

-Mark

--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

There is nothing so useless as doing efficiently that which shouldn't be
done at all.  -Peter F. Drucker, 1909-2005


-----Original Message-----
From: Mladen Gogala [mailto:mgogala_at_verizon.net] 
Sent: Friday, November 10, 2006 7:49 AM
To: Bobak, Mark
Cc: mcdonald.connor_at_gmail.com; oracle-l_at_freelists.org
Subject: Re: "create index offline"


On 11/10/2006 07:37:54 AM, Bobak, Mark wrote:


> Chalk that up to "learn something new every day"!

> 

> I never knew you could create an index 'unusable'!

> 

> Thanks Connor!

> 


SQL> create table emp1 as select * from emp;

Table created.

SQL> create unique index emp1_pk on emp1(empno) unusable;

Index created.

SQL> select /*+ index(emp1,emp1_pk) */ max(empno) from emp1;
select /*+ index(emp1,emp1_pk) */ max(empno) from emp1
*
ERROR at line 1:
ORA-01502: index 'SCOTT.EMP1_PK' or partition of such index is in
unusable state

Index, apparently, can be created as "unusable" but, unfortunately, it
cannot be used.
--
Mladen Gogala
http://www.mladen-gogala.com

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