Re: Estimating a PK index rebuild with REVERSE

>
> For reference - you had a fast full scan, so you needed the hint
> no_index_ffs() as well. (no_)index() is about index range scans and index
> full scans.

Jonathan, thanks for the input!

*--*
*Att*

*Luis Santos*

2017-10-25 18:33 GMT-02:00 Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>:

>
> For reference - you had a fast full scan, so you needed the hint
> no_index_ffs() as well. (no_)index() is about index range scans and index
> full scans.
>
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Luis Santos <lsantos_at_pobox.com>
> Sent: 25 October 2017 20:53:14
> To: Mark W. Farnham
> Cc: ORACLE-L
> Subject: 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
>
> [https://lh3.googleusercontent.com/-AD-URpt0jeE/AAAAAAAAAAI/
> AAAAAAAB-9c/LrffscVVpf8/s90-c-k/photo.jpg]
>
> 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
> [https://lh3.googleusercontent.com/-AD-URpt0jeE/AAAAAAAAAAI/
> AAAAAAAB-9c/LrffscVVpf8/s90-c-k/photo.jpg]
>
>

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