• Loading
    • Oracle Schema Objects

      Learning Objectives
      After completing this session, you will be able to:
      • Describe and create views
      • Work with indexes
      • Create public or private synonym
      • Write sequence
      • Work with materialized view
      • Work with cluster
      • Work with database link



      Schema Objects
      A schema is a collection of objects .Schema objects are the logical structures that directly refer to the data in a database. Schema objects include
      • Tables
      • Views
      • Synonyms
      • Sequences
      • Stored procedures
      • Indexes
      • Clusters
      • Database links


      Views

      A view is a logical table based on a table or another view. A view contains no data of its own but is like a window through which data from tables can be viewed or changed .The tables on which a view is based are called base tables. The view is stored as a SELECT statement in the in the data dictionary.
      Advantages of Views:
      • Views restrict access to the data because the view can display selective columns from the table.
      • Views can be used to make simple queries to retrieve the result of complex queries.
      • Views provide data independence for ad hoc users and application programs. One view can be used to retrieve data from several tables.
      • Views provide group of users access to data according to their particular criteria.



      There are two classification of View simple and complex.
      • A simple view is one that
      • o Derives data from only one table.
      • o Contains no function or group of data.
      • o Can perform DML operations through the View.
      • A Complex view is one that
      • o Derives data from many tables.
      • o Contains function or group of data.
      • o Does not always allow operations through the View.


      Syntax:
      Code:
      CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
      [(alias)[,alias]…)]
      AS subquery
      [WITH CHECK OPTION [CONSTRAINT constraint]]
      [WITH READ ONLY [CONSTRAINT constraint]];

      OR REPLACE Recreate the view if it already exist
      FORCE Create the view regardless of whether the base table exist or not
      NO FORCE Create the views only if the database table exists. This is the default.
      View Is the name of the view.
      alias Specifies names for the expression selected by the view’s query.
      Subquery Is a complete SELECT statement.
      WITH CHECK OPTION Specifies that only rows accessible to the view can be inserted or updated.
      Constraint Is the name assigned to the CHECK OPTION constraint
      WITH READ ONLY Ensures that no DML operations can be performed on this view.

      Example:
      Code:
      CREATE VIEW funded_loan 
      AS SELECT loanno,loan_amount,loan_status 
      FROM daily_pipeline_loan_fact 
      WHERE loan_status=‘FUNDED’
      Guidelines for creating a View:
      • The subquery that defines a view can contain complex SELECT syntax, including joins, groups and sub queries.
      • The subquery that defines a view can not contain an ORDER BY clause
      • If you do not specify a constrain name for a view created with the WITH CHECK OPTON, the system assigns a default name in the format SYS_Cn.
      • You can use the OR REPLACE option to change the definition of the view without dropping and recreating it or regranting object privileges previously granted on it.


      Retrieving data from a View
      SELECT * FROM funded_loan;
      • When you access data using a view the Oracle server performs the following operations.
      • It retrieves the view definition from the data dictionary table USER_VIEWS.
      • It checks access privileges for the view base table.
      • It converts the view query into an equivalent operation on the underlying base table or tables.


      Modifying a view: A view can be modified by using CREATE OR REPLACE.
      Example:
      Code:
      CREATE OR REPLACE VIEW funded_loan 
      (loanno,loanamount,loanstatus) 
      AS SELECT loanno,loan_amount,loan_status 
      FROM daily_pipeline_loan_fact 
      WHERE loan_status=‘FUNDED’

      Example of Complex VIEW:

      Code:
      CREATE OR REPLACE VIEW funded_loan 
      (product_name,minloanamount,maxloanamount) 
      AS SELECT product_name,min(loan_amount), 
      max(loan_amount) 
      loan_status 
      FROM daily_pipeline_loan_fact d, 
      product_dim p 
      Where d.product_id=p.product_id 
      GROUP BY p. product_name

      DML Operations on view:

      You cannot remove a row if the row contains the following:
      • Group functions
      • A GROUP BY clause
      • The DISTINCT keywords
      • The pseudocolumn ROWNUM keyword.



      You cannot modify data in a view if it contains the following:
      • Group functions
      • A GROUP BY clause
      • The DISTINCT keywords
      • The pseudocolumn ROWNUM keyword.


      Columns defined by expressions.

      You cannot modify data through a view if it contains the following:
      • Group functions
      • A GROUP BY clause
      • The DISTINCT keywords
      • The pseudocolumn ROWNUM keyword.
      • Columns defined by expressions
      • NOT NULL columns in the base table that are not selected by view.


      Use of CHECK Option in View Definition: The check option restricts updation of the column which forms the view definition:
      Code:
      CREATE VIEW funded_loan 
      AS SELECT loanno,loan_amount,loan_status 
      FROM daily_pipeline_loan_fact 
      WHERE loan_status=‘FUNDED’ 
      WITH CHECK OPTION;
      The following insert and update command on the view would not work:
      Code:
      INSERT INTO funded_loan (loanno,loan_status) VALUES (1001,’SHIPPED’); 
      UPDATE funded_loan SET loan_status = ‘SHIPPED’;

      Read-only Views:
      The Read-only view allows the user only to read from the view:
      • CREATE VIEW funded_loan
      • AS SELECT loanno,loan_amount,loan_status
      • FROM daily_pipeline_loan_fact
      • WHERE loan_status=‘FUNDED’
      • WITH READ ONLY;
      • WITH READ ONLY clause specifies that you will only be able to select records from the view.

      Thus modifications to the base table through the view are prevented.

      Removing a View: You can remove a view without losing data because a view is based on underlying tables in the database.
      Code:
      DROP VIEW funded_loan;

      Inline Views:

      Code:
      An inline view is a subquery with an alias (or correlation name) that you can use within a SQL statement. 
      A named subquery in the FROM clause of the main query is an example of inline view. 
      An inline view is not a schema object.
      Example:
      Code:
      SELECT loan.loanno,loan.loan_amount,loan.product_id, maxloan.maxloanamount 
      FROM daily_pipeline_loan_fact loan, 
      (SELECT max(loan_amount) maxloanamount, 
      product_id 
      FROM daily_pipeline_loan_fact 
      GROUP BY product_id) maxloan 
      WHERE loan.product_id=maxloan.product_id 
      AND loan.loanamount< maxloan. Maxloanamount

      Indexes


      Usually, the application developer is responsible for designing the elements of an application, including the tables. DB administrators are responsible for setting storage parameters and defining clusters for tables, based on information from the application developer about how the application works and the types of data expected.

      While working with your application developer, carefully plan each table so that the following occurs:
      • Tables are normalized.
      • Each column is of the proper data type.
      • Columns that allow nulls are defined last, to conserve storage space


      Indexes are optional structures associated with tables. Indexes can be created to increase the performance of data retrieval. Just as the index in this manual helps you quickly locate specific information, an Oracle index provides an access path to table data.

      Indexes are created on one or more columns of a table. After it is created, an index is automatically maintained and used by Oracle. Changes to table data (such as adding new rows, updating rows, or deleting rows) are automatically incorporated into all relevant indexes with complete transparency to the users.

      An index can be created automatically or manually.
      • Automatically: A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.
      • Manually: Users can create non-unique indexes on columns to speed up the access to the rows


      Syntax:
      Code:
      CREATE INDEX index_name 
      ON table (column[, column]…);
      Example:
      Code:
      CREATE INDEX daily_loan_status_idx 
      ON daily_pipeline_loan_fact(loan_status)

      You should create an index if:

      • A column contains a wide range of values.
      • A column contains large number of null values.
      • One or more columns are frequently used together in a where clause or a join condition.
      • The table is large and most queries are expected to retrieve less than 2 to 4 % of the rows.


      It is usually not worth creating an index if:
      • The table is small.
      • The columns are not often used as a condition in the query.
      • Most queries are expected to retrieve more than 2 to 4 % of the rows in the table.
      • The table is updated frequently.
      • The indexed columns are referenced as part of an expression


      B-tree index
      B-tree indexes are used to avoid large sorting operations. For example, a SQL query requiring 10,000 rows to be presented in sorted order will often use a b-tree index to avoid the very large sort required to deliver the data to the end user. Oracle offers several options when creating an index using the default b-tree structure. It allows you to index on multiple columns (concatenated indexes) to improve access speeds. Also, it allows for individual columns to be sorted in different orders. For example, we could create a b-tree index on a column called last_name in ascending order and have a second column within the index that displays the salary column in descending order.
      Code:
      Create index 
      name_salary_idx 
      on
      person
      (
      last_name asc,
      salary desc);

      When processing a request, Oracle can use some or all of the available indexes to locate the requested rows efficiently. Indexes are useful when applications frequently query a table for a range of rows (for example, all employees with a salary greater than 1000 dollars) or a specific row.


      While b-tree indexes are great for simple queries, they are not very good for the following situations:
      • Low-cardinality columns—columns with less than 200 distinct values do not have the selectivity required in order to benefit from standard b-tree index structures.
      • No support for SQL functions—B-tree indexes are not able to support SQL queries using Oracle's built-in functions. Oracle9i provides a variety of built-in functions that allow SQL statements to query on a piece of an indexed column or on any one of a number of transformations against the indexed column.


      Bitmapped indexes
      Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table. At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values. These matching values are delivered to Oracle in the form of a Row-ID list, and these Row-ID values may directly access the required information.

      Function – Based Index

      • A function – based index is an index based on expressions.
      • The index expression is built from table columns, constants, SQL functions, and user defined functions.


      Example:
      Code:
      CREATE INDEX upper_loan_status_idx 
      ON daily_pipeline_loan_fact(UPPER(loan_status))

      Removing an index:
      Remove an index by using DROP index command
      Example:
      Code:
      DROP INDEX upper_loan_status_idx;
      To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege


      Synonym
      Simplify access to objects by creating a synonym (another name for an object) with synonym you can do the following:
      • Easy referring to a table owned by another user
      • Shorten lengthy objects name


      Code:
      CREATE [PUBLIC] SYNONYM synonym_name 
      FOR object;
      [PUBLIC] Create a synonym accessible to all users.

      Example:

      Code:
      CREATE SYNONYM daily_loan FOR daily_pipeline_loan_fact;

      Removing a synonym

      Code:
      DROP SYNONYM daily_loan;

      Sequence


      A sequence:
      • Automatically generates unique numbers.
      • Is a sharable object.
      • Is typically used to create a primary key value.


      Syntax:
      Code:
      CREATE SEQUENCE sequence_name 
      [INCREMENT BY n] 
      [START WITH n] 
      [{MAXVALUE n | NOMAXVALUE}] 
      [{MINVALUE n | NOMINVALUE}] 
      [{CYCLE | NOCYCLE }] 
      [{CACHE n | NOCACHE];
      Sequence_name Is the name of the sequence generator
      INCREMENT BY n Specifies the interval between sequence numbers where n is an integer. If this clause omitted, the sequence increments by 1.
      START WITH n Specifies the first sequence number to be generated. Integer. If this clause omitted, the sequence increments by 1.
      MAXVALUE n Specifies the maximum value the sequence can generate.
      NOMAXVALUE Specifies a maximum value of 10^27 for an ascending sequence and -1 for a descending sequence.
      MINVALUE n Specifies the minimum sequence value.

      NOMINVALUE Specifies a minimum value of 1 for an ascending sequence and - 10^27 for a descending sequence
      CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after reaching its minimum or maximum value. NOCYCLE is the default option.
      CACHE n| NOCACHE Specifies how many values the Oracle server pre-allocates and keeps in memory. By default, the oracle server caches 20 values.


      Example:
      Code:
      CREATE SEQUENCE proddim_product_id_seq 
      INCREMENT BY 10 
      START WITH 120 
      MAXVALUE 9999 
      NO CACHE 
      NO CYCLE;
      Verify the sequence values in the USER_SEQUENCES data dictionary table.
      Code:
      SELECT sequence_name,min_value,max_value , 
      increment_by,last_number 
      FROM user_sequences;
      The last_number column specifies the next available sequence number if nocache is specified.

      NEXT VAL and CURRVAL pseudocolumns:

      • NEXTVAL returns the next available sequence value.
      • CURRVAL obtains the current sequence value.
      • NEXTVAL must be issued for that sequence before CURRVAL contains a value.


      You can use NEXTVAL and CURRVAL in the following contexts:
      • The SELECT list of a SELECT statement that is not part of a subquery.
      • The SELECT list of a subquery in an INSERT statement.
      • The values clause of an INSERT statement
      • The SET clause of an UPDATE statement

      You cannot use NEXTVAL and CURRVAL in the following contexts
      • The select list of a view.
      • A select statement with the distinct keyword.
      • A select statement with the GROUP BY, HAVING, or ORDER_BY clause.
      • A subquery in a SELECT, DELETE, or UPDATE statement.
      • The DEFAULT expression in a CREATE TABLE or ALTER TABLE statement.


      Modifying a sequence: A sequence can be modified by using ALTER SEQUENCE statement.

      • You must be the owner or have the ALTER privilege for the sequence in order to modify it.
      • Only future sequence numbers are affected by the ALTER SEQUENCE statement.
      • The START WITH option cannot be changed using ALTER SEQUENCE. The sequence must be dropped and re-created in order to restart the sequence at a different number.
      • Some validation is performed. For example, a new MAXVALUE that is less than the current sequence number cannot be imposed.

      Example:
      Code:
      ALTER SEQUENCE proddim_product_id_seq 
      INCREMENT BY 20 
      MAXVALUE 99999 
      NO CACHE 
      NO CYCLE

      Removing a sequence:
      Remove a sequence from the data dictionary by using the DROP SEQUENCE statement. Once removed, the sequence can no longer be referenced.
      Code:
      DROP SEQUENCE proddim_product_id_seq;

      Materialized View


      Materialized view is a database object that stores the result of a query. Materialized view automates the data replication and refreshes process. When Materialized view are created a refresh interval is established to schedule refreshes of replicated data .
      Features/Capabilities
      • Can be partitioned and indexed
      • Can be queried directly
      • Can have DML applied against it
      • Several refresh options are available
      • Best in read-intensive environments


      Advantages
      • Useful for summarizing, pre-computing, replicating and distributing data
      • Faster access for expensive and complex joins
      • Transparent to end-users
      • MVs can be added/dropped without invalidating coded SQL


      Disadvantages
      Code:
      Performance costs of maintaining the views 
      Storage costs of maintaining the views
      Syntax:
      • CREATE MATERIALIZED VIEW <name>
      • {<storage parameters>}
      • <build option>
      • REFRESH <refresh option> <refresh mode>
      • [FOR UPDATE]
      • [ENABLE|DISABLE] QUERY REWRITE
      • AS SELECT <select clause>;


      The create MV’s command has four major section.

      • CREATE MATERIALIZED VIEW <name>
      • The second section of the create MV’s is as follows:

      {<storage parameters>}
      <build option>
      The <build option> determines when MV is built
      BUILD IMMEDIATE: view is built at creation time
      BUILD DEFFERED: view is built at a later time
      ON PREBUILT TABLE: use an existing table as view source
      • REFRESH <refresh option> <refresh mode>

      Refresh Options are as follows:
      COMPLETE:
      • Totally refreshes the view
      • Can be done at any time; can be time consuming


      FAST:
      • incrementally applies data changes
      • A materialized view log is required on each detail table
      • Data changes are recorded in MV logs or direct loader logs
      • Many other requirements must be met for fast refreshes


      FORCE:
      • Does FAST refresh in favour of a COMPLETE
      • The default refresh option


      Refresh Modes:


      ON COMMIT:
      Refreshes occur whenever a commit is performed on one of the view’s underlying detail table(s)
      • Available only with single table aggregate or join based views
      • Keeps view data transactionally accurate
      • Need to check alert log for view creation errors


      ON DEMAND: Refreshes are initiated manually using one of the procedures in the DBMS_MVIEW package
      • Can be used with all types of materialized views
      • Manual Refresh Procedures


      Code:
      DBMS_MVIEW.REFRESH(<mv_name>, <refresh_option>)
      Code:
      DBMS_MVIEW.REFRESH_ALL_MVIEWS()

      START WITH [NEXT] <date>:
      Refreshes start at a specified date/time and continue at regular intervals
      • The fourth section of the create MV’s command is the query that the MV will use.


      Code:
      [FOR UPDATE] 
      [ENABLE|DISABLE] QUERY REWRITE 
      AS SELECT <select clause>
      • If you specify FOR UPDATE, the MV’s will be updatable; Otherwise,
      • It will be read only.

      Example:
      Code:
      CREATE MATERIALIZED VIEW loan_info 
      REFRESH FORCE 
      AS 
      SELECT product_id,count(loan_no),sum(loan_amount) 
      FROM daily_pipeline_loan_fact 
      GROUP BY product_id;

      Cluster


      A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common. Oracle Database stores together all the rows from all the tables that share the same cluster key.
      • To create a cluster in your own schema, you must have CREATE CLUSTER system privilege.
      • To create a cluster in another user's schema, you must have CREATE ANYCLUSTER system privilege. Also, the owner of the schema to contain the cluster must have either space quota on the tablespace containing the cluster or the UNLIMITED TABLESPACE system privilege.
      • Oracle Database does not automatically create an index for a cluster when the cluster is initially created. Data manipulation language (DML) statements cannot be issued against cluster tables in an indexed cluster until you create a cluster index with a CREATE INDEX statement.



      Syntax:
      Code:
      CREATE CLUSTER cluster_name 
      (column datatype[, column datatype]…) 
      [other options]

      Creating a Cluster:
      Example The following statement creates a cluster named personnel with the cluster key column department, a cluster size of 512 bytes, and storage parameter values:
      Code:
      CREATE CLUSTER personnel 
      (department NUMBER(4)) 
      SIZE 512 
      STORAGE (initial 100K next 50K);

      Cluster Keys:
      Example The following statement creates the cluster index on the cluster key of personnel:
      Code:
      CREATE INDEX idx_personnel ON CLUSTER personnel;
      After creating the cluster index, you can add tables to the index and perform DML operations on those tables.
      Departmental tables from the sample hr.employees table and add them to the personnel cluster created in the earlier example:
      Code:
      CREATE TABLE dept_10 
      CLUSTER personnel (department_id) 
      AS SELECT * FROM employees WHERE department_id = 10;

      Database Link


      Objects in the remote database can be access from a local database through a database link.
      Syntax:
      Code:
      CREATE [SHARED][PUBLIC] DATABASE LINK <link_name> 
      CONNECT TO {current user|user_name 
      IDENTIFIED BY password [authentication clause]} 
      USING ‘connect string’
      • To create a database link you must have the CREATE DATABASE LINK system privileges.
      • To create a public database link you must have the CREATE PUBLIC DATABASE LINK system privileges


      Example:

      Code:
      CREATE PUBLIC DATABASE LINK remote_connect 
      CONNECT TO user_name IDENTIFIED BY password 
      USING ‘connect string’

      A public database link is available to all users in a database. A private database link is only available to the user, who created it.
      Access remote table using database link.
      Code:
      SELECT * FROM user_info@remote_connect.

      Summary

      • A view is a logical table based on a table or another view
      • Views restrict access to the data because the view can display selective columns from the table
      • The check option restricts updation of the column which forms the view definition
      • The Read-only view allows the user only to read from the view
      • Index is used by the oracle server to speed up the retrieval of rows by using a pointer
      • use synonym to provide alternative names for objects
      • Sequence Is a sharable object which automatically generates unique numbers.
      • Materialized view is a database object that stores the result of a query .
      • Materialized view automate the data replication and refresh process
      • A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common
      • Objects in the remote database can be access from a local database through a database link


      Test Your Understanding

      • What are the different types of view?
      • What is the significance of FORCE key word in the View definition?
      • What is the use of WITH CHECK OPTION and WITH READ ONLY in the View definition?
      • What are the advantages of Index?
      • What is a function based index?
      • What is a synonym?
      • What is the difference between public synonym and private synonym?
      • What is the maximum value and minimum value for a sequence?
      • What is the use of CACHE and CYCLE option?
      • What are the different refresh mode and refresh option available for MVs?
      • What is the difference between Views and MVs?
      • What is a cluster?
      • Why database links are required?
    • Enter your email address:

      Delivered by FeedBurner

    • Categories




    Disclaimer: Users of techforum4u.com are responsible for ensuring that any material they post (article, blog posts, images or other mulitimedia content) does not violate or infringe upon the copyright, patent, trademark, or any personal or proprietary rights of any third party, and is posted with the permission of the owner of such rights.Anyone who violates these rules may have their access privileges removed without warning.