Too bad you are not on 12c.
I wonder if You could create the new index invisible to avoid ORA-01408.
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@xxxxxxxxx <mailto:lsantos@xxxxxxxxx>> wrote:
Ok, it's here. First our DB version and PSU.
[11g]> @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@P01CRV.brux0387 [11g]> @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@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@xxxxxxxx
<mailto:mwf@xxxxxxxx>>:
Would you add the actual SQL text you submitted?
mwf
*From:*oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] *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]> @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@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/
//
