Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: indexes and organize by dimensions

    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •