indexes and organize by dimensions

Wim · #1 (**permalink**) 03-08-07, 12:03

Hi,

I have used the ORGANIZE BY DIMENSIONS option for the first time in our data warehouse.

Code:

CREATE TABLE myTable (
   ID_COMPANY      INTEGER  NOT NULL,
   YEAR_FISCAL     SMALLINT NOT NULL,
   ID_CO_ACTIVITY  SMALLINT  NOT NULL,
   .....
   constraint pkMYtABLE primary key (ID_COMPANY, YEAR_FISCAL, ID_CO_ACTIVITY)
) ORGANIZE BY DIMENSIONS (YEAR_FISCAL)
;

I noticed that inserting records was slower (8 minutes now vs. 5 min before). I hope the performance gain will follow.

I wonder what we must do with the existing indexes on that table.

CREATE INDEX index1 ON myTable (YEAR_FISCAL);
Can it be omitted totally?

CREATE INDEX index2 ON myTable (ID_COMPANY, YEAR_FISCAL);
What should this become ?
CREATE INDEX index2 ON myTable (ID_COMPANY); ?

I have found some information about MDC tables (Multi-Dimensional Clustering). I assume that, though my table is single dimensional, the same rules apply.

Are there any good sources of information about ORGANIZE BY DIMENSIONS?

This is new territory for me, any help is welcome.

sathyaram_s · #2 (**permalink**) 03-08-07, 12:29

YEAR_FISCAL only-index will be obsolete now..

The second one may still be useful ... It could depend on cardinality, your actual query, filter factors etc. From another perspective, this is not useful as you have these two columns as the leading columns in the PK index.

HTH

Sathyaram

Wim · #3 (**permalink**) 03-12-07, 05:44

Sathyaram,

Thank you for your reply.

Have you any experience about the performance gain? It's in a table at the end of our data warehouse creation chain, so only users will notice any performance gains.

Internet Services

Web Development

Digital Marketing

Consumer Tech