Bases de datos

Oracle Materialized views to optimize a Datawarehouse

Submitted by carlos on 23 October, 2008 - 12:13.

Datawarehouse loadings are periodic, and summary tables are useful to improve efficiency and the answer time of our reports. A physical optimisation resource that can bring big improvements in our system is the utilization of materialized views.

The view materialized is not more that a view, defined with one sentences SQL, of which in addition to storing his definition, store the data that returns, realizing an initial load and afterwards each true time a freshen of the same.

Like this, if we have a Datawarehouse that update daily, could use views materialized to go updating intermediate tables that feed our diagrams of DWH, or directly to implement tables agregadas that will freshen from our tables base. 

The creation of this type of views no so complex as it can seem, the most important is to have clear each how much time want to update the information of the views, and which method of freshen to use.

Also will have to ensure us that our licence of base of data allows us use them (has to be a version Enterprise).

 

How to create a new Oracle scheme step by step

Submitted by carlos on 23 July, 2008 - 15:53.

In order to be able to make all the steps you must initiate the session in the database with a user with administration permissions, you can use the SYSTEM user, for example

  • Creation of tablespace for data, and another one for index.

These tablespaces are the location where Oracle will save the objects of the scheme which we are going to create.

Tablespace for data, with initial size of 1024 Mb, and autoextensive

CREATE TABLESPACE "APPDAT" LOGGING DATAFILE '/export/home/oracle/oradata/datafiles/APPDAT.dbf' SIZE 1024M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

  • Tablespace for indices, with initial size of 512 Mb, and autoextensive

CREATE TABLESPACE "APPIDX" LOGGING DATAFILE '/export/home/oracle/oradata/datafiles/APPIDX.dbf' SIZE 512M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

  • Creation of the user who is going to work on these tablespaces, and who will be the owner of the objects that are created in them

CREATE USER “APP” PROFILE “DEFAULT” IDENTIFIED BY “APPPWD”

DEFAULT TABLESPACE “APPDAT” TEMPORARY TABLESPACE “TEMP” ACCOUNT UNLOCK;

Oracle online documentation

Submitted by cfb on 23 September, 2006 - 20:21.

If you want to access the Oracle 9i online documentation you can do it following the link

  • Help online Oracle 9i

    • Is the original Oracle Help installed in a public access site.

    Find with Google engine

    Google
     
         
    Syndicate content