Home » SQL & PL/SQL » SQL & PL/SQL » Optimizing ALTER TABLE DDL statement
Optimizing ALTER TABLE DDL statement [message #250687] Tue, 10 July 2007 18:58 Go to next message
concorde800
Messages: 52
Registered: May 2007
Member
I have the below code:

ALTER TABLE rpt_fam_FWEQ_detail_aggr_pivot TRUNCATE PARTITION P_EMEA UPDATE GLOBAL INDEXES; 


I intend to optimize execution on the above table. Creating indexes on this large table is one thing on my mind.

What else can I do to optimize operations on this table?

Re: Optimizing ALTER TABLE DDL statement [message #250688 is a reply to message #250687] Tue, 10 July 2007 20:22 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
STATISTICS...
Re: Optimizing ALTER TABLE DDL statement [message #250691 is a reply to message #250688] Tue, 10 July 2007 21:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Truncating a partition is fast, removing the truncated rows from affected global indexes is slow. It's nigh impossible to tune deletes from an index except possibly by tuning your database setup and/or hardware.

If possible, you can reduce the problem by converting some global indexes to local.
- Any index that contains the table partition key should be local, not global.
- Indexes that are only used for long range scans or full scans tend not to impact performance when they are local.
- Converting Global indexes to local excluding the partition key can adversely affect performance of short range scans.
- Bitmap indexes should always be local, because they are never used for short scans.

These should only be used as a guide. Every rule has its exceptions, but this should tell you whether you have too many global indexes.

Ross Leishman
Re: Optimizing ALTER TABLE DDL statement [message #261055 is a reply to message #250691] Tue, 21 August 2007 11:52 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
Thanks for your tip.

Do you suggest I invalidate the global indexes by performing and INVALIDATE of global indexes? Since the table already has indexes created, do I have to recreate local indexes on the same columns?

ALTER TABLE rpt_fam_FWEQ_detail_aggr TRUNCATE PARTITION P_EMEA INVALIDATE GLOBAL INDEXES


Since the table already has indexes created, do I have to recreate local bitmap indexes (after invalidating global indexes) on the same columns over again?

Thanks.


Re: Optimizing ALTER TABLE DDL statement [message #261093 is a reply to message #261055] Tue, 21 August 2007 15:48 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
I have taken out the updating global indexes and created local bitmap index and now on a syntax error "ORA-14010: this physical attribute may not be specified for an index partition"

Could any one possibly suggest why this error occurs?



ALTER TABLE rpt_fam_FWEQ_detail_aggr TRUNCATE PARTITION P_EMEA;
/
CREATE bitmap INDEX OPS_SUPPORT.RPT_FAM_FWEQ_DETAIL_AGGR_IDX1 ON RPT_FAM_FWEQ_DETAIL_AGGR (PLAN_CTRY)
 LOCAL
 (
   PARTITION P_EMEA 
   TABLESPACE RPT_DATA 
   NOLOGGING 
   PCTFREE 10 
   MAXTRANS 255 
   STORAGE (
   			INITIAL 16M  
			NEXT 16M  
			MINEXTENTS 1   
			MAXEXTENTS 2147483645   
			FREELISTS 1   
			FREELIST GROUPS 1   
			BUFFER_POOL DEFAULT
		   )
NOPARALLEL;
/
Re: Optimizing ALTER TABLE DDL statement [message #261126 is a reply to message #261093] Tue, 21 August 2007 22:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Are you using locally managed or dictionary managed tablespaces? You shouldn't need the storage clause, and some of the other attributes are defaulted.

I think your SQL is also missing a bracket.

CREATE bitmap INDEX OPS_SUPPORT.RPT_FAM_FWEQ_DETAIL_AGGR_IDX1 ON RPT_FAM_FWEQ_DETAIL_AGGR (PLAN_CTRY)
 LOCAL
 (
   PARTITION P_EMEA 
   TABLESPACE RPT_DATA 
 )


When you create an index on a partitioned table, you need to specify all of the partitions.

Ross Leishman
Re: Optimizing ALTER TABLE DDL statement [message #261503 is a reply to message #261126] Wed, 22 August 2007 16:34 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
The tablespaces are dictionary managed.

The question that intrigues me is, Do I invalidate and/or drop all the indexes (on other columns) and add local bitmap index on partition key?

OR Do I keep the other indexes intact (only avoid updating the indexes) and simply add local bitmap to the partition key column of plan_ctry.

The table and indexes gets created using the below code:

CREATE TABLE RPT_FAM_APRL_DETAIL_AGGR
(
  PLAN_CTRY                VARCHAR2(4 BYTE),
  DIVISION                 VARCHAR2(2 BYTE),
  SEASON                   VARCHAR2(4 BYTE),
  FS_VA                    VARCHAR2(1 BYTE),
  TIER_NUM                 NUMBER(2),
  LEVEL_NUM                NUMBER(3),
  TIER_VOLUME              NUMBER,
  TRG_EVENT_NUM            NUMBER,
  DISP_EVENT_NUM           NUMBER(3),
  COMPARE_NAME             VARCHAR2(100 BYTE),
  FIRST_DISPLAY_EVENT_NUM  NUMBER(3),
  LEVEL_TYPE               VARCHAR2(10 BYTE),
  LEVEL_VALUE              VARCHAR2(163 BYTE),
  ROW_TYPE                 VARCHAR2(6 BYTE),
  MKT_TYP_ID               VARCHAR2(4 BYTE),
  MATERIAL                 VARCHAR2(14 BYTE),
  STY_ID                   NUMBER(19),
  PROD_ID                  NUMBER(19),
  SRC_STY_ID               NUMBER(6),
  STY_DSP_NBR              VARCHAR2(14 BYTE),
  STY_NM                   VARCHAR2(45 BYTE),
  CAT_BUS_CD1              NUMBER(6),
  CAT_BUS_DESC1            VARCHAR2(45 BYTE),
  CAT_BUS_CD2              NUMBER(6),
  CAT_BUS_DESC2            VARCHAR2(45 BYTE),
  CAT_BUS_CD3              NUMBER(6),
  CAT_BUS_DESC3            VARCHAR2(45 BYTE),
  CAT_BUS_CD4              NUMBER(6),
  CAT_BUS_DESC4            VARCHAR2(45 BYTE),
  COLR_COMB_ID             NUMBER(19),
  COLR_COMB_SDESC          VARCHAR2(40 BYTE),
  EXP_STY_IND              VARCHAR2(1 BYTE),
  SILH_CD                  VARCHAR2(8 BYTE),
  SILH_DESC                VARCHAR2(45 BYTE),
  KEY_MATERIAL             VARCHAR2(24 BYTE),
  F1_QTY                   NUMBER,
  F2_QTY                   NUMBER,
  AE                       NUMBER,
  APE                      NUMBER,
  ZZ_INSERT_TMST           DATE
)
TABLESPACE RPT_DATA
PCTUSED    40
PCTFREE    10
INITRANS   13
MAXTRANS   255
PARTITION BY LIST (PLAN_CTRY) 
(  
  PARTITION P_EMEA VALUES ('EMEA')
    NOLOGGING
    NOCOMPRESS
    TABLESPACE RPT_DATA
    PCTUSED    40
    PCTFREE    2
    INITRANS   13
    MAXTRANS   255
    STORAGE    (
                INITIAL          16M
                NEXT             16M
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION P_USA VALUES ('USA')
    NOLOGGING
    NOCOMPRESS
    TABLESPACE RPT_DATA
    PCTUSED    40
    PCTFREE    2
    INITRANS   13
    MAXTRANS   255
    STORAGE    (
                INITIAL          16M
                NEXT             16M
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
                BUFFER_POOL      DEFAULT
               )
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE INDEX RPT_FAM_APRL_DETAIL_AGGR_IDX1 ON RPT_FAM_APRL_DETAIL_AGGR
(PLAN_CTRY, DIVISION, SEASON, FS_VA, LEVEL_NUM, 
TRG_EVENT_NUM, DISP_EVENT_NUM, COMPARE_NAME, LEVEL_VALUE)
NOLOGGING
TABLESPACE RPT_INDEX
PCTFREE    1
INITRANS   13
MAXTRANS   255
STORAGE    (
            INITIAL          16M
            NEXT             16M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX RPT_FAM_APRL_DETAIL_AGGR_IDX2 ON RPT_FAM_APRL_DETAIL_AGGR
(PLAN_CTRY, LEVEL_NUM, DISP_EVENT_NUM, ROW_TYPE)
NOLOGGING
TABLESPACE RPT_INDEX
PCTFREE    1
INITRANS   13
MAXTRANS   255
STORAGE    (
            INITIAL          16M
            NEXT             16M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
Re: Optimizing ALTER TABLE DDL statement [message #261627 is a reply to message #261503] Thu, 23 August 2007 02:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Your choice of indexes should not be dictated by speed of truncating partitions; it is based on the need for indexed access to the table.

* If any primary or unique key includes the partition column, make it LOCAL, otherwise make it global.
* If any non-unique key is used for short range scans (say, between 1 and 10n, where n is the number of partitions) it probably should be global unless the range scans will include the partition key column whereby it could be made local.
* Long range scan non-unique indexes - including all bitmap indexes - should be made local.

In your example, both indexes are prefixed with the partition column, meaning that scans MUST use the partition key in the WHERE clause. This means they should be locally partitioned but you have not specified partitions or the LOCAL keyword, so you will get GLOBAL indexes.

Based on the number of columns present in these indexes, I very much doubt you want them to be bitmap indexes. B-tree seems much more likely.

You should alter the CREATE INDEX statements to include the LOCAL keyword and partition specifications. Then, when you truncate a partition, just specify the UPDATE INDEXES clause to efficiently drop the LOCAL index partitions whilst keeping the remaining partitions valid.

I'd also suggest upgrading to locally managed tablespaces.

Ross Leishman
Re: Optimizing ALTER TABLE DDL statement [message #261830 is a reply to message #261627] Thu, 23 August 2007 13:47 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
Thank you for guidance provided on this.

I have now created the index and the alter table statements using the guidance.



1.
CREATE INDEX RPT_FAM_FWEQ_DETAIL_AGGR_IDX1 ON RPT_FAM_FWEQ_DETAIL_AGGR
(PLAN_CTRY, DIVISION, SEASON, MONTHLY_SEQ, BUCKET_MONTH_DATE, 
LEVEL_NUM, TRG_EVENT_NUM, DISP_EVENT_NUM, COMPARE_NAME, LEVEL_VALUE)
NOLOGGING
TABLESPACE RPT_INDEX
PCTFREE    1
INITRANS   13
MAXTRANS   255
STORAGE    (
            INITIAL          16M
            NEXT             16M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
[B]LOCAL
PARTITION BY LIST (PLAN_CTRY)
		    (
			PARTITION P_EMEA VALUES ('NEON'),
			PARTITION P_USA VALUES ('USA')
		    )[/B]NOPARALLEL;


2.
ALTER TABLE rpt_fam_FWEQ_detail_aggr TRUNCATE PARTITION P_EMEA UPDATE INDEXES;

Re: Optimizing ALTER TABLE DDL statement [message #262073 is a reply to message #261830] Fri, 24 August 2007 07:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
And now that you're using locally partitioned indexes instead of global indexes, you're going to regression test your application. Right?

By the way, did it solve your problem?

Ross Leishman
Re: Optimizing ALTER TABLE DDL statement [message #262182 is a reply to message #262073] Fri, 24 August 2007 17:30 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
Regression testing is done.

There's still a small issue with syntax of missing keyword on the alter table statement.

ALTER TABLE rpt_fam_FWEQ_detail_aggr TRUNCATE PARTITION P_EMEA UPDATE INDEXES


This statement gives the error "missing keyword", as the keyword global has been removed from the original statement.

I thought removing the keyword would make sure the non-partitioned indexes get updated!
Re: Optimizing ALTER TABLE DDL statement [message #262384 is a reply to message #262182] Sun, 26 August 2007 21:27 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
UPDATE INDEXES is a 10g thing. I included it out of habit although I suspect it is unneccessary. If you are on 9i, just remove those two words - Oracle will truncate the local indexes.

Ross Leishman
Re: Optimizing ALTER TABLE DDL statement [message #266331 is a reply to message #262384] Mon, 10 September 2007 10:03 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
Thanks. So now the global indexes are changed to local indexes. I am doing so by having a script that drops the existing (global) indexes and recreating them as local b-tree index.

Do I need to reload the data prior to recreating local indexes? Or a simple drop and recreate would be fine, since data exists in the table anyway?
Re: Optimizing ALTER TABLE DDL statement [message #266337 is a reply to message #266331] Mon, 10 September 2007 10:44 Go to previous message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hopefully, you don't need to reload data when you want to drop, create or rebuild indexes.

Regards
Michel
Previous Topic: RowType In Function List
Next Topic: External tables -alter table
Goto Forum:
  


Current Time: Thu Dec 08 06:02:21 CST 2016

Total time taken to generate the page: 0.26703 seconds