RE: "create index offline"

Interesting....thanks Riyaj...I guess I'm still more of an OLTP DBA than a DW DBA...;-)  

--

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

 

________________________________

From: Riyaj Shamsudeen [mailto:rshamsud_at_jcpenney.com] 
Sent: Friday, November 10, 2006 9:50 AM
To: Bobak, Mark
Cc: Mladen Gogala; mcdonald.connor_at_gmail.com; oracle-l_at_freelists.org
Subject: Re: "create index offline"


We use this feature in one of our weekly loads. We create a partitioned
index on a huge table ( just loaded ) and then rebuild individual
partitions in parallel using 30+ processes.

Thanks

Riyaj


Bobak, Mark wrote: 

	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
	
	
	
	  



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