Re: Estimating a PK index rebuild with REVERSE
- From: Luis Santos <lsantos@xxxxxxxxx>
- To: Jonathan <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Tue, 31 Oct 2017 11:34:59 -0200
For reference - you had a fast full scan, so you needed the hint
no_index_ffs() as well. (no_)index() is about index range scans and index
full scans.
Jonathan, thanks for the input!
*--*
*Att*
*Luis Santos*
2017-10-25 18:33 GMT-02:00 Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>:
For reference - you had a fast full scan, so you needed the hint
no_index_ffs() as well. (no_)index() is about index range scans and index
full scans.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Luis Santos <lsantos@xxxxxxxxx>
Sent: 25 October 2017 20:53:14
To: Mark W. Farnham
Cc: ORACLE-L
Subject: Re: Estimating a PK index rebuild with REVERSE
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
[https://lh3.googleusercontent.com/-AD-URpt0jeE/AAAAAAAAAAI/
AAAAAAAB-9c/LrffscVVpf8/s90-c-k/photo.jpg]
2017-10-25 12:32 GMT-02:00 Mark W. Farnham <mwf@xxxxxxxx<mailto:mwf@rsiz.
com>>:
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
[https://lh3.googleusercontent.com/-AD-URpt0jeE/AAAAAAAAAAI/
AAAAAAAB-9c/LrffscVVpf8/s90-c-k/photo.jpg]
Other related posts:
