Forum Stats

  • 3,872,010 Users
  • 2,266,363 Discussions
  • 7,911,026 Comments

Discussions

While Creating an ORACLE table configure the ORIENTATION(Row Orientation/Column Orientation)

user9319583
user9319583 Member Posts: 10
edited Jan 11, 2016 5:49PM in Database Ideas - Ideas

As we know we are in the age of BIG DATA and slowly and steadily as the data gets bigger DWH environments will slowly find its way in cloud-based scale out systems.

In a typical DWH environment having very HUGE fact tables sometimes we want to keep several layers of  hierarchy in the same table rather than recalculating the roll-ups each time .Here  the usage of centipede fact tables are very essential which can store a number of hierararchy

within the same FACT table.In typical RDMS the method osf drawing data from table consists of 2 below mentioned methods as they are ROW oriented.

1.Selection

2.Projection

Due to the above mentioned property a databse like ORACLE or DB2 tends to select the entire (SELECTION) then it tends to select the REQUIRED COLUMNS.But in DWH environment this degrades the performance especially while we tend to use CENTIPEDE FACT tables.

As in particular SLICING report we tend to see a e few column esepecially in rolled-up queries.So here if ORACLE can come up with a concept of COLUMN-ORIENTATION or COLUMN-FAMILY based approach then we can have the luxury to follow the Method of PROJECTION wherein the column family or COLUMN group is taken first then the required number of rows taken according to the predicates.This will add a lot of performance to the rolled-up Queries and Performance enhancements of CENTIPEDE FACT table or Fast delivery of hierarchial reports.

The main theme of the thsi IDEA is that while we create a table in ORACLE by

CREATE TABLE(COL DATA_TYPE) command ,

a new clause can be introduced like ROW-RIENTED/COLUMN_ORIENTED can be introduced which will align the table according To ROW_FORMAT like normal ORACLE table or COLUMN-FORMATTED like HBASE or CASSANDRA according to appropriate situation @user9319583

top.gun
9 votes

Active · Last Updated

Comments

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown

    I've never heard of Centipede Fact Tables before, and when I look it up I only find it mentioned as an anti-pattern (too many interrelated dimensions that would be better combined) e.g. Kimball Group: Centipede Fact Tables.

    It's also not clear what this has to do with projection, or what projection has to do with performance or "orientation", or really what problem is being addressed by this suggestion. Sorry.

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    Doesn't Oracle already allow both columnar and row-based tables?

  • user9319583
    user9319583 Member Posts: 10

    Doesn't Oracle already allow both columnar and row-based tables?

    Centipede FACT tables are used where we want to store multiple hierarchies in the fact table itself instead og re-calculating them by rolling up.

    So this will make fact tables to grow very wide as it contains day key as well as week key as well as month key and so on.

    Now if we follow Selection and projection rule then we have to load the entire rows then get the necessary columns required in specific reports as the reports

    will need only specific set of columns for the desired hierarchy not all of them.

    So in this case if the DBMS has the capability of storing the tables as column-sets merged together then it will come handy as we will choose the specific column-set or column-family that

    contains the hierarchy supporting the reports.Only those columns will be chosen from tables which will reduce the MEMORY consumption and lower DISK reads for optimal performance.

  • Gerald Venzl-Oracle
    Gerald Venzl-Oracle Member, Moderator Posts: 85 Employee

    Oracle does indeed already provide columnar oriented data format and also holds that format in-memory for ultra fast access. This feature (including others) is bundled in the Oracle In-Memory Option on top of Oracle EE 12.1.0.2: http://www.oracle.com/technetwork/database/options/database-in-memory-ds-2210927.pdf

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown

    Centipede FACT tables are used where we want to store multiple hierarchies in the fact table itself instead og re-calculating them by rolling up.

    So this will make fact tables to grow very wide as it contains day key as well as week key as well as month key and so on.

    Now if we follow Selection and projection rule then we have to load the entire rows then get the necessary columns required in specific reports as the reports

    will need only specific set of columns for the desired hierarchy not all of them.

    So in this case if the DBMS has the capability of storing the tables as column-sets merged together then it will come handy as we will choose the specific column-set or column-family that

    contains the hierarchy supporting the reports.Only those columns will be chosen from tables which will reduce the MEMORY consumption and lower DISK reads for optimal performance.

    I don't think that is called a Centipede Fact Table. Do you have a reference to a definition for this term? I found one (mentioned above) at Kimball Group and it meant something else.

    Also, storing data from different levels of a hierarchy in one fact table already seems to be part of the standard dimensional warehouse model e.g. described here: https://docs.oracle.com/database/121/DWHSG/dimen.htm#DWHSG8256

  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy

    Hi,

    Oracle has currently two columnar approaches:

    - bitmap indexes which is very efficient for selection. With star transformation and bitmap join index you probably don't need the centripede fact for selection. However, projection still has to access to blocks in row storage

    - in-memory column store where the column store is used both for selection and projection.

    Regards,

    Franck.

    Pravin Takpire
  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    Centipede FACT tables are used where we want to store multiple hierarchies in the fact table itself instead og re-calculating them by rolling up.

    So this will make fact tables to grow very wide as it contains day key as well as week key as well as month key and so on.

    Now if we follow Selection and projection rule then we have to load the entire rows then get the necessary columns required in specific reports as the reports

    will need only specific set of columns for the desired hierarchy not all of them.

    So in this case if the DBMS has the capability of storing the tables as column-sets merged together then it will come handy as we will choose the specific column-set or column-family that

    contains the hierarchy supporting the reports.Only those columns will be chosen from tables which will reduce the MEMORY consumption and lower DISK reads for optimal performance.

    This looks like viloating normalization. In addition it seems to me mviews on dimensions would achieve the same thing. I wonder if the TO besides quoting HBase and Cassandra properly knows relational design.