Re: Estimating a PK index rebuild with REVERSE

to avoid ORA-01408 <01%20408> you can always create an interim index with additional "column" with the number 1.
then the creation of your target-index will be faster, as this interim index is used.

not a beautiful, but practical solution.

2017-10-26 10:33 GMT+02:00 Lothar Flatz <l.flatz_at_bluewin.ch>:

> Too bad you are not on 12c.
> I wonder if You could create the new index invisible to avoid ORA-01408
> <01%20408>.
> 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> 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*
>>>
>>>
>>
> --
>
>
>
>
>
>

-- 
Martin Berger         +43 660 2978929 <+436602978929>
martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
^∆x      http://berxblog.blogspot.com


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