Oracle SQL

ORACLE SQL Performance Tuning Series

kods.net — Wed, 03 Feb 2010 23:53:32+0000

File:	Oracle performance optimization. Chm
Size:	106KB
Download:	Download

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle SQL * PLUS based

kods.net — Mon, 01 Feb 2010 21:40:03+0000

rn
Oracle's SQL * PLUS is the design of the basic tools of all application systems. To be applied as a robust system design, superior performance system. The key is to understand the true meaning and structure of the RDBMS, Oracle SQL * PLUS to understand the characteristics and the core of the relational database and the desktop database to ascertain the difference. For example to understand data integrity, consistency, indexes, views, and so on. The only way to design a consistent feature of Oracle applications. In order to ensure the system after use does not appear in the provision of consistency, performance and other issues.
§ 1.1 to understand relational database system (RDBMS)
CODASYL (Data System Language Association) is the Database Task Group (Database Task Group, DBTG) created a database standard, which is a COBOL-based network database standard.
rn
§ 1.1.1 relational model a description of how the two elements of the collection or how to contact one correspondence between the mathematical concept of the database, the relationship has only one table with some special properties. So, are:
rn
l is the relationship between the basis of data entry
l In these tables, the operation produced only on the relationship between a relational table must meet certain conditions in order to become a part of the relational model
l The data stored in the unit must be atomic. Each unit can store a data, called the principle of information (Information Principle). If the store is in breach of the principle of more than one. Te Shu circumstances may require in breach of the principle.
l stored in the data under the column must have the same data type.
l each row is unique (not exactly the same lines).
l there is no order of the column.
l line is not the order of
l contains a unique name.
rn
The other is the integrity of the relationship between the principles of the model. It includes the physical integrity of the principle of (Entity integrity rule), and the principle of referential integrity (Referential integrity rule), such as:
l primary key (Primary key) is to be able to uniquely identify a row or a group of a collection of columns.
l by a number of columns constitute the primary key is called Connect button (Concatenated key), combination of keys (Compound key), also known as a composite key (Composity key).
rn
The other is the external keys (Foreign key) is a table in one or a group of columns, they are in other tables as the primary key exists. A table in the foreign key is considered to be the primary key in another table references. Physical integrity of the principle that the primary key can not be concise manner whole or in part, vacant or empty, the principle of referential integrity succinctly that a foreign key must be null or it refers to the primary key value that currently exists is consistent.
rn
Rule 12 § 1.1.2 Codd
Oracle database system is a perfect database technology in full compliance with the relationship between the database system. In order for your application design principles in accordance with the database to carry out, the most important is to understand the structure of Oracle, statements and orders. Codd's 12 rules proposed in the Oracle system can be found at:
rn
1) Information rules.
2) authorize the storage rule, each data item through a "line of table name + primary key + one of the" combination of the formation of access.
3) must be consistent ways to use null values.
4) an active, on-line data dictionary should be stored as a relational table
5) It must provide access to data access language access.
6) All that can be updated view should be updated.
7) There must be a collection-level insert, update, and delete.
8) physical data independence. That applications are not dependent on physical structure.
9) logical data independence. If a table is divided into two parts, then the application view are connected so as not to impact on the application.
10) the integrity of independence. Integrity of the rule should be stored in the data dictionary.
11) the distribution of independence. A database, even if they are distributed, it should be able to work.
12) the principle of non-destructive. If you allow low-level access, must not bypass the security and integrity of the principles.
rn
§ 1.2 relational database system (RDBMS) the composition of the
RDBMS consists of two parts, namely, the kernel database system (software) and data dictionary (the kernel database system used to manage the data structure) in two parts.
rn
§ 1.2.1 RDBMS kernel
RDBMS is used to control data access operating system. Its mission is to store data according to certain rules, retrieve data and protect data.
rn
§ 1.2.2 the concept of data dictionary data is automatically stored in the database of all objects (such as tables, indexes, views, etc.) the required information. The data dictionary is the Oracle 8i database systems store information in a set of tables from the data dictionary information in the data objects can be identified in the database of basic information and store locations.
rn
§ 1.3 SQL, SQL * Plus and PL / SQL
The following simple language to explain the commonly used Oracle products contain SQL * PLUS and PL / SQL relationship.
§ 1.3.1 SQL and SQL * PLUS the difference between
SQL is a standard structured query language, SQL * PLUS is actually include standard SQL and Oracle, the product composition of a number of commands, thus its name to Oracle's SQL * PLUS. The following is their explanation.
rn
1. SQL (Structured Query Language)
rn
There are many SQL keywords, the following statement is commonly used in the beginning of the statement:
Alter Insert
Audit Lock
Commit Noaudit
Comment Rename
Create Revoke
Delete Select
Drop Update
Grant Validate
rn
Note:
1.Oracle of the SQL buffer can only store a SQL command;
2.Validate (to force) in the oracle used in conjunction with the Enable, but eliminated, Validate, so much of the information is not introduced Validate usage. The syntax such as:
Enable ([Validate] [Novalidate]) ([UNIQUE] [PRIMARY KEY] ...) ...
rn
2. SQL * PLUS
rn
In addition to SQL outside, SQL * PLUS also known as SQL * PLUS command additional commands that is mainly used for the formation of complex reports, edit SQL commands, to provide help to safeguard systems. SQL * PLUS, including the command is as follows:
rn
@ Connect
Host Set
# Copy
Input Show
$ Define
List Spool
/ Del
Newpage Sqlplus
Accept Describe
Pause Start
Append Disconnect
Quit Timing
Break Document
Remark Ttitle
Btitle Edit
Prompt Undefine
Chang Execute
Print Save
Clear Exit
Run Column
Get Runform
Compute Help
rn
3. DDL (Data Define Language)
rn
For the structured query language (sometimes called SQL command), they can be divided into two groups, one group of data definition language (DDL); the other group is the data manipulation language (DML). Data definition language which is used as follows:
rn
Alter procedure re-compilation process
Alter table modify the properties of the table
Analyze the value of statistical properties of database objects
Alter table add Constraint on the table plus the existing constraints
Create table table structure established
Create index index
Drop table drop table entities and the related index
Drop index Remove index
Grant authorization to the user or role
Truncate to delete all the rows in the table
Revoke permissions from the user or role to recover
rn
4. DML (Data Manipulation Language)
rn
For the structured query language, another group of data manipulation language (DML). DML for data manipulation commands of which are as follows:
rn
Insert
Delete
Update
Select
Commit work
Rollback
rn
§ 1.3.2 PL / SQL Language
PL / SQL is Oracle RDBMS (Oracle 6 or later) is an integral part of, PL is a "process-oriented language (Procedure Language)" acronym. PL / SQL language is a combination of the SQL language structure, language composition process the quasi-fourth-generation language.
rn
Using the PL / SQL purpose:
Since most of PL / SQL is a server to run, this could reduce the client needed to run the program when the network data traffic.
You can use PL / SQL places:
l PL / SQL you can write a separate program to complete the general processing functions;
l in the high-level language that can be embedded in PL / SQL block;
l In the 4GL can be embedded in PL / SQL block;
l In the PL / SQL programs can be embedded in HTML and XML.
rn
§ 1.4 Log on to SQL * PLUS
We create any object, such as creating tables, indexes and so on need to connect to Oracle, here, with the "Sign in" interface is mainly provided by Oracle is the Login name for this. In fact, the meaning is the connection. In the Client / Server structure, Oracle provides two ways to connect SQL * PLUS, including SQL * NET V2.x version (the current version no longer use SQL * NET V2.x) provides character mode to connect to SQL * PLUS, SQL * NET V2. x version provides the graphics mode (Window) to use, the current version of ORACLE8/8i use NET8 connection. (NET8 no longer supports character terminals)
rn
§ 1.4.1 UNIX environment in UNIX, we should ensure that the client or server side to connect with the Oracle server system, we must ensure that the two parameters tnsnames.ora and the listener.ora file is configured correctly. The configuration explained in detail in the chapter explains DBA. Here is the file tnsnames.ora and listener.ora displays the contents of the two parameters.
rn
1. Tnsnames.ora parameter file:
INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = dbsvr) (PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = s450)
(PRESENTATION = http://admin)
)
)
rn
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
rn
S450 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = dbsvr) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = s450)
)
)
rn
2. Listener.ora parameter file:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = dbsvr) (PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC))
)
)
)
rn
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = / home/oracle/app/oracle/product/8.1.7)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = s450)
(ORACLE_HOME = / home/oracle/app/oracle/product/8.1.7)
(SID_NAME = s450)
)
)
rn
If the installation has been correctly on the above two parameters, configuration files, you can follow the steps below to log in:
rn
1) First start the server-side listener:
lsnrctl start
rn
2) Use the following command to explicitly log on to SQL * PLUS:
$ sqlplus username / password
Or
$ SQLPLUS username / password @ connect_string
rn
3) implicitly with the following command to log on to SQL * PLUS:
$ sqlplus [enter]
Enter user name: scott
Enter password :******
rn
§ 1.4.2 Windows NT and WINDOWS/2000 environment in the NT/2000 environment, to make the client connect with the Oracle server, tnsnames.ora, and listener.ora file configuration parameters are as follows:
rn
1. Tnsnames.ora parameter file:
rn
ORA816.TAIJI.COM.CN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = zhao) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora816)
)
)
rn
EXTPROC_CONNECTION_DATA.TAIJI.COM.CN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
S450 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = dbsvr) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = s450)
)
)
rn
2. Listener.ora parameter file:
rn
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = zhao) (PORT = 1521))
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP) (HOST = zhao) (PORT = 2481))
)
)
rn
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D: \ oracle)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ora816)
(ORACLE_HOME = D: \ oracle)
(SID_NAME = ora816)
)
)
rn
Windows NT/2000 character to log steps:
1) First start the server-side listener:
lsnrctl start
rn
2) Use the following command to explicitly log on to SQL * PLUS:
$ sqlplus username / password
Or
$ SQLPLUS username / password @ connect_string
rn
Windows NT/2000 graphical login steps:
1) Click "Start" = "" program "=" "Oracle-OraHome81" = "" Application on Development "=" "SQL Plus" to enter the following screen:
rn
In the WINDOWS NT/98/2000 on the login screen (sqlplus Map)
rn
In the User Name box enter the user, password and host string; also be entered separately.
rn
§ 1.5 Additional commands commonly used SQL * PLUS Introduction
Oracle provides additional statement (or command), to meet program staff and administrators of some special operational requirements. For example, records showed that more than hundreds of lines of information, you can use each screen "pause" to achieve. To achieve this objective, we have in the SQL> issued a set pause on the command. Because SQL * PLUS commands more, the following are given only the most commonly used description of a few commands in detail please refer to Appendix.
§ 1.5.1 Log on to SQL * PLUS
rn
Can use the following command to log on to SQL * PLUS, SQL * PLUS commands a simple syntax is as follows:
rn
SQLPLUS [[logon] | [start]]
rn
logon can be:
(username [/ password] [@ connect_identifier] | /) [AS (SYSOPER | SYSDBA)]
| / NOLOG
rn
Note 1: SQLPLUS is mainly used in the command mode, in the NT, WINDOWS/2000, UNIX usage are the same.
Note 2: If the UNIX under, SQLPLUS command is not to be identified (not exist), then the problem in the environment variable PATH is not set correctly, or not set. SQLPLUS executable program in $ ORACLE_HOME / bin directory.
rn
§ 1.5.2 EXIT and QUIT
rn
Can use exit or quit to terminate the operation of SQL * PLUS (session). Syntax is as follows:
rn
(EXIT | QUIT) [SUCCESS | FAILURE | WARNING]
rn
(EXIT | QUIT) can use exit or quit, they are currently functioning.
rn
SUCCESS normal exit
rn
FAILURE error code with a prompt withdrawal of
rn
WARNING exit code with prompt warnings
rn
COMMIT out to save not submitted before the
rn
For example:
SQL> exit
rn
§ 1.5.3 DESCRIBE (display tables, views, structures)
DESCRIBE can use (as long as you can with the DESC) to display the table, view the definition of the column, you can also display a synonym for a function or stored procedure instructions. Syntax is as follows:
rn
DESC [RIBE] ([schema.] Object [@ connect_identifier])
rn
Schema: user name, if omitted, for the owner of an object.
rn
object
Can be a table (table), view (view), type (type), stored procedure (procedure), the function (function), package (package) or synonyms (synonym)
rn
@ connect_identifier
Database connection string
rn
Example: Display the structure of emp table:
rn
SQL> desc emp
rn
§ 1.5.4 LIST (list) command can use LIST command to list the current SQL buffer in one or more lines in the command statement.
rn
L [IST] [n | nm | n * | n LAST | * | * n | * LAST | LAST]
rn
n the first n rows listed
nm are listed in rows n to m
n * are listed in the first n rows to the current row
n LAST lists the first line to last line n
* List of all rows
* N lists the current line to the first n rows
* LAST lists the current line to the last row
LAST lists the last line of
rn
Example:
SQL> LIST
1 SELECT ENAME, DEPTNO, JOB
2 FROM EMP
3 WHERE JOB = 'CLERK'
4 * ORDER BY DEPTNO
rn
§ 1.5.5 Change (replace string) command
rn
Change the command can be used to change the string (ie replace the string). Syntax is as follows:
rn
C [HANGE] sepchar old [sepchar [new [sepchar]]]
rn
Sepchar as separator, which can be "/" or "!" - Asking the users to pay special attention
rn
Old Old string
rn
New new string
rn
Example: In addition to number (/) to multiplication sign (*), you need to command c !/!*!. Namely:
SQL> l
1 * select sal, sal/100 from emp
SQL> c !/!*!
rn
Reminder: For Modify / characters can only be used! To make separators (the cases).
rn
Example: The multiplication sign (*) to a plus sign (+), you need to command c !/!*!. Namely:
SQL> l
1 * select sal, sal * 100 from emp
SQL> c /*/+/
1 * select sal, sal +100 from emp
SQL>
rn
§ 1.5.6 Append (append string) command
rn
Can be completed with the Append command to append the string at the end of the current row. Syntax is as follows:
rn
A [PPEND] text
rn
Text string of the requested additional
rn
Example: In the current row select sal, sal +100 from emp added where sal> = 2000, then:
rn
SQL> l
1 * select sal, sal +100 from emp
SQL> a where sal> = 2000
1 * select sal, sal +100 from emp where sal> = 2000
SQL>
rn
§ 1.5.7 Save command to save the current buffer to file
rn
Can use SAVE command to save the current command line to the operating system file. Syntax is as follows:
rn
SAV [E] filename [. Ext] [CRE [ATE] | REP [LACE] | APP [END]]
rn
Of which:
filename: You will buffer the contents of the deposit to the operating system directory file name.
ext: If you use the file suffix, the default file extension for SQL.
rn
Example:
SQL> select table_name from dict where table_name like '% ROLE%';
SQL> save c: \ get_role
rn
§ 1.5.8 GET command file will be read with the GET command buffer can be the operating system directory under the command file to read the buffer (but not executed). Syntax is as follows:
rn
GET filename [. Ext] [LIS [T] | NOL [IST]]
Of which:
filename: want to load into the SQL buffer file name
ext: extension of the file, the default for SQL.
rn
Example:
rn
SQL> get c: \ get_role
rn
§ 1.5.9 SPOOL log information to a file
Oracle's SPOOL command allows the screen all the information arising from the paper records into the operating system up until the SPOOL OFF. Syntax is as follows:
rn
SPO [OL] [filename [. Ext] | OFF | OUT]
Of which:
filename: you want to output (spool) file name.
ext: file suffix. The default suffix is the LST (or LIS).
rn
SQL> col table_name for a20
SQL> col comments for a80
SQL> set linesize 110
SQL> SPOOl c: \ all_dict
SQL> select table_name, comments from dict;
...... (System query information)
rn
SQL> SPOOL OFF
rn
§ 1.5.10 to run the current buffer command in SQL> mode, if you want to run the current command, can be Run (or R) or with / to achieve, such as:
rn
SQL> set lin 120
SQL> select table_name from dict where table_name like '% ROLE%';
rn
TABLE_NAME
------------------------------
DBA_ROLES
DBA_ROLE_PRIVS
USER_ROLE_PRIVS
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
SESSION_ROLES
rn
Have chosen 7 rows.
rn
SQL> l
1 * select table_name from dict where table_name like '% ROLE%'
SQL> /
rn
TABLE_NAME
------------------------------
DBA_ROLES
DBA_ROLE_PRIVS
USER_ROLE_PRIVS
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
SESSION_ROLES
rn
Have chosen 7 rows.
rn
§ 1.6 Common Data Dictionary Overview
ORACLE provides a number of internal data dictionary for a variety of information management systems and parameters (ie, database objects), the following are a few common data dictionary for beginners and reference, other aspects of the data dictionary will be described in the DBA management.
rn
ORACLE data dictionary named Description:
rn
USER record for the prefix ---- all objects the user information
ALL for the prefix USER ---- records, including records and delegation of authority to PUBLIC for all objects or the user's information.
DBA for the official records of the database objects ---- prefix (non-user object) information.
Dynamic view V $ public system, for system optimization and adjustment of reference.
V_ $ dynamic performance views, you can create a dynamic view of the establishment of the script CATALOG.SQL synonyms.
GV $ new (oracle 8) a fixed view of the additional (Global V $). In the parallel environment, the reaction is
V $ view information. Such as:
rn
SELECT * FROM GV $ LOCK WHERE INST_ID = 2 OR INST_ID = 5;
rn
Returns instances 2 and 5 of the V $ information. Therefore, GV $ response to a group of Instances of parameters. GV $ view restriction is PARALLEL_MAX_SERVERS parameter must be greater than 0.
See Oracle Enterprise Manager Administrator's Guide.
rn
Note: Please note that the following summary:
l General DBA_ view content that contains USER_ and ALL_ for the prefix view;
l DBA_ To view the contents of the prefix is basically capitalized;
l to V $ _ as a prefix of the view contents are basically the lowercase.
rn
1. USER_TABLEs (= TABS) all of the user tables.
2. USER_TAB_COLUMNS (= COLS) the relevant table columns (fields) of information
3. USER_VIEWS users all views
4. USER_SYNONYMS (= SYN) User synonymous
5. USER_SEQUENCES (= SEQ) the user sequence
6. USER_CONSTRAINTS records to create the table structure of the building restrictions.
7. USER_TAB_COMMENTS table notes. Such as:
rn
Comment on table emp is 'employee table';
rn
8. USER_COL_COMMENTS column (field) notes. Such as:
rn
Comment on column emp.ename is 'Name';
rn
9. USER_INDEXES (= IND) brief information on the user index
10. USER_IND_COLUMNS user index column information
11. USER_TRIGGERS the user triggers the information
12. USER_SOURCE user stored procedure
13. USER_TABLESPACE users can use the table space information
14. USER_TS_QUOTAS users of information system resources
15. USER_SEGMENTS spatial information used by the user object
16. USER_EXTENTS the user's extended segment information
17. USER_OBJECTS User Object
= USER_TABLES + USER_VIEWS + USER_INDEXES +
USER_SOURCE + USER_TRIGGERS + USER_JAVA
rn
18. USER_PART_TABLES user partition information
19. USER_TAB_PARTITIONS
20. USER_PART_COL_STATISTICS
21. USER_IND_PARTITIONS
22. USER_FREE_SPACE
23. CAT (= USER_CATALOG) users can access all of the base tables.
24. TAB users to create all the base tables, views, synonyms and so on.
25. DICT (= DICTIONARY) constitute a data dictionary for all tables.
rn
Tip: Although Oracle provides can Comment on column tablename.column is 'xxxx'; and so to achieve the right table or column comments, but is not recommended in such a way designers work. The proposed notes is more intuitive to write the script.
rn
§ 1.7 ORACLE Data Type
Oracle Database data types when compared with other database systems, which limited the type of data, Oracle said the data in the database than other systems that want to save a number of keywords. Oracle only NUMBER (m, n) can represent any complex digital data. Others, such as date type, etc. are also much simpler, says that only the DATE the date and time. Given below in tabular form the various versions of the Oracle system data type of representation. Here are the old version of the Oracle data types aim is to enable readers to understand the changes in Oracle, the other is the old version you are upgrading or data conversion should pay attention to the various versions of the difference.
rn
ORACLE5, ORACLE6 data type
rn
Data Type Description
Char variable length character, ≤ 254
Varchar2 variable length character, ≤ 2000
Number (m, n) number of types, including integers, decimals, etc.
Date date type, with time, default format is mmm-dd-yyyy hh: mi: ss (total 7 bytes)
Long storage of large variable-length strings, ≤ 2GB
Raw storage short binary strings, ≤ 2GB
Long raw storage length binary strings, ≤ 2GB
rn
ORACLE7 data type
rn
Data Type Description
Char fixed-length character, ≤ 255 characters
Varchar variable-length character, ≤ 2000 characters
Varchar2 variable-length character, ≤ 2000 characters
Number (m, n) number of types, including integer, floating point, double precision, etc.
Long storage of large variable-length strings, ≤ 2GB
Raw storage variable short binary, ≤ 2000
Long raw storage of large variable-length binary, ≤ 2GB
rn
ORACLE8/8i data type
rn
Data Type Description
Char fixed-length character, ≤ 2000 characters
Varchar (with Varchar2) variable characters, ≤ 4000 characters
Varchar2 variable-length character, ≤ 4000 characters
Date fixed length (7 bytes) of the date type
Number numeric can store real-and integer
Long variable character, ≤ 2GB characters
Raw binary data variable, ≤ 4000 bytes
Variable Long raw binary data, ≤ 2GB
MLSLABEL using Trusted Oracle only 2 to 5 bytes in length between the
Blob large binary objects, ≤ 4GB
Clob large string objects, ≤ 4GB
Nclob multi-byte character set Clob, ≤ 4GB
Bfile an external binary file, the size of the decision by the OS
rn
CHAR ()
Fixed-length character (in the Oracle5, Oracle6 is a variable length), the character length is not enough spaces automatically sign on the right. When the length exceeds 2000 characters in an error. Do not specify the size of the default is 1.
rn
VARCHAR ()
Variable character, the current and VARCHAR2 () the same.
rn
VARCHAR2 ()
Variable character, the current and VARCHAR () the same. VARCHAR2 types of fields (columns) can store 4000 characters; but the VARCHAR2 variable can store 32,767 characters. Size must be specified.
rn
NCHAR (), and NVARCHAR2 ()
NCHAR and NVARCHAR2 respectively CHAR and VARCHAR2 have the same size. And used in the store National Language Support (NLS) data, Oracle allows storage of data in local languages and query data.
If the column name of the statement into NCHAR, NVARCHAR2 such a type, then the insert, and select the specific values such as statements preceded by N, can not directly operate in accordance with common character type. See the following examples:
rn
SQL> create table nchar_tst (name nchar (6), addr nvarchar2 (16), sal number (9,2));
rn
The table has been created.
rn
SQL> insert into nchar_tst values (N 'ZHAO Yuan-jie', N ', Beijing Haidian District', 9999.99);
rn
Have created a row.
rn
SQL> select * from nchar_tst where name like N 'Zhao%';
rn
NAME ADDR SAL
------ ---------------- ----------
ZHAO Yuan-jie, Beijing Haidian District 9999.99
rn
SQL> select * from nchar_tst where name like 'Zhao%';
select * from nchar_tst where name like 'Zhao%'
*
ERROR at line 1:
ORA-12704: character set mismatch.
rn
Tip: Although Oracle can use the nchar, nvarchar2 type to store character data, but recommends that designers do not use the NCHAR and NVARCHAR2. Because the CHAR and VARCHAR2 can store characters.
rn
NUMBER (
,)
rn
Is the integer part of the data, the accuracy of the data (ie fractional) part, note that some can be expressed as a negative precision. Can be expressed with a decimal point right or left from the number of bits reserved. The following table:
rn
Data type stores the value of the actual value
1234567.89 Number 1234567.89
1234567.89 Number (8) 1234568
1234567.89 Number (6) error
1234567.89 Number (9,1) 1234567.9
1234567.89 Number (9,3) error
1234567.89 Number (7,2) error
1234567.89 Number (5, -2) 1234600
1234511.89 Number (5, -2) 1234500
1234567.89 Number (5, -4) 1230000
1234567.89 Number (*, 1) 1234567.9
rn
Sal number (7,2), - said the 5 integers and 2 decimal places.
rn
DATE
Oracle date type with 7 bytes, each date type that contains the following:
l Century (century)
l Year (years)
l Month (month)
l Day (days)
l Hour (hours)
l Minute (min)
l Second (sec)
rn
Date-type field has the following characteristics:
rn
l date-type field insert and update data type or character, and with to_date function description can be.
l The default date format NLS_DATE_FORMAT parameter control, it is the default format DD-MON-YY.
l The default time is 00:00:00 at night (ie, 0:00:00).
l sysdate returns the server's time, see the following example.
l date format display can be set, see the following examples.
Date l-type can be operations, see the following examples. See the following examples.
l century, said that with the cc; years, said with yyyy, month to use mm, said daily dd said hours with hh24 said the split-mi said that seconds with ss said.
rn
Example:
SQL> create table save_info (per_id varchar2 (20), name varchar2 (20), tran_date date,
2 tran_val number (12,2));
rn
The table has been created.
rn
SQL> insert into save_info values ('110105540609811 ',' ZHAO Yuan-jie ',
2 to_date ('2001 .06.18 ',' yyyy.mm.dd '), 12345.66);
rn
Have created a row.
rn
SQL> select * from save_info;
rn
PER_ID NAME TRAN_DATE TRAN_VAL
-------------------- -------------------- ---------- ----------
110105540609811 ZHAO Yuan-jie 18-6 months -01 1234.66
rn
SQL> select per_id, name, to_char (tran_date, 'yyyy / mm / dd'), tran_val from save_info;
rn
PER_ID NAME TO_CHAR (TR TRAN_VAL
-------------------- -------------------- ---------- ----------
110105540609811 ZHAO Yuan-jie 2001/06/18 12,345.66
rn
SQL> show parameter nls_date_format
rn
NAME TYPE VALUE
------------------------------------ ------- ------- -----------------------
nls_date_format string
rn
SQL> alter session set nls_date_format =
2 ' "Year" yyyy "Year" mm "on" dd "days"';
rn
Conversation has changed.
rn
SQL> select sysdate from dual;
rn
SYSDATE
------------------
AD May 18, 2001
rn
SQL> select to_char (sysdate, 'cc yyyy.mm.dd') from dual;
rn
TO_CHAR (SYSDA
-------------
21 2001.05.18
rn
Type of use on the date details please refer to "Oracle8i National Language Support Guide".
rn
BLOB
Large binary objects, each record can store up to 4GB of data, see the following sections in detail.
rn
CLOB
Large character objects, each record can store up to 4GB of data, see the following sections in detail.
rn
BFILE
External binary file, each record can store up to 4GB of data (with the OS-related), see the following chapters in detail.
rn
RAW
Unstructured binary data, which database system is not explained. RAW can store up to 2,000 bytes.
rn
LONGRAW
Large binary type data, LONGRAW non-binary data structure, these data are database systems are not explained. LONGRAW can store up to 2GB bytes. LONGRAW can not be indexed, but RAW can be indexed.
rn
ROWID
ROWID in Oracle database is a virtual column, that is, the system uses a special column, the column is not our building. Used in the database to locate each record. Details, see "Rowid the use of" section.
rn
UROWID
UROWID is a Universal ROWID meaning. The Global ROWID, it supports logical and physical ROWID, but also as an external table (through getway access to non-Oracle tables) of the ROWID. UROWID type can store all of the ROWID types of data.
rn
% TYPE type of match:
books_printed number (6);
books_sold books_printed% TYPE;
books_sold data types and book_printed types of the same;
(% TYPE type in PL / SQL in the introduction).
rn
Null values and character, the digital-based computing:
null + = null (empty value + figure is still empty value)
null> = null (null value compared with the results of an empty value)
null | | 'string' = string
rn
number type and the following types have the same range:
DEC
Decimal
Double PREcision
Float
Integer
Int
Numeric
Real
Smallint
rn
Tip: Although you can use the above sub-Oracle data types, but the proposal was adopted NUMBER (n, m) is better. Because if the data type definitions using the sub-field type of improper operation may cause data problems.
rn
Long data type restrictions:
l select can be used long;
l update can be used in select clause;
l insert can be used Valus clause;
l Each table can only allow a long column;
l long columns can not list integrity constraints (null, not null exception);
l long columns can not be indexed;
l procedure or stored function can not receive long-type function;
l stored function can not return long-type value.
rn
long is not currently appear in the following situations:
l select the Where, Group by, order by, Connect by, distinct;
l can not long classified as substr, instr;
l expression or condition;
l sub-query or the collection can not be long;
l Create table ... as select can not be used long;
rn
§ 1.8 SQL statement is the basis of the following SQL statement is given a basic introduction, a more detailed description see the following chapters.
§ 1.8.1 SQL statements used in symbolic
rn
Examples of Operator Use
+ - Indicates positive or negative, positive, can be avoided + -1234.56
+ Will be the sum of two numbers or expressions A = c + b
- The two numbers or expressions subtract 34-12
* To multiply two numbers or expressions 12 * 34
/ Divided by a number or expression 18 * 11
NULL null value judgments Where name is null;
| | String to connect '101-'| | tel_num
= Equal to test Select * from emp where name = 'ZHAO Yuan-jie';
! = Or "" or ^ = not equal to test Select * from emp where name! = 'ZHAO Yuan-jie';
"Less than the Test Select * from emp Where sal <5000;
"Greater than the Test Select * from emp Where sal> 5000;
"= Less than or equal test Select * from emp Where sal <= 5000;
"= Greater than or equal test Select * from emp Where sal> = 5000;
Not in testing whether a particular value of a specified result set Select name, addr from expertwhere local not in ( 'Beijing', 'Shanghai');
ANY would be a value compared with a group of values, return to meet the criteria. Must follow !=,<,>,<=,>= select ename, sal from emp where sal <= any (select sal from emp where deptno = 10)
SOME with ANY, must follow !=,<,>,<=,>=
ALL will be a value with a set of value, to return all the column values to meet the conditions. Must follow !=,<,>,<=,>= Select name, sal from empWhere sal <= all (500,800,1200);
Not betweenA and B to determine whether a value in between. Select name, sal from emp Where sal between 500 and 1200;
[not] exists to determine whether a column exists in a set of values. select dname, deptno from dept where exists (select * from emp where dept.deptno = emp.deptno)
A [not] like b [Escape 'char'] compare the two models is similar, like statements when using Oracle not to access the index. Select * from empWhere ename like 'TH%';
Is [not] null test value is empty. Select ename, deptno from empWhere comm. Is null or comm .= 0;
Not the negative results. Select * from empWhere sal not (sal <1000); equivalent to select ename, sal from emp where sal> = 1000;
AND is used to determine both the two conditions are met. Select * from emp where Ename = 'SIMTH' and sal> = 1000;
OR is used to determine whether there are two conditions to satisfy one. Select * from emp where Ename = 'SIMTH' or ename = 'SCOTT';
UNION is used to return (combination) of all the only two rows in the query. Select ename from emp unionSelect ename from emp;
UNION ALL is used to return (combination) of all two check all the rows.
INTERSECT is used to return two query the same row. Select ename from emp1 intersect select ename from emp2;
MINUS query used to return two different lines.
rn
§ 1.8.2 Simple select query if we can use SQL * PLUS to log on to SQL> Next, we can use DESC show the structure of a table, you can also use a simple select statement to query the table some of the column content.
rn
Example: To query the table EMP employee's name, salary and date of birth, then:
rn
SQL> select ename, sal, hiredate from emp;
rn
§ 1.9 Pseudo-out and pseudo-table
Oracle systems in order to achieve a complete relational database capabilities, the system provides a set of special pseudo-columns (Pseudocolumn) of the database column, these columns are not in the establishment of an object (such as the construction of the table) is done by us, but we build objects automatically done by the Oracle. Oracle currently has the following pseudo-columns:
rn
l CURRVAL and NEXTVAL the use of the serial number of the reserved words
l LEVEL query data corresponding to class
l ROWID to uniquely identify records
l ROWNUM limit the number of query result sets
rn
A detailed explanation of the pseudo-column and use, see the relevant sections.
rn
Oracle also provides a pseudo-DUAL table, which main purpose is to ensure that the use of SELECT statement provided the integrity of statements, such as: we have to query the current system date and time, and the system date and time, and is putting the in a specified table. Therefore, from statement after the table name is not given. In order to use from the table after a name, we will use DUAL to replace. Such as:
rn
Example 1: Query Oracle system date and time:
SQL> select to_char (sysdate, 'yyyy.mm.dd hh24: mi: ss') from DUAL;
rn
TO_CHAR (SYSDATE, 'YY
-------------------
2001.06.02 07:28:09
rn
Example 2: calculate the results of 5000 +5000 * 0.1 What is, then:
SQL> select 5000 +5000 * 0.1 from DUAL;
rn
5000 +5000 * 0.1
-------------------
5500
rn

rn
This article comes from CSDN blog, reproduced please indicate source: http://blog.csdn.net/jack_rao/archive/2009/05/11/4168359.aspx

oracle sql statement based on 2009-05-20 15:41:25
Oracle SQL optimization principles (pick) 2009-05-20 02:43:26
shell script examples of the implementation of Oracle SQL script 2009-04-14 06:08:39
Foreign key fields to build the index is not caused by deadlock 2009-04-09 17:55:59
oracle basic concepts and terminology 2009-04-06 22:17:39

Oracle SQL * PLUS basic and sqlplus command Xiangjie -2

kods.net — Mon, 01 Feb 2010 19:55:02+0000

§ 1.10 using the SQL Worksheet in the new version of Oracle, the system provides a graphical SQL called SQL Worksheet working environment, which is part of the ORACLE Enterprise Manager. Use it can be removed from the SQL> edit the line under the command of inconvenience. It is completely in a graphical manner. It starts with the following methods:
l start from the ORACLE Enterprise Manager
l start from the Administrtor Tools
l from the operating system to start
rn
Here's the operating system to start the SQL Worksheet tool to work these steps:
1) Click on the SQL Worksheet icon, choose Start - "programs -> Oracle-OraHome81-> database Administration-> SQLPLUS Worksheet. SQLPLUS Worksheet is selected after the following screen:
rn
Figure 1.10-1 login screen using the SQL Worksheet
rn
In the user name, password and imports of services ORACLE valid user name, password and connection string and then click OK after entering the picture as shown in Figure 1.10-2.
rn
2) When entered after the screen shown in Figure 1.10-2. You can work. The current version is in the top of the screen enter the command, the bottom of the screen is the system displays the results.
rn
3) We will click the cursor to the top of the screen, enter the SQL commands, such as the type:
select ename, sal from scott.emp;
Enter the completion of command, with the implementation of the cursor icon to click on the left side, Oracle will be the results displayed in the screen below:
rn
Figure 1. 10-2 after a successful login screen
rn
Figure 1.10-3 query command and display the results of the screen
rn
From the SQL Worksheet tool can be seen, the tool provides a very rich functionality. The following gives a brief functional description:
rn
1. File:
l change the database connection: you can connect to another user and database up;
l Open: You can open an existing file;
l will enter the Save As: save the current command to a file;
l will be displayed below the result saved as a file.
2. Editor:
l Cut: The characters in the currently selected shear;
l Copy: Cut the selected characters;
l Paste: The paste buffer characters;
l Select: all the characters on the current screen Select All;
l All Clear: Clear all the contents of the current.
rn
3. Worksheet:
l Execution: execution of the current order;
l run a local script: run an operating system. SQL file (when clicked, prompts the input file name);
l Command history: view the command history;
l at a command: Display on a command.
rn
Tip: If your SQL Worksheet are not shown in Chinese characters (which appears chaotic character), you NT/2000/98 your environment variable settings of Chinese characters the same as with the server just fine. Chinese characters in the WINDOWS NT environment variables to set these steps:
rn
Using regedit to modify the registry of all ORACLE character set items, such as:
1) Click on: Start = "Run =" type regedit;
2) Click "HEY_LOCAL_MACHINE" the left "+" to expand the directory;
3) Click on the "SOFTWARE" the left "+" to expand the directory;
4) Click on the "ORACLE" item;
5) Revise the ORACLE under the tree for all "NLS" variables and props $ in agreement.
rn
On the Chinese environment variable settings, please refer to the "Oracle8i/9I the primary database management."
rn
-----------------------------------------------
rn
1, Oracle startup and shutdown
1, in the stand-alone environment, in order to start or shut down ORACLE system must first switch to the ORACLE user, as follows
su - oracle
rn
a, start the ORACLE system
oracle> svrmgrl
SVRMGR> connect internal
SVRMGR> startup
SVRMGR> quit
rn
b, shut down ORACLE system
oracle> svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown
SVRMGR> quit
rn
Start oracle9i database command:
$ Sqlplus / nolog
rn
SQL * Plus: Release 9.2.0.1.0 - ProdUCtion on Fri Oct 31 13:53:53 2003
rn
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
rn
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup ^ C
rn
SQL> startup
ORACLE instance started.
rn
2, in the two-plane environment, in order to start or shut down ORACLE system must first switch to root user, as follows
su - root
rn
a, start the ORACLE system
hareg-y oracle
rn
b, shut down ORACLE system
hareg-n oracle
rn
What are a few to start Oracle database method
rn
Description:
rn
There are several ways to start:
1, startup nomount
Non-installation start, in this way to launch the next executable: rebuild control file, rebuild the database
rn
Read init.ora file to start the instance, that launched SGA and background processes, which started just init.ora file.
rn
2, startup mount dbname
Installation starts, this approach to launch the next executable:
Database log archiving,
Database media recovery,
The data file online or offline,
To re-locate data files, redo log files.
rn
Implementation of the "nomount", then open the control file, confirm data files and online log file location,
But this time not data files and log files and checks.
rn
3, startup open dbname
The first implementation of the "nomount", then run the "mount", then open the Redo log file, including all databases, including documents,
This way can access data in the database.
rn
4, startup, equivalent to the following three commands
startup nomount
alter database mount
alter database open
rn
5, startup restrict
Constrained in this way can be activated to start the database, but only allowed to have a certain privileged users to access non-privileged user access, the following tips:
ERROR:
ORA-01035: ORACLE only allows users with the RESTRICTED SESSION privileges to use
rn
6, startup force
Forced to start way when you can not close the database, you can use startup force to complete the closure of the database to close the database, then the normal database commands to start
rn
7, startup pfile = parameter file names start with initialization parameter file means the first to read parameter file, and then set the parameter file to start the database in the cases of: startup pfile = E: Oracleadminoradbpfileinit.ora
rn
8, startup EXCLUSIVE
2, the user how to effectively use of ORACLE data dictionary data dictionary is an important part of the database, which arise as a database created with the database and change,
Reflected in sys user at some of the tables and views. Data dictionary name is uppercase English characters.
rn
The data dictionary, there is user information, the user's access information, all data objects information, table constraints, such as statistical analysis of the database view.
We can not manually modify the data dictionary information.
rn
In many cases, the general ORACLE users do not know how to effectively use it.
rn
dictionary all the data dictionary table names and explanations, it has a synonym dict
dict_column all the data dictionary table field names and explanations
rn
If we want to query the data dictionary with the index, you can this with the following SQL statement:
rn
SQL> select * from dictionary where instr (comments, 'index')> 0;
rn
If we want to know user_indexes table details the meaning of each field name, you can use the following clause in SQL statement:
rn
SQL> select column_name, comments from dict_columns where table_name = 'USER_INDEXES';
rn
And so on, you can easily know the details of the name of the data dictionary and explanation do not see the other documentation of ORACLE.
rn
The following are listed by category of some commonly used ORACLE user queries the data dictionary to use.
rn
1, the user
rn
View the current user's default tablespace
rn
SQL> select username, default_tablespace from user_users;
rn
View the current user's role
SQL> select * from user_role_privs;
rn
View the current user's system privileges, and table-level permissions
SQL> select * from user_sys_privs;
SQL> select * from user_tab_privs;
rn
2, Table
rn
See the table of all users
SQL> select * from user_tables;
rn
See the table name contains characters log
SQL> select object_name, object_id from user_objects
where instr (object_name, 'LOG')> 0;
rn
View of a table creation time
SQL> select object_name, created from user_objects where object_name = upper ( '& table_name');
rn
See the size of a table
SQL> select sum (bytes) / (1024 * 1024) as "size (M)" from user_segments
where segment_name = upper ( '& table_name');
rn
View on the ORACLE table in the memory area where
SQL> select table_name, cache from user_tables where instr (cache, 'Y')> 0;
rn
3, index
rn
View the index number and type of
SQL> select index_name, index_type, table_name from user_indexes order by table_name;
rn
View the index fields have been indexed
SQL> select * from user_ind_columns where index_name = upper ( '& index_name');
rn
View the index size of the
SQL> select sum (bytes) / (1024 * 1024) as "size (M)" from user_segments
where segment_name = upper ( '& index_name');
rn
4, serial number
rn
View serial number, last_number is the current value of
SQL> select * from user_sequences;
rn
5, view
rn
View the name of the view
SQL> select view_name from user_views;
rn
View of the select statement to create the view
SQL> set view_name, text_length from user_views;
SQL> set long 2000; Note: You can view text_length value is set according to set long the size of the
SQL> select text from user_views where view_name = upper ( '& view_name');
rn
6, synonyms
rn
See the name of the synonym
SQL> select * from user_synonyms;
rn
7, constraints
rn
View the constraints of a table
SQL> select constraint_name, constraint_type, search_condition, r_constraint_name
from user_constraints where table_name = upper ( '& table_name');
rn
SQL> select c.constraint_name, c.constraint_type, cc.column_name
from user_constraints c, user_cons_columns cc
where c.owner = upper ( '& table_owner') and c.table_name = upper ( '& table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;
rn
8, stored functions and procedures
rn
View the status of functions and procedures
SQL> select object_name, status from user_objects where object_type = 'FUNCTION';
SQL> select object_name, status from user_objects where object_type = 'PROCEDURE';
rn
View the source code for functions and procedures
SQL> select text from all_source where owner = user and name = upper ( '& plsql_name');
rn
3, view database, SQL
1, view the table space name and size of the
rn
select t.tablespace_name, round (sum (bytes / (1024 * 1024)), 0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
rn
2, view tablespace physical file name and size of the
rn
select tablespace_name, file_id, file_name,
round (bytes / (1024 * 1024), 0) total_space
from dba_data_files
order by tablespace_name;
rn
3, view the name and size of rollback segments
rn
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v $ rollstat v
Where r.segment_id = v.usn (+)
order by segment_name;
rn
4, view control files
rn
select name from v $ controlfile;
rn
5, view the log file
rn
select member from v $ logfile;
rn
6, view the table space is being used
rn
select sum (bytes) / (1024 * 1024) as free_space, tablespace_name
from dba_free_space
group by tablespace_name;
rn
SELECT A. TABLESPACE_NAME, A. BYTES TOTAL, B. BYTES USED, C. BYTES FREE,
rn
(B. BYTES * 100) / A. BYTES "% USED", (C. BYTES * 100) / A. BYTES "% FREE"
FROM SYS.SM $ TS_AVAIL A, SYS.SM $ TS_USED B, SYS.SM $ TS_FREE C
WHERE A. TABLESPACE_NAME = B. TABLESPACE_NAME AND A. TABLESPACE_NAME = C. TABLESPACE_NAME;
rn
7, view the database object library
rn
select owner, object_type, status, count (*) count # from all_objects group by owner, object_type, status;
rn
8, view the version of the database
rn
Select version FROM Product_component_version
Where SUBSTR (PRODUCT, 1,6) = 'Oracle';
rn
9, see the database creation date and archiving methods
rn
Select Created, Log_Mode, Log_Mode From V $ Database; 4, ORACLE management of user connections
rn
With the system administrator to see the current database, there are several user connections:
rn
SQL> select username, sid, serial # from v $ session;
rn
If you want to stop a connection with the
rn
SQL> alter system kill session 'sid, serial #';
rn
If this command not work, find its UNIX process number
rn
SQL> select pro.spid from v $ session ses, v $ process pro where ses.sid = 21 and ses.paddr = pro.addr;
rn
Note: 21 is the number of a connection sid
rn
Then use the kill command to kill this process ID.
rn
5, SQL * PLUS using the
a, almost into the SQL * Plus
$ sqlplus username / password
rn
Exit SQL * Plus
SQL> exit
rn
b, In sqlplus under the help information lists all the SQL commands and SQL * Plus commands
SQL> help
Commands are listed in a specific information
SQL> help command name
rn
c, shows the table structure of the command DESCRIBE
SQL> DESC table name
rn
d, SQL * Plus editing commands shown in the SQL buffer commands
SQL> L
rn
First of all you want to modify the SQL command line becomes the current line to be corrected
SQL> n
Modify the contents of the command with the CHANGE
SQL> c / old / new re-verify that the correct
SQL> L
rn
Using the INPUT command in the SQL buffer to add one or more rows
SQL> i
SQL> input
rn
e, call the external system editor
SQL> edit the file name you can use the DEFINE command to set system variable EDITOR to change the type of text editor in the file are defined as follows login.sql row
DEFINE_EDITOR = vi
rn
f, run the command file
SQL> START test
SQL> @ test
rn
Common SQL * Plus Statements
a, the table to create, modify, delete command to create the table the following format:
create table table name (column description list);
rn
As the base table to add a new column command is as follows:
ALTER TABLE table ADD (column description list)
Example: In order to test the table to increase an Age, used to store the age
sql> alter table test
add (Age number (3));
rn
Modify the base table column definition command is as follows:
ALTER TABLE table name
MODIFY (column name data type)
Example: The test table in the Count column width extended to 10 characters
sql> alter atble test
modify (County char (10));
rn
b, will be a table delete statement the following format:
DORP TABLE table name;
Example: Table to delete will also delete the table data and table definitions
sql> drop table test
rn
c, table space, create, delete 6, ORACLE logical backup of the SH file
rn
Full backup of the SH file: eXP_comp.sh
rn
rq = `date +"% m% d "`
rn
su - oracle-c "exp system / manager full = y inctype = complete file = / oracle / export / db_comp $ rq.dmp"
rn
SH cumulative backup file: exp_cumu.sh
rn
rq = `date +"% m% d "`
rn
su - oracle-c "exp system / manager full = y inctype = cumulative file = / oracle / export / db_cumu $ rq.dmp"
rn
SH incremental backup files: exp_incr.sh
rn
rq = `date +"% m% d "`
rn
su - oracle-c "exp system / manager full = y inctype = incremental file = / oracle / export / db_incr $ rq.dmp"
rn
root user crontab file
/ var / spool / cron / crontabs / root to add the following content
rn
0 2 1 * * / oracle / exp_comp.sh
rn
30 2 * * 0-5 / oracle / exp_incr.sh
rn
45 2 * * 6 / oracle / exp_cumu.sh
rn
Of course, this timetable can be changed according to different needs, and this is just one example.
rn
7, ORACLE commonly used in SQL syntax and data objects
rn
1. Data control statements (DML) part of the
rn
1.INSERT (insert record to the data table statement)
rn
INSERT INTO table (field name 1, field name 2, ... ...) VALUES (value 1, the value 2, ... ...);
INSERT INTO table (field name 1, field name 2, ... ...) SELECT (field name 1, field name 2, ... ...) FROM another table name;
rn
String type field values must be enclosed in single quotation marks, for example: 'GOOD DAY'
If the field value contains a single quote inside 'the need for a string conversion, we replace it into two single quotation marks''.
String type field value exceeds the length of the definition of an error, preferably inserted before the length of the checksum.
rn
The date field the field value can be a database system time with the current SYSDATE, accurate to seconds, or use string to date type function TO_DATE ('2001-08-01 ',' YYYY-MM-DD ')
TO_DATE () There are many date formats, you can see ORACLE DOC.
Year - month - day hours: minutes: seconds format YYYY-MM-DD HH24: MI: SS
rn
INSERT maximum operational length of the string is less than equal to 4000 single-byte, if you want to insert a longer string, please consider using CLOB field type,
Way to use ORACLE Lane comes DBMS_LOB package.
rn
If you use the INSERT, when starting from an automatic increase in the serial number, it should first establish a serial number
CREATE SEQUENCE serial number of the name (preferably the table name + serial number tag) INCREMENT BY 1 START WITH 1
MAXVALUE 99999 CYCLE NOCACHE;
One of the biggest values determined by the length of the field, if the definition of automatic increase in the serial number NUMBER (6), max 999999
INSERT statement inserts the value of this field: the name of the serial number. NEXTVAL
rn
2.DELETE (delete data table records statement)
rn
DELETE FROM table WHERE conditions;
rn
Note: does not delete the record and the release of ORACLE data block inside the occupied table space. Only those who have been deleted data blocks marked as unused.
rn
If you really want to delete all the records of a large table, you can use TRUNCATE command, it can block the release of the data table space occupied by
TRUNCATE TABLE table;
This action can not be rollback.
rn
3.UPDATE (modify data table records statement)
rn
UPDATE table SET field name 1 = value 1, field name 2 = value 2, ... ... WHERE condition;
rn
If you modify the value of N is not an assignment or definition, will clear the contents of the original record of NULL, preferably before modifying non-empty calibration;
Over the definition of the length of the value of N would be an error, it is best inserted before the length of the check ..
rn
Note:
A. above SQL statement is added to the tables are row-level locks,
Upon completion, must be added the end of the command COMMIT transaction processing in order to come into force,
Otherwise, change is not necessarily written to the database.
If you want to withdraw these operations, you can use the command ROLLBACK recovery.
rn
B. run the INSERT, DELETE, and UPDATE statements before the best estimates of what the record may be operating range,
It should be limited to small (10000 records) within the framework. Or ORACLE uses a great deal with this thing rollback segment.
Procedures to respond to slow or even loss of response. If the number of records on the hundreds of thousands of these operations, these SQL statements can be sub-sub-sub-complete,
Together with confirmation of things during COMMIT processing. 2. Data definition (DDL) part of the
rn
1.CREATE (to create tables, indexes, views, synonyms, procedures, functions, database links, etc.)
rn
ORACLE common field types are
CHAR fixed-length strings
VARCHAR2 variable-length strings
NUMBER (M, N) number of type M is the median total length, N is the length of the decimal
DATE date type
rn
When we are young to create the table is not empty field on the front of the field may be empty on the back
rn
Can be used to create the table field names in Chinese, but the best of the field names in English
rn
To create the table fields can be added to the default value, for example, DEFAULT SYSDATE
So each insertion and modification, do not have access to procedures for operation of this field action time
rn
Create a table can be added to the field, for example, the constraints do not allow duplicate UNIQUE, keyword PRIMARY KEY
rn
2.ALTER (changing tables, indexes, views, etc.)
rn
To change the name of the table
ALTER TABLE table 1 TO table 2;
rn
In the table behind the increase in a field
ALTER TABLE table name ADD name of the field names described in paragraph;
rn
Modify the definition of the field description table
ALTER TABLE table MODIFY field name field name description;
rn
Field to the table with constraint
ALTER TABLE table name ADD CONSTRAINT constraint name PRIMARY KEY (field name);
rn
ALTER TABLE table name ADD CONSTRAINT constraint name UNIQUE (field names);
rn
Put the table on the database, or remove the memory area
ALTER TABLE table name CACHE;
ALTER TABLE table name NOCACHE;
rn
3.DROP (delete tables, indexes, views, synonyms, procedures, functions, database links, etc.)
rn
Remove the table and all its constraints
DROP TABLE table CASCADE CONSTRAINTS;
rn
4.TRUNCATE (empty table all the records, retaining the table structure)
rn
TRUNCATE table;
rn
3. Query (SELECT) part of the
rn
SELECT field name 1, field name 2, ... ... FROM table 1, [table 2, ... ...] WHERE conditions;
rn
Field names can be brought into the functions such as: COUNT (*), MIN (field names), MAX (field names), AVG (field names), DISTINCT (field names),
TO_CHAR (DATE field names, 'YYYY-MM-DD HH24: MI: SS')
rn
NVL (EXPR1, EXPR2) function is explained:
IF EXPR1 = NULL
RETURN EXPR2
ELSE
RETURN EXPR1
rn
DECODE (AA, V1, R1, V2, R2 ....) function, explained:
IF AA = V1 THEN RETURN R1
IF AA = V2 THEN RETURN R2
.. ... ELSE
RETURN NULL
rn
LPAD (char1, n, char2) function is explained:
Character char1 according to the number of bits n to develop shows, less than the number of bits to replace the string with the left side of the space char2
rn
Field names can be between the arithmetic such as: (a field name field name * 1) / 3
rn
For example, can be nested query: SELECT ... ... FROM
(SELECT ... ... FROM table 1, [table 2, ... ...] WHERE condition) WHERE condition 2;
rn
2 the results of query operations such as the collection can be done: and set UNION (remove duplicate records), and set UNION ALL (do not remove duplicate records), and poor collection MINUS, intersection INTERSECT
rn
Group Inquiries
SELECT field name 1, field name 2, ... ... FROM table 1, [table 2, ... ...] GROUP BY field name 1
[HAVING conditions];
rn
The connection between two or more tables query
rn
SELECT field name 1, field name 2, ... ... FROM table 1, [table 2, ... ...] WHERE
Table 1. Field name = table name 2. Field name [AND ... ...];
rn
SELECT field name 1, field name 2, ... ... FROM table 1, [table 2, ... ...] WHERE
Table 1. Field name = table name 2. Field name (+) [AND ... ...];
rn
With (+) of the field position auto-fill empty value
rn
Query result set sorting operation, the default sort is ascending ASC, descending order is DESC
rn
SELECT field name 1, field name 2, ... ... FROM table 1, [table 2, ... ...]
ORDER BY field name 1, field name 2 DESC;
rn
String comparison method of fuzzy
rn
INSTR (field name, 'string') "0
Field Name LIKE 'string%' [ '% string%']
rn
Each table has a hidden field ROWID, which marks the uniqueness of records.
rn
4. ORACLE where commonly used data objects (SCHEMA)
rn
1. Index (INDEX)
rn
CREATE INDEX index name ON table (field 1, [field 2, ... ...]);
ALTER INDEX index name REBUILD;
rn
The best index of a table should not exceed three (except for special big table), the best single-field index, combined with an analysis of the implementation of the SQL statement,
Can also be a combination of a multi-field index and function-based index
rn
ORACLE8.1.7 string can index the maximum length of 1578 single-byte
ORACLE8.0.6 string can index the maximum length of 758 single-byte
rn
2. The view (VIEW)
rn
CREATE VIEW view name AS SELECT .... FROM ... ..;
ALTER VIEW view name COMPILE;
rn
The view is only one SQL query, which can form complex relationship between the simplicity of.
rn
3. Synonym (SYNONMY)
CREATE SYNONYM synonym name FOR table;
CREATE SYNONYM synonym name FOR table @ database link name;
rn
4. Database link (DATABASE LINK)
CREATE DATABASE LINK database link name CONNECT TO username IDENTIFIED BY password USING 'database connection string';
rn
Database connection string can be NET8 EASY CONFIG or directly modify the TNSNAMES.ORA where defined.
rn
Database Parameters global_name = true to request the database link name with the same remote database name
rn
Database global name can be found using the following command
SELECT * FROM GLOBAL_NAME;
rn
Queries the remote database table
SELECT ... ... FROM table @ database link name;
rn
5. Rights Management (DCL) statements
rn
1.GRANT the system assigns permissions to access a collection of commonly used has the following three:
CONNECT (basic connectivity), RESOURCE (Programming), DBA (database management)
rn
Commonly used data objects have the following five rights:
ALL ON data object names, SELECT ON data object names, UPDATE ON data object names,
DELETE ON data object name, INSERT ON data object name, ALTER ON data object name
rn
GRANT CONNECT, RESOURCE TO username;
GRANT SELECT ON table TO user name;
GRANT SELECT, INSERT, DELETE ON table TO user name one, user name 2;
rn
2.REVOKE recovery Permissions
rn
REVOKE CONNECT, RESOURCE FROM username;
REVOKE SELECT ON table FROM user name;
REVOKE SELECT, INSERT, DELETE ON table FROM user name one, user name 2;
rn
Query the database Error No. 63:
select orgaddr, destaddr from sm_histable0116 where error_code = '63 ';
rn
Query the database user accounts and the largest issued to submit the largest number: select MSISDN, TCOS, OCOS from ms_usertable;
rn
Query the database in the sum of the various error codes:
select error_code, count (*) from sm_histable0513 group by error_code order
by error_code;
rn
Query statements in the database, then a single statistical types of queries.
select sum (Successcount) from tbl_MiddleMt0411 where ServiceType2 = 111
select sum (successcount), servicetype from tbl_middlemt0411 group by servicetype
rn

This article comes from CSDN blog, reproduced please indicate source: http://blog.csdn.net/jack_rao/archive/2009/05/11/4168371.aspx

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

oracle sql outer join predicates the application of a time when the order will affect the outcome of

kods.net — Tue, 26 Jan 2010 17:53:08+0000

oracle sql outer join predicates the application of a time when the order will affect the outcome, there is no relationship between the non-outer join, but the oracle so because, although the understanding that a bit strange, it should also have their reasons.

SELECT COUNT (DISTINCT B. COMPANY_ID)
FROM (SELECT *
FROM ODS.BAS_TAB_EMPLOYEE_FJYD C
WHERE C. END_DATE = TO_DATE ('3000-12-31 ',' YYYY-MM-DD ')
AND C. REG_DATE 'YYYY-MM-DD HH24: MI: SS')
AND (C. EMPLOYEE_STATUS = 1 OR
C. EMPLOYEE_STATUS = 0 AND
C. PAUSE_DATE> =
TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS') OR
NVL (C. PAUSE_DATE, C. UNREG_DATE)> =
TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS'))) C,
ODS.BAS_TAB_COMPANY_FJYD B
WHERE B. COMPANY_ID = C. COMPANY_ID

AND B. END_DATE = TO_DATE ('3000-12-31 ',' YYYY-MM-DD ')
AND B. REG_DATE <
TO_DATE ('2010-01-24 00:00:00 '/ *: v_end_dt * /,' YYYY-MM-DD HH24: MI: SS ')
AND (B. COMPANY_STATUS = 1 OR
B. COMPANY_STATUS = 0 AND
B. PAUSE_DATE> = TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS') OR
NVL (B. PAUSE_DATE, B. UNREG_DATE)> =
TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS'))

4813 --- OK

SELECT COUNT (DISTINCT B. COMPANY_ID)
FROM ODS.BAS_TAB_COMPANY_FJYD B, ODS.BAS_TAB_EMPLOYEE_FJYD C
WHERE C. COMPANY_ID = B. COMPANY_ID

AND B. END_DATE = TO_DATE ('3000-12-31 ',' YYYY-MM-DD ')
AND B. REG_DATE <
TO_DATE ('2010-01-24 00:00:00 '/ *: v_end_dt * /,' YYYY-MM-DD HH24: MI: SS ')
AND (B. COMPANY_STATUS = 1 OR
B. COMPANY_STATUS = 0 AND
B. PAUSE_DATE> = TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS') OR
NVL (B. PAUSE_DATE, B. UNREG_DATE)> =
TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS'))

AND C. END_DATE = TO_DATE ('3000-12-31 ',' YYYY-MM-DD ')
AND C. REG_DATE <
TO_DATE ('2010-01-24 00:00:00 '/ *: v_end_dt * /,' YYYY-MM-DD HH24: MI: SS ')
AND (C. EMPLOYEE_STATUS = 1 OR
C. EMPLOYEE_STATUS = 0 AND
C. PAUSE_DATE> = TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS') OR
NVL (C. PAUSE_DATE, C. UNREG_DATE)> =
TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS'));

- 4813 line.

SELECT COUNT (DISTINCT B. COMPANY_ID)
FROM (SELECT *
FROM ODS.BAS_TAB_EMPLOYEE_FJYD C
WHERE C. END_DATE = TO_DATE ('3000-12-31 ',' YYYY-MM-DD ')
AND C. REG_DATE 'YYYY-MM-DD HH24: MI: SS')
AND (C. EMPLOYEE_STATUS = 1 OR
C. EMPLOYEE_STATUS = 0 AND
C. PAUSE_DATE> =
TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS') OR
NVL (C. PAUSE_DATE, C. UNREG_DATE)> =
TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS'))) C,
ODS.BAS_TAB_COMPANY_FJYD B
WHERE B. COMPANY_ID = C. COMPANY_ID (+)

AND B. END_DATE = TO_DATE ('3000-12-31 ',' YYYY-MM-DD ')
AND B. REG_DATE <
TO_DATE ('2010-01-24 00:00:00 '/ *: v_end_dt * /,' YYYY-MM-DD HH24: MI: SS ')
AND (B. COMPANY_STATUS = 1 OR
B. COMPANY_STATUS = 0 AND
B. PAUSE_DATE> = TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS') OR
NVL (B. PAUSE_DATE, B. UNREG_DATE)> =
TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS'))

- 8441 Line

SELECT COUNT (DISTINCT B. COMPANY_ID)
FROM ODS.BAS_TAB_COMPANY_FJYD B, ODS.BAS_TAB_EMPLOYEE_FJYD C
WHERE C. COMPANY_ID (+) = B. COMPANY_ID

AND B. END_DATE = TO_DATE ('3000-12-31 ',' YYYY-MM-DD ')
AND B. REG_DATE <
TO_DATE ('2010-01-24 00:00:00 '/ *: v_end_dt * /,' YYYY-MM-DD HH24: MI: SS ')
AND (B. COMPANY_STATUS = 1 OR
B. COMPANY_STATUS = 0 AND
B. PAUSE_DATE> = TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS') OR
NVL (B. PAUSE_DATE, B. UNREG_DATE)> =
TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS'))

AND C. END_DATE = TO_DATE ('3000-12-31 ',' YYYY-MM-DD ')
AND C. REG_DATE <
TO_DATE ('2010-01-24 00:00:00 '/ *: v_end_dt * /,' YYYY-MM-DD HH24: MI: SS ')
AND (C. EMPLOYEE_STATUS = 1 OR
C. EMPLOYEE_STATUS = 0 AND
C. PAUSE_DATE> = TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS') OR
NVL (C. PAUSE_DATE, C. UNREG_DATE)> =
TO_DATE ('2010-01-23 00:00:00 '/ *: v_start_dt * /,
'YYYY-MM-DD HH24: MI: SS'));
- 4813 lines, and non-outer join the same result set.

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle SQL exquisite SQL (Reprinted)

kods.net — Tue, 05 Jan 2010 03:30:03+0000

rn
Recently due to the reasons of work, in the painstaking research Oracle. Just found the article to this vote, reproduced over.

Convert row to switch out the ranks of
DROP TABLE t_change_lc;

CREATE TABLE t_change_lc (card_code VARCHAR2 (3), q NUMBER, bal NUMBER);
INSERT INTO t_change_lc
SELECT '001 'card_code, ROWNUM q, trunc (dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4
UNION
SELECT '002 'card_code, ROWNUM q, trunc (dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4;

SELECT * FROM t_change_lc;

SELECT a.card_code,
SUM (decode (aq, 1, a.bal, 0)) q1,
SUM (decode (aq, 2, a.bal, 0)) q2,
SUM (decode (aq, 3, a.bal, 0)) q3,
SUM (decode (aq, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;

- Convert the ranks of the column switch
DROP TABLE t_change_cl;
CREATE TABLE t_change_cl AS
SELECT a.card_code,
SUM (decode (aq, 1, a.bal, 0)) q1,
SUM (decode (aq, 2, a.bal, 0)) q2,
SUM (decode (aq, 3, a.bal, 0)) q3,
SUM (decode (aq, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;

SELECT * FROM t_change_cl;

SELECT t.card_code,
t.rn q,
decode (t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal
FROM (SELECT a. *, b.rn
FROM t_change_cl a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t
ORDER BY 1, 2;

- Convert line to switch out the ranks of the merger
DROP TABLE t_change_lc_comma;
CREATE TABLE t_change_lc_comma AS SELECT card_code, 'quarter_' | | q AS q FROM t_change_lc;

SELECT * FROM t_change_lc_comma;

SELECT t1.card_code, substr (MAX (sys_connect_by_path (t1.q,';')), 2) q
FROM (SELECT a.card_code,
aq,
row_number () over (PARTITION BY a.card_code ORDER BY aq) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;

- Switch to split the ranks of Conversion Columns
DROP TABLE t_change_cl_comma;
CREATE TABLE t_change_cl_comma AS
SELECT t1.card_code, substr (MAX (sys_connect_by_path (t1.q,';')), 2) q
FROM (SELECT a.card_code,
aq,
row_number () over (PARTITION BY a.card_code ORDER BY aq) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;

SELECT * FROM t_change_cl_comma;

SELECT t.card_code,
substr (tq,
instr ( ';' | | tq, ';', 1, rn),
instr (tq | | ';', ';', 1, rn) - instr ( ';' | | tq, ';', 1, rn)) q
FROM (SELECT a.card_code, aq, b.rn
FROM t_change_cl_comma a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) b
WHERE instr ( ';' | | aq, ';', 1, rn)> 0) t
ORDER BY 1, 2;

- To achieve a record under the conditions of multi-table insert
DROP TABLE t_ia_src;
CREATE TABLE t_ia_src AS SELECT 'a' | | ROWNUM c1, 'b' | | ROWNUM c2 FROM dual CONNECT BY ROWNUM <= 5;
DROP TABLE t_ia_dest_1;
CREATE TABLE t_ia_dest_1 (flag VARCHAR2 (10), c VARCHAR2 (10));
DROP TABLE t_ia_dest_2;
CREATE TABLE t_ia_dest_2 (flag VARCHAR2 (10), c VARCHAR2 (10));
DROP TABLE t_ia_dest_3;
CREATE TABLE t_ia_dest_3 (flag VARCHAR2 (10), c VARCHAR2 (10));

SELECT * FROM t_ia_src;
SELECT * FROM t_ia_dest_1;
SELECT * FROM t_ia_dest_2;
SELECT * FROM t_ia_dest_3;

INSERT ALL
WHEN (c1 IN ( 'a1', 'a3')) THEN
INTO t_ia_dest_1 (flag, c) VALUES (flag1, c2)
WHEN (c1 IN ( 'a2', 'a4')) THEN
INTO t_ia_dest_2 (flag, c) VALUES (flag2, c2)
ELSE
INTO t_ia_dest_3 (flag, c) VALUES (flag1 | | flag2, c1 | | c2)
SELECT c1, c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;

- If there is on the update, there is no insert with a statement on the implementation
DROP TABLE t_mg;
CREATE TABLE t_mg (code VARCHAR2 (10), NAME VARCHAR2 (10));

SELECT * FROM t_mg;

MERGE INTO t_mg a
USING (SELECT 'the code' code, 'the name' NAME FROM dual) b
ON (a.code = b.code)
WHEN MATCHED THEN
UPDATE SET a.NAME = b.NAME
WHEN NOT MATCHED THEN
INSERT (code, NAME) VALUES (b.code, b.NAME);

- Extract / delete duplicate records
DROP TABLE t_dup;
CREATE TABLE t_dup AS SELECT 'code_' | | ROWNUM code, dbms_random.string ( 'z', 5) NAME FROM dual CONNECT BY ROWNUM <= 10;
INSERT INTO t_dup SELECT 'code_' | | ROWNUM code, dbms_random.string ( 'z', 5) NAME FROM dual CONNECT BY ROWNUM <= 2;

SELECT * FROM t_dup;

SELECT * FROM t_dup a WHERE a.ROWID <> (SELECT MIN (b.ROWID) FROM t_dup b WHERE a.code = b.code);

SELECT b.code, b.NAME
FROM (SELECT a.code,
a.NAME,
row_number () over (PARTITION BY a.code ORDER BY a.ROWID) rn
FROM t_dup a) b
WHERE b.rn> 1;

- IN / EXISTS different from the application of environmental
- T_orders.customer_id an index
SELECT a. *
FROM t_employees a
WHERE a.employee_id IN
(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);

SELECT a. *
FROM t_employees a
WHERE EXISTS (SELECT 1
FROM t_orders b
WHERE b.customer_id = 12
AND a.employee_id = b.sales_rep_id);

- T_employees.department_id an index
SELECT a. *
FROM t_employees a
WHERE a.department_id = 10
AND EXISTS
(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);

SELECT a. *
FROM t_employees a
WHERE a.department_id = 10
AND a.employee_id IN (SELECT b.sales_rep_id FROM t_orders b);

- FBI
DROP TABLE t_fbi;
CREATE TABLE t_fbi AS
SELECT ROWNUM rn, dbms_random.STRING ( 'z', 10) NAME, SYSDATE + dbms_random.VALUE * 10 dt FROM dual
CONNECT BY ROWNUM <= 10;

CREATE INDEX idx_nonfbi ON t_fbi (dt);

DROP INDEX idx_fbi_1;
CREATE INDEX idx_fbi_1 ON t_fbi (trunc (dt));

SELECT * FROM t_fbi WHERE trunc (dt) = to_date ('2006-09-21 ',' yyyy-mm-dd ');

- Do not recommend the use of
SELECT * FROM t_fbi WHERE to_char (dt, 'yyyy-mm-dd') = '2006-09-21 ';

- LOOP in the COMMIT / ROLLBACK
DROP TABLE t_loop PURGE;
create TABLE t_loop AS SELECT * FROM user_objects WHERE 1 = 2;

SELECT * FROM t_loop;

- Progressive author
DECLARE
BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP
INSERT INTO t_loop VALUES cur;
COMMIT;
END LOOP;
END;

- Simulation of bulk submitted to http://blog.knowsky.com/
DECLARE
v_count NUMBER;
BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP
INSERT INTO t_loop VALUES cur;
v_count: = v_count + 1;
IF v_count> = 100 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;

- The real bulk submission
DECLARE
CURSOR cur IS
SELECT * FROM user_objects;
TYPE rec IS TABLE OF user_objects% ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 100;
- Forall to realize a fixed
FORALL i IN 1 .. recs.COUNT
INSERT INTO t_loop VALUES recs (i);
COMMIT;
EXIT WHEN cur% NOTFOUND;
END LOOP;
CLOSE cur;
END;

- Pessimistic locking / optimistic locking
DROP TABLE t_lock PURGE;
CREATE TABLE t_lock AS SELECT 1 ID FROM dual;

SELECT * FROM t_lock;

- The realization of common logic, hidden bug
DECLARE
v_cnt NUMBER;
BEGIN
- There is a bug concurrency
SELECT MAX (ID) INTO v_cnt FROM t_lock;

- Here for other operation
v_cnt: = v_cnt + 1;
INSERT INTO t_lock (ID) VALUES (v_cnt);
COMMIT;
END;

- High-concurrency environment, security, realization of the logic
DECLARE
v_cnt NUMBER;
BEGIN
- Obtained lock on the specified line
SELECT ID INTO v_cnt FROM t_lock WHERE
- In the case of a lock to continue the operation of the following
SELECT MAX (ID) INTO v_cnt FROM t_lock;

- Here for other operation
v_cnt: = v_cnt + 1;
INSERT INTO t_lock (ID) VALUES (v_cnt);
COMMIT; - submission and release the lock
END;

- Hard parse / soft parse
DROP TABLE t_hard PURGE;
CREATE TABLE t_hard (ID INT);

SELECT * FROM t_hard;

DECLARE
sql_1 VARCHAR2 (200);
BEGIN
- Hard parse
- Java in the same statement is Statement.execute ()
FOR i IN 1 .. 1000 LOOP
sql_1: = 'insert into t_hard (id) values (' | | i | | ')';
EXECUTE IMMEDIATE sql_1;
END LOOP;
COMMIT;

- Soft parse
- java in the same statement is PreparedStatement.execute ()
sql_1: = 'insert into t_hard (id) values (: id)';
FOR i IN 1 .. 1000 LOOP
EXECUTE IMMEDIATE sql_1
USING i;
END LOOP;
COMMIT;
END;

- The correct paging algorithm
SELECT *
FROM (SELECT a. *, ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a
WHERE ROWNUM <= 500)
WHERE rn> 480;

- Paging algorithm (why not this one)
SELECT a. *, ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a
WHERE ROWNUM <= 500 AND ROWNUM> 480;

- Paging algorithm (why not this one)
SELECT b. *
FROM (SELECT a. *, ROWNUM rn
FROM t_employees a
WHERE ROWNUM <= 500
ORDER BY first_name) b
WHERE b.rn> 480;

- OLAP
- Subtotal Total
SELECT CASE
WHEN a.deptno IS NULL THEN
'Total'
WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN
'Subtotal'
ELSE
''| | A.deptno
END deptno,
a.empno,
a.ename,
SUM (a.sal) total_sal
FROM scott.emp a
GROUP BY GROUPING SETS ((a.deptno), (a.deptno, a.empno, a.ename ),());

- Packet Sorting
SELECT a.deptno,
a.empno,
a.ename,
a.sal,
- May be jumping rank
rank () over (PARTITION BY a.deptno ORDER BY a.sal DESC) r1,
- Intensive rank
dense_rank () over (PARTITION BY a.deptno ORDER BY a.sal DESC) r2,
- No group sorting
rank () over (ORDER BY sal DESC) r3
FROM scott.emp a
ORDER BY a.deptno, a.sal DESC;

- The current row of data and before / after n rows of data comparison
SELECT a.empno,
a.ename,
a.sal,
- Above the line
lag (a.sal) over (ORDER BY a.sal DESC) lag_1,
- The following three lines of
lead (a.sal, 3) over (ORDER BY a.sal DESC) lead_3
FROM scott.emp a
ORDER BY a.sal DESC;

rn rn rn rn rn rn

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle SQL exquisite SQL statement to explain

kods.net — Thu, 19 Nov 2009 04:38:31+0000

Good things, we hastened to Favorites Bar ~ ~ ~

- The ranks of conversion lines turn out
DROP TABLE t_change_lc;
CREATE TABLE t_change_lc (card_code VARCHAR2 (3), q NUMBER, bal NUMBER);

INSERT INTO t_change_lc
SELECT '001 'card_code, ROWNUM q, trunc (dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4
UNION
SELECT '002 'card_code, ROWNUM q, trunc (dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4;

SELECT * FROM t_change_lc;

SELECT a.card_code,
SUM (decode (aq, 1, a.bal, 0)) q1,
SUM (decode (aq, 2, a.bal, 0)) q2,
SUM (decode (aq, 3, a.bal, 0)) q3,
SUM (decode (aq, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;

- Convert the ranks of the column switch
DROP TABLE t_change_cl;
CREATE TABLE t_change_cl AS
SELECT a.card_code,
SUM (decode (aq, 1, a.bal, 0)) q1,
SUM (decode (aq, 2, a.bal, 0)) q2,
SUM (decode (aq, 3, a.bal, 0)) q3,
SUM (decode (aq, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;

SELECT * FROM t_change_cl;

SELECT t.card_code,
t.rn q,
decode (t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal
FROM (SELECT a. *, b.rn
FROM t_change_cl a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t
ORDER BY 1, 2;

- Convert line to switch out the ranks of the merger
DROP TABLE t_change_lc_comma;
CREATE TABLE t_change_lc_comma AS SELECT card_code, 'quarter_' | | q AS q FROM t_change_lc;

SELECT * FROM t_change_lc_comma;

SELECT t1.card_code, substr (MAX (sys_connect_by_path (t1.q,';')), 2) q
FROM (SELECT a.card_code,
aq,
row_number () over (PARTITION BY a.card_code ORDER BY aq) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;

- Switch to split the ranks of Conversion Columns
DROP TABLE t_change_cl_comma;
CREATE TABLE t_change_cl_comma AS
SELECT t1.card_code, substr (MAX (sys_connect_by_path (t1.q,';')), 2) q
FROM (SELECT a.card_code,
aq,
row_number () over (PARTITION BY a.card_code ORDER BY aq) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;

SELECT * FROM t_change_cl_comma;

SELECT t.card_code,
substr (tq,
instr ( ';' | | tq, ';', 1, rn),
instr (tq | | ';', ';', 1, rn) - instr ( ';' | | tq, ';', 1, rn)) q
FROM (SELECT a.card_code, aq, b.rn
FROM t_change_cl_comma a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) b
WHERE instr ( ';' | | aq, ';', 1, rn)> 0) t
ORDER BY 1, 2;

- To achieve a record under the conditions of multi-table insert
DROP TABLE t_ia_src;
CREATE TABLE t_ia_src AS SELECT 'a' | | ROWNUM c1, 'b' | | ROWNUM c2 FROM dual CONNECT BY ROWNUM <= 5;
DROP TABLE t_ia_dest_1;
CREATE TABLE t_ia_dest_1 (flag VARCHAR2 (10), c VARCHAR2 (10));
DROP TABLE t_ia_dest_2;
CREATE TABLE t_ia_dest_2 (flag VARCHAR2 (10), c VARCHAR2 (10));
DROP TABLE t_ia_dest_3;
CREATE TABLE t_ia_dest_3 (flag VARCHAR2 (10), c VARCHAR2 (10));

SELECT * FROM t_ia_src;
SELECT * FROM t_ia_dest_1;
SELECT * FROM t_ia_dest_2;
SELECT * FROM t_ia_dest_3;

INSERT ALL
WHEN (c1 IN ( 'a1', 'a3')) THEN
INTO t_ia_dest_1 (flag, c) VALUES (flag1, c2)
WHEN (c1 IN ( 'a2', 'a4')) THEN
INTO t_ia_dest_2 (flag, c) VALUES (flag2, c2)
ELSE
INTO t_ia_dest_3 (flag, c) VALUES (flag1 | | flag2, c1 | | c2)
SELECT c1, c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;

- If there is on the update, there is no insert with a statement on the implementation
DROP TABLE t_mg;
CREATE TABLE t_mg (code VARCHAR2 (10), NAME VARCHAR2 (10));

SELECT * FROM t_mg;

MERGE INTO t_mg a
USING (SELECT 'the code' code, 'the name' NAME FROM dual) b
ON (a.code = b.code)
WHEN MATCHED THEN
UPDATE SET a.NAME = b.NAME
WHEN NOT MATCHED THEN
INSERT (code, NAME) VALUES (b.code, b.NAME);

- Extract / delete duplicate records
DROP TABLE t_dup;
CREATE TABLE t_dup AS SELECT 'code_' | | ROWNUM code, dbms_random.string ( 'z', 5) NAME FROM dual CONNECT BY ROWNUM <= 10;
INSERT INTO t_dup SELECT 'code_' | | ROWNUM code, dbms_random.string ( 'z', 5) NAME FROM dual CONNECT BY ROWNUM <= 2;

SELECT * FROM t_dup;

SELECT * FROM t_dup a WHERE a.ROWID <> (SELECT MIN (b.ROWID) FROM t_dup b WHERE a.code = b.code);

SELECT b.code, b.NAME
FROM (SELECT a.code,
a.NAME,
row_number () over (PARTITION BY a.code ORDER BY a.ROWID) rn
FROM t_dup a) b
WHERE b.rn> 1;

- IN / EXISTS different from the application of environmental
- T_orders.customer_id an index
SELECT a. *
FROM t_employees a
WHERE a.employee_id IN
(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);

SELECT a. *
FROM t_employees a
WHERE EXISTS (SELECT 1
FROM t_orders b
WHERE b.customer_id = 12
AND a.employee_id = b.sales_rep_id);

- T_employees.department_id an index
SELECT a. *
FROM t_employees a
WHERE a.department_id = 10
AND EXISTS
(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);

SELECT a. *
FROM t_employees a
WHERE a.department_id = 10
AND a.employee_id IN (SELECT b.sales_rep_id FROM t_orders b);

- FBI
DROP TABLE t_fbi;
CREATE TABLE t_fbi AS
SELECT ROWNUM rn, dbms_random.STRING ( 'z', 10) NAME, SYSDATE + dbms_random.VALUE * 10 dt FROM dual
CONNECT BY ROWNUM <= 10;

CREATE INDEX idx_nonfbi ON t_fbi (dt);

DROP INDEX idx_fbi_1;
CREATE INDEX idx_fbi_1 ON t_fbi (trunc (dt));

SELECT * FROM t_fbi WHERE trunc (dt) = to_date ('2006-09-21 ',' yyyy-mm-dd ');

- Do not recommend the use of
SELECT * FROM t_fbi WHERE to_char (dt, 'yyyy-mm-dd') = '2006-09-21 ';

- LOOP in the COMMIT / ROLLBACK
DROP TABLE t_loop PURGE;
create TABLE t_loop AS SELECT * FROM user_objects WHERE 1 = 2;

SELECT * FROM t_loop;

- Progressive author
DECLARE
BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP
INSERT INTO t_loop VALUES cur;
COMMIT;
END LOOP;
END;

- Simulation of bulk submitted to
DECLARE
v_count NUMBER;
BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP
INSERT INTO t_loop VALUES cur;
v_count: = v_count + 1;
IF v_count> = 100 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;

- The real bulk submission
DECLARE
CURSOR cur IS
SELECT * FROM user_objects;
TYPE rec IS TABLE OF user_objects% ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 100;
- Forall to realize a fixed
FORALL i IN 1 .. recs.COUNT
INSERT INTO t_loop VALUES recs (i);
COMMIT;
EXIT WHEN cur% NOTFOUND;
END LOOP;
CLOSE cur;
END;

- Pessimistic locking / optimistic locking
DROP TABLE t_lock PURGE;
CREATE TABLE t_lock AS SELECT 1 ID FROM dual;

SELECT * FROM t_lock;

- The realization of common logic, hidden bug
DECLARE
v_cnt NUMBER;
BEGIN
- There is a bug concurrency
SELECT MAX (ID) INTO v_cnt FROM t_lock;

- Here for other operation
v_cnt: = v_cnt + 1;
INSERT INTO t_lock (ID) VALUES (v_cnt);
COMMIT;
END;

- High-concurrency environment, security, realization of the logic
DECLARE
v_cnt NUMBER;
BEGIN
- Obtained lock on the specified line
SELECT ID INTO v_cnt FROM t_lock WHERE
- In the case of a lock to continue the operation of the following
SELECT MAX (ID) INTO v_cnt FROM t_lock;

- Here for other operation
v_cnt: = v_cnt + 1;
INSERT INTO t_lock (ID) VALUES (v_cnt);
COMMIT; - submission and release the lock
END;

- Hard parse / soft parse
DROP TABLE t_hard PURGE;
CREATE TABLE t_hard (ID INT);

SELECT * FROM t_hard;

DECLARE
sql_1 VARCHAR2 (200);
BEGIN
- Hard parse
- Java in the same statement is Statement.execute ()
FOR i IN 1 .. 1000 LOOP
sql_1: = 'insert into t_hard (id) values (' | | i | | ')';
EXECUTE IMMEDIATE sql_1;
END LOOP;
COMMIT;

- Soft parse
- java in the same statement is PreparedStatement.execute ()
sql_1: = 'insert into t_hard (id) values (: id)';
FOR i IN 1 .. 1000 LOOP
EXECUTE IMMEDIATE sql_1
USING i;
END LOOP;
COMMIT;
END;

- The correct paging algorithm
SELECT *
FROM (SELECT a. *, ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a
WHERE ROWNUM <= 500)
WHERE rn> 480;

- Paging algorithm (why not this one)
SELECT a. *, ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a
WHERE ROWNUM <= 500 AND ROWNUM> 480;

- Paging algorithm (why not this one)
SELECT b. *
FROM (SELECT a. *, ROWNUM rn
FROM t_employees a
WHERE ROWNUM <= 500
ORDER BY first_name) b
WHERE b.rn> 480;

- OLAP
- Subtotal Total
SELECT CASE
WHEN a.deptno IS NULL THEN
'Total'
WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN
'Subtotal'
ELSE
''| | A.deptno
END deptno,
a.empno,
a.ename,
SUM (a.sal) total_sal
FROM scott.emp a
GROUP BY GROUPING SETS ((a.deptno), (a.deptno, a.empno, a.ename ),());

- Packet Sorting
SELECT a.deptno,
a.empno,
a.ename,
a.sal,
- May be jumping rank
rank () over (PARTITION BY a.deptno ORDER BY a.sal DESC) r1,
- Intensive rank
dense_rank () over (PARTITION BY a.deptno ORDER BY a.sal DESC) r2,
- No group sorting
rank () over (ORDER BY sal DESC) r3
FROM scott.emp a
ORDER BY a.deptno, a.sal DESC;

- The current row of data and before / after n rows of data comparison
SELECT a.empno,
a.ename,
a.sal,
- Above the line
lag (a.sal) over (ORDER BY a.sal DESC) lag_1,
- The following three lines of
lead (a.sal, 3) over (ORDER BY a.sal DESC) lead_3
FROM scott.emp a
ORDER BY a.sal DESC;

RMAN created physical standby 2009-03-30 22:49:29
ProC will select the data on the two-dimensional array 2009-03-30 11:50:27
SQL optimization (1) - full table scan and index the test 2009-03-28 09:02:39
Oracle locking mechanism (reprint) 2009-03-28 05:20:43

Oracle SQL Loader Data Import

kods.net — Tue, 03 Nov 2009 05:55:02+0000

rn
1, SQL LOADER is the ORACLE data loading tools normally used to operating system files to migrate to ORACLE database. SQL * LOADER is the option of using a large data warehouse loading, because it provides the fastest route (DIRECT, PARALLEL).

rn

rn
It uses the command as follows: In the NT,, SQL LOADER command for SQLLDR, under the UNIX, compared with sqlldr / sqlload.

rn

rn
2, the general usage

rn

sqlldr scott/tiger control=control_file.ctl log=logFile.log direct=true

rn

rn

rn
Other parameters in the type sqlldr command appears:

rn
Usage: SQLLDR keyword = value [, keyword = value ,...]

rn
Valid keywords:

rn
userid - ORACLE username / password
control - Control file name
log - Log file name
bad - Bad file name
data - Data file name
discard - Discard file name
discardmax - Number of discards to allow (all default)
skip - Number of logical records to skip (Default 0)
load - Number of logical records to load (all default)
errors - Number of errors to allow (Default 50)
rows - Number of rows in conventional path bind array or between direct path data saves (Default: Conventional path 64, all direct paths)
bindsize - Size of conventional path bind array in bytes (default 256,000)
silent - Suppress messages during run (header, feedback, errors, discards, partitions)
direct - use direct path (Default FALSE)
parfile - parameter file: name of file that contains parameter specifications
parallel - do parallel load (Default FALSE)
file - File to allocate extents from
skip_unusable_indexes - disallow / allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance - do not maintain indexes, mark affected indexes as unusable (Default FALSE)
readsize - Size of Read buffer (Default 1048576)
external_table - use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED)
columnarrayrows - Number of rows for direct path column array (default 5000)
streamsize - Size of direct path stream buffer in bytes (default 256,000)
multithreading - use multithreading in direct path
resumable - enable or disable resumable for current session (Default FALSE)
resumable_name - text string to help identify resumable statement
resumable_timeout - wait time (in seconds) for RESUMABLE (default 7200)
date_cache - size (in entries) of date conversion cache (Default 1000)

rn
PLEASE NOTE: command-line parameters can be specified by a location or keyword. Examples of the former 'sqlload scott / tiger foo'; an example of the latter situation is' sqlldr control = foo
userid = scott / tiger '. location of the specified parameters of time must be earlier but not later than the parameters specified by the keyword. For example, the
Allow 'sqlldr scott / tiger control = foo logfile = log', but is not allowed 'sqlldr scott / tiger control = foo log', even if the parameter 'log' of the correct location.

rn

rn
3, control document to develop

rn
LOAD DATA
INFILE 'input_File.csv'
INTO TABLE tableName
FIELDS TERMINATED BY ","

rn
(field1, field2, field3, field4)

rn

rn

rn
Explained:

rn
infile refers to the data source file where we omitted the default discardfile result.dsc badfile result.bad
into table tableName default is INSERT, you can APPEND into table tableName as an additional way, or REPLACE

rn
Note:

rn
1, insert, as the default way of loading the beginning of the data requirements table is empty
2, append, append a new record in the table
3, replace, delete the old records, replace the load into a new record
4, truncate, ibid

rn
terminated by ',' refers to a comma-separated
terminated by whitespace at the end separated by a space

rn
fields terminated by X'09 'fields terminated at X'09', is a tab (TAB)

rn
tableName: Data Sheet

rn
(field1, field2, field3, field4): Data Sheet Import Fields

rn

rn
Note: When infile *, the import data in the control file .....

rn
Such as:

rn

rn

rn
LOAD DATA
INFILE *
APPEND INTO TABLE tableName
FIELDS TERMINATED BY "," Optionally enclosed by ' "'
(field1, field2, field3, field4)

rn

rn
BEGINDATA

rn

rn
field1_data, field2_data, field3_data, field4_data

rn
field1_data, field2_data, field3_data, field4_data

rn
field1_data, field2_data, field3_data, field4_data

rn

rn

rn
Note: "*" represents the data file with this file the same name, that is behind the use of BEGINDATA segment to identify the data

rn

rn
4, concurrent operation

rn

rn

sqlldr scott/tiger control=control_file.ctl log=logFile.log direct=true parallel=true

rn

rn

rn
5, derived rules of data

rn

rn

Oracle 没有提供将数据导出到一个文件的工具。但是，我们可以用SQL*Plus的select 及 format 数据来输出到一个文件

    set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
　　spool oradata.txt
　　select col1 || ',' || col2 || ',' || col3
　　from tab1
　　where col2 = 'XYZ';
　　spool off

rn

rn

rn
In addition, you can use use the UTL_FILE PL / SQL packet processing:
rem Remember to update initSID.ora, utl_file_dir = 'c: \ oradata' parameter
declare
fp utl_file.file_type;
begin
fp: = utl_file.fopen ( 'c: \ oradata', 'tab1.txt', 'w');
utl_file.putf (fp, '% s,% s \ n', 'TextField', 55);
utl_file.fclose (fp);
end;

rn

rn
Of course, you can also use third-party tools, such as SQLWays, TOAD for Quest and so on.
Load variable length or a specified length of records such as:
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY ' "'
TRAILING NULLCOLS
(Data1,
data2
)
BEGINDATA
11111, AAAAAAAAAA
22222, "A, B, C, D,"
The following is to import a fixed position (fixed length) data Example:
LOAD DATA
INFILE *
INTO TABLE load_positional_data
(Data1 POSITION (1:5),
data2 POSITION (6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
Skip data rows:
Can be "SKIP n" keyword to specify the number of rows to import the data can be skipped. Such as:
LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 5
(Data1 POSITION (1:5),
data2 POSITION (6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
Importing data to modify data:
When you import the data into the database, you can modify the data. Note that this is only suitable for regular import, is not suitable for direct import method.

rn
rn rn rn

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

oracle sql date comparison

kods.net — Sun, 01 Nov 2009 12:15:02+0000

oracle sql日期比较:
在今天之前:
select * from up_date where update < to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update <= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')

在今天只后:
select * from up_date where update > to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update >= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')

精确时间:
select * from up_date where update = to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')

在某段时间内:
select * from up_date where update between to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update < to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and update > to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
select * from up_date where update <= to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss') and update >= to_date('2007-07-07 00:00:00','yyyy-mm-dd hh24:mi:ss')

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Encyclopedia of common command oracle sqlplus

kods.net — Thu, 18 Jun 2009 14:17:23+0000

show and the set command is used for the maintenance of the two SQL * Plus system variables in order
SQL> show all - View all 68 values of system variables
SQL> show user - shows the current connection users
SQL> show error - error
SQL> set heading off - to prohibit the output column header, default is ON
SQL> set feedback off - the prohibition of the count showed that the last line of feedback, the default value of "six or more of the records, loopback ON"
SQL> set timing on - default is OFF, set the query time-consuming, can be used to estimate the execution time of SQL statements to test the performance
SQL> set sqlprompt "SQL>" - set the default prompt, default value is "SQL>"
SQL> set linesize 1000 - set the screen display line width, default 100
SQL> set autocommit ON - settings are automatically submitted, it will default to OFF
SQL> set pause on - default is OFF, the suspension settings, the screen will show the stop and wait for pressing the ENTER key, and then show the next page
SQL> set arraysize 1 - default is 15
SQL> set long 1000 - the default is 80
Description:
long value defaults to 80, 1000 is set to show more content, because many data dictionary views used in a long data type, such as:
SQL> desc user_views
Names can be any type of null value
------------------------------- -------- ----
VIEW_NAME NOT NULL VARCHAR2 (30)
TEXT_LENGTH NUMBER
TEXT LONG
SQL> define a ='''20000101 12:01:01'' '- the definition of local variables, if you want to use a similar display in a variety of return included in the kind of constant,
- Can be used to define the command set
SQL> select & a from dual;
Original value of 1: select & a from dual
New value 1: select'20000101 12:01:01 'from dual
'2000010112: 01:01
-----------------
20000101 12:01:01
Issues:
1, users need the database user under the table each have the same implementation of a SQL operation, then, again, again, type the SQL statement is a very cumbersome method:
SQL> set heading off - to prohibit the output column headings
SQL> set feedback off - the prohibition of the count showed that the last line of the current user feedback information listed under the definition of all the synonyms can be used to test the actual existence of synonyms
select 'desc' | | tname from tab where tabtype = 'SYNONYM';
Under the current user query the number of records in all tables
select 'select'''|| tname ||''', count (*) from' | | tname ||';' from tab where tabtype = 'TABLE';
To all eligible grant select the table for the public
select 'grant select on' | | table_name | | 'to public;' from user_tables where "conditions";
Under a variety of objects to delete user
select 'drop' | | tabtype | | '' | | tname from tab;
Eligible users to delete
select 'drop user' | | username | | 'cascade;' from all_users where user_id> 25;
Quickly compile all views
---- When the database into the new server after the (database reconstruction), the need to recompile the view again,
---- View of the tablespace to other tablespace of the table will have problems connecting, you can use PL / SQL language features, fast compiler.
SQL> SPOOL ON.SQL
SQL> SELECT'ALTER VIEW '| | TNAME | |' COMPILE; 'FROM TAB;
SQL> SPOOL OFF
ON.SQL can then implement.
SQL> @ ON.SQL
Of course, the mandate and the creation of synonyms can be quickly, such as:
SQL> SELECT 'GRANT SELECT ON' | | TNAME | | 'TO username;' FROM TAB;
SQL> SELECT 'CREATE SYNONYM' | | TNAME | | 'FOR user name.' | | TNAME ||';' FROM TAB;
Command list:
The assumption that the current implementation of an order for: select * from tab;
(a) ppend add text to the current end-of-line buffer a order by tname results: select * from tab order by tname;
(Note: a followed by two spaces)
(c) hange / old / new line in the current text with new text to replace the old c / * / tname results: select tname from tab;
(c) hange / text to delete text from the current line c / tab results: select tname from;
del to delete the current row
del n delete the first n lines
(i) nput text after the current line add a line
(l) ist all line display buffer
(l) ist n display buffer in the first n lines
(l) ist mn display buffer lines m to n
the implementation of the current run of the command buffer
/ Implementation of the current command buffer
r the implementation of the current command buffer
@ File name to run the sql files transferred to memory, such as:
SQL> edit s
If the current directory does not exist s.sql document, the system automatically s.sql documents
In which the importation of "select * from tab;", from the disk.
SQL> @ s
The system will automatically query the current user all tables, views, synonyms.
@ @ The file name in the. Sql file to make a call. Sql file using the
save the buffer's file of papers in order to save, the default file name extension is. sql
Save the file name get transferred to the sql file
start the file name to run the sql files transferred to memory
spool file name after this operation and the implementation of the outcome of a variety of "spool" that is, save to disk file, the default file extension. lst
Displays the current spool of "spool" state
spool off to stop the output example:
SQL> spool a
SQL> spool
Is the spool A. LST
SQL> spool off
SQL> spool
No current spool
exit from the SQL * PLUS
desc table shows the structure
show user displays the current connection users
show error error
show all show all 68 values of system variables
edit to open the default editor, Windows system default notepad.exe, to buffer the last SQL statement afiedt.buf transferred document to edit
edit the file name specified in the current directory. sql file editor to edit transferred
Clear the screen clear screen shows the current second. Oracle sqlplus command to edit the first statement we enter such a command:
SELECT emp_id, emp_name
FROM Employees
Then input the command to add a directive statement, such as in the above statement is running enter:
input WHERE emp_age> 30
Instructions can be as follows:
SELECT emp_id, emp_name
FROM Employees
WHERE emp_age> 30
ln command is used to specify the first n input line statement to operate, such as in the above statement is running enter:
l1 statement is the current row is selected
SELECT emp_id, emp_name
(Statement before the "*" means)
a command is used directly in the line added at the end of the current characters, such as in the above statement is running enter:
a, emp_dept
The implementation of the Directive is to:
SELECT emp_id, emp_name, emp_dept
FROM Employees
WHERE emp_age> 30
c command is used to modify the current statement of the characters, such as in the above statement is running enter:
c / emp_name / emp_age / is the implementation of the directive to:
SELECT emp_id, emp_age, emp_dept
FROM Employees
WHERE emp_age> 30
del n command is used to delete the first n command line, such as in the above statement is running enter:
DEL 3
The implementation of the Directive is to:
SELECT emp_id, emp_age, emp_dept
FROM Employees

char, varchar2 difference 2009-04-06 22:10:03
SQL optimization (5) - commonly used test hints 2009-03-29 08:39:54
Oracle locking mechanism (reprint) 2009-03-28 05:20:43
Oracle10g with cross-platform endian transfer 2009-03-14 22:09:48
Diagnosis and principles of order 2009-02-25 20:27:28

oracle sql statement based on

kods.net — Wed, 20 May 2009 15:41:25+0000

- sql structured query language

- DML - Data Manipulation Language - Data Manipulation Language

query information (SELECT),
add new rows (INSERT),
modify existing rows (UPDATE),
delete existing rows (DELETE),
perform a conditional update or insert operation (MERGE),
see an execution plan of SQL (EXPLAIN PLAN),
and lock a table to restrict access (LOCK TABLE).

- DDL - Data Definition Language - Data Definition Language
create, modify, drop, or rename objects (CREATE, ALTER, DROP, RENAME),
remove all rows from a database object without dropping the structure (TRUNCATE),
manage access privileges (GRANT, REVOKE),
audit database use (AUDIT, NOAUDIT)
and add a description about an object to the dictionary (COMMENT).

- Transaction Control Transaction Control statement
save the changes (COMMIT)
or discard the changes (ROLLBACK) made by DML statements.
Also included in the transaction-control statements are statements to set a point or marker in the transaction for possible rollback (SAVEPOINT)
and to define the properties for the transaction (SET TRANSACTION).
Used to manage the properties of the database.
There isonly one statement in this category (ALTER SYSTEM).

- DCL - Data Control Language - with development of the relationship is not very close, for the distribution of authority and recycling
grant, revoke, data control

- Session Control
control the session properties (ALTER SESSION)
and to enable / disable roles (SET ROLE).

- System Control

-------------------------------------------------- ------
select the usage of

- Every employee all the information
select * from emp
- Everyone's department number, name, salary
select deptno, ename, sal from emp;
- Each person's annual salary
select ename, sal * 12 from emp;
- Calculating the value 2 * 3
select 2 * 3 from emp;

select 2 * 3 from dual;
select * from dual;
- Get current time
select sysdate from dual
- Can give out from the alias, such as the annual salary for each person
select ename, sal * 12 salperyear from emp;
- If an alias in the space, need to use double quotes
select ename, sal * 12 "sal per year" from emp;
- If there is no content, compared with empty
select comm from emp;
- When the air field to participate in the calculation, the result is null
- For example: the year of each person's income, including monthly and year-end awards
select ename, sal * 12 + comm from emp;
- Can be more than string together fight. For example: the salary for each person, the format for the smith-sal-123
select ename | | '-sal-' | | sal from emp;
- If the string in single quotation marks, need to escape a single quote, for example: a string: he's friend
select ename ||''' s sal is' | | sal from emp;

-------------------------------------------------- ------
- distinct usage of words
- What are the departments for
select distinct deptno from emp
- Modified a number of fields can be used. For example: What are the order of a combination of departments and job
select distinct deptno, job from emp

-------------------------------------------------- ------
where the usage of words
- Value types can be judged equivalent. For example: 10 for all employees in this sector
select * from emp where deptno = 20
- String type can be judged equivalent. For example: KING called for the information of the person
select * from emp where ename = 'KING'
- Can not be judged equivalent. For example: for salaries of less than 2000 employees
select * from emp where sal <2000;
- The string can be equivalent to do not to judge, such as: for all ename than 'CBA' of employee information.
select * from emp where ename> 'CBA';
- Instead of 10 for department staff
select * from emp where deptno <> 10;
- In order to pay between 800 and 1500 employees
select * from emp where sal> = 800 and sal <= 1500;
- Can also be written
select * from emp where sal between 800 and 1500
- Can not be written like this
----------------------------- select * from emp where 800 <= sal <= 1500
- where ... in .. the usage. For example: is the salary for 1500 or 800 or 2000 employees
select * from emp where sal = 800 or sal = 1500 or sal = 2000
- The equivalent of writing this
select * from emp where sal in (1500,800,2000,1500,1500,1500,1500);
- Another example is the name for KING, SMITH, AA staff information
select * from emp where ename in ( 'KING', 'SMITH', 'AA')
- Order entry time -81 months after the 20-2 staff information
select * from emp where hiredate <'23-5 on -87 ';

-------------------------------------------------- ------
- and or not the use of
- For salaries, or greater than 1000 in 20 employees in this sector
select * from emp where sal> 1000 and deptno = 20
- Not for salaries instead of 800 or 1500 or 3000 rather than employees
select * from emp where sal not in (800,1500,3000)
- We can also write
select * from emp where sal <> 800 and sal <> 1500 and sal <> 3000

-------------------------------------------------- ------
- like the use of
- Seek to include the names of the three characters ALL employees
select * from emp where ename like '% E%';
- For the name of the second letter is A staff
select * from emp where ename like '_A%';
- The need to escape special characters. For example: the staff in order to contain special characters% of employees
select * from emp where ename like '% \%%' escape '\'

-------------------------------------------------- ------
- null usage
- Not the end of the year award for staff
select * from emp where comm is null
- Award for a financial year-end staff
select * from emp where comm is not null

-------------------------------------------------- ------
- order by the use of
- The names of employees in accordance with the positive sequence with
select * from emp order by ename asc;
- Employees with information in accordance with the flashback
select * from emp order by ename desc;
- It could be a portfolio with a number of fields. For example: employees with positive sequence in accordance with the sector, and in accordance with the order of the names of flashback
select * from emp order by deptno asc, ename desc

-------------------------------------------------- ------
- function usage
- All the names into lower case
select lower (ename) from emp;
- All the names into upper case
select upper (ename) from emp;
- For all were included in 'a' of the employees are not case-sensitive
select * from emp where lower (ename) like '% a%'
- Interception of sub-string, such as for part of Hello
select substr ( 'hello', 2,2) from dual;
select substr (ename, 2,2) from emp;
- For part of Hello and specify the length of

- Ascii code for the corresponding characters
select chr (65) from dual
- For the corresponding ascii character code
select ascii ( 'in') from dual
- Rounding
select round (12.456,2) from dual
select round (12.456, -1) from dual
- The number of places after the decimal point rounding

- Rounded to the nearest decimal point in front of the number of those who

-------------------------------------------------- ------
- important! the date of transfer function
-------------------------------------------------- ------
- Will be converted to the current date 1981-03-12 12:00:00 This form of string
select to_char (sysdate, 'YYYY-MM-DD HH24: MI: SS') from dual;

- 1981-03-12 12:00:00 would convert the date string
select to_date ('1981-03-12 12:00:00 ',' YYYY-MM-DD HH24: MI: SS ') from dual;

- Everyone's salary will be converted into a fixed format string
select to_char (sal, '$ 999,999,999.99') from emp;
- Fixed format string will be converted into numerical
select to_number ( '$ 8,000.00', '$ 999,999,999.99') from dual;

- When calculating the time to participate in null, nvl this function can be used. For example, each year for a total income
select ename, sal * 12 + comm from emp

-------------------------------------------------- ------
- group function group function
- For the sum of all salaries, average, maximum, minimum
select sum (sal), avg (sal), max (sal), min (sal) from emp;
- For the total number of rows
select count (*) from emp;
- The total for the tree line, (you can specify a specific field) but if the field have null values need to be careful with the use of time
select count (comm) from emp;

- Also can filter out duplicate rows after the visit statistics

select count (distinct deptno) from emp
- Can specify which fields in accordance with the group. For example; sub-sectors the highest salary statistics
select deptno, max (sal) from emp where deptno is not null group by deptno
- Can also be grouped in accordance with the number of field statistics, such as: sub-sectors and jobs, the highest salary statistics and the number of rows
select deptno, job, max (sal), count (*) from emp group by deptno, job

-------------------------------------------------- ------
- Important: appear in the select field list, if there is no function in the group, it must appear in the group by clause.
-------------------------------------------------- ------
select ename, deptno, job, max (sal), count (*) from emp group by deptno, job

- The highest salaries for employees of the name
select * from emp where sal = (select max (sal) from emp);
delete from emp where ename = 'TEST2'
update emp set deptno = 10 where deptno = 99
select * from dept
insert into dept (deptno, dname, loc) values ('10 ',' ACCOUNTING ',' NEW YORK ');
- having clause is used
- For the average salary is more than 2000 departments
select deptno, avg (sal) as avg_sal from emp group by deptno
having avg (sal)> 2000
-------------------------------------------------- ------
- Select syntax summary
select
from
where
group by
having
order by
-------------------------------------------------- ------
- The implementation of the order of very important!
- First implementation of the original record of statement where filtration;
- The implementation of the second group by group;
- The implementation of having a third sub-filter;
- And then select the field values in the elected;
- The last sort order by the Executive;

-------------------------------------------------- ------
/ *
Group are in accordance with the department, and the highest salaries, average salary is only 1200 more than salary to be involved in statistical and subgroup results only include the average salary of more than 1,500 department and arranged in accordance with the average salary flashback
* /
select max (sal), avg (sal) from emp
where sal> 1200
group by deptno
having avg (sal)> 1500
order by avg (sal) desc

-------------------------------------------------- ------
/ *
The employees grouped by department,
Seek the highest salary, department number,
Filter out the second letter in the name of the 'A' and
Claim the average salary after> 1500,
Reverse order in accordance with the sector number
* /
select max (sal), deptno from emp where ename not like '_A%' group by deptno
having avg (sal)> 1500
order by deptno desc

/ * Very very important! * /
select ename, deptno from emp;
select deptno, dname from dept;
-------------------------------------------------- ------------------------------------
- Old syntax :--------------------------------------------- -------------------------------
-------------------------------------------------- ------------------------------------
- Equivalent to connect: for employees, as well as the names of the names of department employees at the same time show
select ename, emp.deptno, dname, dept.deptno from emp, dept
where emp.deptno = dept.deptno

select ename, e.deptno, dname, d.deptno from emp e, dept d
where e.deptno = d.deptno

- Non-equivalent connections: for each employee's salary grade
select * from salgrade
select ename, sal, grade, losal, hisal from emp, salgrade
where sal> = losal and sal <= hisal
- Inter-Table 3: jobs for the 'PRESIDENT' employee name, department name and salary grade
select ename, dname, grade from emp, dept, salgrade
where emp.deptno = dept.deptno
and sal> = losal and sal <= hisal
and job = 'PRESIDENT'

- Can also be a table with cross-table connections: for each employee's name, and its parent manager's name

select e1.ename, e2.ename from emp e1, emp e2
where e1.mgr = e2.empno

-------------------------------------------------- ------------------------------------
- New syntax ---------------------------------------------- --------------------------------
- In the SQL1992 syntax rules, statement table filters to connect the conditions and the conditions have been placed on the where clause, when the conditions too much, it is easy to cause confusion,
- SQL1999 to amend the shortcomings of the conditions and data will be connected to separate filters,
-------------------------------------------------- ------------------------------------

- Cross-Connect
- The results of the two tables will have the Cartesian product

select * from emp cross join dept
- To use as the equivalent connection deptno conditions, we can write
select * from emp join dept using (deptno)
select ename, dname from emp join dept using (deptno);
- The equivalent of
select ename, dname from emp join dept on emp.deptno = dept.deptno
- Can also be written in such

- Can also be used to connect non-equivalent
- For each employee's salary grade
select * from emp join salgrade on (sal> = losal and sal <= hisal)

- A number of join, where used in combination
- (For jobs is' PRESIDENT 'employee name, department name and salary grade)

select * from emp join dept on emp.deptno = dept.deptno
join salgrade on (sal> = losal and sal <= hisal)
where job = 'PRESIDENT'

- Outer Join - Remove the table not connected with the need for redundant data
- Not wholly within the connection, there is no right in connection

- Which can also omit the outer, short for the left join, right join, full join
- left inner join can be abbreviated into a inner join can also be the initials into a join, within the meaning of the left.
- update emp set deptno = 20 where ename = 'SMITH';
- commit;
select * from emp;
select * from dept;
delete from dept where deptno = 99;
- Left inside, looking from left to right, matching the records do not show
select ename, emp.deptno from emp join dept on emp.deptno = dept.deptno;
select ename, emp.deptno from emp inner join dept on emp.deptno = dept.deptno;
- There is no such syntax: select ename, emp.deptno from emp left inner join dept on emp.deptno = dept.deptno;
- Left outer join, right from the left to find, match the record does not show his
select ename, dept.deptno from emp left / * outer * / join dept on emp.deptno = dept.deptno;

- Right outer join, right to the left to find the records do not match, it also shows his
select ename, dept.deptno from emp right / * outer * / join dept on emp.deptno = dept.deptno;
- There is no right to connect with: select ename, dept.deptno from emp right inner join dept on emp.deptno = dept.deptno;

- Full outer join
select ename, dept.deptno from emp full / * outer * / join dept on emp.deptno = dept.deptno;

- Left, right outside the distinction between

- When connecting them with outside? For example, leading to you to a list of all students, the way to their classes are also listed, on the need to connect

- Where language used in subquery
-------------------------------------------------- ---------------
- Employees in the name of the highest staff salary
- 1, to derive the maximum salary
- 2, then the maximum salary for employees in the name of the staff
select ename from emp where sal = (select max (sal) from emp)

- What are the salary is the average salary of the employees on
select ename, sal from emp where sal> (select avg (sal) from emp)

-------------------------------------------------- ---------------
- Employees who are managers
- 1, first of all, what inquiries mgr number
- 2, look at what number of personnel in this emerging
select distinct mgr from emp where mgr is not null order by mgr

select ename
from emp
where empno in (select distinct mgr from emp where mgr is not null)
- where in China and not to write orderby
select ename
from emp
where empno in (select distinct mgr from emp where mgr is not null order by mgr)

-------------------------------------------------- ---------------
- Used in the From clause subquery
-------------------------------------------------- ----------------

- The level of average salary
- 1, first of all, each department will be seeking out the average salary
- 2, and then the results as a table, and then the results of this table and table do salgrade connected to salary grades obtained
select deptno, avg (sal) from emp group by deptno
select * from (select deptno, avg (sal) avg_sal from emp group by deptno) t join salgrade
on avg_sal between losal and hisal;

-------------------------------------------------- ---------------
- The highest salary in each department the name of the staff
- 1, first of all, each department will be seeking out the highest salaries
- 2, and then the results as a table, and then the results of emp table and do this connection in order to achieve the highest salaries in each department the name of the staff

select deptno, max (sal) from emp where deptno is not null group by deptno

select ename from emp e join
(select deptno, max (sal) max_sal from emp where deptno is not null group by deptno) t
on sal = max_sal and e.deptno = t.deptno

-------------------------------------------------- ---------------
- What are the salary of the average salary in the sector on
- 1, first of all, each department will be seeking out the average salary
- 2, and then the results as a table, and then the results of emp table and do this connection, as the salaries of those who seek in the department's average salary over
select deptno, avg (sal) avg_sal from emp group by deptno

select * from emp join (select deptno, avg (sal) avg_sal from emp group by deptno) t
on (sal> avg_sal and emp.deptno = t.deptno)
-------------------------------------------------- ---------------
- For sector (all of) the average salary level, forms such as:
- Deptno avg_grade
- 10 3.67
- 20 2.8
- 30 2.5
- 1, first for each person's salary level
- 2, and then grouped according to sector, and the average
select deptno, sal, grade from emp join salgrade on sal between losal and hisal
select deptno, avg (grade) from (select deptno, sal, grade from emp join salgrade on sal between losal and hisal) t group by deptno

-------------------------------------------------- ----------------------------------------
- The use of pseudo-field: rownum ,----------------------
-------------------------------------------------- ----------------------------------------
- Used to identify each record line number, line number starting at 1 for each incremental 1
select rownum, emp .* from emp;
- oracle only under rownum <<=, can not be used =>> = comparison operator,
select rownum, emp .* from emp where rownum <5;
- Rownum and order by when used together, it will first elect a record of compliance with the conditions rownum, and then sort
- (Wrong wording) For example, when we asked for the maximum salary of the top 5 individuals, the most direct way of thinking can be written:
select * from emp where rownum <5 order by sal desc
- (Correct wording) can be written

select * from
(select * from emp order by sal desc) t
where rownum <= 5

-------------------------------------------------- ------
- Not allowed to use group function (ie, MAX ()), for the highest salary values (surface test)
- The first solution:
- 1, first of all salaries in accordance with the reverse order
- 2, and then take the first line of
select * from
(select sal from emp order by sal desc) t
where rownum = 1

- The second solution:
- 1, their first inter-table query, first the results obtained, e1.sal maximum number will not happen
- 2, and then not in
select e2.sal from emp e1, emp e2 where e1.sal> e2.sal
select sal from emp where sal not in (select e2.sal from emp e1, emp e2 where e1.sal> e2.sal)

-------------------------------------------------- ---------------
- The average salary for the highest number of departmental
- The first solution:
- 1, first calculated the average salary of each department,
select deptno, avg (sal) avg_sal from emp group by deptno
- 2, then for each sector the average salary of the highest value,
select max (avg_sal) from (1111111111111111111111111)
- 3, then the results for the first step avg_sal = highest salary records.

select deptno from (111111111111) where avg_sal = (22222222)

select deptno
from (select deptno, avg (sal) avg_sal from emp group by deptno)
where avg_sal =
(select max (avg_sal)
from (select deptno, avg (sal) avg_sal from emp group by deptno))

- Can not consider the case of a tie for first
select deptno from
(select deptno, avg (sal) avg_sal from emp group by deptno order by avg (sal) desc)
where rownum <= 1

- The second solution:
- 1, will be the first step above the second step merger, first for the highest average salary, with max (avg (sal)) approach
- Can not be written in select deptno, max (avg (sal)) from emp group by deptno
select max (avg (sal)) from emp group by deptno
- 2, find the average salary of each department
select deptno, avg (sal) avg_sal from emp group by deptno
- 3, then the results for the second step (that is, the average salary of each department), avg_sal = (first results) records. Avg_sal = highest salary that is the record.
select deptno from (select deptno, avg (sal) avg_sal from emp group by deptno)
where avg_sal = (select max (avg (sal)) from emp group by deptno)

- The third solution:
- 1, first calculated the average salary of each department,
select avg (sal) avg_sal from emp group by deptno
- 2, and the highest average salary, with max (avg (sal)) approach
select max (avg (sal)) from emp group by deptno
- 3, and then having to use words, avg (sal) = the second step the results Note: for the group since the alias function can not be used in having

select deptno from emp group by deptno
having avg (sal) = (select max (avg (sal)) from emp group by deptno)

-------------------------------------------------- ---------------
- The highest average salary for the department the name of the department
- 1, the highest average salary
- 2, be a list of department numbers, paying attention to use group by deptno
- 3, and then having the application clause, having avg (sal) = (first results)
- 4, the highest average salary that the number of departments
- 5, and then get the name of the department

select dname from dept where deptno in

(
select deptno
from (select deptno, avg (sal) avg_sal from emp group by deptno)
where avg_sal =
(select max (avg_sal)
from (select deptno, avg (sal) avg_sal from emp group by deptno))

)

-------------------------------------------------- ---------------
- The average salary for the lowest level of the name of the department of the sector
- The first step: the level of average salary, divided into two small steps, the first step is the average salary for department

select * from
(select deptno, avg (sal) avg_sal from emp group by deptno) t
join salgrade on avg_sal between losal and hisal

- Step two: the lowest level of the value of

select min (grade) from (1111111111111111111111111)

- The third step: the value equivalent to the minimum number of sectors
------------ A mistake, should be grade =
select deptno from (111111111111) where grade = (22222222222222)
- Step four: for the name of
select dname from dept where deptno in (33333333333)

select dname
from dept
where deptno in
(

select deptno
from (select *
from (select deptno, avg (sal) avg_sal
from emp
group by deptno) t
join salgrade on avg_sal between losal and hisal)
where grade =
(select min (grade)
from (select *
from (select deptno, avg (sal) avg_sal
from emp
group by deptno) t
join salgrade on avg_sal between losal and hisal)))

- Views can also be used to solve
- conn sys / bjsxt as sysdba
- grant create table, create view, create sequence to scott
- According to the results of the first step in the establishment of a view

create or replace view v1 as
- Must be a clear definition of out
select deptno, avg_sal, grade from
(select deptno, avg (sal) avg_sal from emp group by deptno) t
join salgrade on avg_sal between losal and hisal

- Check
select * from v1
- Inquiries about

- Into the view

select dname from dept where deptno in
(select deptno from (v1) where grade = (select min (grade) from v1))

-------------------------------------------------- -----------
- Why in the back can not order by?

-------------------------------------------------- -------------
- For department managers the lowest average salary in the name of the department (thinking title)
The first step, and the employee ID Manager
select distinct mgr from emp where mgr is not null
The second step, according to department statistics, and the average salary manager
select deptno, avg (sal) avg_sal from emp where empno in (select distinct mgr from emp where mgr is not null) group by deptno
The third step, and the minimum value
select min (avg (sal)) from emp where empno in (select distinct mgr from emp where mgr is not null) group by deptno
The fourth step, and department managers the lowest average salary in the name of the department
select deptno from (2222222222222) where avg_sal = (333333333333333333333333)

select dname from dept where deptno in (select deptno from (select deptno, avg (sal) avg_sal from emp where empno in (select distinct mgr from emp where mgr is not null) group by deptno) where avg_sal = (select min (avg ( sal)) from emp where empno in (select distinct mgr from emp where mgr is not null) group by deptno))
-------------------------------------------------- --------------------------
- For the highest than that of ordinary employees with high salaries but also the name of the manager
- 1, and the number of all managers
create or replace view v1 as
select distinct mgr from emp where mgr is not null
select * from v1
- 2, the general staff of the highest salaries
select max (sal) from emp where empno not in (select distinct mgr from emp where mgr is not null)
- 3,

select ename from emp where empno in (select * from v1)
and sal> (select max (sal) from emp where empno not in (select distinct mgr from emp where mgr is not null))

- That is:

select ename from emp where empno in (select distinct mgr from emp where mgr is not null)
and sal> (select max (sal) from emp where empno not in (select distinct mgr from emp where mgr is not null))

-------------------------------------------------- ----------------------------
- The highest salary for the first 5 employees
- 1, first look

- 2, take a look at the role of rownum

- 3, not the result we want
select ename, sal from emp where rownum <= 5 order by sal desc
- 4, the first order by, then rownum
select * from
(select ename, sal from emp order by sal desc) t
where rownum <= 5

-------------------------------------------------- ------------------------------
- The highest salary for the first 6 to 10 employees (master key)
- Can not achieve this, oracle can only be used under rownum <<=, can not be used =>> = comparison operator, etc.
- Rownum attention inside and outside the difference between rownum, outside to inside access rownum, must obtain an alias.

select * from
(select ename, sal from emp order by sal desc) t
where rownum> = 5
and rownum <= 10

- So select a layer of re-sets
select * from
(select t. *, rownum r from
(select ename, sal from emp order by sal desc) t
)
where r> = 5
and r <= 10

- There is also a way to sort
select * from
(select * from emp order by sal desc) where rownum <= 10
minus
select * from
(select * from emp order by sal desc) where rownum <= 5
-------------------------------------------------- ------------------
- Practice: the final entry for the five staff
- 1, each time the entry

- 2, take the top 5 lines

-------------------------------------------------- ---------------
- Demand the highest salaries in each department in the top two employees

- 1, each employee's name, department, salary and wages by sector (reverse) with
select ename, deptno, sal from emp order by deptno, sal desc
- 2, sets of one, coupled with a r
select ename, deptno, sal, rownum r from
(select ename, deptno, sal from emp order by deptno, sal desc) t
- 3, the creation of an attempt
create or replace view v1
as
select ename, deptno, sal, rownum r from
(select ename, deptno, sal from emp order by deptno, sal desc) t
- Look
select * from v1

- For each sector, the first line of the highest salaries and the creation of an attempt
create or replace view v2 as
select deptno, min (r) min_r from v1 group by deptno

- Two cross-table view connected to the maximum salary is greater than the number of rows, the maximum number of rows is less than +1, and sector number to match
select ename from v1 join v2
on (v1.deptno = v2.deptno and v1.r> = v2.min_r and v1.r <= v2.min_r +1)

-------------------------------------------------- -----------------------------
- Face the questions: To compare the efficiency of
select * from emp where deptno = 10 and ename like '% A%';
select * from emp where ename like '% A%' and deptno = 10;

-------------------------------------------------- -------
- The use of union, minus

- The use of union, minus the result set can be used to achieve the merger and to remove (can be interpreted as a plus and minus), such as:
select * from emp where deptno = 10
union
select * from emp where deptno = 20;

- The equivalent of
select * from emp where deptno = 10 or deptno = 20

- The following statement
select * from emp where deptno in (10,20)
minus
select * from emp where sal <1500;
- The equivalent of
select * from emp where deptno in (10,20) and sal> = 1500

- Show the salaries for the number of sub -

Such as:
scale total
<800 0
801-1000 2
1001-2000 3
2001-5000 6
> 50,008

select '<800' as scale, count (*) as total from emp where sal <800
union
select '<801-1000' as scale, count (*) as total from emp where sal <= 1000 and sal> = 801

- Or display as a
- Note: the use of between .. and .. the time, including the largest and the minimum value.
800-1000 1001 -2000 2001-5000
2 3 6
select * from
(select count (*) as "800-1000" from emp where sal> = 800 and sal <= 1000),
(select count (*) as "1001-2000" from emp where sal> = 1001 and sal <= 2000),
(select count (*) as "2001-5000" from emp where sal> = 2001 and sal <= 5000)

- Or display as a
DEPTNO 800-2000 2001 -5000
------ ---------- ----------
30 5 1
20 2 3
10 1 2
select t.deptno, "800-2000", "2001-5000" from
(select deptno, count (*) as "800-2000" from emp where sal between 800 and 2000 group by deptno) t
join
(select deptno, count (*) as "2001-5000" from emp where sal between 2001 and 5000 group by deptno) t1
on t.deptno = t1.deptno

RMAN created physical standby 2009-03-30 22:49:29
ProC will select the data on the two-dimensional array 2009-03-30 11:50:27
Oracle locking mechanism (reprint) 2009-03-28 05:20:43
Turning optimization oracle article 2009-03-26 21:39:35
Using SQL TRACE and TKPROF 2009-02-26 21:36:37

ORACLE SQL and SQL * PLUS (1)

kods.net — Wed, 20 May 2009 12:05:34+0000

Preface: a brief introduction of relational database
1970.Dr.EFcodded created a relational database model (similar to the now commonly used two-dimensional form)
Relational database composed of a number of data objects, the relationship between the operation of the management of SQL commands, and data integrity between the constraints.
RDBMS (relational database management system)
ORDBMS (object relational database management system)
ORACLE RDBMS often called ORACLE SERVER
Its core is written in C language.

A simple introduction to ORACLE
ORACLE Corporation is the leading provider of integrated technology products, programs, services, database company
ORACLE database designed to be a large number of stored data, rapid query data, ensure data security and consistency,
Distributed inter-network management and customers - such as server configuration.
ORACLE SQL syntax in line with the ANSI 1986 standard
SQL and databases in order to exchange language
SQL * PLUS ORACLE a tool to run SQL and PL / SQL statement
PL / SQL ORACLE process of programming language

First, choose line
1. A simple SELECT statement
SELECT [DISTINCT] (*, COLUMN [ALIAS], ... ...) FROM table_name;
Field of non-unique set of results
DISTINCT field name 1 [, field names 2]
Between the number of types of field arithmetic operations can be carried out
+ - * /, Such as for example: (1 * Field Name Field Name 2) / 3
Connect the content of the combined field of characters | |
Field Name 1 | | Field Name 2
Field can have an alias
SELECT fieldname 1 [AS] 'explained a field name' FROM table;

2. To deal with NULL
NULL is not defined, not allowed to operate, nothing is
NULL! = 0 NULL! = 'Space'
And any operator NULL return NULL
Undefined variables in ORACLE for NULL values
NVL function can be NULL converted to other types of symbols
NVL (EXPR1, EXPR2) Function explanation: IF EXPR1 = NULL RETURN EXPR2
ELSE RETURN EXPR1
Programming skills: NVL function in a multi-fuzzy query conditions more useful when
nvl ( '+ input_value +','0 ') ='0' or filed_name like '% + input_value +%'
NVL function can return a variety of data types:
Return date NVL (start_date,'2002-02-01 ')
Return string NVL (title, 'no title')
Return the number of NVL (salary, 1000)

3. The use of SQL * PLUS (1)
Log in SQL * PLUS method user names, passwords, database connection string the database connection string can be used to establish net8 easy configuration of the local network service configuration,
Can also manually edit the $ ORACLE_HOME / network / admin / tnsnames.ora file.
In UNIX systems do not use
$ sqlplus username / password @ dbname
So other users can use $ ps command to see the user's password
SQL> desc table; display table structure
SQL> select * from tab; See the table of all users
SQL> set pause on; can result set in a large number of users by "Enter" (carriage return) after the page
SQL> set pagesize 100; settings SQL statement to return a result set number of rows 100, the default value is 14
SQL> set linesize 100; settings SQL statements to return result sets the width of line 100, the default value is 80

3. The use of SQL * PLUS (2) SQL * PLUS command recent editorial in a statement the existence of SQL command ORACLE memory (sql buffer), the SQL * PLUS command but does not keep into account.
SQL * PLUS editor in the command:
A [PPEND] text to the text after the current row
C [HANGE] / old / new / the old symbol to replace the current line into a new symbol, new is empty, delete the old symbol
CL [EAR] BUFF [ER] from the sql buffer to remove all the lines in
DEL n delete sql buffer line in the first n
I [NPUT] text in the text insert sql buffer
L [IST] n show sql buffer line in the first n
n so that the first acts of the current line n
n text article text replaced by n line
0 text inserted in the first row in front of his

3. The use of SQL * PLUS (3) SQL * PLUS command in the document:
SAV [E] filename [REP [LACE] | APP [END]]
The sql buffer kept in the SQL command to the OS under a file filename, the default file name suffix to. Sql.
REP [LACE] replace the filename in the SQL command
APP [END] to SQL in the sql buffer added to the filename after the command
GET filename from the filename in the SQL command to read sql buffer
STA [RT] filename running last saved to a file filename in the SQL command
@ filename to run the last saved to the file filename in the SQL command
EDIT to open the edit window, edit the sql buffer in SQL command
EDIT filename to open the edit window, edit the document filename in the SQL command
SPO [OL] filename [OFF | OUT] the results of the SQL command output to a file under OS end filename OFF the output to a file, OUT the end of the output to a file and print documents

4. SQL * PLUS in the field provides the number of the display format of the display format provides
SQL> column field names format 99999999999;
SQL> column field names format 999,999,999,999;
The provisions of the display width of string
SQL> column name format a number of fields [word_wrapped];
Description: his only show the length of the number of places over the length of fold line, plus word_wrapped, the word line will not be provided for folding the display width of characters long
SQL> set long 200;
The provisions of the display field names
SQL> column field name heading 'field names display content';
SQL> set heading off; query field names do not show the provisions of the alignment field direction
SQL> column field names justify [left | right | center];
Clear the form fields
SQL> column field names clear;

5. SQL * PLUS in the field provides an example of the display format
SQL> column last_name heading 'Employee | Name' format a15;
SQL> column salary justify right format $ 99,999.99;
SQL> column start_date format a10 null 'Not Hired';
Note: If start_date is null, show the string 'Not Hired'

6. To determine questions (T / F)
(1). SQL command are always held in sql buffer. [T]
(2). SQL * PLUS command assit with query data. [T]
SQL * PLUS command SELECT result set to control only the display and control file format. Only SQL commands to access the database.

Second, restrictions on choice of line
1. In accordance with the rules specified in order
SELECT expr FROM table [ORDER BY (column, expr) [ASC | DESC]];
ASC default is ascending order (from small to big)
ORDER BY can also be the location of field names [1] | [2] ASC | DESC;
Very complicated when the field names or field arithmetic expression used to sort of show is very convenient location.

2. With restrictions on choice of WHERE line (1)
Comparison Operators => <> = <=! = <> ^ = NULL comparison with the above comparison can not use ANY SOME ALL operator
SQL Operator BETWEEN ... AND ... IN LIKE IS NULL
NOT BETWEEN ... AND ... NOT IN NOT LIKE IS NOT NULL
Logical Operators AND OR NOT

3. With restrictions on choice of WHERE line (2)
Comparison of the order (in brackets can be used to change their order)
(1). = <>> = <= In like is null between
(2). And
(3). Or
Note: char and varchar2 different comparison rules:
char string comparison will ignore the spaces behind. varchar2 string will be taken into account the space behind

4. LIKE operation
% Zero to any number of characters _ a character such as: field names like 'M%' field names like '% m%' field names like 'job_'
If you are looking for characters with an underscore, it is necessary to add a backslash For example: field names like '% X / _Y%' escape '/'

5. Comparison of date fields, for example:
Date fields between to_date ('2001-12-12 ',' YYYY-MM-DD ') and to_date ('2002-02-01', 'YYYY-MM-DD')
Date field> to_date ('2001-12-12 ',' YYYY-MM-DD ') and the date field <= to_date ('2002-02-01', 'YYYY-MM-DD');

6. Can not be used for comparison of the index operator
IS NULL
IS NOT NULL
LIKE '% m%'

Third, one-way function
1. The number of function
POWER absolute involution ABS check for LN 10 base exponentiation
SQRT square root of n times EXP e involution LOG (m, n) m for the base n exponentiation arithmetic functions: ACOS ATAN ATAN2 COS COSH SIGN SIN SINH TAN TANH
CEIL take an integer greater than or equal to
FLOOR integer less than or equal to check
MOD take more than a few
ROUND (n, m) according to the median m value check if rounding round (date): at 12 after the date will be tomorrow. Round (sysdate, 'Y') is the first day of the year
TRUNC (n, m) by m in front of the median value check if trunc (date), there is removed from the province of the time

2. Characters function
CHR by the database character set to return by the number of characters
CONCAT (c1, c2) of the two characters c1, c2 as a combination of characters, and | | the same
REPLACE (c, s, r) the character c in s character appears to replace into r, the return of new characters
SUBSTR (c, m, n) m> 0, m characters c from the start in front of n-bit characters check, m is equal to 0 and 1, the
small m and 0, m characters c Department from behind and started to admit n-bit characters
TRANSLATE (c, f1, t1) character c in accordance with the rules of f1 to t1 into a new string
The first letter capitalized INITCAP characters, other characters lowercase
All lowercase characters LOWER
All uppercase characters UPPER
LTRIM (c1, c2) characters removed c1 characters left c2
RTRIM (c1, c2)
TRIM (c1, c2) characters removed c1 about the characters on both sides of c2
LPAD (c1, n, c2) in accordance with the development of characters c1 show n less than the median of the median c2 string with the replacement of the left side of the space
RPAD (c1, n, c2)

3. Date function
ADD_MONTHS (d, n) the date on the value of n increases
LAST_DAY (d) the last day of the month to return date
MONTHS_BETWEEN (d1, d2) between the two dates the value of the month, d1 NEXT_DAY (d) the date the return value of the date of the next day
SYSDATE The current system time
DUAL is the SYS user of an empty table, it is only one dummy field
4. Transfer function (1)
TO_CHAR (date, 'date format')
TO_CHAR (number) used to display or report format aligned
TO_DATE (char, 'date format')
Long field TO_LOB to lob into the field
TO_NUMBER (char) used to calculate or compare the size of

4. Transfer function (2)
to_date date in display format
YYYY-year YEAR YYY YY Y
Q quarter
MM on MONTH MON
W week (week of month) WW, IW (week of year)
(Note: weeks are based on ISO standards, from January 1 after the number of weeks to seven days to one week, may not necessarily be from Monday to Sunday)
DD on DAY DY
HH12 HH-hour HH24
MI-minute
SS seconds if you want a fixed date display format parameter can be in the database in the new document to write his initorasid.ora parameter NLS_DATE_FORMAT = yyyy-mm-dd hh24: mi: ss in the NT or UNIX environment variable settings in the registry NLS_DATE_FORMAT = yyyy-mm-dd hh24: mi: ss

4. Transfer function (3)
If you want a fixed date display format can be used alter session command to change
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss';
Its role in the following order:
initialization parameter
Environment variable
ALTER SESSION command

4. Transfer function (4)
to_char (number) in the number display format
9 the number of spaces
0 the number of front fill 0 to_char (-1200,'00000 .00 ')
. Decimal point position
, Marking the location of the comma format used in the figures of the left
L according to the database character set plus the currency symbol to_char (-1200, 'L9999.99')
B shows the number of 0 for the space, the figures used in the right format

MI negative tags right to_char (-1200,'9999 .99 MI ')
The negative PR enclose to_char (-1200,'9999 .99 PR ')
EEEE index expressed by to_char (-1200,'9999 .99 EEEE ')

5. Enter the characters, the return of the number of function
instr (c1, c2) characters appear in the c2 position c1, there is no return to 0, commonly used in the fuzzy query
length? by the database character set, calculate the length of characters c, with the database character set, and a length of 1 character

6. Logical comparison function NVL (EXPR1, EXPR2) Function explanation: IF EXPR1 = NULL RETURN EXPR2
ELSE RETURN EXPR1
DECODE (AA, V1, R1, V2, R2 ....) interpretation function: IF AA = V1 THEN RETURN R1
IF AA = V2 THEN RETURN R2
.. ...
ELSE
RETURN NULL
For example: decode (id, 1, 'dept sale', 2, 'dept tech'

////////////////////////////////////////////////// //////

Fourth, select the data from multiple table records
1. The connection between data tables connected simple syntax:
SELECT fields of 1, Field Name 2, ... ... FROM table 1, [table 2, ... ...]
WHERE table 1. Field name = table name 2. Fieldname [AND ... ...];
SELECT fields of 1, Field Name 2, ... ... FROM table 1, [table 2, ... ...]
WHERE table 1. Field name = table name 2. Field Name (+) [AND ... ...];
There are (+) of the field-position auto-fill empty value

Connect with the classification:
Equivalent to the connection =
The connection does not mean that! = BETWEEN ... AND ... IN attention with IN and OR can not be connected with a field outside of (+), does not meet the conditions for space-fill value from a table to connect with their own connections with their own example to find duplicate records

2. Datasheets example to delete the link between the exterior field names table_name duplicate records email:
SQL> delete from table_name t1
where t1.rowid>
(select min (rowid) from table_name t2
where t1.email = t2.email
group by email
having count (email)> 1);

Mobile phone users to find the service area:
SQL> select a.handphoneno, nvl (c.name, 'null'), a.totalscore
from topscore a, chargeoperator cc, chargeoperatorinfo c
where substr (a.handphoneno, 1,7) = cc.hpnohead (+)
and cc.chargetype = c.chargetype (+)
order by a.totalscore desc;

3. Datasheets skills to connect the link between the N tables, the need for N-1 connections table are connected to operate the best characters to build a single alias, field names with the single character before the alias
BETWEEN .. AND .. than> = AND <= is better to connect the field of operation should be the best index of the field operation to connect the best type by an integer number of things connected, can not be used OR or IN comparison operator

4. How to analyze and implement multi-table SQL statement to connect write SQL statements to know that its analysis of implementation of the plan.
Sys users run @ / ORACLE_HOME / sqlplus / admin / plustrce.sql
Plustrace have a role
Sys users given this role under the general user SQL> grant plustrace to &username;
Run under the general user @ / ORACLE_HOME / rdbms / admin / utlxplan.sql
Have plan_table
SQL> set time on; Description: Open the time display
SQL> set autotrace on; Description: Open the automatic analysis of statistics, SQL statement and display the results
SQL> set autotrace traceonly; Description: Open the automatic analysis of statistics, do not show the results of SQL statements to run the next test you run SQL statements, see the results of its analysis.
In general, the SQL statement that we should avoid large table full table scan.
SQL> set autotrace off; Description: turn off the automatic analysis of statistics

Fifth, the collection function and group by together with the regular use of
1. Collection function list
AVG (DISTINCT | ALL | N) averaged
COUNT (DISTINCT | ALL | N | expr | *) statistics on the number of
MAX (DISTINCT | ALL | N) from the maximum
MIN (DISTINCT | ALL | N) from the minimum
SUM (DISTINCT | ALL | N) from the total value of
STDDEV (DISTINCT | ALL | N) deviation from the value, if the group have the same choice of content, the result is 0
VARIANCE (DISTINCT | ALL | N) from the square deviation

2. The use of a collection of grammatical function
SELECT column, group_function FROM table
WHERE condition GROUP BY group_by_expression
HAVING group_condition ORDER BY column;

3. The use of count Notes
SELECT COUNT (*) FROM table;
SELECT COUNT (constant) FROM table;

Are recorded in the number of tables, if there is no better than some of the latter PK
SELECT COUNT (all field names) FROM table;
SELECT COUNT (fieldname) FROM table;

Not the field statistics for the number of NULL
SUM, AVG will be ignored when the field is NULL

4. With the group by time constraints
SELECT field names can not be, to be included in the field in the GROUP BY
ORDER BY after the GROUP BY can not be used when the alias restrictions on the location of symbols and display the results of GROUP BY, and HAVING conditions

5. Examples of
SQL> select title, sum (salary) payroll from s_emp
where title like 'VP%' group by title
having sum (salary)> 5000 order by sum (salary) desc;

Exterior field to identify a number of duplicate records, and display
SQL> select (duplicate field names) from table_name
group by (list out fields) having count (*)> 1;

6. To determine questions (T / F)
(1) Group functions include nulls in calculations [F]
(2) Using the having clause to exclude rows from a group calculation [F]
Explained:
Group function are neglected NULL values NULL If you want to calculate value, using NVL function
Where in the Group By statement before the result set Having excluded in the Group By statement after the result set to exclude

7. SQL * PLUS can be used in other command:

Ctrl ^ C to terminate the SQL statement is running
remark /*...*/ - Note symbol
HOST executable command under the operating system can use some unix!

BREAK ON column_name SKIP n [ON column_name SKIP n]
According to the name field separated by column_name show more clearly, SKIP n Department in the blank lines to separate the number of n
BREAK ON ROW SKIP n between each line is put a blank line n

COMPUTE collection OF field operator 1 ON field 2 field 2 on the field by a collection of operations carried out
COMPUTE back to work with a collection of operators:
SUM MINIMUM MAXIMUM AVG STD VARIANCE COUNT NUMBER

8. In SQL * PLUS can be used in other commands, for example: (scott users)

BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF SAL ON REPORT behind in the count all the result sets together
select ename, sal from emp where job = 'SALESMAN';

COMPUTE AVG LABEL avg OF SAL ON REPORT in all the result set back the average balance
/ Re-implementation of the last sql statement

break on DEPTNO skip 2 on JOB skip 1 fields in the result set back BREAK total count
COMPUTE SUM OF SAL ON DEPTNO
SELECT DEPTNO, JOB, ENAME, SAL FROM EMP ORDER BY DEPTNO, JOB;

SQL> CLEAR BREAKS; clear conditions set BREAK
SQL> CLEAR COMPUTES; clear conditions set COMPUTE

VI subquery
1. For example, can be nested query: SELECT ... ... FROM (SELECT ... ... FROM table 1, [table 2, ... ...] WHERE conditions) WHERE condition 2;

2. Subquery can be used where?
When the query is uncertain conditions
DML (insert, update, delete) statement can also be used in subquery
HAVING can also be used in subquery

3. The results of two query set operation can be done such as:
And set UNION (removing duplicate records)
And set UNION ALL (do not remove duplicate records)
Difference Sets MINUS,
Intersection INTERSECT

4. Subquery matters to the attention of the implementation of the SQL statement inside the brackets, the outside layers to the implementation of an internal inquiry only if the inner layer of the result set to return to a number, can not be used => <> = <= comparison site to use, such as IN .

5. Subquery examples of (1)
SQL> select title, avg (salary) from s_emp
group by title Having avg (salary) =
(select min (avg (salary)) from s_emp
group by title);
Find the minimum average wage of job titles and wages

5. Subquery examples (2)
Subquery can query the parent table in the SQL statement that is correct:
SQL> select cty_name from city where st_code in
(select st_code from state where st_name = 'TENNESSEE' and
city.cnt_code = state.cnt_code);
Note: For inquiries, call the parent subquery only once.

6. Sets out the results of 8-10's SQL statement
ORACLE to deal with each set of results indicate that there is only one field ROWNUM the logic of its position,
And can only be used ROWNUM <100, can not be used ROWNUM> 80.
The following is an analysis of two ORACLE better results 80-100 sets the SQL statement (ID is the only keyword field names):
Written statement:
SQL> select * from (
(Select rownum as numrow, c. * from (
select [field_name ,...] from table_name where condition 1 order by conditions 2) c)
where numrow> 80 and numrow <= 100)
order by the conditions of 3;

Seven, in the implementation of SQL statements to bind variables when
1. To receive and the definition of variables SQL * PLUS command
ACCEPT
DEFINE UNDEFINE
&

2. Bind variable SQL statement examples (1)
SQL> select id, last_name, salary from s_emp where dept_id = & department_number;
Enter value for department_number: 10
old 1: select id, last_name, salary from s_emp where dept_id = & department_number;
new 1: select id, last_name, salary from s_emp where dept_id = 10
SQL> SET VERIFY OFF | ON; can close and open the confirmation prompt old 1 and new 1's shows.

3. Bind variable SQL statement examples (2)
SQL> select id, last_name, salary
from s_emp
where title = '& job_title';
Enter value for job_title: Stock Clerk

SQL> select id, last_name, salary
from s_emp
where hiredate> to_date ( '& start_hire_date', 'YYYY-MM-DD');
Enter value for start_hire_date: 2001-01-01

To bind the type of string and date variables, the variables to be added outside the single quotation marks can also bind variables to query the different fields of input variables when the value will not increase; and other symbols

4. ACCEPT syntax and examples
SQL> ACCEPT variable [datatype] [FORMAT] [PROMPT text] [HIDE]
Note: variable variable name refers to the type of datatype refers to variables such as number, char variables, such as display format format means prompt text can be customized pop-up prompt hidden text hide the contents of the user's input ACCEPT examples of the use of symbols:
ACCEPT p_dname PROMPT 'Provide the department name:'
ACCEPT p_salary NUMBER PROMPT 'Salary amount:'
ACCEPT pswd CHAR PROMPT 'Password:' HIDE
ACCEPT low_date date format 'YYYY-MM-DD' PROMPT "Enter the low date range ( 'YYYY-MM-DD'):"

4. DEFINE syntax and examples
SQL> DEFINE variable = value
Note: variable variable name refers to the variable value means the value of the variable definition of a good good value, the implementation of the SQL statements to bind variables when the variables are no longer prompted to enter examples of the use of DEFINE:
SQL> DEFINE dname = sales
SQL> DEFINE dname
DEFINE dname = "sales" (CHAR)
SQL> select name from dept where lower (name) = '& dname';
NAME
-------------------------
sales
sales
SQL> UNDEFINE dname
SQL> DEFINE dname
Symbol dname is UNDEFINED

5. SQL * PLUS in passing parameters to the preservation of good *. sql file
SQL> @ / path name / filename argument to name 1 [, parameter name 2, ....]
SQL> start / path name / filename argument to name 1 [, parameter name 2, ....]

Note:
Can only obtain a maximum of 9 & variable, the variable name can only be from the & 1, & 2 to & 9
Variable name will not increase after the end of special symbols in to SQL * PLUS Lane & symbols stored in the ORACLE database, it is necessary to amend the sql * plus environment variables define
SQL> set define off;

8, an overview of the data model and database design
1. The stage of system development:
Strategy and Analysis
Design
Build and Document
Transition
Production

2. Data Model
Model of system in client's mind
Entity model of client's model
Table model of entity model
Tables on disk

3. Entity Relationship Model (ERM) concept
ERM (entity relationship modeling)
Entities have the objective of specific information and events such as: customers, orders and other attributes to describe the attributes of entities such as: name, phone number, such as the relationship between the relationship between two entities such as: order and product model entity-relationship diagrams, etc. in the agreement
Dashed line (dotted line) optional parameters "may be"
Solid line (solid line) will be selected parameters "must be"
Crow's foot (multi-line) the level of the parameters "one or more"
Single line (one-way) the extent of the parameters "one and only one"

4. Entity relationship model examples of each order must have one or a few customers each customer may be one or more orders of the applicant

5. The relationship between the type of entity
1:1 one-to-one example: the taxi and drivers
M: 1 many-to-one for example: passenger and aircraft
1: M one-to-many example: staff and skills

6. Correction entities of the principle of the relationship between a single attribute value, there will be no duplication of attributes must be dependent on entities, should be marked not only the sole property of non-dependent on another non-unique attributes
7. The definition of the structure of Notes to reduce data redundancy to reduce the problem of integrity constraints have omitted to confirm the entities, relations and attributes

8. The requirements of integrity constraints
Primary key primary key the only non-NULL
Foreign key foreign key depends on a Primary key, may be NULL
Column field names in line with the definition of the type and length of
Constraint constraint bound user-defined conditions, it is necessary to meet the requirements of workflow
For example: a sales force which can not exceed the percentage of basic salary
Candidate primary key Candidate key field names can be composed of a number of candidates for the main keyword, its portfolio is the only non-NULL and the

9. The relationship between entities is mapped to the object-relational database approach to a simple entity is mapped to the database table mapped to the attribute table database fields, marked with the type and the Notes to the only tag is mapped to the database only a key word to the relationship between entities is mapped to the database of the foreign key

Other considerations:
The design of the index, so that the establishment of the view query faster, so that the presentation of information have different surface, the SQL statements to reduce the complexity of storage space allocation plans to redefine the conditions of integrity constraints

10. Entity relationship diagram in the meaning of symbols
PK field is the only keyword
FK a foreign key field
FK1, FK2 a table with two different foreign key
FK1, FK1 two fields together to form a foreign key
NN non-null fields
The only field U
U1, U1 to form two fields a unique field

RMAN created physical standby 2009-03-30 22:49:29
ProC will select the data on the two-dimensional array 2009-03-30 11:50:27
Oracle locking mechanism (reprint) 2009-03-28 05:20:43
Turning optimization oracle article 2009-03-26 21:39:35

oracle sql query principle

kods.net — Wed, 20 May 2009 03:46:59+0000

A step in the implementation of sql statement
1) Syntax analysis syntax statement with the norms, measured statement of the meaning of the expression.
2) semantic analysis, to check all statements relating to the existence of database objects, and users have the appropriate permissions.
3) view of conversion, will involve the view query is converted to the corresponding base form of the query.
4) The expression of conversion, complex SQL expression is converted to a simpler expression equivalent connection.
5) Select Optimizer, Optimizer different in general have a different "implementation plan"
6) Select connection, ORACLE there are three connections on the multi-table connection ORACLE way to choose the appropriate connection.
7) Select the order to connect to multi-table select which ORACLE connection to connect to the table, select the two tables in which the source table as a data table.
8) Select the data search path, select the appropriate conditions for the basis of the above data search path, in the case of selection or use of the entire search index table or in other ways.
9) run "implementation plan"

Second, the principle of shared oracle
ORACLE will be implemented over the SQL statements stored in the shared memory pool (shared buffer pool), you can by all users to share the database when you implement a SQL statement (sometimes called a cursor), if it is before carried out exactly the same statement, ORACLE has been able to quickly resolve the statement, as well as the implementation of the best path. This feature greatly enhance the performance of the SQL and save the use of memory to improve query efficiency statement oracle approach
.1: .. Where column in (select * from ... where ...);
2: ... where exists (select 'X' from ... where ...);
The second format to a format than the first high efficiency. Oracle can be in almost all of the IN operator subquery rewritten to use the EXISTS subquery use EXIST, Oracle will first check the system operator, and then run until it subquery to find the first match, which saves time
Oracle system in the implementation of IN subquery, the first implementation of sub-queries, and results in a list stored in a temporary increase in the index table

Avoid the use of words to avoid having to use HAVING clause, HAVING retrieved only after all records should filter the result set. This sort to deal with the need for a total of such operations. If the WHERE clause can limit the number of records that can be reduced spending in this area

SQL optimization (1) - full table scan and index the test 2009-03-28 09:02:39
Oracle locking mechanism (reprint) 2009-03-28 05:20:43
Oracle10g with cross-platform endian transfer 2009-03-14 22:09:48
Diagnosis and principles of order 2009-02-25 20:27:28

Oracle SQL optimization principles (pick)

kods.net — Wed, 20 May 2009 02:43:26+0000

When people in the use of SQL in a misunderstanding sometimes, that is too much focus on the result SQL is correct, the neglect of the realization of the different methods that may exist between the differences in performance, in particular the statements of Web more complex system used Select the command of the complex or large database environment (such as online transaction processing OLTP) has been particularly evident after the application of the relevant SQL analysis and related data collection, we find that bad from the SQL Command is often inappropriate index design, the connection is not fully optimized conditions and not where clause. in their proper optimization, its speed has improved! The following is a summary and excerpts of some optimization principles.

When people in the use of SQL in a misunderstanding sometimes, that is too much focus on the result SQL is correct, the neglect of the realization of the different methods that may exist between the differences in performance, in particular the statements of Web more complex system used Select the command of the complex or large database environment (such as online transaction processing OLTP) has been particularly evident after the application of the relevant SQL analysis and related data collection, we find that bad from the SQL Command is often inappropriate index design, the connection is not fully optimized conditions and not where clause. in their proper optimization, its speed has improved! The following is a summary and excerpts of some optimization principles:

A. Index unreasonable design

Example: Table 620,000 recorded in the record line, take a look at the index in different circumstances, following the operation of SQL:

1. In the field has a date on a non-clustered index

Select count (*) from record where date>'19991201 'and date <'19991214' and amount> 2000 (25 seconds)

Select date, sum (amount) from record group by date (55 seconds) Select count (*) from record where date>'19990901 'and place in (' BJ ',' SH ') (27 seconds)

Analysis: Record on a large number of repeat value, in a non-clustered index, the data is physically stored in data random page, in the scope of search, it is important to the implementation of a table scan to find all of the line.

2. In the date field to build a clustered index on

Select count (*) from record where date>

'19991201 'And date <'19991214' and amount> 2000 (14 seconds) Select date, sum (amount) from record group by date (28 seconds) Select count (*) from record where date>'19990901 'and place in ( 'BJ', 'SH') (14 seconds)

Analysis: In the clustered index, the data in the physical sequence in the data page, together with repeat value, which in the scope of search can be found at the end of this range from points, and only in the range of scan data page to avoid a large-scale scanning, to improve query speed.

3. In the place, date, amount of the combination of the index

Select count (*) from record where date>'19991201 'and date <'19991214' and amount> 2000 (26 seconds) select date, sum (amount) from record group by date (27 seconds) select count (*) from record where date>'19990901 'and place in (' BJ, 'SH') (<1 sec)

Analysis: This is a reasonable combination of the index, because it is the pre-out place, the first and second SQL does not refer to place, and therefore did not make use of the index; third SQL used place, and all listed references are included in the index portfolio, the formation of the index coverage, its speed is very fast

4. In the date, place, amount on a combination of the index

select count (*) from record where date>

'19991201 'And date <'19991214' and amount> 2000 (<1 seconds) select date, sum (amount) from record group by date (11 seconds) select count (*) from record where date>'19990901 'and place in ( 'BJ', 'SH') (<1 sec)

Analysis: This is a reasonable combination of the index. Date it as pre-out so that everyone can use SQL indexes, and in the first and the third formed SQL index coverage, and thus achieve the optimal performance.

5. Summary:

Default index to establish a non-clustered index, but sometimes it is not the best; rational design of the index based on a variety of query analysis and forecasting. In general:

①. A large number of repeat value, and often the scope of inquiry (between,>, <,>=,< =) and order by, group by the occurrence of the series may wish to consider the establishment of the cluster index;

②. Often at the same time access to multi-column, and each column contains duplicate values could be considered the establishment of the index portfolio;

③. Portfolio index should be as far as possible the formation of the key index query coverage, the pre-column must be listed the most frequently used.

B. Connect inadequate conditions:

Example: there are 7896 line card table in card_no, there is a non-clustered index, table line account has 191,122 in account_no, there is a non-clustered index, take a look at the table to connect in different conditions, both the implementation of SQL:

select sum (a.amount) from account a, card b where a.card_no = b.card_no (20 seconds)

SQL change to: select sum (a.amount) from account a, card b where a.card_no = b.card_no and a.account_no = b.account_no (<1 sec)

Analysis of connectivity in the first :---- conditions, the best query program is to account for the outer table, card table for the inner layer, using the index card, its I / O number estimated by the following formula:

---- Account on the outer table of the 22,541 + (outer table account of the inner table * 191,122 line card on the first line corresponds to the outer table to be looking for 3) = 595907 times I / O

---- In the second connecting condition, the best card is to query the program for the outer table, account for the inner table, use the index on account of its I / O number estimated by the following formula:

---- Card on the outer table of the 1944 + (the outer table of the 7896 line card * account on the inner table each line corresponds to the outer table to be looking for 4) = 33528 times I / O

---- Can be seen, only the fully connected condition, the real implementation will be the best option.

Summary:

1. Multi-table in the actual implementation of the operation before the Query Optimizer connect the light conditions, the possible sets of connectivity solutions and systems to find the best option for the smallest expenses. To take full account of the conditions connected with the index table, number of table rows; with the appearance of choice may be the formula: the matching outer table rows * inner table to find the number of each identified as the best product of the smallest program.

2. See the implementation of the program's methods - using set showplanon, open showplan option, you can see the connection sequence, the use of what the index information; want to see more detailed information, necessary to implement sa role dbcc (3604,310,302) .

C. The where clause can not be optimized

1. For example: the conditions of the following SQL statements are built out of a proper index, but the speed is very slow:

Select * from record where substring (card_no, 1,4) ='5378 '(13 seconds) select * from record where amount/30 <1000 (11 seconds) select * from record where convert (char (10), date, 112 ) ='19991201 '(10 seconds)

Analysis: where clause in the list of the results of any operation in the SQL run-time calculated by column, so it had to form a search and did not use the index above the column; If these results can be compile-time query available, it can be optimized SQL Optimizer, use the index to avoid the table search, it will be re-written in SQL to do the following:

select * from record where card_no like'5378% '(<1 seconds) select * from record where amount <1000 * 30 (<1 seconds) select * from record where date ='1999 / 12/01' (<1 sec) you will find it much faster than the SQL!

2. For example: table stuff has 200,000 lines, id_no on non-clustered index, see the following SQL:

select count (*) from stuff where id_no in ('0 ','1') (23 seconds)

Analysis: where the conditions of the 'in' in the logical equivalent of 'or', so the parser in ('0 ','1') into id_no ='0 'or id_no ='1' to perform. We hope that it will or clause, respectively, according to each search, and then add up the results, it can make use of the index id_no; but in fact (according to showplan), it adopted the "OR strategy", that is, first out to meet each or clause of the line, into the temporary database table, and then create a unique index to remove duplicate lines, and finally from the temporary table to calculate the results. Therefore, the actual process of id_no did not make use of the index, and also by the completion time performance of tempdb database.

Practice has proved that the number of rows in table the more work the worse the performance table, when the stuff when there are 620000, the execution time reached 220 seconds! Might as well be separate or clause:

select count (*) from stuff where id_no ='0 'select count (*) from stuff where id_no ='1'

The results obtained from the two, and then make a worthwhile addition. Because every use of the index, the execution time is only three seconds, in the next 620,000 lines, and time is only 4 seconds. Or, better still, write a simple stored procedure to calculate the direct results of the execution time as fast as with the above!

Summary:

Can be seen that the so-called optimized use of the index where clause can not be optimized that have taken place in a table scan or additional overhead.

---- 1. Any of the operations listed will result in a table scan, which includes a database function, the calculation expression, etc., inquiries should be moved as far as possible to equate the right side of the operation.

---- 2.in, or clause regular use of the work table, so that failure index; If you do not have a lot of repeat value, you can consider opening clause; break clause should be included in the index.

---- 3. To be good at using the stored procedure, which allows SQL to become more flexible and efficient.

---- From the above we can see these examples, SQL optimization is the result of the substance of the right under the premise of optimizing the device can be identified with the statement, take full advantage of indexing to reduce the table scan of the I / O frequency, to avoid the search form from happening. In fact, SQL performance optimization is a complex process, the above-mentioned levels in the application of these are just a reflection, in-depth study will involve the allocation of resources database layer, network layer, as well as the flow control layer of the operating system design.

The following is a summary of SFC systems optimized SQL principles:

1. Prohibition of unnecessary and unnecessary full table scan of query, such as Select serial_number, mo_number from sfism4.r_wip_tracking_t Where model_name like '%' and Select serial_number, mo_number from sfism4.r_wip_tracking_t statements have the same results, but the speed of operation the latter about 10 times faster (when the data were more than the obvious).

2. Multi-table query, the amount of data on the table in front of a small speed increase

For example: select count (*) from r_repair_t a, r_sn_detail_t b

where a.serial_number = b.serial_number

Faster than the statistics do select count (*) from r_sn_detail_t b, r_repair_t a

where a.serial_number = b.serial_number the statistics of high-speed

3. A number of conditions, the conditions found to be small immediately behind where.

For example:

where in_station_time> = sysdate-20 and in_station_time> = sysdate-1

where in_station_time> = sysdate-1 and in_station_time> = sysdate-20

Investigate and deal with these two statements is the same as the number of statistics, but behind the pace has greatly statement

The increased speed.

4. In the free time query statistics when to_char conditions as far as possible not to use statistics, but statistics on the use of to_date conditions

For example:

select * from r_wip_tracking_t

where to_char ( 'yyyyymmddhh24', 'in_station_time')> = '20020101000000'

The for in_station_time> = to_date ('20020101000000 ',' yyyymmddhh24miss')

The use of such a query to the index element in_station_time field, the speed improved statistics, this statement of the use of a large number of seriously affected performance.

The following is a excerpt of the SQL statements to optimize the principles of writing (for reference):

1 to avoid unplanned full table scan

The following full-table scan:

- The table-free index

- No one on the line to return to the conditions and restrictions (no Where clause)

- The index of the main column (the first column of index) unrestricted conditions

- The main index of the conditions set out in the expression containing

- The main index is out of the constraints is (not) null or! =

Out on the main index of the restrictions and the value of the operation is like a bind variable or% of the value of the head

Selective use of two index

Selectivity index is the index column is worth a different number and the number of signs than the record, the best non-selective and air out the only index of 1.0.

Composite index of the issue of the order listed:

1 in the qualification of the most frequently used is the main column should be included

2 out of the most selective (that is, clear out the most) should be the main out

If 1 and 2 are inconsistent, you can consider the establishment of a number of the index.

In the composite index and the index for more than a single choice:

Consider the selective index to consider the number of readers consider the AND-EQUAL operation

3 the management of multi-table connection (Nested Loops, Merge Joins and Hash Joins) connection optimization operation

Merge Joins the collection operation Hash Joins and Nested Loops is the first record of the operation to return to record rapid

Merge Joins operation applied to a batch operation, form a huge and long-range query

1 full table scan -> 2 Sort -> 3, the performance comparison and merger of the major expenses in the former two-step

Applicable to the case of full-table scan, it applies to all operations Merge Joins (Nested Loops than effective).

To improve the efficiency of 1: Optimizing I / O, use of ORACLE to enhance the capacity of multi-block read, the use of parallel query option

To improve the efficiency of 1: To improve the value of Sort_Area_Size, use the Sort Direct Writes, for the provision of a dedicated temporary table space paragraph

4, the management of the SQL statement contains the view

Optimizer implementation of the SQL statement contains the view that there were two ways:

- First implementation of view, to complete a full set of results, and then the rest of the query filter for the implementation of inquiry

- Will be integrated into the query text view into

The group by clause contains a view can not be integrated into a query to.

In view of the use of union, non-stop view into the SQL syntax to query.

5 subquery optimization

6 the use of composite Keys / Star query

7 Index Connect By proper operation

8 to limit access to the remote table

9 great management table visit

- Manage data close (proximity) recorded in the table to deposit a range of table used to scan the longest to sort out the order they were asked to contribute to the scan data, especially for large tables.

- To avoid scanning the index does not help when returning large data set, use the index on the SGA cache of data blocks occupied by a larger impact on other users; can scan the entire table from ORACLE to read the multi-block mechanism and the "consistency access / each "benefit characteristics.

- Create a fully indexed access to the table so that a more comprehensive index to read only the data set for different number of main index

- Create a hash cluster

- Create a partition table and view

- The use of parallel options

10 using the Union All instead of Union

UNION ALL operator does not include the Sort Unique operation, the first line of response retrieval speed, most cases do not complete the operation of temporary paragraph,

The establishment of the UNION ALL view can be used in the query into the query syntax, and improve efficiency

11, refrain from the use of SQL in PL / SQL function calls

12 bind variables (Bind Variable) the use of management

Bind Variable and use Execute using methods

Will be like: name ||'%' read between: name and: name | | char (225), has been to avoid full-table scan, but the use of the index.

Optimization of the process of return of 13

Data changes, the re-optimization case study

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
High Availability Oracle Flashback 2009-02-26 17:23:02
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

shell script examples of the implementation of Oracle SQL script

kods.net — Tue, 14 Apr 2009 06:08:39+0000

A survey of my colleagues to write the script EBS information.
# Standard output settings, the use of DBMS_OUTPUT.put_line () output to the screen.
# size the buffer size settings.
set SERVEROUTPUT ON size 100000;
# Search result set rows
set FEEDBACK OFF;
# Whether to display the results of the first information
set HEADING OFF;
# Whether to display the command
set ECHO OFF;
# If set to 0, then all the output for the page and column headings do not show
set PAGES 0
# Set the line length
set LINESIZE 255
# Output format
COL TABLE_NAME FORMAT a30;

# Search results directed to a specified parameter file
spool & 1

declare
NUM_COUNT NUMBER;
BEGIN
FOR CUR IN (SELECT * FROM USER_TABLES) LOOP
EXECUTE IMMEDIATE 'SELECT COUNT (*) INTO: NUM_COUNT FROM' | |
CUR.TABLE_NAME INTO NUM_COUNT;
DBMS_OUTPUT.PUT_LINE (CUR.TABLE_NAME | | ',' | | NUM_COUNT);
END LOOP;
END;
/

spool off
quit

I wrote a shell script.
# / bin / sh
# input
# Scrptnm: SQL script name
# Outflnm: Output file name
# function
# SQL running result

runSQLS ()
(
# input parameter
if [ "$ #"-lt 3]; then
echo "use: ./0030.sh scrptnm outflnm"
return
fi

# run SQL script
ERRORBUF = `sqlplus apps / apps @ $ 1 $ 2`

echo "ERRORBUF: $ ERRORBUF"
)

# shell main program
# 1, configure environment
export ORACLE_SID = eoss
export ORACLE_HOME = / app/eoss/eossdb/9.2.0
PATH = $ PATH: $ ORACLE_HOME / bin
# call function
runSQLS "$ 1" "$ 2"
echo "run 0030.sh over ..."

Results:

SGA extended the principle of 32bit oracle 2009-03-31 09:48:01
An example of the use of TKPROF 2009-02-26 01:38:19
Diagnosis and principles of order 2009-02-25 20:27:28
ORACLE 10G dataguard configuration Step by Step 2009-02-16 01:13:55

Oracle SQL

ORACLE SQL Performance Tuning Series

Related Posts

Oracle SQL * PLUS based

Related Posts

Oracle SQL * PLUS basic and sqlplus command Xiangjie -2

Related Posts

oracle sql outer join predicates the application of a time when the order will affect the outcome of

Related Posts

Oracle SQL exquisite SQL (Reprinted)

Related Posts

Oracle SQL exquisite SQL statement to explain

Related Posts

Oracle SQL Loader Data Import

Related Posts

oracle sql date comparison

Related Posts

Encyclopedia of common command oracle sqlplus

Related Posts

oracle sql statement based on

Related Posts

ORACLE SQL and SQL * PLUS (1)

Related Posts

oracle sql query principle

Related Posts

Oracle SQL optimization principles (pick)

Related Posts

shell script examples of the implementation of Oracle SQL script

Related Posts