sql

SQL

http://www.postgresonline.com/journal/index.php?/archives/51-Cross-Compare-of-SQL-Server,-MySQL,-and-PostgreSQL.html Cross Compare SQL Server, MySQL and PostgreSQL

What is a transaction and what are ACID properties?

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.

Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS

Isolation levels

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level. See more here: http://www.developer.com/db/article.php/3706251

INFORMATION_SCHEMA

The INFORMATION_SCHEMA views provide meta data information about the tables, columns, and other parts of your database. INFORMATION_SCHEMA is part of the SQL-92 standard, so (in theory!!) you can write SQL statements that work on various database platforms.

List of the information schema views:

- INFORMATION_SCHEMA.SCHEMATA
- INFORMATION_SCHEMA.TABLES
- INFORMATION_SCHEMA.COLUMNS
- INFORMATION_SCHEMA.STATISTICS
- INFORMATION_SCHEMA.USER_PRIVILEGES
- INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
- INFORMATION_SCHEMA.TABLE_PRIVILEGES
- INFORMATION_SCHEMA.COLUMN_PRIVILEGES
- INFORMATION_SCHEMA.CHARACTER_SETS
- INFORMATION_SCHEMA.COLLATIONS
- INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
- INFORMATION_SCHEMA.TABLE_CONSTRAINTS
- INFORMATION_SCHEMA.KEY_COLUMN_USAGE
- INFORMATION_SCHEMA.ROUTINES
- INFORMATION_SCHEMA.VIEWS
- INFORMATION_SCHEMA.TRIGGERS
- INFORMATION_SCHEMA.PROFILING

Example:

SELECT table_name, column_name, is_nullable, data_type, character_maximum_length

FROM INFORMATION_SCHEMA.Columns WHERE table_name = 'employees'

Links:

http://www.alberton.info/firebird_sql_meta_info.html

http://www.alberton.info/oracle_meta_info.html

http://www.alberton.info/sql_server_meta_info.html

http://www.alberton.info/postgresql_meta_info.html

SQL syntax

Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name

SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name

This cartesian SQL joins "everything to everything":

SELECT * FROM TableA CROSS JOIN TableB

Examples

есть отделы (id,name) и сотрудники(id,отдел_id,name). Найдите отделы без сотрудников

select d.* from dep d ( if not exists select count(e.id) emp_count

from emp e where d.id = e.dep_id && emp_count > 0)

select d.* from dep d where not exists

(select 1 from emp e where e.dep_id = d.dep_id)

select * from dep where id not in (select dep_id from emp)

TSQL:

select dep.*

from dep left join emp on dep.id = emp.dep_id

where emp.dep_id is null

Oracle

select d.* from dep d, emp e

where d.id=e.dep_id(+) and e.dep_id is null

SQL for graph processing

Joe Celko's Trees and Hierarchies in SQL for Smarties

Joe Celko's Analytics and OLAP in SQL

http://hansolav.net/sql/graphs.html

How do you delete duplicate rows in a table and still maintain one copy of the duplicate?

DELETE FROM sometable WHERE someuniquekey NOT IN

(SELECT MAX(dup.someuniquekey) FROM sometable As dup

GROUP BY dup.dupcolumn1, dup.dupcolumn2, dup.dupcolum3)

delete from tab a where exists (select 1 from tab b where a.uniq1=b.uniq1 and a.uniq2=b.uniq2 and a.prkey>b.prkey)

CROSSTAB http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=25

SELECT

SUM(CASE WHEN purchase_date BETWEEN '2004-08-01' and '2004-08-31' THEN amount ELSE 0 END) As m2004_08,

SUM(CASE WHEN purchase_date BETWEEN '2004-09-01' and '2004-09-30' THEN amount ELSE 0 END) As m2004_09,

SUM(CASE WHEN purchase_date BETWEEN '2004-10-01' and '2004-10-31' THEN amount ELSE 0 END) As m2004_10,

SUM(amount) As Total

FROM purchases WHERE purchase_date BETWEEN '2004-08-01' AND '2004-10-31'

MySQL

mysql -uroot

mysql> help

List of all MySQL commands:

Note that all text commands must be first on line and end with ';'

? (\?) Synonym for `help'.

clear (\c) Clear command.

connect (\r) Reconnect to the server. Optional arguments are db and host.

delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.

ego (\G) Send command to mysql server, display result vertically.

exit (\q) Exit mysql. Same as quit.

go (\g) Send command to mysql server.

help (\h) Display this help.

notee (\t) Don't write into outfile.

print (\p) Print current command.

prompt (\R) Change your mysql prompt.

quit (\q) Quit mysql.

rehash (\#) Rebuild completion hash.

source (\.) Execute an SQL script file. Takes a file name as an argument.

status (\s) Get status information from the server.

tee (\T) Set outfile [to_outfile]. Append everything into given outfile.

use (\u) Use another database. Takes database name as argument.

charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.

warnings (\W) Show warnings after every statement.

nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

lots of reads, use MyISAM; lots of writes, use InnoDB

indexes (at least in MySQL) come in two main flavors: clustered, and non-clustered (there are other attributes like ‘hashed’, etc that can be applied to indexes, but let’s keep it simple for now). MyISAM uses non-clustered indexes. This can be good or bad depending on your needs. InnoDB uses clustered indexes, which can also be good or bad depending on your needs.

USE INDEX, IGNORE INDEX, and FORCE INDEX affect only which indexes are used when MySQL decides how to find rows in the table and how to do the join. They do not affect whether an index is used when resolving an ORDER BY or GROUP BY clause.

Non-clustered indexing means that the index consists of a key, and a pointer to the data the key represents. Clustered indexes: the index and the data are stored together, and in order. The good news here is that all of that random I/O you had to go through for sequential range values of the index goes away, because the data is right there, and in the order dictated by the index. Another big win here which can be really dramatic is if you have an index-covered query (a query that can be completely satisfied by data in the index). This results in virtually no I/O, and extremely fast queries, even on tables with a million rows or more. The price you pay for this benefit, though, can be large, depending on your system configuration: in order to keep all of that data together in the index, more memory is required. Since InnoDB used clustered indexes, and MyISAM doesn’t, this is what most people cite as the reason for InnoDB’s larger memory footprint

SELECT * FROM table1 USE INDEX (col1_index,col2_index)

WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM table1 IGNORE INDEX (col3_index)

WHERE col1=1 AND col2=2 AND col3=3;

SELECT table1.*,table2.* FROM table1 USE INDEX (col2_index), table2 WHERE table1.col1=table2.col1 AND table1.col2=2 AND table1.col3=3;

MS SQL SERVER

USE master;

EXEC sp_configure 'show advanced option', '1';

RECONFIGURE;

EXEC sp_configure;

sp_createstats -creates statistics for all eligible columns (all except XML columns) for all user tables in the current database

dbcc dbreindex - rebuilds one or more indexes for a table in the specified database

CREATE STATISTICS FirstLast2 ON MySchema.MyTable(FirstName,LastName)

WITH SAMPLE 50 PERCENT

CREATE STATISTICS … WITH FULLSCAN, NORECOMPUTE

ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS ON

Check: codepage for SQL Server engine

I just discovered that the problem disappears if we switch back to old "cp1252". The problem only occurs under the unicode (utf8) codepage

sp_helpstats N'MySchema.MyTable', 'ALL'

PostgreSQL

psql commands: \d \dv \dt \ds

PostgreSQL provides 3 functions that work with SEQUENCEs.

- nextval() generates (and returns) a new value from a SEQUENCE

- currval() retrieves the most-recently generated value.

- setval() resets a SEQUENCE to any value between MINVALUE and MAXVALUE

SELECT nextval( 'customer_id_seq' );

SELECT setval( 'customer_id_seq', 0 );

Serial

Besides the numeric data types PostgreSQL supports two "advanced" numeric types: SERIAL and BIGSERIAL. A SERIAL column is really an unsigned INTEGER whose value automatically increases (or decreases) by a defined increment as you add new rows. Likewise, a BIGSERIAL is a BIGINT that increases in value. When you create a BIGSERIAL or SERIAL column, PostgreSQL will automatically create a SEQUENCE for you. A SEQUENCE is an object that generates sequence numbers for you.

CREATE TABLE serial_test ( pkey SERIAL, payload INTEGER );

NOTICE: CREATE TABLE will create implicit sequence 'serial_test_pkey_seq' for SERIAL column 'serial_test.pkey'

NOTICE: CREATE TABLE/UNIQUE will create implicit index 'serial_test_pkey_key' for table 'serial_test'

Constraints: column level only or table level: NOT NULL, UNIQUE,, REFERENCES, CHECK

both(column and table level): CHECK, UNIQUE, PRIMARY KEY

Arrays in column: The index for an array starts at 1 by default

CREATE TABLE customers (

customer_id INTEGER UNIQUE,

customer_name VARCHAR(50),

phone CHAR(8),

birth_date DATE,

balance DECIMAL(7,2) CONSTRAINT invalid_balance CHECK( balance > 0 AND balance < 10000 ),

monthly_balances DECIMAL(7,2)[12]

);

By default, a REFERENCES constraint prevents from changing data in such a way that the constraint would be violated. You can use the ON UPDATE clause to relax the constraint a little, much the same as the ON DELETE clause.

The syntax required for ON UPDATE / ON DELETE is

REFERENCES table [ (column) ] ON DELETE NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT

REFERENCES table [ (column) ] ON UPDATE NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT

To INSERT values into an array, you enclose all the array elements in single quotes and braces ({}) and separate multiple elements with a comma:

INSERT INTO customers(

customer_id, customer_name, phone, birth_date, balance, monthly_balances)

VALUES(8,'Wink Wankel','555-1000','1988-12-25',0.00,

'{1,2,3,4,5,6,7,8,9,10,11,12}'

);

SELECT customer_name, monthly_balances[1:3]

FROM customers

WHERE monthly_balances[1] > 0;

UPDATE customers SET monthly_balances[1] = 22;

UPDATE customers SET monthly_balances[1:3] = '{11,22,33}';

Transactions

Begin Work; ... Rollback; Commit;

After Begin Work can be:

Set Transaction Isolation Level { Read Commited | Serializable}

For the whole session:

Set Session Characteristics As Transaction Isolation Level { Read Commited | Serializable}

Vacuum [Analyze] Table

EXPLAIN [ANALYZE][VERBOSE] query;

http://www.paragoncorporation.com

PostgreSQL Cheat Sheet

CREATE DATABASE

CREATE DATABASE dbName;

CREATE TABLE (with auto numbering integer id)

CREATE TABLE tableName ( id serial PRIMARY KEY, name varchar(50) UNIQUE NOT NULL, dateCreated timestamp DEFAULT current_timestamp );

Add a primary key

ALTER TABLE tableName ADD PRIMARY KEY (id);

Create an INDEX

CREATE UNIQUE INDEX indexName ON tableName (columnNames);

Backup a database (command line)

pg_dump dbName > dbName.sql

Backup all databases (command line)

pg_dumpall > pgbackup.sql

Run a SQL script (command line)

psql -f script.sql databaseName

Search using a regular expression

SELECT column FROM table WHERE column ~ 'foo.*';

The first N records

SELECT columns FROM table LIMIT 10;

Pagination

SELECT cols FROM table LIMIT 10 OFFSET 30;

Prepared Statements

PREPARE preparedInsert (int, varchar) AS INSERT INTO tableName (intColumn, charColumn) VALUES ($1, $2); EXECUTE preparedInsert (1,'a'); EXECUTE preparedInsert (2,'b'); DEALLOCATE preparedInsert;

Create a Function

CREATE OR REPLACE FUNCTION month (timestamp) RETURNS integer AS 'SELECT date_part(''month'', $1)::integer;' LANGUAGE 'sql';

Table Maintenance

VACUUM ANALYZE table;

Reindex a database, table or index

REINDEX DATABASE dbName;

Show query plan

EXPLAIN SELECT * FROM table;

Import from a file

COPY destTable FROM '/tmp/somefile';

Show all runtime parameters

SHOW ALL;

Grant all permissions to a user

GRANT ALL PRIVILEGES ON table TO username;

Perform a transaction

BEGIN TRANSACTION UPDATE accounts SET balance += 50 WHERE id = 1; COMMIT;

CDB and others new databases

http://www.unixuser.org/~euske/doc/cdbinternals/index.html

http://cr.yp.to/cdb.html

http://www.corpit.ru/mjt/tinycdb.html

http://www.luciddb.org/ http://couchdb.apache.org/

http://horicky.blogspot.com/2009/11/nosql-patterns.html

Indexes

http://en.wikipedia.org/wiki/Bitmap_index

Oracle

http://www.akadia.com/html/publications.html

http://www.oracle.com/technology/documentation/index.html

INSERT statement in 10G:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm

System Global Area (SGA) and the Program Global Area (PGA)

The PGA memory can be classified into Private SQL Area, Cursors, and Session Memory

Cost based optimizer (CBO): ALL_ROWS; FIRST_ROWS

Table access: full table scans; row ID scans; various types of index scans including index

unique scans, index range scans, index skips scans, and fast full index scans;

and cluster and hash access.

Join methods: nested loop, hash, sort merge, Cartesian, and outer joins

Query optimization, hints: http://www.dba-oracle.com/art_otn_cbo.htm

create table people(pid primary key )

organization index

as select rownum from all_objects

where rownum <=10000;

ALTER TABLE chicken ADD CONSTRAINT chickenREFegg

FOREIGN KEY (eID) REFERENCES egg(eID)

INITIALLY DEFERRED DEFERRABLE;

MERGE

Oracle Downloads

http://www.oracle.com/technology/software/products/database/xe/htdocs/102xewinsoft.html

PL/SQL http://www.orafaq.com/wiki/PL/SQL_FAQ

http://www.techonthenet.com/oracle/cursors/current_of.php

autonomoius transaction:

CREATE OR REPLACE PROCEDURE logging_ins (

i_username IN VARCHAR2,

i_datetime IN TIMESTAMP)

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

...

commit;

CURSORS attributes:

Attribute Name Description

%BULK_EXCEPTIONS This attribute is used for array or Bulk Collect operations. It provides information regarding exceptions encountered during such operations.

%BULK_ROWCOUNT Also used for Bulk Collect operations, this attribute

provides information regarding the number of rows changed during the operation.

%FOUND - tests whether a FETCH returned a record. The return value is of Boolean

type. If TRUE, a row was returned by the FETCH. If FALSE, a row was not returned.

%ISOPEN This attribute tests to see if a cursor is already open. If TRUE cursor is open. If FALSE not open.

%NOTFOUND returns TRUE if a row was not returned by the FETCH and FALSE if one was returned.

%ROWCOUNT tests for the number of rows fetched from the cursor and returns a number.

Implicit cursor example:

UPDATE books SET price = price * .90 WHERE isbn = '78824389';

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' rows updated');

IF SQL%NOTFOUND

THEN

DBMS_OUTPUT.PUT_LINE('Unable to update isbn 78824389');

END IF;

-- Define a record type.

TYPE individual_record IS RECORD

(individual_id INTEGER,first_name VARCHAR2(30 CHAR)

,middle_initial individuals.middle_initial%TYPE);

-- Define a variable of the record type.

individual INDIVIDUAL_RECORD;

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [ NOT NULL ];

–– Declare and initialize a varray that allows nulls.

varray_integer INTEGER_VARRAY := integer_varray(NULL,NULL,NULL);

CURSOR cursor_name [parameter_list]

[RETURN return_type]

IS query

[FOR UPDATE [OF (column_list)][NOWAIT]];

If NOWAIT is specified, the program will exit immediately on open if an exclusive lock cannot be obtained

%TYPE %ROWTYPE REF CURSOR

SET SERVEROUTPUT ON

DECLARE

v_title books.title%TYPE;

v_first_name authors.first_name%TYPE;

v_last_name authors.last_name%TYPE;

CURSOR book_cur IS

SELECT b.title, a.first_name, a.last_name

FROM authors a, books b WHERE a.id = b.author1;

BEGIN

DBMS_OUTPUT.ENABLE(1000000);

OPEN book_cur;

LOOP

FETCH book_cur INTO v_title, v_first_name, v_last_name;

EXIT WHEN book_cur%NOTFOUND;

IF v_last_name = 'Hardman' THEN

DBMS_OUTPUT.PUT_LINE('Ron Hardman co-authored '||v_title);

ELSE

DBMS_OUTPUT.PUT_LINE('Ron Hardman did not write '||v_title);

END IF;

CASE v_last_name

WHEN 'Oracle Basics' THEN

v_discount := .15;

WHEN 'Oracle Server' THEN

v_discount := .10;

WHEN v_price BETWEEN 40 AND 50 THEN

WHEN v_price >60 THEN

.....

ELSE

v_discount := .5;

END CASE;

END LOOP;

CLOSE book_cur;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('SQLERRM: '||SQLERRM);

DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE);

END;

Triggers

before, after, row and statement

CREATE OR REPLACE TRIGGER Print_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab

FOR EACH ROW

WHEN (new.Empno > 0)

DECLARE

sal_diff number;

BEGIN

sal_diff := :new.sal - :old.sal;

dbms_output.put('Old salary: ' || :old.sal);

dbms_output.put(' New salary: ' || :new.sal);

dbms_output.put_line(' Difference ' || sal_diff);

END;

update trigger:

CREATE OR REPLACE TRIGGER ...

... UPDATE OF Sal, Comm ON Emp_tab ...

BEGIN

... IF UPDATING ('SAL') THEN ... END IF;

END;

triggers to be fired on any of the following:

DML statements (DELETE, INSERT, UPDATE)

DDL statements (CREATE, ALTER, DROP)

Database operations (SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN)

Any view that contains one of those constructs can be made updatable by

-- A row-level trigger must not query or modify a mutating table.

(Of course, NEW and OLD still can be accessed by the trigger.)

-- A statement-level trigger must not query or modify a mutating table if the trigger

is fired as the result of a CASCADE delete.

INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through UPDATE, INSERT, and DELETE statements

SQL Optimization

emloyees, the department in which they work, and the date they received a bonus (not everybody get it!)

1 select e.ename, d.loc, eb.received

2 from emp e join dept d

3 on (e.deptno=d.deptno)

4 left join emp_bonus eb

5 on (e.empno=eb.empno)

6 order by 2

==================================

1 select e.ename, d.loc, eb.received

2 from emp e, dept d, emp_bonus eb

3 where e.deptno=d.deptno

4 and e.empno=eb.empno (+)

5 order by 2

=================================

a subquery placed in the SELECT list to mimic an outer join:

1 select e.ename, d.loc,

2 (select eb.received from emp_bonus eb

3 where eb.empno=e.empno) as received

4 from emp e, dept d

5 where e.deptno=d.deptno

6 order by 2

========================================================

Retrieving Rows from One Table That Do Not Correspond to Rows in Another

1 select d.*

2 from dept d left outer join emp e

3 on (d.deptno = e.deptno)

4 where e.deptno is null

1 select d.*

2 from dept d, emp e

3 where d.deptno = e.deptno (+)

4 and e.deptno is null

========================================

Retrieving Values from One Table That Do Not Exist in Another

1 select deptno from dept

2 minus

3 select deptno from emp

1 select deptno

2 from dept

3 where deptno not in (select deptno from emp)

==============================

in SQL, "TRUE or NULL" is TRUE, but "FALSE or NULL" is NULL!

And once you have a NULL result, you'll continue to have NULL result

You must keep this in mind when using IN predicates and when performing logical OR evaluations, and NULL values are involved.

To avoid the problem with NOT IN and NULLs, use a correlated subquery in conjunction with NOT EXISTS. The term "correlated subquery" is used because rows from the outer query are referenced in the subquery.

The following example is an alternative solution that will not be affected by NULL rows

select d.deptno from dept d

where not exists ( select null from emp e where d.deptno = e.deptno )

===============

select d.deptno from dept d

where not exists ( select null from emp e where d.deptno = e.deptno )

Materialized Views

ORACLE ETL

http://www.akadia.com/services/ora_etl.html

http://www.dba-oracle.com/art_dbazine_foot_oracle_instructors_guide_to_external_tables.htm

http://www.orafusion.com/art_etl.htm

http://www.adp-gmbh.ch/ora/sql/hints/index.html SQL Hints

external tables and pipelined table functions : http://www.oracle-developer.com/oracle_etl.html

http://www.orafaq.com/node/38 http://blog.lishman.com/2008/03/oracle-external-tables.html

the context-switching happens when the PL/SQL engine has to pass over to the SQL engine, then back to the PL/SQL engine, and so on, when you individually access rows one at a time. To do bulk binds with INSERT, UPDATE, and DELETE statements, you enclose the SQL statement within a PL/SQL FORALL statement. To do bulk binds with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.

"The keywords BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the SELECT INTO, FETCH INTO, and RETURNING INTO clauses. Here is the syntax:

... BULK COLLECT INTO collection_name[, collection_name] ..."

and FORALL is defined as

"The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not a FOR loop. Its syntax follows:

FORALL index IN lower_bound..upper_bound

sql_statement;

The index can be referenced only within the FORALL statement and only as a collection subscript. The SQL statement must be an INSERT, UPDATE, or DELETE statement that references collection elements. And, the bounds must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index number in the range."

http://www.dba-oracle.com/oracle_news/2004_1_31_plsqlL_bulk_binds_FORALL.htm

http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_plsql.html

http://www.dbspecialists.com/files/presentations/bulk_binds.html

multi-table insert:

INSERT ALL

INTO DIMENSION_T1 VALUES (DELTA_T.C1, .., ..)

INTO DIMENSION_T2 VALUES (DELTA_T.Cn, .., ..)

INTO XREF_T VALUES (DELTA_T.Cm, ..,..)

INTO FACT_T VALUES (DELTA_T.Cr, .., ..)

SELECT * FROM DELTA_T;

inOracle9i, the MERGE statement INSERTS and UPDATES the data with a single SQL statement.

MERGE INTO SALES_FACT D

USING SALES_JUL01 S

ON (D.TIME_ID = S.TIME_ID

AND D.STORE_ID = S.STORE_ID

AND D.REGION_ID = S.REGION_ID)

WHEN MATCHED THEN

UPDATE

SET d_parts = d_parts + s_parts,

d_sales_amt = d_sales_amt + s_sales_amt,

d_tax_amt = d_tax_amt + s_tax_amt,

d_discount = d_discount + s_discount

WHEN NOT MATCHED THEN

INSERT (D.TIME_ID ,D.STORE_ID ,D.REGION_ID,

D.PARTS ,D.SALES_AMT ,D.TAX_AMT ,D.DISCOUNT)

VALUES (

S.TIME_ID ,S.STORE_ID ,S.REGION_ID,

S.PARTS ,S.SALES_AMT ,S.TAX_AMT ,S.DISCOUNT);

=======================

the main difference between External Tables and

regular tables is that externally organized tables are read-only. No DML

operations (update/insert/delete) are possible and no indexes can be created on

them.

Example:

CREATE TABLE products_ext

(prod_id NUMBER, prod_name VARCHAR2(50), ...,

price NUMBER(6,2), discount NUMBER(6,2))

ORGANIZATION EXTERNAL

(

TYPE oracle_loader

DEFAULT DIRECTORY stage_dir

ACCESS PARAMETERS

( RECORDS DELIMITED BY NEWLINE

LOGFILE log_dir:’log_products_ext’

FIELDS TERMINATED BY ‘,’

MISSING FIELDS ARE NULL

)

LOCATION (’new_prod1.txt’,’new_prod2.txt’)

)

PARALLEL 5

REJECT LIMIT UNLIMITED;

CREATE VIEW products_delta AS

SELECT prod_id, prod_name,.., price as list_price,

(price-discount) AS min_price FROM products_ext;

External tables are better than SQL*LOADER. If you've upgraded to 10gR2, PL/SQL would only be a wrapper around SQL - the new "DML Error Logging" feature makes an INSERT, UPDATE, or MERGE statements work like SQL*LOADER (bad records are pushed into a separate table).

Page updated

Google Sites

Report abuse