ORACLE AND MSSQLSERVER

SQL queries under workspace can suffer performance issues. Performance can affect due to huge SQL statements. Simpler SQL statements require less processing and generally perform better than complex statements. If you understand the purpose of statement...

The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time. CREATE TABLE forall_test ( id NUMBER(10), code VARCHAR2(10),...

SQL%BULK_ROWCOUNT The SQL%BULK_ROWCOUNT cursor attribute gives granular information about the rows affected by each iteration of the FORALL statement. Every row in the driving collection has a corresponding row in the SQL%BULK_ROWCOUNT cursor attribute....

The DBMS_XPLAN package is used to format the output of an explain plan. It was introduced in Oracle 9i as a replacement for the "utlxpls.sql" script or custom queries of the plan table. Subsequent database versions have increased the functionality of...

The DBMS_PARALLEL_EXECUTE package allows a workload associated with a base table to be broken down into smaller chunks which can be run in parallel

The DBMS_PIPE package provides a non-secure mechanism for inter-session messaging. It is considered non-secure because messages can be lost from the pipe if the instance crashes or is shutdown before they are processed. Advanced Queues are arguably a...

Types of Materialized Views 1) Read-Only : This type of MVs cannot send data back to the server Master tables. These server only one way communication i.e. from server to the client. 2) Updatable : This type of MVs can send the data, changed locally,...

Refresh Materialized Views If a materialized view is configured to refresh on commit, you should never need to manually refresh it, unless a rebuild is necessary. Remember, refreshing on commit is a very intensive operation for volatile base tables. It...

Since a complete refresh involves truncating the materialized view segment and re-populating it using the related query, it can be quite time consuming and involve a considerable amount of network traffic when performed against a remote table. To reduce...

A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table. Using materialized views against remote tables is the simplest way to...