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
