RE: "create index offline"

From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: "Riyaj Shamsudeen" <rshamsud@xxxxxxxxxxxx>
Date: Fri, 10 Nov 2006 09:58:41 -0500
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@xxxxxxxxxxxx] 
Sent: Friday, November 10, 2006 9:50 AM
To: Bobak, Mark
Cc: Mladen Gogala; mcdonald.connor@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx] 
        Sent: Friday, November 10, 2006 7:49 AM
        To: Bobak, Mark
        Cc: mcdonald.connor@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
        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
        
        --
        //www.freelists.org/webpage/oracle-l
RE: "create index offline"

Other related posts:
