RE: Estimating a PK index rebuild with REVERSE

From: Reen, Elizabeth <"Reen,>
Date: Tue, 31 Oct 2017 14:25:30 +0000
Message-ID: <258575162B63424EB58DAE3A5475B6ED012CC26623_at_EXNJMB25.nam.nsroot.net>

Talk about having your cake but not being able to eat it.

Liz

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Luis Santos Sent: Tuesday, October 31, 2017 9:56 AM To: andysayer_at_gmail.com
Cc: ORACLE-L
Subject: Re: Estimating a PK index rebuild with REVERSE

I have just discovered that I can issue this below!

ALTER INDEX <INDEX_NAME> REBUILD REVERSE

But, unfortunately to my case, I can't perform this on a partitioned index.

--
Att
Luis Santos
[https://lh3.googleusercontent.com/-AD-URpt0jeE/AAAAAAAAAAI/AAAAAAAB-9c/LrffscVVpf8/s90-c-k/photo.jpg]

2017-10-31 11:47 GMT-02:00 Luis Santos <lsantos_at_pobox.com<mailto:lsantos_at_pobox.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<mailto:U71013576_at_P01CRV.brux0387> [11g]> @xplan

PLAN_TABLE_OUTPUT

Plan hash value: 2391156251

| 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
[https://lh3.googleusercontent.com/-AD-URpt0jeE/AAAAAAAAAAI/AAAAAAAB-9c/LrffscVVpf8/s90-c-k/photo.jpg]

2017-10-25 17:58 GMT-02:00 Andy Sayer <andysayer_at_gmail.com<mailto: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<mailto: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<https://urldefense.proofpoint.com/v2/url?u=https-3A__blog.pythian.com_oracles-2Dcreate-2Dindex-2Dcommand-2Dcan-2Dtake-2Dhint&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=Hji1HXQku6kimvIrNw6ziLtCsuEy-Vbv0Nnj82uyLRs&s=B0sZT0bFPX_PZANleyibUUf31QcjDnZjPyzPlLxH2Mw&e=>

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<mailto:U71013576_at_P01CRV.brux0387> [11g]> @xplan PLAN_TABLE_OUTPUT

Plan hash value: 961639110

| 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
[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_at_rsiz.com<mailto: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> [mailto: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

| 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
[https://lh3.googleusercontent.com/-AD-URpt0jeE/AAAAAAAAAAI/AAAAAAAB-9c/LrffscVVpf8/s90-c-k/photo.jpg]

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

Received on Tue Oct 31 2017 - 15:25:30 CET

This message: [ Message body ]
Next message: Iggy Fernandez: "Community announcement: NoCOUG Journal and NoCOUG conference"
Previous message: Luis Santos: "Re: Estimating a PK index rebuild with REVERSE"
In reply to: Luis Santos: "Re: Estimating a PK index rebuild with REVERSE"
In reply to Mark W. Farnham: "RE: Estimating a PK index rebuild with REVERSE"
Next in thread: Bill Ferguson: "Re: TNS-12541: TNS:no listener"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message