Re: Estimating a PK index rebuild with REVERSE

I have just discovered that I can issue this below!

  ALTER INDEX <*INDEX_NAME> *REBUILD REVERSE

But, unfortunately to my case, I can't perform this on a partitioned index.

*--*
*Att*

*Luis Santos*

2017-10-31 11:47 GMT-02:00 Luis Santos <lsantos_at_pobox.com>:

> Using the online clause of the create index statement will force the table
>> to be read instead.
>
>
> ​Thanks Andrew! This option worked like a charm. I got the plan with the
> expected FULL SCAN.
>
> But, weird, the plan shows the exact same time as before.
>
> [11g]> explain plan for
> 2 CREATE 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 ONLINE
> 9 /
>
> Explicado.
>
> Decorrido: 00:00:00.30
> U71013576_at_P01CRV.brux0387 [11g]> _at_xplan
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------
> ------------------------------------------------------------
> ----------------------------------
> Plan hash value: 2391156251
>
> ------------------------------------------------------------
> ------------------------------------------------------------
> ---------------------
> | Id | Operation | Name | Rows | Bytes |
> Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
> ------------------------------------------------------------
> ------------------------------------------------------------
> ---------------------
> | 0 | CREATE INDEX STATEMENT | | 7462M| 111G|
> 124K (1)| 00:24:58 | | | | | |
> | 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 | TABLE ACCESS FULL | 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]
>
> ​
> And the table TABLE_NAME1 has 403 Gb, and its PK_TABLE_NAME1 has 259 Gb.
>
>
>
> *--*
> *Att*
>
>
> *Luis Santos*
>
>
> 2017-10-25 17:58 GMT-02:00 Andy Sayer <andysayer_at_gmail.com>:
>
>> 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-comma
>>> nd-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_freeli
>>>> sts.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