Re: Estimating a PK index rebuild with REVERSE

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