DDL for LOCAL INDEXES not properly generated

[orca777]

Oracle 10.2 AIX
PL/SQL-DEV 7.1.b2; Windows Server 2003

Hi DevTeam,

On a partitioned table indexes created as local are not generated properly by the edit/view Table dialog with 'View SQL';

You use only dba_indxes view for the SQL generation. But if an index has the property db_indexes.partitioned = 'YES' the you must read the storage from dba_ind_partitions.

-- wrong DDL
create index COMPANY_FX01 on COMPANY (UPPER(TRIM(CUSTOMERNAME)));
create index COMPANY_FX02 on COMPANY (UPPER(TRIM(REALNAME)));
create index COMPANY_FX03 on COMPANY (UPPER(TRIM(CITY)));
create index COMPANY_FX04 on COMPANY (UPPER(TRIM(SHORTNAME)));
create index COMPANY_IX01 on COMPANY (CUSTOMER, OPENINGDATE);
create index COMPANY_IX02 on COMPANY (CUSTOMER, CLOSINGDATE);
create index COMPANY_IX03 on COMPANY (CUSTOMER);

-- correct DDL
create index COMPANY_FX01 on COMPANY (UPPER(TRIM(CUSTOMERNAME)))
  LOCAL tablespace ISISIXL
;

create index COMPANY_FX02 on COMPANY (UPPER(TRIM(REALNAME)))
  LOCAL tablespace ISISIXL
;

create index COMPANY_FX03 on COMPANY (UPPER(TRIM(CITY)))
  LOCAL tablespace ISISIXL
;

create index COMPANY_FX04 on COMPANY (UPPER(TRIM(SHORTNAME)))
  LOCAL tablespace ISISIXL
;

create index COMPANY_IX01 on COMPANY (CUSTOMER, OPENINGDATE)
  LOCAL tablespace ISISIXL
;

create index COMPANY_IX02 on COMPANY (CUSTOMER, CLOSINGDATE)
  LOCAL tablespace ISISIXL
;
create index COMPANY_IX03 on COMPANY (CUSTOMER)
  LOCAL tablespace ISISIXL
;

cheers Karl

 

[Marco Kalter]

This is on the list of enhancement requests.

 

[orca777]

Yeah!
Marco on which rank ;-)

Karl