Re: Estimating a PK index rebuild with REVERSE

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>:

> Would you add the actual SQL text you submitted?
>
>
>
> mwf
>
>
>
> *From:* 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