DML Autocommit in oracle

In oracle we need to do commit the DML (Data Manipulation Language) transaction explicitly.
By default DML operation are not autocommited in oracle.
But there is provision in oracle to do autocommit on for every DML transaction.

SQL>set autommit on;

After doing this, oracle will commit every dml, and data will written in datafiles.

Topic	Auto-Commit On	Auto-Commit Off
Transactions	Each statement is a separate transaction.	Commit() or rollback() completes a transaction.
Database-side JDBC routines (routines that use nested connections)	Auto-commit is turned off.	Works (no explicit commits or rollbacks are allowed).
Updatable cursors	Works for holdable cursors; does not work for non-holdable cursors.	Works.
Multiple connections accessing the same data	Works.	Works. Lower concurrency when applications use SERIALIZABLE isolation mode and table-level locking.
Updatable ResultSets	Works.	Works.
Savepoints	Does not work.	Works.

source - http://docs.oracle.com/javadb/10.8.1.2/devguide/cdevconcepts29416.html

Fact Tables and Dimension Tables

In Datawarehouse we use can use two types database :
    1. snowflake schema
    2. star schema
   
Generally we used star schema on other hand snowflake schema used very rare.

What is star schema:-
Star schema use fact tables and dimension tables. In star schema only one join
establishes the relationship between the fact table and any one of the dimension tables.A star
schema has one fact table and is associated with numerous dimensions table and depicts a star.

Fact tables:
    A fact table typically has two types of columns: those that contain numeric facts (often called measurements),
    and those that are foreign keys to dimension tables.
    A fact table contains either detail-level facts or facts that have been aggregated.
   
    In other words , if we design query for report we always use aggregate functions in select clause of query.
    The column we used in select clause    for aggregation is always taken from fact table i.e. called numeric facts.

Dimension tables:
    A dimension is a structure, often composed of one or more hierarchies, that categorizes data. Dimensional
    attributes help to describe the dimensional value. They are normally descriptive, textual values an.
    Dimension tables are generally small in size as compared to fact table.
   
    In other words, these are columns used in query for "group by" clause and used when we need some extra detail
    for the aggregate columns.
   

A example for fact ,dimension table as following:

Fact table :- Fact_UserData
Dimension table :- Dim_User, Dim_Location, Dim_Occupation

Fact_UserData
    User ID
    Location ID
    Occupation ID
    columns contains numeric data that can be meaningfully aggregated

Dim_User
    User ID
    Gender
    Ethnicity

Dim_Location
    Location ID
    District
    City
    State

Dim_Occupation
    Occupation ID
    Occupation Name

query might be like this
select
    aggregate_function(column numeric data),columns_from_dim_table
from
Fact_UserData join Dim_User on Fact_UserData.User ID=Dim_User.User ID
left outer join Dim_Location on Fact_UserData.Location ID=Dim_Location.Location ID
left outer join Dim_Occupation on Fact_UserData.Occupation ID=Dim_Occupation.Occupation ID
group by dim_table_columns
   
What is Snowflake schema:-
    The snowflake schema (sometimes called snowflake join schema) is a more complex schema
    than the star schema because the tables which describe the dimensions are normalized. It is used when a dimensional
    table becomes very big.In snow flake schema since there is relationship between the dimensions Tables it
    has to do many joins to fetch the data.Every dimension table is associated with sub dimension table.
    A star schema may be partially normalized (snowflaked), with related information stored
    in multiple related dimension tables, to support specific data warehousing needs.



OLAP data is typically stored in a star schema or snowflake schema in a relational data warehouse or in a special-purpose data management system. Measures are derived from the records in the fact table and dimensions are derived from the dimension tables.