A)Schema Owners and Application Users
In the context of this article, the schema owner represents the Oracle user that owns all your database objects, while application users are Oracle users that need access to those schema objects.
Allowing applications to make direct connections to the schema owner is a bad idea because it gives those applications far to many privileges, which can easily result in damage to your data and the objects themselves. Instead, it is better to define application users and grant those users the necessary privileges on the schema owners objects.
CURRENT_SCHEMA Approach:
1) First, we create the schema owner and an application user.
CREATE USER schema_owner IDENTIFIED BY password
GRANT CONNECT, CREATE TABLE TO schema_owner;
CREATE USER app_user IDENTIFIED BY password
GRANT CONNECT TO app_user;
2)Next, we create some roles to allow read-write and read-only access.
CREATE ROLE schema_rw_role;
CREATE ROLE schema_ro_role;
3)We want to give our application user read-write access to the schema objects, so we grant the relevant role.
GRANT schema_rw_role TO app_user;
4)Now we are ready to create an object in the schema owner.
CONN schema_owner/password
CREATE TABLE test_tab (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT test_tab_pk PRIMARY KEY (id)
);
GRANT SELECT ON test_tab TO schema_ro_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;
Notice how the privileges are granted to the relevant roles.
This method is ideal where the application user is requiring no objects of its own. It is clean and doesn't require management of thousands of synonyms
Synonym Approach:
This method relies on synonyms owned by the application user to point to the correct location of the schema objects.
1)First, we create the users in a similar way to the previous example.
CREATE USER schema_owner IDENTIFIED BY password
GRANT CONNECT, CREATE TABLE TO schema_owner;
CREATE USER app_user IDENTIFIED BY password
GRANT CONNECT, CREATE SYNONYM TO app_user;
2)Next, we create some roles to allow read-write and read-only access and grant the read-write role to the application user.
CREATE ROLE schema_rw_role;
CREATE ROLE schema_ro_role;
3)We want to give our application user read-write access to the schema objects, so we grant the relevant role.
GRANT schema_rw_role TO app_user;
4)Now we are ready to create an object in the schema owner in the same way we did in the previous example.
CONN schema_owner/password
CREATE TABLE test_tab (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT test_tab_pk PRIMARY KEY (id)
);
GRANT SELECT ON test_tab TO schema_ro_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;
If we now connect to the application user we are not able to see the object without qualifying it with a schema name
SQL> CONN app_user/password
Connected.
SQL> DESC test_tab
ERROR:
ORA-04043: object test_tab does not exist
SQL> DESC schema_owner.test_tab
Name Null? Type
----------------------------------------------------- ---------
ID NOT NULL NUMBER
DESCRIPTION VARCHAR2(50)
SQL> CREATE SYNONYM test_tab FOR schema_owner.test_tab;
Synonym created.
SQL> DESC test_tab
Name Null? Type
----------------------------------------------------- -------- -------------
ID NOT NULL NUMBER
DESCRIPTION VARCHAR2(50)
When there are a large number of application users, this method is cumbersome due to the huge number of synonyms required.
Obviously, it is possible to use public synonyms, but this can be problematic when you have multiple application schemas on a single instance.
B)Db Link :
DB Links are pretty much the name of the game here. If you can't get one created on your own,
then check if there are any public DB links that you could use.
CREATE DATABASE LINK new_york
CONNECT TO tom IDENTIFIED BY thumb
USING 'NEWYORK.world'; -- entry from tnsnames.ora
select * from emp@new_york
union
select * from dick.emp;
Wednesday, September 21, 2011
Performance tuning thru table partitions
Oracle partitions addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions।
Once partitions are defined, SQL statements can access and manipulate the partitions rather than entire tables or indexes।
Partitions are especially useful in data warehouse applications, which commonly store and analyze large amounts of historical data.
Partitioning the table along defined boundaries and separating the partitions onto separate physical disks.
Most of databases allow horizontal partitioning, while others also include vertical partitioning । Horizontal positioning divides a table based on rows, and vertical partitioning divides the table based on columns.
When a table gets very large, basic operations and maintenance on that table becomes
cumbersome. Table locks persist for too long. All manner of SQL queries begin to take an impractical amount of
time to return results. The classic method of dealing with this scenario is to divide the table into
smaller tables and then use a view to combine the smaller tables into a comprehensive view।
Tables can be partitioned by
a range
Common examples of range based partitioning
schemes might be invoice numbers, dates, or zip codes। Anything with a serial ordering can be divided by ranges.
A list
A list-based partition will divide the tables based on a list of values
a hash
A partition defined by a hash will segment the table based on the value returned from a hash function. Once a table is partitioned based on a sensible boundary, the pieces of the table can be physically located on separate disks.
If you are running into performance issues with large tables, seriously consider optimizing your database by partitioning the large tables onto separate disks instead of over-optimizing your existing queries।
How do we create and manage partitions?
SELECT * FROM schema.table PARTITION(part_name);
This syntax provides a simple way of viewing individual partitions as tables
ALTER TABLE DROP PARTITION
ALTER TABLE TRUNCATE PARTITION
ALTER TABLE SPLIT PARTITION
ALTER TABLE ADD आटीटीण
A striped RAID device may contain multiple physical disks, but it appears to Adaptive Server as a single logical device.
For a striped RAID device, you can use multiple partitions on the single logical device and achieve good parallel
query performance.
Once partitions are defined, SQL statements can access and manipulate the partitions rather than entire tables or indexes।
Partitions are especially useful in data warehouse applications, which commonly store and analyze large amounts of historical data.
Partitioning the table along defined boundaries and separating the partitions onto separate physical disks.
Most of databases allow horizontal partitioning, while others also include vertical partitioning । Horizontal positioning divides a table based on rows, and vertical partitioning divides the table based on columns.
When a table gets very large, basic operations and maintenance on that table becomes
cumbersome. Table locks persist for too long. All manner of SQL queries begin to take an impractical amount of
time to return results. The classic method of dealing with this scenario is to divide the table into
smaller tables and then use a view to combine the smaller tables into a comprehensive view।
Tables can be partitioned by
a range
Common examples of range based partitioning
schemes might be invoice numbers, dates, or zip codes। Anything with a serial ordering can be divided by ranges.
A list
A list-based partition will divide the tables based on a list of values
a hash
A partition defined by a hash will segment the table based on the value returned from a hash function. Once a table is partitioned based on a sensible boundary, the pieces of the table can be physically located on separate disks.
If you are running into performance issues with large tables, seriously consider optimizing your database by partitioning the large tables onto separate disks instead of over-optimizing your existing queries।
How do we create and manage partitions?
SELECT * FROM schema.table PARTITION(part_name);
This syntax provides a simple way of viewing individual partitions as tables
ALTER TABLE DROP PARTITION
ALTER TABLE TRUNCATE PARTITION
ALTER TABLE SPLIT PARTITION
ALTER TABLE ADD आटीटीण
A striped RAID device may contain multiple physical disks, but it appears to Adaptive Server as a single logical device.
For a striped RAID device, you can use multiple partitions on the single logical device and achieve good parallel
query performance.
Thursday, September 15, 2011
Cursor
What is a cursor:
When a query is executed in oracle, a result set is produced and stored in the memory(RAM). Oracle allows the programmer to access this result set in the memory through cursor.
Also we can say it is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement.
It is defined within the declaration section of your PLSQL code.
Why use a cursor?
Many times, when a query returns more than one row as a result, we might want to go through each row and process the data in different way for them. Cursor is handy here.
Types of cursors:
Oracle PL/SQL declares a cursor implicitly for all queries and DML statements (including queries that return only one row). But in most of the cases we don’t use these cursors for queries that return one row. Based on this, there are 2 types of cursors
1. Explicit cursor
2. Implicit cursor
Explicit cursor:
The programmer declares a cursor explicitly to process the result set of query (Mostly the query that returns multiple rows as result). The following are the steps to use an explicit cursor.
a. Declare the cursor -- This means give the cursor a name and associate the query that is going to return multiple rows.
b. Open the cursor -- execute the query
c. Fetch the cursor -- Get the result set and loop through to process them
d. Close the cursor -- Close cursor processing.
Implicit Cursor:
Oracle implicitly opens a cursor to process each sql statement that is not associated with an explicit cursor. An implicit cursor is opened for all queries (even if it returns only one row as the result set). Unlike explicit cursors, there is no necessity to use the OPEN, FETCH and CLOSE statements with these cursors. Instead cursor attributes could be used.
Cursor attributes:
Cursor attributes are variables that take some value about the status of the cursor. These values are automatically set by Oracle and the programmer can read them not write values for them. There are four cursor attributes. They are
1. %FOUND
2. %ISOPEN
3. %NOTFOUND
4. %ROWCOUNT
%FOUND Attribute: Has a Row Been Fetched?
After a cursor or cursor variable is opened but before the first fetch, %FOUND returns NULL. After any fetches, it returns TRUE if the last fetch returned a row, or FALSE if the last fetch did not return a row.
DECLARE
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
IF c1%FOUND THEN -- fetch succeeded
DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary);
ELSE -- fetch failed, so exit loop
EXIT;
END IF;
END LOOP;
END;
/
If a cursor or cursor variable is not open, referencing it with %FOUND raises the predefined exception INVALID_CURSOR.
%ISOPEN Attribute: Is the Cursor Open?
%ISOPEN returns TRUE if its cursor or cursor variable is open; otherwise, %ISOPEN returns FALSE
DECLARE
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
the_name employees.last_name%TYPE;
the_salary employees.salary%TYPE;
BEGIN
IF c1%ISOPEN = FALSE THEN -- cursor was not already open
OPEN c1;
END IF;
FETCH c1 INTO the_name, the_salary;
CLOSE c1;
END;
/
%NOTFOUND Attribute: Has a Fetch Failed?
%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.
DECLARE
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
IF c1%NOTFOUND THEN -- fetch failed, so exit loop
-- Another form of this test is "EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;"
EXIT;
ELSE -- fetch succeeded
DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary);
END IF;
END LOOP;
END;
/
%ROWCOUNT Attribute: How Many Rows Fetched So Far?
When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row.
DECLARE
CURSOR c1 IS SELECT last_name FROM employees WHERE ROWNUM < 11;
name employees.last_name%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO name;
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || '. ' || name);
IF c1%ROWCOUNT = 5 THEN
DBMS_OUTPUT.PUT_LINE('--- Fetched 5th record ---');
END IF;
END LOOP;
CLOSE c1;
END;
/
Referencing %FOUND, %NOTFOUND, or %ROWCOUNT before a cursor is opened or after it is closed raises INVALID_CURSOR.
After the first FETCH, if the result set was empty, %FOUND yields FALSE, %NOTFOUND yields TRUE, and %ROWCOUNT yields 0.
Another example of cursor:
CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;
The result set of this cursor is all course_numbers whose course_name matches the variable called name_in.
Below is a function that uses this cursor.
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
close c1;
RETURN cnumber;
END;
Cursor with parameters:
CURSOR c2 (subject_id_in IN varchar2)
IS
SELECT course_number
from courses_tbl
where subject_id = subject_id_in;
Trigger
An SQL trigger may call stored procedures or user-defined functions to perform additional processing when the trigger is executed. Unlike stored procedures, an SQL trigger cannot be directly called from an application. Instead, an SQL trigger is invoked by the database management system on the execution of a triggering insert, update, or delete operation. The definition of the SQL trigger is stored in the database management system and is invoked by the database management system, when the SQL table, that the trigger is defined on, is modified. So it's add additional assistance to manage group of data base tables when ever insert, delete, update operation is performed. For example in banking normal business hours for transactions are from 9 am to 3 pm. suppose some one initiated the transaction after 3 pm. then we can have a mechanism to invoke a trigger by DBMS to raise application error: Transaction is not allowed. So it adds additional features in DBMS system to manage, monitor and control in our data base application. There are 12 kinds (3*2*2) of DML triggers for relational tables:
DML statements: Insert, update, delete
Timing: before , after
Level: Statement level, Row level
Examples:
Before row levels trigger:
1)
CREATE TRIGGER ins_trig BEFORE INSERT ON Emp
FOR EACH ROW
BEGIN
UPDATE Employee SET Salary=Salary-300 WHERE Perks>500;
END;
/
2)
CREATE TRIGGER updtrigger BEFORE UPDATE ON Employee
FOR EACH ROW
BEGIN
IF NEW.Salary<=500 THEN
SET NEW.Salary=10000;
ELSEIF NEW.Salary>500 THEN
SET NEW.Salary=15000;
END IF;
END
/
After statement level trigger:
CREATE OR REPLACE TRIGGER emp_audit_trig
AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
v_action VARCHAR2(20);
BEGIN
IF INSERTING THEN
v_action := 'Added employee(s)';
ELSIF UPDATING THEN
v_action := 'Updated employee(s)';
ELSIF DELETING THEN
v_action := 'Deleted employee(s)';
END IF;
INSERT INTO empauditlog VALUES (SYSDATE, USER,
v_action);
END;
DROP TRIGGER
The general syntax of DROP TRIGGER is :
DROP TRIGGER trigger_name
Sunday, September 11, 2011
Static vs. Dynamic testing
Dynamic Testing: Testing software through executing it.
Static Testing: Analysis of a program carried out without executing the program.
Metric
A standard of measurement. A metric should be a real objective measurement of something such as number of bugs per lines of code.
Alpha & Beta Testing
It's actual operational testing by potential users/customers or an independent test team at the developers' site.
Alpha testing is internal acceptance testing, before the software goes to beta testing.
A product's beta is an officially released version of a product which includes most of the product's functionality. The beta version is intended for external testing of the product in order to identify configurations that cause problems, as well as collect requirements and suggestions from users.
In prototype model development approach this kind of testing is used.
Beta testing comes after alpha testing and can be considered a form of external user acceptance testing. Versions of the software, known as beta versions, are released to a limited audience outside of the programming team. The software is released to groups of people so that further testing can ensure the product has few faults or bugs. Sometimes, beta versions are made available to the open public to increase the feedback.
Boundary Value Analysis
In boundary value analysis, test cases are generated using the extremes of the input , e.g. maximum, minimum, just inside/outside boundaries, typical values, and error values.
N+1 Testing
A variation of Regression Testing. Testing conducted with multiple cycles in which errors found in test cycle N are resolved and the solution is retested in test cycle N+1. The cycles are typically repeated until the solution reaches a steady state and there are no errors.
Smoke Testing
A quick-and-dirty test that the major functions of a piece of software work. Originated in the hardware testing practice of turning on a new piece of hardware for the first time and considering it a success if it does not catch fire
Smoke testing refers to physical tests made to closed systems of pipes to test for leaks. By metaphorical extension, the term is also used for the first test made after assembly or repairs to a system, to provide some assurance that the system under test will not catastrophically fail. After a smoke test proves that "the pipes will not leak, the keys seal properly, the circuit will not burn, or the software will not crash outright, system is ready for more stressful testing.
Guide lines for Smoke testing:
Because smoke testing focuses on changed code, you must work with the developer who wrote the code. You will have to understand:
-
What changed in the code. To understand the change, you will also have to understand the technology used; the developer can help explain it.
-
How the change affects the functionality.
-
How the change affects the interdependencies of various components.
Conduct a Code Review Before Smoke Testing
Before you run a smoke test, conduct a code review that focuses on any changes in the code. Code reviews are the most effective and efficient method to validate code quality and ensure against code defects and faults of commission. Smoke tests ensure that the primary critical or weak area identified either by code review or risk assessment is primarily validated, because if it fails the testing cannot continue.
Bottom-up and top-down approaches in Regression Testing.
Bottom-up approach :
In this approach testing is conducted from sub module to main module. If the main module is not developed a temporary program called DRIVERS is used to simulate the main module.
Top-down approach : In this approach testing is conducted from main module to sub module. if the sub module is not developed a temporary program called STUB is used for simulate the submodule.
Negative testing
Testing the system using negative data is called negative testing, e.g. testing the password where it should be minimum of 8 characters so testing it using 6 characters is negative testing. It is designed to determine if the system doesn't crash with unexpected input.Negative testing is testing the tool with improper inputs. For example entering the special characters for phone number. testing the application for fail conditions.
White box & Black box testing.
White box testing:
White box testing is when the tester has access to the internal data structures and algorithms including the code that implement these.
Also known as glass box, structural, clear box and open box testing.
Knowledge of the internal workings of the item being tested are used to select the test data.
The test is accurate only if the tester knows what the program is supposed to do.
For a complete software examination, both white box and black box tests are required.
White box testing is used in three of the six basic types of testing: unit, integration, and regression testing.
Unit testing is done on a small piece, or unit, of code. This unit is usually a class. When a unit is integrated into the main code base, it is more difficult to find a bug in that unit. That's why it's done in separate. Integration testing looks at how all components of an application interact.
Regression testing verifies that modifications to the system have not damaged the whole of the system. Unit tests and integration tests can be rerun in regression testing to verify that modifications to the application work properly.
The goal of white box testing is to cover testing as many of the statements, decision point, and branches in the code base as possible.
Black box testing:
Black box testing treats the software as a "black box"—without any knowledge of internal implementation.
Unlike black box testing, white box testing uses specific knowledge of programming code to examine outputs.
Black box testing is a software testing techniques in which
functionality of the software under test (SUT) is tested without looking
at the internal code structure, implementation details and knowledge of
internal paths of the software.This type of testing is based entirely on
the software requirements and specifications.
Black box testing - StepsHere are the generic steps followed to carry out any type of Black Box Testing.
1.Initially requirements and specifications of the system are examined.2.Tester chooses valid inputs (positive test scenario) to check whether SUT processes them correctly . Also some invalid inputs (negative test scenario) are chosen to verify that the SUT is able to detect them.3.Tester determines expected outputs for all those inputs.4.Software tester constructs test cases with the selected inputs.5.The test cases are executed.6.Software tester compares the actual outputs with the expected outputs.7.Defects if any are fixed and re-tested.
Types of Black Box Testing
There are many types of Black Box Testing but following are the prominent ones -
1.Functional testing – This black box testing type is related to functional requirements of a system; it is done by software testers.
2.Non-functional testing – This type of black box testing is not related to testing of a specific functionality , but non-functional requirements such as performance.
3.Regression testing – Regression testing is done after code fixes , upgrades or any other system maintenance to check the new code has not affected the existing code.
For Functional/ Regression Tests you can use - QTP
lack Box Testing and Software Development Life Cycle (SDLC)
Black box testing has its own life cycle called Software Test Life Cycle (STLC) and it is relative to every stage of Software Development Life Cycle.
1)Requirement – This is the initial stage of SDLC and in this stage requirement is gathered. Software testers also take part in this stage.
2)Test Planning & Analysis – Testing Types applicable to the project are determined. A Test Plan is created which determines possible project risks and their mitigation.
3)Design – In this stage Test cases/scripts are created on the basis of software requirement documents
4)Test Execution- In this stage Test Cases prepared are executed. Bugs if any are fixed and re-tested.
Gray Box Testing:
Involves having knowledge of internal data structures and algorithms for purposes of designing the test cases,but testing at the user, or black-box level. Gray Box Testing is a software testing method which is a combination of Black Box Testing method and White Box Testing method.In Gray Box Testing, the internal structure is partially known. Gray Box Testing is named so because the software program, in the eyes of the tester is like a gray/semi-transparent box; inside which one can partially see.
System Integration Testing
As Unit testing is done in development environment, SIT is done in QA environment. Here we test entire program and all integrated modules. Success of all dependent systems/environments and impact analysis are taken care in this type of testing. From data volume perspective total 90 % of production volume data should be processed in SIT. Here also we should maintain test case and test plan as two separate documents.
Test plan :
The purpose of this document is to identify the activities of the QA team will conduct during the QA System Integration Test phase of the deliverables. It will identify specific features/functionality to be tested, testing tasks to be performed, personnel and resources necessary to perform those tests, and outline any associated risks/assumptions.
The System Integration (SI) Test Phase will be conducted after the Development Team completes their Unit Testing. SI testing will be completed before formal User Acceptance Testing (UAT) is conducted by the business users.
Test plan consists of:
1)Test Objectives
2)Test Scope
a)In Scope
b)Out of Scope
for example:
1)E2E test not necessary for this change.
2)Outbound to Data mart data move not necessary to check, no impact.
3) Assumptions & Risks
4)Test Strategy
5)Test Schedule
6)Roles and responsibilities
7)Dependencies
8)Functions to be tested
9)Test Management
10)Tools:
examples are-
Mercury Interactive Quality center for defect tracking
QA inspection check list
TOAD – Tool for Oracle Application Development
Tidal – Tool for running the jobs
Putty – Open source SSH client
11)Test Acceptance Criteria
Before exiting the construction phase and entering the System Integration Test Phase the following criteria should be met.
All Unit Testing completed and results documented in a Unit Test Plan.
All known defects exiting Unit Testing should be entered into the Mercury Defect Tracking Tool.
All software should be installed in the ITG environment.
A construction exist review meeting should be held and sign off received to exit the construction phase
12)Test Exit Criteria
a.Test Plan Development.
Test Cases created.
Test Plan Developed.
b.Test Preparation.
Test environment set up and ready.
Test scenarios developed and reviewed.
c. Unit testing.
Program compiles and programmers are confident of requirement functionality.
d.Test in QA environment.
Successful execution of QA scenarios .
Report and track the defects in QA environment.
Critical and major integration defects are closed.
QA test sign off provided to UAT.
e.Regression Testing.Successful execution of Regression Test scenarios.
System and gaps are identified and documented
Critical and serious defects found are tracked and closed.
Regression test sign off by the QA team/IT functional Leads
e.User Acceptance Test (UAT)
System Test environment is set up and stable.
Executed UAT test scenarios successfully.
Critical and serious UAT test defects are closed.
System and process gaps are identified and documented.
UAT test sign off by the Business leads.
13)UAT scope for QA Team.
QA team to assist the UAT team in testing.
QA team to track the UAT defects until closure.
QA team to send Outbound jobs reports based on the request from UAT.
14)Defect Management
15)Approvals
16)Requirements Traceability Matrix (RTM)
A Requirements Traceability Matrix is a method used to validate the compliance of a process or product with the requirements for that process or product. Each requirement is listed in a row of the matrix, and the columns of the matrix are used to identify how and where each requirement has been addressed by the project.
Test plan :
The purpose of this document is to identify the activities of the QA team will conduct during the QA System Integration Test phase of the deliverables. It will identify specific features/functionality to be tested, testing tasks to be performed, personnel and resources necessary to perform those tests, and outline any associated risks/assumptions.
The System Integration (SI) Test Phase will be conducted after the Development Team completes their Unit Testing. SI testing will be completed before formal User Acceptance Testing (UAT) is conducted by the business users.
Test plan consists of:
1)Test Objectives
2)Test Scope
a)In Scope
b)Out of Scope
for example:
1)E2E test not necessary for this change.
2)Outbound to Data mart data move not necessary to check, no impact.
3) Assumptions & Risks
4)Test Strategy
5)Test Schedule
6)Roles and responsibilities
7)Dependencies
8)Functions to be tested
9)Test Management
10)Tools:
examples are-
Mercury Interactive Quality center for defect tracking
QA inspection check list
TOAD – Tool for Oracle Application Development
Tidal – Tool for running the jobs
Putty – Open source SSH client
11)Test Acceptance Criteria
Before exiting the construction phase and entering the System Integration Test Phase the following criteria should be met.
All Unit Testing completed and results documented in a Unit Test Plan.
All known defects exiting Unit Testing should be entered into the Mercury Defect Tracking Tool.
All software should be installed in the ITG environment.
A construction exist review meeting should be held and sign off received to exit the construction phase
12)Test Exit Criteria
a.Test Plan Development.
Test Cases created.
Test Plan Developed.
b.Test Preparation.
Test environment set up and ready.
Test scenarios developed and reviewed.
c. Unit testing.
Program compiles and programmers are confident of requirement functionality.
d.Test in QA environment.
Successful execution of QA scenarios .
Report and track the defects in QA environment.
Critical and major integration defects are closed.
QA test sign off provided to UAT.
e.Regression Testing.Successful execution of Regression Test scenarios.
System and gaps are identified and documented
Critical and serious defects found are tracked and closed.
Regression test sign off by the QA team/IT functional Leads
e.User Acceptance Test (UAT)
System Test environment is set up and stable.
Executed UAT test scenarios successfully.
Critical and serious UAT test defects are closed.
System and process gaps are identified and documented.
UAT test sign off by the Business leads.
13)UAT scope for QA Team.
QA team to assist the UAT team in testing.
QA team to track the UAT defects until closure.
QA team to send Outbound jobs reports based on the request from UAT.
14)Defect Management
15)Approvals
16)Requirements Traceability Matrix (RTM)
A Requirements Traceability Matrix is a method used to validate the compliance of a process or product with the requirements for that process or product. Each requirement is listed in a row of the matrix, and the columns of the matrix are used to identify how and where each requirement has been addressed by the project.
Unit Testing
Unit testing is part of development activities. Developer is responsible to perform this testing immediate after
construction activity. No one can test better than the person who developed a particular module so that's why development
phase includes construction followed by Unit testing. Unit testing has very limited scope as it's testing of only newly
constructed or modified module rather than testing of whole system & dependent environments. From data volume perspective we should process almost
construction activity. No one can test better than the person who developed a particular module so that's why development
phase includes construction followed by Unit testing. Unit testing has very limited scope as it's testing of only newly
constructed or modified module rather than testing of whole system & dependent environments. From data volume perspective we should process almost
70% of total volume of data in production. As a good practice we should maintain two docs for
Unit test plan and Unit test cases, one for each.Unit test cases include set of use case. Each use case consists of test steps
Regression Testing
The retesting of a software system that has been modified to ensure that any bugs have been fixed and that no other previously working functions have failed as a result of the reparations and that newly added features have not created problems with previous versions of the software.
Also referred to as verification testing, regression testing is initiated after a programmer has attempted to fix a recognized problem or has added source code to a program that may have introduced errors. It is to ensure that the newly modified code still complies with its specified requirements and that unmodified code has not been affected by the maintenance activity.
A test that was written when a bug was fixed. It ensure that this specific bug will not occur again. The full name is "non-regression test".
Regression testing is to uncover new errors,in existing functionality after changes have been made to a system,such as functional enhancements or configuration changes.
One of the main reasons for regression testing is that it's often extremely difficult for a programmer to figure out how a change in one part of the software will
echo in other parts of the software.
Common methods of regression testing include rerunning previously run tests and checking whether program behavior
has changed and whether previously fixed faults have re-emerged.
Regression testing can be used to test a system efficiently by systematically selecting the appropriate minimum set of tests needed to adequately cover a particular change.
Experience has shown that as software is fixed, emergence of new and/or reemergence of old faults is quite common.
Sometimes reemergence occurs because a fix gets lost through poor revision control practices (or simple human error in revision control). So new persion who os making changes on top of latest version may not aware 100% about previous versions functionality due to poor revision control.
Frequently, a fix for a problem in one area inadvertently causes a software bug in another area. Finally, often when some feature is redesigned, some of the same mistakes that were made in the original implementation of the feature were made in the redesign. so It's impotent to execute previous test cases also along with new test cases.
software tools allow the testing environment to execute all the regression test cases automatically; For example we can schedule test cases at mercury quality center.
Common strategies are to run such a system after every successful compile (for small projects), every night, or once a week. Those strategies can be automated by an external tool, such as MQC 9.0, HPIT QC 9.2.
Saturday, September 3, 2011
Factless Fact Tables
Fact tables which contain only foreign keys to join dimensional tables but not any measures for analysis to feed the user's query. It is used to define many to many relationship between two dimensional tables.It works as an interface table to correlate the data residing in 2 or more than 2 dimensional tables. Suppose our query requires data fetching from both table then fact less dimensional table is required which relates 2 dimensional tables. However we do not require any data in fact table to feed the query. But it contains only foreign keys and not any fact or event.
So many to many relationship can be defined between 2 dimensional tables using fact less fact tables।
So many to many relationship can be defined between 2 dimensional tables using fact less fact tables।
Tuesday, August 30, 2011
Fact Vs Dimension tables
Fact table contains events or facts in a business. A fact contains measurable values. We do analysis of facts or events
based on the query fired by end users. For example sales in a business process. so every sale is an event. So in
fact table we have fact or record for each and every event.
Dimension table contains factors by which analysis is done. For example product id is a foreign key in fact table and primary key
for a dimension table called
product. So in product table we have more information about a product which are useful facts for analyzing facts and events.
So it provides descriptive or context information for performing analysis. Fact table is in center surrounded by
dimension tables। Surrogate key of every dimension table is also kept in fact table as foreign key to define many to many relationship between ant two dimensional tables.
based on the query fired by end users. For example sales in a business process. so every sale is an event. So in
fact table we have fact or record for each and every event.
Dimension table contains factors by which analysis is done. For example product id is a foreign key in fact table and primary key
for a dimension table called
product. So in product table we have more information about a product which are useful facts for analyzing facts and events.
So it provides descriptive or context information for performing analysis. Fact table is in center surrounded by
dimension tables। Surrogate key of every dimension table is also kept in fact table as foreign key to define many to many relationship between ant two dimensional tables.
Surrogate Key
It's a system generated key which is not actual defined primary but work as a primary key in a table.For example in employee table we have employee number as an actual primary key, as it uniquely identifies all records in employee table. But rownum is system generated key, Oracle application generates by default one row number for each record in relational table. This row num can be used as primary key. In Informatica ETL tool we use sequence generator to generates sequences (surrogate keys). For example let's relational table has one record for employee number 101. We have one field like address for this column. Suppose tomorrow employee has three addresses. We can not increase number of fields for each record every time as it will cost. Now system will not allow for having 3 records for same emloyee id(Primary key violation). So we will generate 3 sequences for these three reocrds. Primary key constraint will be set on sequece no rather than on employee id. It's usual to have surrugate key data type as decimal. it's so because we can generate any number of desired sequences by incrementing (Arthmetic operation) existing one.
Saturday, August 27, 2011
Galaxy schema
Galaxy schema contains many fact tables with some common dimensions (conformed dimensions)। This schema is a combination of many data marts. One fact table may represent one data mart।
It is also known as fact constellation schema। It is a combination of star schema and snow flack schema. As discussed earlier it consists of conformed dimensions.
The dimensions in this schema are segregated into independent dimensions based on the levels of hierarchy. For example, if geography has four levels of hierarchy like region, country, state and city; constellation schema would have four dimensions instead of one.
As we see in the two star schemas as below, the two fact tables, sales table and purchase table are now sharing both the 'product' and 'time' dimension tables. Therefore we decide to choose Galaxy Schema as the model for our data warehouse, which is displayed as follow:
Star schema for sales
As per galaxy schema:
It is also known as fact constellation schema। It is a combination of star schema and snow flack schema. As discussed earlier it consists of conformed dimensions.
The dimensions in this schema are segregated into independent dimensions based on the levels of hierarchy. For example, if geography has four levels of hierarchy like region, country, state and city; constellation schema would have four dimensions instead of one.
As we see in the two star schemas as below, the two fact tables, sales table and purchase table are now sharing both the 'product' and 'time' dimension tables. Therefore we decide to choose Galaxy Schema as the model for our data warehouse, which is displayed as follow:
Star schema for sales
As per galaxy schema:
Friday, August 26, 2011
Various categories of Dimensions:
Though reports are generated on fact tables, data in dimension tables is used to describe the data in fact tables।
Dimensions are mainly below types.
Slowly Changing Dimension:
Slowly Changing Dimensions (SCDs) are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule.For example, you may have a dimension in your database that tracks the sales records of your company's salespeople. Creating sales reports seems simple enough, until a salesperson is transferred from one regional office to another. How do you record such a change in your sales dimension?You could sum or average the sales by salesperson, but if you use that to compare the performance of salesmen, that might give misleading information. If the salesperson that was transferred used to work in a hot market where sales were easy, and now works in a market where sales are infrequent, her totals will look much stronger than the other salespeople in her new region, even if they are just as good.
Conformed Dimension:
At the most basic level, conformed dimensions mean the exact same thing with every possible fact table to which they are joined. The date dimension table connected to the sales facts is identical to the date dimension connected to the inventory facts.
These dimensions are something that is built once in your model and can be reused multiple times with different fact tables. For example consider a model containing multiple fact tables representing different data marts. Now look for a dimension that is common to these facts tables. In this example let’s consider that the product dimension is common and hence can be reused by joining the different fact tables.
Degenerate Dimension:
The data items thar are not facts and data items that do not fit into the existing dimensions are termed as Degenerate Dimensions।They can be used as primary key for the fact table but they cannot act as foreign keys।
A column of the key section of the fact table that does not have the associated dimension table but used for reporting and analysis such column is called degenerate dimension or line item dimension।
For example we have a fact table with customer_id, product_id, branch_id, employee_id, bill_no, date in key section and price, quantity, amount in measure section. In this fact table bill_no from key section is a single value it has no associated dimension table. Instead of cteating a seperate dimension table for that single value we can
include it in fact table to improve performance.SO here the column bill_no is a degenerate dimension or line item dimension.
A Degenerate dimension is a Dimension which has only a single attribute.
This dimension is typically represented as a single field in a fact table.
When the cardinality of column value is high instead of maintaining a separate dimension and having the overhead of making a join with fact table degenerated dimensions can be build.
For example In sales fact table Invoice number is a degenerated dimension. Since Invoice Number is not tied up to an order header table hence there is no need for invoice number to join a dimensional table; hence it is referred as degenerate dimension.
Junk Dimension:
When you consolidate lots of small dimensions and instead of having 100s of small dimensions that will have few records in them all records from all these small dimension tables are loaded into ONE dimension table and we call this dimension table Junk dimension table। (Since we are storing all the junk in this one table) .
Inferred Dimension:
Inferred dimensions are the dimensions that are not retrieved when the fact data that are related to them, is ready to insert into the fact table।
Role Playing Dimension:
Dimensions are often recycled for multiple applications within the same database. For instance, a "Date" dimension can be used for "Date of Sale", as well as "Date of Delivery", or "Date of Hire". This is often referred to as a "role-playing dimension".
Dirty dimension:
A dimension which can exits more than once in dimension table.
Dimensions are mainly below types.
Slowly Changing Dimension:
Slowly Changing Dimensions (SCDs) are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule.For example, you may have a dimension in your database that tracks the sales records of your company's salespeople. Creating sales reports seems simple enough, until a salesperson is transferred from one regional office to another. How do you record such a change in your sales dimension?You could sum or average the sales by salesperson, but if you use that to compare the performance of salesmen, that might give misleading information. If the salesperson that was transferred used to work in a hot market where sales were easy, and now works in a market where sales are infrequent, her totals will look much stronger than the other salespeople in her new region, even if they are just as good.
Conformed Dimension:
At the most basic level, conformed dimensions mean the exact same thing with every possible fact table to which they are joined. The date dimension table connected to the sales facts is identical to the date dimension connected to the inventory facts.
These dimensions are something that is built once in your model and can be reused multiple times with different fact tables. For example consider a model containing multiple fact tables representing different data marts. Now look for a dimension that is common to these facts tables. In this example let’s consider that the product dimension is common and hence can be reused by joining the different fact tables.
Degenerate Dimension:
The data items thar are not facts and data items that do not fit into the existing dimensions are termed as Degenerate Dimensions।They can be used as primary key for the fact table but they cannot act as foreign keys।
A column of the key section of the fact table that does not have the associated dimension table but used for reporting and analysis such column is called degenerate dimension or line item dimension।
For example we have a fact table with customer_id, product_id, branch_id, employee_id, bill_no, date in key section and price, quantity, amount in measure section. In this fact table bill_no from key section is a single value it has no associated dimension table. Instead of cteating a seperate dimension table for that single value we can
include it in fact table to improve performance.SO here the column bill_no is a degenerate dimension or line item dimension.
A Degenerate dimension is a Dimension which has only a single attribute.
This dimension is typically represented as a single field in a fact table.
When the cardinality of column value is high instead of maintaining a separate dimension and having the overhead of making a join with fact table degenerated dimensions can be build.
For example In sales fact table Invoice number is a degenerated dimension. Since Invoice Number is not tied up to an order header table hence there is no need for invoice number to join a dimensional table; hence it is referred as degenerate dimension.
Junk Dimension:
When you consolidate lots of small dimensions and instead of having 100s of small dimensions that will have few records in them all records from all these small dimension tables are loaded into ONE dimension table and we call this dimension table Junk dimension table। (Since we are storing all the junk in this one table) .
Inferred Dimension:
Inferred dimensions are the dimensions that are not retrieved when the fact data that are related to them, is ready to insert into the fact table।
Role Playing Dimension:
Dimensions are often recycled for multiple applications within the same database. For instance, a "Date" dimension can be used for "Date of Sale", as well as "Date of Delivery", or "Date of Hire". This is often referred to as a "role-playing dimension".
Dirty dimension:
A dimension which can exits more than once in dimension table.
Seeing locks on table
select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b, v$session v
where a.object_id=b.object_id and a.SESSION_ID=v.sid
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b, v$session v
where a.object_id=b.object_id and a.SESSION_ID=v.sid
Thursday, August 25, 2011
Bitmap , Btree and B+ tree indexes:
B-Tree indexes (Normal)
By default, Oracle creates B-tree indexes As the name suggests it makes use of the B-Tree parsing method to find the value being searched for. When it gets a value to be searched, it gets the nearest possible index value. Then it goes to the left node if the value it lesser or to the right if the value is higher. It goes on doing this until it reaches the leaf node which contains the value it is searching for.
How to Create an Index?
CREATE INDEX IDX_NAME
ON TABLE_NAME (Col_nam1,Col_nam2, )
COMPUTE STATISTICS ; --optional
How to Rename an Index?
ALTER INDEX IDX_NAME
RENAME TO NEW_IDX_NAME;
How to Collect Statistics on an Index?
ALTER INDEX IDX_NAME
COMPUTE STATISTICS;
How to Drop an Index?
DROP INDEX IDX_NAME;
Bitmap indexes
Oracle Bitmap Indexes are different from Standard B-Tree Indexes in the way they store the information about the indexes. Bitmap Indexes stores the information about the value in bits; with 0s and 1s; thus called Bitmaps. Something like this:
Bitmap Index creates a two-dimensional array with one column for every row in the table being indexed. If the value being indexed is found in the row, it will specify a 1 and if not found then a 0 . So while searching, Oracle takes only the information from those rows which shows a 1 in the Bitmap Index. That means, the records which are pointed by the 1 s in the Index, have got the searched/indexed value.
How to Create a Bitmap Index?
CREATE BITMAP INDEX IDX_NAME
ON TABLE_NAME (Col_nam1,Col_nam2, )
COMPUTE STATISTICS ;
DML Operations:
If we have too much DML operations (insert/update/delete) on a table on which the columns are indexed, it is better to use B-Tree indexes।Updating bitmapped indexes consumes lot of resources. So, it is better to use Bitmap Indexes on columns of Tables which are updated/inserted/deleted less frequently .
OLAP system and hence for Star scema: Bit map index is more useful.
OLTP and ODS system B tree index is more usefull.
Performance:
Compressed structure in Bitmap Indexes makes it faster to read from disk; but utilises more CPU to decompress the information.
Uncompressed structure in B-Tree involves a lesser CPU load compared to Bitmap but it requires more bandwidth to read since it is uncompressed data.
Cardinality:
Cardinality means, the ratio of distinct values to the total number of records. So, a low cardinality means, distinct values are very less.
In such a scenario, Bitmap indexes are considered to be more efficient thatB-Tree indexes.
ex indexing on primary key means lot of distinct values. hence after decompress also it will not take considerable less space so searching time will not be much lesser compared to B tree index.
Indexing on non primary key like gender will have less distinct values and hence Bitmap index will help here.
B+ tree indexes:
A B+-tree in certain aspects is a generalization of a binary search tree (BST). The main difference is that nodes of a B+-tree will point to many children nodes rather than being limited to only two. Since our goal is to minimize disk accesses whenever we are trying to locate records, we want to make the height of the multiway search tree as small as possible. This goal is achieved by having the tree branch in large amounts at each node.
The B+-tree stores records (or pointers to actual records) only at the leaf nodes, which are all found at the same level in the tree, so the tree is always height balanced.
Internal nodes store search key values, and are used only as placeholders to guide the search.
By default, Oracle creates B-tree indexes As the name suggests it makes use of the B-Tree parsing method to find the value being searched for. When it gets a value to be searched, it gets the nearest possible index value. Then it goes to the left node if the value it lesser or to the right if the value is higher. It goes on doing this until it reaches the leaf node which contains the value it is searching for.
How to Create an Index?
CREATE INDEX IDX_NAME
ON TABLE_NAME (Col_nam1,Col_nam2, )
COMPUTE STATISTICS ; --optional
How to Rename an Index?
ALTER INDEX IDX_NAME
RENAME TO NEW_IDX_NAME;
How to Collect Statistics on an Index?
ALTER INDEX IDX_NAME
COMPUTE STATISTICS;
How to Drop an Index?
DROP INDEX IDX_NAME;
Bitmap indexes
Oracle Bitmap Indexes are different from Standard B-Tree Indexes in the way they store the information about the indexes. Bitmap Indexes stores the information about the value in bits; with 0s and 1s; thus called Bitmaps. Something like this:
Bitmap Index creates a two-dimensional array with one column for every row in the table being indexed. If the value being indexed is found in the row, it will specify a 1 and if not found then a 0 . So while searching, Oracle takes only the information from those rows which shows a 1 in the Bitmap Index. That means, the records which are pointed by the 1 s in the Index, have got the searched/indexed value.
How to Create a Bitmap Index?
CREATE BITMAP INDEX IDX_NAME
ON TABLE_NAME (Col_nam1,Col_nam2, )
COMPUTE STATISTICS ;
DML Operations:
If we have too much DML operations (insert/update/delete) on a table on which the columns are indexed, it is better to use B-Tree indexes।Updating bitmapped indexes consumes lot of resources. So, it is better to use Bitmap Indexes on columns of Tables which are updated/inserted/deleted less frequently .
OLAP system and hence for Star scema: Bit map index is more useful.
OLTP and ODS system B tree index is more usefull.
Performance:
Compressed structure in Bitmap Indexes makes it faster to read from disk; but utilises more CPU to decompress the information.
Uncompressed structure in B-Tree involves a lesser CPU load compared to Bitmap but it requires more bandwidth to read since it is uncompressed data.
Cardinality:
Cardinality means, the ratio of distinct values to the total number of records. So, a low cardinality means, distinct values are very less.
In such a scenario, Bitmap indexes are considered to be more efficient thatB-Tree indexes.
ex indexing on primary key means lot of distinct values. hence after decompress also it will not take considerable less space so searching time will not be much lesser compared to B tree index.
Indexing on non primary key like gender will have less distinct values and hence Bitmap index will help here.
B+ tree indexes:
A B+-tree in certain aspects is a generalization of a binary search tree (BST). The main difference is that nodes of a B+-tree will point to many children nodes rather than being limited to only two. Since our goal is to minimize disk accesses whenever we are trying to locate records, we want to make the height of the multiway search tree as small as possible. This goal is achieved by having the tree branch in large amounts at each node.
The B+-tree stores records (or pointers to actual records) only at the leaf nodes, which are all found at the same level in the tree, so the tree is always height balanced.
Internal nodes store search key values, and are used only as placeholders to guide the search.
Materialized View
A materialized view is a table that stores derived data (Data from main table for which view is created)
During its creation, we specify the SQL used to populate the materialized view.
For example, if you have a large SALES table, we may create a materialized view that sums the SALES data by region. If a user queries the SALES table for the sum of the SALES data for a region, ORACLE can redirect that query to use your materialized view in place of the SALES table.
As a result, we can reduce the number of accesses against our largest tables, improving the system performance.
Example:
create materialized view SALES_MONTH_MV
tablespace AGG_DATA
refresh complete
start with sysdate
next sysdate+1
enable query rewrite
as
select Sales_Month, SUM(Amount)
from SALES
group by Sales_Month;
The enable query rewrite clause enables the optimizer to redirect queries of SALES to SALES_MONTH_MV if appropriate.
The tablespace clause tells ORACLE where to store the local table for the materialized view.
The data will be automatically refreshed if you have enabled background job processes (via the JOB_QUEUE_PROCESSES init.ora parameter).
The start with and next clauses tell ORACLE when to schedule refreshes of the data.
In this example, a complete refresh of the view is chosen-each time the view is refreshed, its data will be completely deleted and re-created।
Fast refreshes of materialized views use materialized view logs. A materialized view log is a table stored along with the master table for the materialized view. As rows change in the master table, the changes are written to the materialized view log. During a fast refresh, the changed rows from the master table, as identified via the materialized view log, are sent to the materialized view. If the changes account for less than 25 percent of the rows in the master table, a fast refresh is generally faster than a complete refresh.
When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.
A materialized view log is located in the master database in the same schema as the master table। A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.
Example:
CREATE MATERIALIZED VIEW LOG ON customers WITH PRIMARY KEY, ROWID;
We can index the materialized view’s local table to improve the performance of queries against the materialized view.
Depending on the complexity of the materialized view, ORACLE may also create an index on the materialized view’s local table।
To enable a materialized view for query rewrite, all of the master tables for the materialized view must be in the materialized view’s schema, and you must have the QUERY REWRITE system privilege। If the view and the tables are in separate schemas, you must have the GLOBAL QUERY REWRITE system privilege. In general, you should create materialized views in the same schema as the tables on which they are based; otherwise, you will need to manage the permissions and grants required to create and maintain the materialized view.
Example:
CREATE MATERIALIZED VIEW products_mv
REFRESH FAST ON COMMIT
AS SELECT SUM(list_price - min_price), category_id
FROM product_information
GROUP BY category_id;
During its creation, we specify the SQL used to populate the materialized view.
For example, if you have a large SALES table, we may create a materialized view that sums the SALES data by region. If a user queries the SALES table for the sum of the SALES data for a region, ORACLE can redirect that query to use your materialized view in place of the SALES table.
As a result, we can reduce the number of accesses against our largest tables, improving the system performance.
Example:
create materialized view SALES_MONTH_MV
tablespace AGG_DATA
refresh complete
start with sysdate
next sysdate+1
enable query rewrite
as
select Sales_Month, SUM(Amount)
from SALES
group by Sales_Month;
The enable query rewrite clause enables the optimizer to redirect queries of SALES to SALES_MONTH_MV if appropriate.
The tablespace clause tells ORACLE where to store the local table for the materialized view.
The data will be automatically refreshed if you have enabled background job processes (via the JOB_QUEUE_PROCESSES init.ora parameter).
The start with and next clauses tell ORACLE when to schedule refreshes of the data.
In this example, a complete refresh of the view is chosen-each time the view is refreshed, its data will be completely deleted and re-created।
Fast refreshes of materialized views use materialized view logs. A materialized view log is a table stored along with the master table for the materialized view. As rows change in the master table, the changes are written to the materialized view log. During a fast refresh, the changed rows from the master table, as identified via the materialized view log, are sent to the materialized view. If the changes account for less than 25 percent of the rows in the master table, a fast refresh is generally faster than a complete refresh.
When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.
A materialized view log is located in the master database in the same schema as the master table। A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.
Example:
CREATE MATERIALIZED VIEW LOG ON customers WITH PRIMARY KEY, ROWID;
We can index the materialized view’s local table to improve the performance of queries against the materialized view.
Depending on the complexity of the materialized view, ORACLE may also create an index on the materialized view’s local table।
To enable a materialized view for query rewrite, all of the master tables for the materialized view must be in the materialized view’s schema, and you must have the QUERY REWRITE system privilege। If the view and the tables are in separate schemas, you must have the GLOBAL QUERY REWRITE system privilege. In general, you should create materialized views in the same schema as the tables on which they are based; otherwise, you will need to manage the permissions and grants required to create and maintain the materialized view.
Example:
CREATE MATERIALIZED VIEW products_mv
REFRESH FAST ON COMMIT
AS SELECT SUM(list_price - min_price), category_id
FROM product_information
GROUP BY category_id;
Subscribe to:
Posts (Atom)