Oracle Query Statement Encyclopedia (Oracle Basic Command Encyclopedia one) _oracle

1.create user username identified by password;//build username and password Oracle
2.grant CONNECT,RESOURCE,DBA to username;//authorize Grant CONNECT,RESOURCE,DBA,SYSDBA to username;
3.connect username/password//Enter.
4.select table_name,column_name from User_tab_columns where table_name= ' mview_log ';//table name in query table, field name, and so on.
5. How do I execute a script sql file? Sql> @PATH/filename.sql;
6.Oracle OLE DB Provider executing multiple SQL statements in the command differs slightly from SQL Server, with SQL Server using only the ";" Split multiple SQL statements, and Oracle needs to comply with the Oracle invocation specification, that is, in addition to semicolon splitting, but also to begin/end; surround the statement body.
Using the C # Description should look like the following:
This.oleDbCommand1.CommandText = "Begin INSERT into Group_info (group_id, Group_name) VALUES (1, \ ' 2\ ');" INSERT into Group_info (group_id, Group_name) VALUES (2, \ ' 2\ '); end; ";
7. Query all tables under the user SELECT DISTINCT table_name from User_tab_columns;
8. How to search out the first n records? Select A.*,rownum from (SELECT * to Cardkind ORDER by Cardkind) a where rownum<n
9. Find all the tables under the User: SELECT * from Tab;
2. Display the current connected user
Sql> Show User
3. See which users the system has
Sql> select * from All_users;
4. New user and authorize
Sql> create User a identified by A; (default is built under System tablespace)
Sql> Grant Connect,resource to A;
5. Connect to New User
Sql> Conn a/a
6. Query all objects under current user
Sql> select * from tab;
7, establish the first table
Sql> CREATE Table A (a number);
8. Query table structure
Sql> desc A
9. Insert new record
Sql> INSERT into a values (1);
10. Inquiry record
Sql> select * from A;
11, change the record
sql> update a set a=2;
12, delete the record
Sql> Delete from A;
13. Roll Back
Sql> roll;
sql> rollback;
14. Submit
Sql> commit;
SELECT * FROM
(select T.*,dense_rank () cardkind rank from Cardkind t)
where rank = 2;
46. How to add a carriage return in a string?
Select ' Welcome to visit ' | | Chr (10) | | Www.CSDN.NET ' from dual;
47. How are the Chinese sorted?
Before Oracle9i, Chinese is sorted according to binary encoding.
In oracle9i, according to pinyin, radical, stroke sorting function. Set Nls_sort value
Schinese_radical_m sorted by radical (first order), stroke (second order)
Schinese_stroke_m sorted by stroke (first order), radical (second order)
Schinese_pinyin_m sorted by Pinyin
Can the object name in Oracle8i be in Chinese?
OK
49. How to change the Sql*plus startup option in Win?
Sql*plus its own option settings we can set in $oracle_home/sqlplus/admin/glogin.sql.
50. How do I modify the default date for the Oracel database?
Alter session set nls_date_format= ' Yyyymmddhh24miss ';
OR
You can add a row to the Init.ora
nls_date_format= ' Yyyymmddhh24miss '
51. How do I put a small table in a keep pool?
ALTER TABLE XXX storage (buffer_pool keep);
52. How do I check if a patch is installed?
Check that Orainventory
53. How do I make a SELECT statement to automatically generate ordinal numbers for query results?
Select Rownum,col from table;
54. How do I know the tablespace of a table in the data pants?
Select Tablespace_name from User_tables where table_name= ' TEST ';
There is a field in the select * from User_tables tablespace_name, (Oracle);
SELECT * from Dba_segments where ...;
55. How can you quickly make a backup table like the original table?
CREATE TABLE new_table as (SELECT * from old_table);
55. How to modify procedure under Sqlplus?
Select Line,trim (text) t from User_source where name = ' an ' order by line;
56. How to remove procedure is accidentally locked?
Alter system kill session, killing that session, but you need to find out her session ID first.
Or
It's OK to change the process to a new name.
What is a SQL reference?
is a manual of SQL, including syntax, functions, and so on, which is downloaded from the Documentation Center of the official Oracle website.
58. How do I view the status of the database?
Under Unix
Ps-ef | grep ora
Under Windows
See if the service is up
Whether you can connect to the database
59. How can I modify the primary key of a table?
ALTER TABLE AAA
Drop constraint Aaa_key;
ALTER TABLE AAA
Add constraint Aaa_key primary key (A1,B1);
60. Change the size of the data file?
With ALTER DATABASE .... DataFile ...;
Manually changing the size of the data file, there is no damage to the original data file.
61. How do I see what programs are running in Oracle?
View V$sessions Table
62. How can you see how many tablespace there are in the database?
SELECT * from Dba_tablespaces;
63. How do I modify the number of user connections for an Oracle database?
Modify the Initsid.ora to enlarge the process and restart the database.
64. How do I find out when a record was last updated?
You can use Logminer to see
65. How to read and write files in Pl/sql?
The Utl_file package allows users to read and write operating system files through Pl/sql.
66. How to Put "&" in a record?
Insert into a values (translate (' at{&}t ', ' at{} ', ' at '));
How does EXP add query parameters?
EXP user/pass file=a.dmp TABLES (BSEMPMS)
Query= ' "WHERE emp_no=\ ' s09394\ '";
68. About oracle8i support for simplified and traditional character set issues?
ZHS16GBK can support
What software is the Data guard?
is standby's replacement product.
70. How do I create spfile?
Sql> Connect/as SYSDBA
Sql> select * from V$version;
Sql> create Pfile from SPFile;
Sql> CREATE SPFILE from pfile= ' E:\ora9i\admin\eygle\pfile\init.ora ';
The file has been created.
sql> CREATE spfile= ' E:\ora9i\database\SPFILEEYGLE. ORA ' from
pfile= ' E:\ora9i\admin\eygle\pfile\init.ora ';
The file has been created.
71. Application of kernel parameters?
Shmmax
Meaning: This setting does not determine how much physical memory the Oracle database or operating system uses, only determines
The maximum number of memory that can be used. This setting also does not affect the kernel resources of the operating system.
Set method: 0.5* Physical Memory
Example: Set shmsys:shminfo_shmmax=10485760
Shmmin
Meaning: The minimum size of shared memory.
Set method: Generally set to be 1.
Example: Set shmsys:shminfo_shmmin=1:
Shmmni
Meaning: The maximum number of shared memory segments in the system.
Example: Set shmsys:shminfo_shmmni=100
Shmseg
Meaning: The maximum number of shared memory segments that can be used by each user process.
Example: Set shmsys:shminfo_shmseg=20:
Semmni
Meaning: The maximum number of semaphore identifierer in the system.
Set method: Set the value of this variable to the maximum of all instances of Oracle on this system Init.ora
That processes of that value plus 10.
Example: Set semsys:seminfo_semmni=100
Semmns
Meaning: The maximum number of emaphores in the system.
Set method: This value can be computed in the following way: The Initsid.ora inside of each Oracle instance
The sum of the values of the processes (excluding the largest processes parameter) + the largest processesx2+10x
The number of Oracle instances.
Example: Set semsys:seminfo_semmns=200
SEMMSL:
Meaning: The maximum number of semaphore in a set.
Set up method: Sets the value of the largest processes in the Initsid.ora of all Oracle instances 10+.
Example: Set semsys:seminfo_semmsl=-200
72. How to see which users have Sysdba, sysoper permissions?
Sql>conn Sys/change_on_install
Sql>select * from V_$pwfile_users;
73. How do I back up one or more tables separately?
Exp User/Password tables= (table 1,..., table 2)
74. How to back up one or more users individually?
Exp System/manager owner= (user 1, user 2,..., user n) file= export file
75. How do I search the Clob field for Full-text?
SELECT * from A WHERE dbms_lob.instr (a.a, ' K ', 1,1) >0; 76. How do I display the current connection user?
Show USER
77. How do I see the path where the data file is placed?
Col file_name Format A50
Sql> Select Tablespace_name,file_id,bytes/1024/1024,file_name from Dba_data_files
Order by file_id;
78. How do I view existing rollback segments and their status?
Sql> Col Segment Format A30
Sql> SELECT Segment_name,owner,tablespace_name,segment_id,file _id,status from
Dba_rollback_segs
79. How do I change the check scope of a field's initial definition?
sql> ALTER TABLE XXX drop constraint constraint_name;
And then create a new constraint:
sql> ALTER TABLE XXX add constraint constraint_name check ();
What are the common system files for Oracle?
These file information is displayed through the following view: V$database,v$datafile,v$logfile V$controlfile
V$parameter;
81. Inner JOIN INNER join?
Select a.* from Bsempms A,bsdptms b where a.dpt_no=b.dpt_no;
82. How to connect outside?
Select a.* from Bsempms A,bsdptms b where a.dpt_no=b.dpt_no (+);
Select a.* from Bsempms A,bsdptms b wherea.dpt_no (+) =b.dpt_no;
83. How do I execute a script sql file?
sql>@ $PATH/filename.sql;
84. How to quickly clear a large table?
Sql>truncate table table_name;
85. How do I find out how many database instances are available?
Sql>select * from V$instance;
86. How many tables are there to query the database?
Sql>select * from All_tables;
87. How do I test the time it takes to execute an SQL statement?
Sql>set timing on;
Sql>select * FROM TableName;
The inverse function of CHR () is?
ASCII ()
SELECT CHAR (DUAL) from;
SELECT ASCII (' A ') from DUAL;
89. Connection of strings
SELECT CONCAT (col1,col2) from TABLE;
SELECT col1| | COL2 from TABLE;
90. How to guide the results of a select to a text file?
Sql>spool C:\ABCD. TXT;
Sql>select * FROM table;
SQL >spool off;
91. How do I estimate the number of I/O performed by SQL?
Sql>set autotrace on;
Sql>select * from TABLE;
OR
Sql>select * from V$filestat;
can view IO count
92. How do I change the field size under Sqlplus?
ALTER TABLE table_name Modify (field_name VARCHAR2 (100));
Change the big line, change the small no (unless all is empty)
93. How to query the data of a day?
SELECT * FROM table_name where trunc (date field) =to_date (' 2003-05-02 ', ' yyyy-mm-
DD ');
How does the SQL statement insert a full year date?
CREATE TABLE Bsyear (d date);
INSERT INTO Bsyear
Select To_date (' 20030101 ', ' YYYYMMDD ') +rownum-1
From All_objects
where RowNum <= to_char (to_date (' 20031231 ', ' yyyymmdd '), ' ddd ');
95. What if you modify the table name?
ALTER TABLE Old_table_name Rename to New_table_name;
96. How do I get the return status value of the command?
Sqlcode=0
97. How do I know what permissions the user has?
SELECT * from Dba_sys_privs;
98. What is the difference between the oracle9i downloaded from the Internet and the Standard Edition sold on the market?
There is no difference in functionality, except that Oracle has a clear stipulation that Oracle products downloaded from the Web site are not intended for
Commercial use, otherwise infringement.
99. How can I tell if the database is running in archive mode or not in archive mode?
Enter Dbastudio, History--〉 Database---archive view.
What's the difference between Sql>startup pfile and ifile,spfiled?
Pfile is the traditional Oracle initialization parameter file, text format.
IFile is similar to the Include in C language, used to introduce another file into the
SPFile is new in 9i and is the default parameter file, binary format
You should only pick up pfile after startup
101. How to search out the first n records?
SELECT * from EmpLOYEE WHERE rownum < n order by Empno;
102. How do I know how many concurrent users the Oracle supports on the machine?
Sql>conn internal;
Sql>show parameter processes;
Can db_block_size be modified?
In general, it is not recommended to do so.
104. How do I count the total number of records in two tables?
Select (select count (id) from AA) + (select count (IDs) from BB) total from dual;
105. How to use SQL statement to find a column of the nth large value?
SELECT * FROM
(select T.*,dense_rank () over (order by Sal) rank from employee)
where rank = N;
106. How to add 2 years to the existing date? (
Select Add_months (sysdate,24) from dual;
What does used_ublk mean by negative values?
It is "harmless".
What does 108 Connect string mean?
Should be the content following the service name in Tnsnames.ora
109. How to enlarge the size of redo log?
Create a temporary redolog group, then switch logs, delete previous logs, and create new logs.
is tablespace not greater than 4G?
There is no limit.
111. Return a minimum integer value greater than or equal to n?
SELECT ceil (N) from DUAL;
112. Return a minimum integer value less than or equal to n?
SELECT FLOOR (N) from DUAL;
113. Return to the last day of the current month?
SELECT Last_day (sysdate) from DUAL;
114. How to import data between different users?
IMP System/manager File=aa. DMP fromuser=user_old touser=user_new rows=y indexes=y

115. How do I find the name of the primary key field for the database table?
Sql>select * from user_constraints WHERE constraint_type= ' P ' and
Table_name= ' table_name ';
116. Two result sets are added to each other function?
Sql>select * FROM Bsempms_old INTERSECT SELECT * from Bsempms_new;
Sql>select * from the Bsempms_old UNION SELECT * from Bsempms_new;
Sql>select * from Bsempms_old UNION all SELECT * from bsempms_new;
117. Two result set reciprocal subtraction function?
Sql>select * from Bsempms_old minus SELECT * from Bsempms_new;
118. How to configure sequence?
Jian sequence Seq_custid
Create sequence Seq_custid start 1 incrememt by 1;
When building a table:
CREATE TABLE Cust
{cust_id smallint NOT NULL,
...}
When insert:
INSERT INTO Table Cust
VALUES (Seq_cust.nextval, ...)
The usual wording of the various parts of a date
119&gt: The writing of the year in which the time point is taken.
SELECT to_char (sysdate, ' YYYY ') from DUAL;
120&gt: The writing of the month in which the time point is taken.
SELECT to_char (sysdate, ' MM ') from DUAL;
121&gt the day of the time:
SELECT to_char (sysdate, ' DD ') from DUAL;
122&gt: The writing of a time point.
SELECT to_char (sysdate, ' HH24 ') from DUAL;
123&gt: To take the point of the time
SELECT to_char (sysdate, ' MI ') from DUAL;
124&gt: To take the time point of the second
SELECT to_char (sysdate, ' SS ') from DUAL;
125&gt the date of the time point:
SELECT TRUNC (sysdate) from DUAL;
126&gt: To take the time of the time
SELECT to_char (sysdate, ' HH24:MI:SS ') from DUAL;
127> date, time form becomes character form
SELECT To_char (sysdate) from DUAL;
128> Converts a string to a date or time form:
SELECT to_date (' 2003/08/01 ') from DUAL; 129> Returns the number of days of the week for a parameter:
SELECT to_char (sysdate, ' D ') from DUAL;
130> Returns the spelling of the day ordinal of the year:
SELECT to_char (sysdate, ' DDD ') from DUAL;
131&gt. Returns the number of seconds between midnight and the time value specified in the parameter:
SELECT to_char (sysdate, ' sssss ') from DUAL;
132> Returns the first week of the year in the parameter:
SELECT to_char (sysdate, ' WW ') from DUAL;
Virtual fields
Currval and Nextval.
Create a sequence for a table
CREATE SEQUENCE empseq ...;
SELECT Empseq.currval from DUAL;
numeric values for automatically inserting a sequence
INSERT into EMP
VALUES (Empseq.nextval, ' LEWIS ', ' clerk ',
7902, Sysdate, 1200, NULL, 20);
134. RowNum
Ordinal of a row sorted by set
SELECT * from emp WHERE rownum < 10;
135. ROWID
Returns the physical address of a row
SELECT ROWID, ename from emp WHERE deptno = 20;
136. Convert n seconds to last minute format?
Set Serverout on
Declare
N number: = 1000000;
RET VARCHAR2 (100);
Begin
RET: = trunc (n/3600) | | ' Hour ' | | To_char (to_date mod (n,3600), ' sssss '), ' Fmmi ' points
"ss" Seconds "");
Dbms_output.put_line (ret);
End
137. How do I query the process of making a larger sort?
SELECT B.tablespace, b.segfile#, b.segblk#, B.blocks, A.sid, a.serial#,
A.username, A.osuser, A.status
From V$session A,v$sort_usage b
WHERE a.saddr = b.session_addr
Order by B.tablespace, b.segfile#, b.segblk#, b.blocks;
138. How do I query for SQL statements that make a larger sort of process?
Select/*+ ORDERED * * Sql_text from V$sqltext a
where A.hash_value = (
Select Sql_hash_value from V$session b
where B.sid = &sid and b.serial# = &serial)
Order by piece ASC;
139. How do I find duplicate records?
SELECT * FROM table_name
WHERE rowid!= (SELECT MAX (ROWID) from table_name D
WHERE table_name. COL1=D.COL1 and TABLE_NAME. COL2=D.COL2);
140. How do I delete duplicate records?
DELETE from table_name
WHERE rowid!= (SELECT MAX (ROWID) from table_name D
WHERE table_name. COL1=D.COL1 and TABLE_NAME. COL2=D.COL2);
141. How do I quickly compile all views?
SQL >spool VIEW1. Sql
SQL >select ' ALTER VIEW ' | | tname| | '
COMPILE; ' From TAB;
SQL >spool off
Then execute the view1.sql.
SQL > @VIEW1. SQL;
Solution of ORA-01555 SNAPSHOT TOO old
Increase the value of the minextents, increase the size of the zone, and set a high optimal value.
143. Transaction requirements of the rollback segment space is not enough, the performance of the table space filled (ORA-01560 error), rollback segment extension to reach
The solution to the value of the parameter maxextents (ORA-01628).
Add a file to the rollback segment tablespace or make an existing file larger; Increase the value of the maxextents.
144. How do I encrypt Oracle's stored procedures?
The following stored procedure contents are placed in the Aa.sql file
Create or Replace procedure TESTCCB (i in number) as
Begin
Dbms_output.put_line (' input parameter is ' | | To_char (i));
End
Sql>wrap Iname=a.sql;
Pl/sql wrapper:release 8.1.7.0.0-production on Tue Nov 27 22:26:48 2001
Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.
Processing Aa.sql to AA.PLB
Run AA.PLB
Sql> @AA. PLB;
145. How to monitor the waiting of cases?
Select Event,sum (Decode (wait_time,0,0,1)) "Prev",
SUM (decode (wait_time,0,1,0)) "Curr", COUNT (*) "Tot"
From v$session_wait
Group BY event order by 4;
146. How do I roll back the contention for the segment?
Select name, waits, gets, waits/gets "Ratio"
From V$rollstat C, V$rollname D
where C.usn = D.usn;
147. How to monitor the I/O ratio in table space?
Select B.tablespace_name name,b.file_name "file", A.phyrds Pyr,
A.phyblkrd pbr,a.phywrts pyw, A.phyblkwrt PBW
From V$filestat A, Dba_data_files B
where a.file# = b.file_id
Order BY B.tablespace_name;
148. How to monitor the I/O ratio of the file system?
Select substr (c.file#,1,2) "#", substr (c.name,1,30) "Name",
C.status, C.bytes, D.phyrds, D.phywrts
From V$datafile C, V$filestat D
where c.file# = d.file#;
149. How to find all the indexes under a user?
Select User_indexes.table_name, User_indexes.index_name,uniqueness, column_name
From User_ind_columns, user_indexes
where user_ind_columns.index_name = User_indexes.index_name
and user_ind_columns.table_name = User_indexes.table_name
Order by User_indexes.table_type, User_indexes.table_name,
User_indexes.index_name, column_position;
150. How to monitor the percentage of SGA?
Select A.value + b.value "logical_reads", C.value "Phys_reads",
Round ((a.value+b.value)-c.value)/(A.value+b.value)) "BUFFER HIT RATIO"
From V$sysstat A, V$sysstat B, V$sysstat c
where a.statistic# = b.statistic# = 39
and c.statistic# = 40;
151. How to monitor the hit rate of the dictionary buffer in the SGA?
Select parameter, gets,getmisses, getmisses/(gets+getmisses) *100 "Miss Ratio",
(SUM (getmisses)/(SUM (gets) +sum (getmisses))) *100 "Hit ratio"
From V$rowcache
where Gets+getmisses <>0
Group BY parameter, gets, getmisses;
152. How to monitor the percentage of the shared cache in the SGA, should be less than 1%?
Select SUM (Pins) "Total pins", sum (reloads) "Total reloads",
SUM (reloads)/sum (pins) *100 Libcache
From V$librarycache;
Select SUM (pinhits-reloads)/sum (Pins) "Hit Radio", SUM (reloads)/sum (Pins) "Reload
Percent
From V$librarycache;
153. How do i show the categories and sizes of all database objects?
Select count (name) num_instances, type, sum (source_size) source_size,
SUM (parsed_size) parsed_size, sum (code_size) code_size, sum (error_size)
Error_size,
SUM (source_size) +sum (parsed_size) +sum (code_size) +sum (error_size) size_required
From Dba_object_size
Group By type Order by 2;
154. Monitoring the SGA in the redo log buffer hit rate, should be less than 1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode (gets,0,0,misses/gets*100) Ratio1,
Decode (immediate_gets+immediate_misses,0,0,
Immediate_misses/(immediate_gets+immediate_misses) *100) Ratio2
From V$latch WHERE name in (' Redo allocation ', ' redo copy ');
155. Monitor memory and hard drive sorting ratio, preferably make it less than. 10, increase sort_area_size
SELECT name, value from V$sysstat WHERE name in (' Sorts (memory) ', ' sorts
(disk) ');
156. How to monitor the current database who is running what SQL statement?
SELECT Osuser, username, sql_text from v$session A, V$sqltext b
where a.sql_address =b.address order by address, piece;
157. How do I monitor the dictionary buffer?
Select (sum (pins-reloads))/sum (PINS) "LIB CACHE" from V$librarycache;
SELECT (sum (gets-getmisses-usage-fixed))/sum (GETS) "ROW CACHE" from
V$rowcache;
Select SUM (PINS) "Executions", sum (reloads) "CACHE misses while executing" from
V$librarycache;
The latter divided by the former, this ratio is less than 1%, close to 0% is good.
Select SUM (GETS) "DICTIONARY GETS", Sum (getmisses) "DICTIONARY CACHE Get Misses"
From V$rowcache
158. Monitoring MTS
Select busy/(Busy+idle) "Shared servers Busy" from V$dispatcher;
When this value is greater than 0.5, the parameter needs to be increased
Select sum (Wait)/sum (TOTALQ) "Dispatcher waits" from V$queue where
Type= ' Dispatcher ';
Select COUNT (*) from V$dispatcher;
Select Servers_highwater from V$mts;
When Servers_highwater approach mts_max_servers, the parameters need to be increased
159. How do I know the ID number of the current user?
Sql>show USER;
OR
Sql>select user from Dual;
160. How can I view a table with a high degree of fragmentation?
SELECT segment_name table_name, COUNT (*) extents
From Dba_segments WHERE owner is in (' SYS ', ' SYSTEM ') GROUP by segment_name
Having COUNT (*) = (SELECT MAX (COUNT (*)) from Dba_segments GROUP by
Segment_name);
162. How do I know how a table is stored in a tablespace?
Select Segment_name,sum (bytes), COUNT (*) Ext_quan from dba_extents where
Tablespace_name= ' &tablespace_name ' and segment_type= ' TABLE ' GROUP by
Tablespace_name,segment_name;
163. How do I know how the index is stored in the table space?
Select Segment_name,count (*) from dba_extents where segment_type= ' INDEX ' and
Owner= ' &owner '
Group BY Segment_name;
164, how to know how to use CPU user session?
11 is CPU used by the session
Select A.sid,spid,status,substr (a.program,1,40)
prog,a.terminal,osuser,value/60/100 value
From V$session a,v$process B,v$sesstat C
where c.statistic#=11 and C.sid=a.sid and a.paddr=b.addr order by value Desc;
165. How do I know the listener log file?
Take 8I as an example
$ORACLE _home/network/log/listener. LOG
166. How do I know the listener parameter file?
Take 8I as an example
$ORACLE _home/network/admin/listener. ORA
167. How do I know the TNS connection file?
Take 8I as an example
$ORACLE _home/network/admin/tnsnames. ORA
168. How do I know the sql*net environment document?
Take 8I as an example
$ORACLE _home/network/admin/sqlnet. ORA
169. How do I know the warning log file?
Take 8I as an example
$ORACLE _HOME/ADMIN/SID/BDUMP/SIDALRT. LOG
170. How do I know the basic structure?
Take 8I as an example
$ORACLE _home/rdbms/admin/standard. Sql
171. How do I know how to build a data dictionary view?
Take 8I as an example
$ORACLE _home/rdbms/admin/catalog. Sql
172. How do I know how to build an audit data dictionary view?
Take 8I as an example
$ORACLE _home/rdbms/admin/cataudit. Sql
173. How do I know how to set up a snapshot with a data dictionary view?
Take 8I as an example
$ORACLE _home/rdbms/admin/catsnap. Sql
This talk is mainly about the optimization of the SQL sentence Method! Mainly based on the oracle9i.
174./*+all_rows*/
It is shown that the cost-based optimization method is chosen for the statement block, and the optimal throughput is achieved to minimize resource consumption.
For example:
SELECT/*+all+_rows*/emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' Ccbzzp ';
175./*+first_rows*/
It is shown that the cost-based optimization method is chosen for the statement block, and the optimal response time is obtained to minimize the resource consumption.
For example:
SELECT/*+first_rows*/emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' Ccbzzp '; 176./*+choose*/
Indicates that if the data dictionary has access to the statistical information of the table, it will be based on the cost optimization method and obtain the best throughput;
Indicates that if there is no statistical information in the data dictionary to access the table, an optimization method based on rule cost is proposed.
For example:
SELECT/*+choose*/emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' Ccbzzp ';
177./*+rule*/
It shows that the rule-based optimization method is chosen for the statement block.
For example:
SELECT/*+ Rule * * emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' Ccbzzp ';
178./*+full (TABLE) * *
Indicates a method for selecting a global scan on a table.
For example:
SELECT/*+full (a) * * * Emp_no,emp_nam from Bsempms a WHERE emp_no= ' ccbzzp ';
179./*+rowid (TABLE) * *
The prompt explicitly indicates that the specified table is accessed according to ROWID.
For example:
SELECT/*+rowid (BSEMPMS) * * from Bsempms WHERE rowid>= ' aaaaaaaaaaaaaa '
and emp_no= ' CCBZZP ';
180./*+cluster (TABLE) * *
Tip explicitly indicates the access method that selects a cluster scan for a specified table, which is only valid for a cluster object.
For example:
SELECT/*+cluster */Bsempms. Emp_no,dpt_no from Bsempms,bsdptms
WHERE dpt_no= ' TEC304 ' and Bsempms. Dpt_no=bsdptms. Dpt_no;
181./*+index (TABLE index_name) * *
Indicates the scanning method for selecting indexes on a table.
For example:
SELECT/*+index (Bsempms sex_index) use sex_index because THERE ARE fewmale
BSEMPMS * * from Bsempms WHERE sex= ' M ';
/*+INDEX_ASC (TABLE index_name) * *
Indicates a scan method that selects index ascending for the table.