Re: Estimating a PK index rebuild with REVERSE
Date: Tue, 31 Oct 2017 11:47:47 -0200
Message-ID: <CAPWdmV8jS9Zkqr0asHbkB+zSEFZaVcqM0_pVt-1YdhrLw29bUA_at_mail.gmail.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-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-lReceived on Tue Oct 31 2017 - 14:47:47 CET