tp's blog | 一路走来

You wish to update rows in one table using values from another. For example, you have a table called NEW_SAL, which holds the new salaries for certain employees. The contents of table NEW_SAL are:

select *

from new_sal

DEPTNO        SAL

—— ———-

10       4000

Column DEPTNO is the primary key of table NEW_SAL. You want to update the salaries and commission of certain employees in table EMP using values table NEW_SAL if there is a match between EMP.DEPTNO and NEW_SAL.DEPTNO, update EMP.SAL to NEW_SAL.SAL, and update EMP.COMM to 50% of NEW_SAL.SAL. The rows in EMP are as follows:

select deptno,ename,sal,comm

from emp

order by 1

DEPTNO ENAME             SAL       COMM

—— ———- ———- ———-

10 CLARK           2450

10 KING            5000

10 MILLER          1300

20 SMITH            800

20 ADAMS           1100

20 FORD            3000

20 SCOTT           3000

20 JONES           2975

30 ALLEN           1600         300

30 BLAKE           2850

30 MARTIN          1250        1400

30 JAMES            950

30 TURNER          1500           0

30 WARD            1250         500

Solution

Use a join between NEW_SAL and EMP to find and return the new COMM values to the UPDATE statement. It is quite common for updates such as this one to be performed via correlated subquery. Another technique involves creating a view (traditional or inline, depending on what your database supports), then updating that view.

DB2 and MySQL

Use a correlated subquery to set new SAL and COMM values in EMP. Also use a correlated subquery to identify which rows from EMP should be updated:

1 update emp e set (e.sal,e.comm) = (select ns.sal, ns.sal/2

2                                      from new_sal ns

3                                     where ns.deptno=e.deptno)

4  where exists ( select null

5                   from new_sal ns

6
where ns.deptno = e.deptno )

Oracle

The method for the DB2 solution will certainly work for Oracle, but as an alternative, you can update an inline view:

1 update (

2  select e.sal as emp_sal, e.comm as emp_comm,

3         ns.sal as ns_sal, ns.sal/2 as ns_comm

4    from emp e, new_sal ns

5   where e.deptno = ns.deptno

6 ) set emp_sal = ns_sal, emp_comm = ns_comm

PostgreSQL

The method used for the DB2 solution will work for PostgreSQL, but as an alternative you can (quite conveniently) join directly in the UPDATE statement:

1 update emp

2    set sal = ns.sal,

3        comm = ns.sal/2

4   from new_sal ns

5  where ns.deptno = emp.deptno

SQL Server

The method used for the DB2 solution will work for SQL Server, but as an alternative you can (similarly to the PostgreSQL solution) join directly in the UPDATE statement:

1 update e

2    set e.sal  = ns.sal,

3        e.comm = ns.sal/2

4   from emp e,

5        new_sal ns

6  where ns.deptno = e.deptno

Discussion

Before discussing the different solutions, I’d like to mention something important regarding updates that use queries to supply new values. A WHERE clause in the subquery of a correlated update is not the same as the WHERE clause of the table being updated. If you look at the UPDATE statement in the “Problem” section, the join on DEPTNO between EMP and NEW_SAL is done and returns rows to the SET clause of the UPDATE statement. For employees in DEPTNO 10, valid values are returned because there is a match DEPTNO in table NEW_SAL. But what about employees in the other departments? NEW_SAL does not have any other departments, so the SAL and COMM for employees in DEPTNOs 20 and 30 are set to NULL. Unless you are doing so via LIMIT or TOP or whatever mechanism your vendor supplies for limiting the number of rows returned in a result set, the only way to restrict rows from a table in SQL is to use a WHERE clause. To correctly perform this UPDATE, use a WHERE clause on the table being updated along with a WHERE clause in the correlated subquery.

DB2 and MySQL

To ensure you do not update every row in table EMP, remember to include a correlated subquery in the WHERE clause of the UPDATE. Performing the join (the correlated subquery) in the SET clause is not enough. By using a WHERE clause in the UPDATE, you ensure that only rows in EMP that match on DEPTNO to table NEW_SAL are updated. This holds true for all RDBMSs.

Oracle

In the Oracle solution using the update join view, you are using equi-joins to determine which rows will be updated. You can confirm which rows are being updated by executing the query independently. To be able to successfully use this type of UPDATE, you must first understand the concept of key-preservation. The DEPTNO column of the table NEW_SAL is the primary key of that table, thus its values are unique within the table. When joining between EMP and NEW_SAL, however, NEW_SAL.DEPTNO is not unique in the result set, as can be seen below:

select e.empno, e.deptno e_dept, ns.sal, ns.deptno ns_deptno

from emp e, new_sal ns

where e.deptno = ns.deptno

EMPNO     E_DEPT        SAL  NS_DEPTNO

—– ———- ———- ———-

7782         10       4000         10

7839         10       4000         10

7934         10       4000         10

To enable Oracle to update this join, one of the tables must be key-preserved, meaning that if its values are not unique in the r
esult set, it should at least be unique in the table it comes from. In this case NEW_SAL has a primary key on DEPTNO, which makes it unique in the table. Because it is unique in its table, it may appear multiple times in the result set and will still be considered key-preserved, thus allowing the update to complete successfully.

PostgreSQL and SQL Server

The syntax is a bit different between these two platforms, but the technique is the same. Being able to join directly in the UPDATE statement is extremely convenient. Since you specify which table to update (the table listed after the UPDATE keyword) there’s no confusion as to which table’s rows are modified. Additionally, because you are using joins in the update (since there is an explicit WHERE clause), you can avoid some of the pitfalls when coding correlated subquery updates; in particular, if you missed a join here, it would be very obvious you’d have a problem.

Copy from: http://www.orafaq.com/wiki/Inline_view

An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In-line views are commonly used simplify complex queries by removing join operations and condensing several separate queries into a single query. This feature was introduced in Oracle 7.2.

This feature is commonly referred to in the MSSQL community as a derived table, and in the Postgres community simply refers to it as a subselect (subselects are inline views + subqueries in Oracle nomenclature).

Examples

Example inline view:

SELECT *

FROM (select deptno, count(*) emp_count

from emp

group by deptno) emp,

dept

WHERE dept.deptno = emp.depto;

Another good example of an inline view is:

SELECT a.last_name, a.salary, a.department_id, b.maxsal

  FROM employees a,

       (SELECT department_id, max(salary)maxsal

          FROM employees

      GROUP BY department_id) b

 WHERE a.department_id = b.department_id

   AND a.salary < b.maxsal;

The above query display the employees who earn the highest salary in each department.

<< V$SQL>>

Copy from http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2113.htm

V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.

——————-

<>

Copy from http://www.ss64.com/orav/V$SQL.html

Information about Sql processes running now.

–Examples

Select

substr(sql_text,0,80),

decode(command_type,2,‘INSERT’),

executions

From V$SQL

Where command_type = 2

Order By executions;

Select

substr(sql_text,0,80),

decode(command_type,3,‘SELECT’),

executions

From V$SQL

Where command_type = 3

Order By executions;

/*

List of command_type values with meaning:

Insert 2

Select 3

Update 6

Delete 7

Lock Table 26

Alter Database 35

Alter Session 42

Commit 44

Rollback 45

Savepoint 46

Begin/Declare 47

*/

PS:EXECUTIONS:Number of executions that took place on this object since it was brought into the library cache

——————-

<>

Copy from http://www.oracle-training.cc/oracle_tips_v$sql.htm

Whenever your database is experiencing a performance problem, it is very useful to have a script that displays all of the currently executing SQL statements. The following script joins the v$sql and v$session tables to show you all of the SQL that is executing at that moment in your database.

set pagesize 24

set newpage 1

set linesize 125

column sql_text  format a100

column user_name format a12

SELECT
an lang=”EN-US” style=”font-family:Courier;color:black;background:white;mso-highlight: white”>   u.sid, SUBSTR (u.username, 1, 12) user_name, s.sql_text

FROM v$sql s, v$session u

WHERE s.hash_value = u.sql_hash_value

AND sql_text NOT LIKE ‘%from v$sql s, v$session u%’

ORDER BY u.sid;

——————-

<>

Copy from http://www.dba-oracle.com/concepts/query_active_users_v%24session.htm

Once we figure out who is on the system, we will probably want to know what they are doing. In this case, we will join the v$session view we just queried with another view, the V$SQL view. The V$SQL view will provide us with the SQL that is being executed on our system. Let’s see GRUMPY’s session details:

SQL> select a.sid, a.serial#, b.sql_text

2  from v$session a, v$sqlarea b

3  where a.sql_address=b.address

4  and a.username=’GRUMPY’;

SID    SERIAL# SQL_TEXT

———- ———- ———————————————-

122      61521 select count(*) from gen_person where gen_person_id=95000

——————-

<>

Copy from http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:385254862843

You Asked

<
p class=”MsoNormal”>What is the diff between:

v$sql

v$sqlarea

v$sqltext

v$sqltext_with_newlines

when I query v$sql joining with v$session and filter by a SID I get more than one SQL

(expected) but if I join v$sqltext with v$session with same conditions I get one sql

statement only (last SQL issued) why is this?

Thanks

Alan

and we said…

v$sql the details — if you have multiple copies of the query:

“select * from T”

in your shared pool, v$sql will have a row per query.  This can happen if user U1 and

user U2 both have a table T and both issue “select * from T”.  Those are entirely

different queries with different plans and so on.  v$sql will have 2 rows.

v$sqlarea is a aggregate of v$sql.  It selects out DISTINCT sql.  “select * from T” will

appear there.

It is not clear to me how you are joing v$session to v$sql to get more then one row.  If

you wish to see the queries a session has open (maybe open, we cache cursors so you might

see some queries that are closed) use v$open_cursor by sid.

v$sqltext is simply a way to see the entire query.  the v$sql and v$sqlarea views only

show the first 1000 bytes.  newlines and other control characters are replace with

whitespace.

v$sqltext_with_newlines is v$sqltext without the whitespace replacment.

——————-

Some Definition

Copy from http://articles.techrepublic.com.com/5100-10878_11-6164425.html

V$SQL: This view gives you the full text of the SQL statement in a character large object (CLOB) column, plus the first 1,000 characters of it in a convenient VARCHAR2 column. The statistics available are extensive: number of parses and invalidations, disk reads and writes, execution times, wait times, and optimizer data. You can also tell the user and schema that created the statement, and how many users are currently executing it.

V$SQLAREA: This view contains many of the same statistics as V$SQL. However, V$SQL contains one row for each child of an original SQL statement, whereas this view contains only one row for each SQL string entered.

V$SQLSTATS: New in 10gR2, this view is faster and more convenient than V$SQL and V$SQLAREA; however, it contains only a subset of the other views’ columns. In particular, it does not link to user information. There is one row for each combination of SQL and execution plan hash value. As a bonus, the entries in this view may persist longer than the other views. You may find a statement here even after it has aged out of the shared pool (and, therefore, it disappears from V$SQLAREA).

V$SQLTEXT: There are times that you want each line of a SQL statement separately, instead of one big object as in V$SQL; V$SQLTEXT gives you this. The COMMAND_TYPE column is a code you can use to select certain statement types (i.e., only INSERT or SELECT.) The SQL_TEXT column has one piece of the text, and the PIECE column numbers them for sorting via ORDER BY. A variant, V$SQLTEXT_WITH_NEWLINES, keeps the original linefeeds to improve readability (V$SQLTEXT replaces them with spaces). Neither V$SQLTEXT nor V$SQLTEXT_WITH_NEWLINES tells you who executed the statement or provides you with any statistics. To get that information, you’ll have to join back to V$SQL or V$SQLAREA using the ADDRESS and HASH_VALUE columns.

Create Single Use Sequences

Use each sequence to supply unique ID values for one column of one table.

Do Not Limit the Range of Your Sequences

Do not create sequences that wrap using the CYCLE option or that have a specified MAXVALUE. The total range of sequences is so great that the upper limits realistically are never encountered.

In general, do not design sequences that wrap or have limited ranges.

Use Number Datatypes to Store Sequence Values

Use a NUMBER datatype to store sequence values within PL/SQL.

If you need to handle a sequence generate a sequence value in your C code, do not assume that a sequence–generated value will fit inside a C long variable. The maximum value for an ascending sequence is 10^27,whereas the maximum value for a C signed long integer is 10^9. If 10^9 is not a reasonable limit for your sequence, you may use a double instead of a long integer. Remember that by using a double for your sequence, you may lose some precision on fractional values. If you do not need to do arithmetic, and simply need to fetch your sequence either to print it or store it back, consider retrieving your sequence in a character string.

Performance is a critical aspect of any application. Because network round trips are very costly in a typical client–server environment,minimizing the number of round trips is key to ensuring good performance.

You should decide whether your PL/SQL procedures reside on the server or on the client based on whichever results in the fewest number of network round trips. Here are some guidelines:

*Procedures that call Oracle Forms built–ins (more generally,client built–ins) must reside on the client.

*Procedures that reference fields directly, either as :block.field or via NAME_IN/COPY, must reside on the client. You can avoid referencing fields directly by accepting field values or names as parameters to your PL/SQL procedures, which also improves your code’s modularity.

*If a procedure contains three or more SQL statements, or becomes very complicated, the procedure usually belongs on the server.

*Procedures that perform no SQL and that need no database access should reside wherever they are needed.

If a procedure is called from the server, it must reside on the server. If a procedure is called from both client and server, it should be defined in both places, unless the procedure is very complicated and double maintenance is too costly. In the latter case, the procedure should reside on the server.