Using Oracle Index Hints in SQL statements
Using Oracle Index Hints in SQL statements
Hints are used to give specific information that we know about our data and application, to Oracle. This further improves the performance of our system. There can be instances where the default optimizer may not be efficient for a certain SQL statements. We can specify HINTS with the SQL statements, to improve the efficiency of those SQL statements.
In this article, we shall see how to specify INDEX hints and what the advantages of the same are.
1 How to specify Hints
The Hints are enclosed in comment, /* comment */, in an SQL statement, and can only be specified with SELECT, DELETE and UPDATE keyword.
SELECT /* comment */ ........ ;
All hints should start with a + sign. This tells the SQL Parser that the SQL has a hint specified with it.
SELECT /*+{hint} */ ........ ;
2 Using INDEX Hints
When you specify an INDEX Hint, the optimizer knows that it has to use the INDEX specified in the hint. In this case, the optimizer does not go for a Full Table Scan nor does it use any other index. In addition, it does not calculate the cost of the Index to be used.
If no INDEX hint is specified the optimizer determines the cost of the each index that could be used to access the table and uses the one with the lower cost.
If there are multiple indexes specified with the Hint then the optimizer has to determine the cost of each index to be used with the specified table. Once that is determined, it uses the Index with the lower cost to access the table. In this case, the optimizer does not do a FULL Table Scan. Also note that, the optimizer may choose to use multiple indexes and then merge the result sets to access the table. This method is used if the cost is low.
Syntax:
/*+ INDEX ( table [index [index]...] ) */
Where:
table specifies the name or alias of the table associated with the index to be scanned.
index specifies an index on which an index scan is to be
相关文档：
http://www.umgr.com/blog/PostView.aspx?bpId=36294
1. 执行sql语句
int sqlite3_exec(sqlite3*， const char *sql， sqlite3_callbacksql 语法
， void *， char **errmsg );
这就是执行一条 sql 语句的函数。
第1个参数不再说了，是前面open函数得到的指针。说了是关键数据结构。
第2个参数const char ......
oracle表空间操作详解
1
2
3作者： 来源： 更新日期：2006-01-04
5
6
7建立表空间
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
NULL指的是空值，或者非法值。
NVL (expr1, expr2)->expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致
NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型
NULLIF (expr1, expr2) ->相等返回NULL，不等返回ex ......
在ORACLE中给表、列增加注释以及读取注释
1、给表填加注释：SQL>comment on table 表名 is '表注释";
2、给列加注释：SQL>comment on column 表.列 is '列注释';
3、读取表注释：SQL>select * from user_tab_comments where comments is not null;
4、读取列注释：SQL>select * from user_col_commnents wh ......
先构造一个表:
create table emp2(
id number(2),
name varchar(10),
currdate date,
action varchar2(1)
)
创建触发器：
create or replace trigger d_i_u_emp2
after insert or update or delete on mysort
begin
if inserting then
insert into emp2 values (12,'dog',sysdate,'i');
elsif deleting then ......
