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
相关文档：
CASE表达式可以在SQL中实现if-then-else型的逻辑，而不必使用PL/SQL。CASE的工作方式与DECODE()类似，但应该使用CASE,因为它与ANSI兼容。
CASE有两种表达式:
1. 简单CASE表达式,使用表达式确定返回值.
语法:
CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN ......
select * from (select t.*,rownum rn from (select * from emp) t where rownum<=10) where rn>=6;
创建分页结果集的游标
create or replace package fenyepackage as
type testcursor is ref cursor;
end fenyepackage;
创建分页存储过程
create or replace procedure fenye3(
tableName varchar2, --表名
......
在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 ......
