This three minute video explains how Oracle Exadata Smart Flash Cache helps solve the random I/O bottleneck challenge and delivers extreme performance for consolidated database applications.
Update Joins in Oracle
We have an application running on Ms SQL Server, and we want to move it to Oracle. While I examine the custom queries, I see that “update joins”.
UPDATE table_name
SET colname = value
FROM source_table JOIN source_table2
WHERE condition
This syntax helps user to join tables and update together. Oracle does not support this syntax, so I searched to find an alternative way.
It’s suggested to use subqueries:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE emp ( ID NUMBER, NAME VARCHAR2(100), sal NUMBER, dep_id NUMBER ); CREATE TABLE dep ( ID NUMBER, NAME VARCHAR2(50 ) ); INSERT INTO emp VALUES (1, 'GOKHAN', 10000, 1); INSERT INTO emp VALUES (2, 'OSMAN', 10000, 1); INSERT INTO emp VALUES (3, 'HAKAN', 10000, 1); INSERT INTO emp VALUES (4, 'ACAR', 5000, 2); INSERT INTO emp VALUES (5, 'CEMIL', 5000, 2); INSERT INTO dep VALUES (1, 'IT' ); INSERT INTO dep VALUES (2, 'SALES' ); COMMIT; |
Let’s say we want to increase salaries of sales stuff:
|
1 2 3 4 |
UPDATE (SELECT emp.sal, dep.name FROM emp JOIN dep ON emp.dep_id = dep.ID ) SET sal = sal * 1.2 WHERE name = 'SALES'; |
DB_ULTRA_SAFE Parameter
DB_ULTRA_SAFE is a new parameter introduced in 11g. It provides an integrated mechanism to offer protection from various possible data corruptions. and provides critical high availability benefits for Oracle Database. Setting DB_ULTRA_SAFE initialization parameter will configure the appropriate data protection block checking level in the database. It will control DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_PROTECT.
DB_BLOCK_CHECKING controls whether or not Oracle performs block checking for database blocks.
DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk.
DB_LOST_WRITE_PROTECT enables or disables lost write detection. A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write, while in fact the write did not occur in the persistent storage.
Read MoreHow to Generate XML from the Oracle Database
Oracle provides plenty of XML generation methods, I’ll try to demonstrate two of them: SYS_XMLGEN and DBMS_XMLQUERY.
I’ll start with creating a simple table and fill it with sample data:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE customers ( customer_name VARCHAR2 (30), phone VARCHAR2(11)); INSERT INTO customers VALUES ( 'GOKHAN', '5421000100' ); INSERT INTO customers VALUES ( 'JACK', '5426661010' ); INSERT INTO customers VALUES ( 'JHON', '5352004050' ); COMMIT; |
To be able to use SYS_XMLGEN, I’ll create an object to map our data:
|
1 2 3 |
CREATE OR REPLACE TYPE customers_map_type AS OBJECT ( customer_name VARCHAR2 (30), phone VARCHAR2(11)); |
Automated Maintenance Tasks in Oracle 11g
Oracle Database 11g comes with three predefined automatic maintenance tasks:
- The Automatic Optimizer Statistics Collection task collects statistics for all objects with no or stale statistics.
- The Automatic Segment Advisor task provides advice on which database segments have free space that can be reclaimed.
- Automatic SQL Tuning Advisor task, which examines SQL statement performance and makes SQL profile recommendations to improve the statements.
You can query the DBA_AUTOTASK_OPERATION to find out the names and status of the automatic tasks in your database. as shown in the following example:
|
1 2 3 4 5 6 7 |
SELECT client_name, status FROM dba_autotask_operation; CLIENT_NAME STATUS ---------------------------------------------------------------- -------- auto optimizer stats collection ENABLED auto space advisor ENABLED sql tuning advisor ENABLED |
These tasks are enabled by default.
Read MoreAbout Me
Blogroll
Archives
Follow Me
