Re: Estimating a PK index rebuild with REVERSE

Too bad you are not on 12c.
I wonder if You could create the new index invisible to avoid ORA-01408. Disable the constraint, drop the old index, make the new index visible and reanable the constraint.
(At least I think it works, I have never tried.)

Am 25.10.2017 um 21:58 schrieb Andy Sayer:
> Using the online clause of the create index statement will force the
> table to be read instead.
>
> Regards,
> Andrew
>
> On Wed, 25 Oct 2017 at 20:54, Luis Santos <lsantos_at_pobox.com
> <mailto:lsantos_at_pobox.com>> wrote:
>
> Ok, it's here. First our DB version and PSU.
>
>
> [11g]> _at_psu
> COMMENTS               VERSION
> --------------------------------------------------
> ------------------------------
> PSU 11.2.0.4.170814                11.2.0.4
>
>
>
> Note that I tried to use hints on CREATE INDEX command. Look the
> article:
> https://blog.pythian.com/oracles-create-index-command-can-take-hint
>
> But they did not work.
>
>
> ​​
> [11g]> explain plan for
> 2   CREATE /*+ NO_INDEX(L PK_TABLE_NAME1) FULL(L) */ UNIQUE
> INDEX  "OWNER_O"."PK_TABLE_NAME1" ON "OWNER_O"."TABLE_NAME1" L
> ("ID_TABLE_NAME1", "DATA") REVERSE
>   3   PCTFREE 10 INITRANS 2 MAXTRANS 255
> 4   STORAGE(
> 5   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
> DEFAULT)
> 6   TABLESPACE "CRIVO_INDEX"  LOCAL
> 7   parallel 64
> 8  /
> Explicado.
> U71013576_at_P01CRV.brux0387 [11g]> _at_xplan
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Plan hash value: 961639110
> ---------------------------------------------------------------------------------------------------------------------------------------------
> | Id  | Operation                     | Name           | Rows
>  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ
>  |IN-OUT| PQ Distrib |
> ---------------------------------------------------------------------------------------------------------------------------------------------
> |   0 | CREATE INDEX STATEMENT        |                |
>  7462M|   111G|   292K  (1)| 00:58:27 |       |       |      
>  |      |            |
> |   1 |  PX COORDINATOR               |                |      
> |       |            |          |       | |        |      |  
>          |
> |   2 |   PX SEND QC (RANDOM)         | :TQ10000       |
>  7462M|   111G|            |          |       | |  Q1,00 |
> P->S | QC (RAND)  |
> |   3 |    PX PARTITION RANGE ALL     |                |
>  7462M|   111G|            |          |     1 |    28 |  Q1,00
> | PCWC |            |
>
> |   4 |   INDEX BUILD UNIQUE (LOCAL)| PK_TABLE_NAME1 |       |
>       |            |          |     1 |    28 |  Q1,00 | PCWP
> |            |
>
> |   5 |    SORT CREATE INDEX        |                |  7462M|
>   111G|            |          |       |       |  Q1,00 | PCWP
> |            |
>
> |   6 | INDEX FAST FULL SCAN    | PK_TABLE_NAME1 |  7462M|  
> 111G|   124K  (1)| 00:24:58 |     1 |    28 |  Q1,00 | PCWP |
>            |
>
> ---------------------------------------------------------------------------------------------------------------------------------------------
> Query Block Name / Object Alias (identified by operation id):
> -------------------------------------------------------------
>  1 - CRI$1
>
>    6 - CRI$1 / L_at_CRI$1
>
>
> Column Projection Information (identified by operation id):
> -----------------------------------------------------------
>  1 - SYSDEF[272], SYSDEF[4]
>  2 - (#keys=0) SYSDEF[272], SYSDEF[4]
>  3 - SYSDEF[272], SYSDEF[4]
>  4 - SYSDEF[272]
>
>    5 - (#keys=2) REVERSE("ID_TABLE_NAME1")[22],
> REVERSE("DATA")[7], "L".ROWID[ROWID,10]
>  6 - "L".ROWID[ROWID,10], "ID_TABLE_NAME1"[NUMBER,22],
> "DATA"[DATE,7]
>
>
> Note
> -----
>  - estimated index size: 225G bytes
>
>
> /
> /
> /--/
> /Att/
> /Luis Santos
>
> /
> /
> /
>
> 2017-10-25 12:32 GMT-02:00 Mark W. Farnham <mwf_at_rsiz.com
> <mailto:mwf_at_rsiz.com>>:
>
> Would you add the actual SQL text you submitted?
>
> mwf
>
> *From:*oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> [mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>] *On Behalf Of *Luis Santos
> *Sent:* Wednesday, October 25, 2017 10:10 AM
> *To:* ORACLE-L
> *Subject:* Estimating a PK index rebuild with REVERSE
>
> Hi Oracle-L,
>
> I'm trying to estimate the time necessary to recreate a PK
> index with the reverse clause. Usually I use explain plan to
> estimate time and size for a new index.
>
> I did a reverse script from the actual PK index using and
> DBMS_METADATA and issued the explain plan, adding the reverse
> clause.
>
> It worked nicely, and the output was.
>
> [11g]> _at_xplan
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------------------------------------------------------------------------------
> Plan hash value: 961639110
> ---------------------------------------------------------------------------------------------------------------------------------------------
> | Id  | Operation   | Name           | Rows  | Bytes |
> Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ
> Distrib |
> ---------------------------------------------------------------------------------------------------------------------------------------------
> |   0 | CREATE INDEX STATEMENT  |                |  7462M|
>   111G|   292K  (1)| 00:58:27 |       |       |        |  
>    |          |
> |   1 |  PX COORDINATOR   |                |       |      
> |            |          |       |   |        |      |    
>        |
> |   2 |   PX SEND QC (RANDOM)   | :TQ10000       |  7462M|
>   111G|            |          |       |   |  Q1,00 | P->S
> | QC (RAND)  |
> |   3 |    PX PARTITION RANGE ALL   |                |
>  7462M|   111G|            |          |     1 |  28 |
>  Q1,00 | PCWC |            |
> |   4 |     INDEX BUILD UNIQUE (LOCAL)| PK_TABLE_DATA1 |  
>     |     |            |          |     1 |    28 |  Q1,00
> | PCWP |  |
> |   5 |      SORT CREATE INDEX  |                |  7462M|
>   111G|            |          |       |   |  Q1,00 | PCWP
> |            |
> |   6 |       INDEX FAST FULL SCAN  | PK_TABLE_DATA1 |
>  7462M|   111G|   124K  (1)| 00:24:58 |     1 |  28 |
>  Q1,00 | PCWP |            |
> ---------------------------------------------------------------------------------------------------------------------------------------------
> Query Block Name / Object Alias (identified by operation id):
> -------------------------------------------------------------
>    1 - CRI$1
>    6 - CRI$1 / TABLE_DATA1_at_CRI$1
> Column Projection Information (identified by operation id):
> -----------------------------------------------------------
>    1 - SYSDEF[272], SYSDEF[4]
>    2 - (#keys=0) SYSDEF[272], SYSDEF[4]
>    3 - SYSDEF[272], SYSDEF[4]
>    4 - SYSDEF[272]
>    5 - (#keys=2) REVERSE("ID_TABLE_DATA1")[22],
> REVERSE("DATA")[7], "TABLE_DATA1".ROWID[ROWID,10]
>    6 - "TABLE_DATA1".ROWID[ROWID,10],
> "ID_TABLE_DATA1"[NUMBER,22], "DATA"[DATE,7]
> Note
> -----
>    - estimated index size: 225G bytes
>
> But this is the evaluation phase. The PK was not, of course,
> already dropped. So the actual non reverse PK index is been
> used in the create index plan...
>
> How can I disable the access to the actual PK for this explain
> plan?
>
> Best regards,
>
> /Luis Santos/
>
> //
>
>

-- 






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