| When to use index [message #122149] |
Fri, 03 June 2005 07:13  |
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
|
Senior Member |
|
|
hi experts,
if my table is having total 75 rows, but the query is retruning on 20 out of 75.(i.e.-27%) of row ,should i prefer full table scan or index range scan( i have an composite index) on the column used in the where condition.
Please reply,
When to use an index.
Thanks
Dinesh
|
|
|
|
|
|
|
|
|
|
| Re: When to use index [message #123114 is a reply to message #122990] |
Thu, 09 June 2005 21:02  |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi,
Nabeel see the example below to know which optimizer is used
V$SQLAREA (To know optimizer mode)
SQL> select SQL_TEXT, OPTIMIZER_MODE FROM V$SQLAREA WHERE SQL_TEXT LIKE ‘% DEPT %’;
Note:- I don't know which sql u want to check so change ur query as per that....
If we want to use rule based optimizer we have to give hints
SQL> select /*+ RULE*/ * from dept;
Parameter for optimizer that can be change
Setting optimizer mode
1. At database level (i.e. by using init.ora)
2. At session level (i.e. by using Alter command)
SQL> Alter session set optimizer_mode = rule ;
3. At statement level (i.ee by using hint)
SQL> select /*+ RULE*/ * from dept;
Type of HINT which can be specify at statement level
• RULE
• CHOOSE
• FIRST_ROW
• ALL_ROW
This HINT can be used with SQL statement.
I hope you got your answer in this reply
Regards
sunilkumar
[Updated on: Thu, 09 June 2005 21:03] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
