Re: Index missing after exp with TRANSPORT

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 17 Oct 2006 08:38:55 +0100
Message-ID: <NPCdnWRENfcTFanYnZ2dnUVZ8sydnZ2d@bt.com>

"steven_nospam at Yahoo! Canada" <steven_nospam_at_yahoo.ca> wrote in message >
> Yes. indexes were there in the source database. Here are some of the
> details I sent to Oracle to help them identify the problem. I included
> the steps we do for the data transfer between source and destination
> SIDs, the check of the indexes, and the commands we use to fix the
> problem.
>
>
>
> We must then issue the following set of SQL commands to get the primary
> key and missing unique index to be displayed when we issue a select
> statement:
>
> ALTER TABLE APTPYD_REC DROP CONSTRAINT APTPYDAPTPYH0;
>
> ALTER TABLE APTPYH_REC DROP PRIMARY KEY;
>
> ALTER TABLE APTPYH_REC ADD PRIMARY KEY
> (PYH_VEN_NO,PYH_REF_DT,PYH_BRH,PYH_REF_CD,PYH_REF_NO);
>
> ALTER TABLE APTPYD_REC ADD CONSTRAINT APTPYDAPTPYH0
> FOREIGN KEY (PYD_VEN_NO, PYD_REF_DT, PYD_BRH, PYD_REF_CD, PYD_REF_NO)
> REFERENCES APTPYH_REC
> (PYH_VEN_NO,PYH_REF_DT,PYH_BRH,PYH_REF_CD,PYH_REF_NO);
>
> CREATE UNIQUE INDEX PYH_KEY1 ON APTPYH_REC
> (PYH_VEN_NO, PYH_REF_CDT, PYH_BRH, PYH_REF_CD, PYH_REF_NO,
> PYH_LOADED_ITM);
>
> CREATE UNIQUE INDEX PYH_KEY2 ON APTPYH_REC
> (PYH_REF_CD, PYH_REF_NO, PYH_VEN_NO, PYH_REF_DT, PYH_BRH);
>

I note that your primary key constraint can be covered by the PYH_KEY2 index. In a "normal" export/import this would allow Oracle to skip creating a specific index for the primary key. The same may be true about the tablespace import - after all, you have not created the PK index, you only have a PK constraint, and PK constraint do not (implicitly) need to have an exact index match.

I can't think of any explanation for the absence of the PYH_KEY1 though - but if the PK/unique index thing triggers a bug, then maybe the PYH_KEY1 is missing because of that bug.

> ALTER TABLE APTPYH_REC ADD PRIMARY KEY
> (PYH_VEN_NO,

    PYH_REF_DT,
    PYH_BRH,
    PYH_REF_CD,
    PYH_REF_NO

);
>
> CREATE UNIQUE INDEX PYH_KEY2 ON APTPYH_REC
> (

    PYH_REF_CD,
    PYH_REF_NO,
    PYH_VEN_NO,
    PYH_REF_DT,
    PYH_BRH

);

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

Received on Tue Oct 17 2006 - 02:38:55 CDT

Re: Index missing after exp with TRANSPORT_TABLESPACE=Y