| Force the optimizer to use the primary key index [message #412422] |
Thu, 09 July 2009 04:42  |
|
|
Hi,
I have couple of questions, Could you please clear my doubts.
1. How would you force the optimizer to use the primary key index when querying table SCOTT.EMP(Assume the table is available and EMPNO is the primary key)
2. If the query plan is using the index how would you for the optimizer not to use the index.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Force the optimizer to use the primary key index [message #412932 is a reply to message #412547] |
Mon, 13 July 2009 08:01  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The /*+ FULL */ hint will NOT disable the use of an index.
A hint affects the CBO by applying a weighting in favour of the specified acess plan/join method. It is entirely possible for the CBO to consider this weighted plan, and still use one that requires the use of an index
|
|
|
|
| Re: Force the optimizer to use the primary key index [message #412964 is a reply to message #412422] |
Mon, 13 July 2009 12:20 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
Can you prove that JRowbottom with an example.
I cannot prove it. It always performs table scan with the FULL hint for me.
From the Oracle Docs:-
FULL
| Quote: | The FULL hint explicitly chooses a full table scan for the specified table.
full_hint::=
Text description of full_hint.gif follows
Text description of the illustration full_hint.gif
where table specifies the name or alias of the table on which the full table scan is to be performed. If the statement does not use aliases, then the table name is the default alias.
For example:
SELECT /*+ FULL(e) */ employee_id, last_name
FROM employees e
WHERE last_name LIKE :b1;
Oracle performs a full table scan on the employees table to execute this statement, even if there is an index on the last_name column that is made available by the condition in the WHERE clause.
|
[Updated on: Mon, 13 July 2009 13:02] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Force the optimizer to use the primary key index [message #413146 is a reply to message #412422] |
Tue, 14 July 2009 10:15 |
|
|
@JRowbottom,
I actually tried @coleing solution and A/c to Explain Plan results its worked fine (think so..)
SQL> explain plan for select empno,ename from emp order by empno;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 156 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 13 | 156 | 2 |
| 2 | INDEX FULL SCAN | PK_EMP | 13 | | 1 |
----------------------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- 'PLAN_TABLE' is old version
- cpu costing is off (consider enabling it)
13 rows selected.
SQL> explain plan for select /*+ FULL(emp) */ empno,ename from emp order by empno;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 156 | 4 |
| 1 | SORT ORDER BY | | 13 | 156 | 4 |
| 2 | TABLE ACCESS FULL| EMP | 13 | 156 | 3 |
-----------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- 'PLAN_TABLE' is old version
- cpu costing is off (consider enabling it)
13 rows selected.
|
|
|
|
|
|
| Re: Force the optimizer to use the primary key index [message #413169 is a reply to message #412422] |
Tue, 14 July 2009 13:22 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
| Quote: | To be honest, now I come to try it, I can't reproduce it either.
Can anyone else shed any light on this?
|
It says what it is going to do in the Oracle Docs I posted earlier - so it is entirely expected behaviour in my book.
| Quote: | Oracle performs a full table scan on the employees table to execute this statement, even if there is an index on the last_name column that is made available by the condition in the WHERE clause.
|
Not all hints are just hints. Some really do act as "compiler directives".
[Updated on: Tue, 14 July 2009 13:25] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Force the optimizer to use the primary key index [message #413505 is a reply to message #412528] |
Thu, 16 July 2009 02:51 |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
If you want to skip index concate null.
But as you can see cost is higher when it access full table scan
create table test1607 as
select level l from dual connect by level <=100000;
alter table test1607 add constraint p_test1607 primary key (l);
explain plan for
select * from test1607 order by l;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84140 | 1068K| 212 |
| 1 | INDEX FULL SCAN | P_TEST1607 | 84140 | 1068K| 212 |
---------------------------------------------------------------
explain plan for
select * from test1607 order by l||null;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84140 | 1068K| 444 |
| 1 | SORT ORDER BY | | 84140 | 1068K| 444 |
| 2 | TABLE ACCESS FULL| TEST1607 | 84140 | 1068K| 36 |
Regards,
Prachi
|
|
|
|
| Re: Force the optimizer to use the primary key index [message #413537 is a reply to message #413505] |
Thu, 16 July 2009 05:18 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
It seems that I was categorically wrong about this.
Metalink Note 69992.1
| Quote: | Background information on hints
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Correctly defined hints are DIRECTIVES to the optimizer to use a particular
access method or access type. They should not be ignored and should be
obeyed at all times by the optimizer.
|
@Prachij - I think you might have missed the point of the post.
|
|
|
|
| Re: Force the optimizer to use the primary key index [message #413554 is a reply to message #413537] |
Thu, 16 July 2009 07:05 |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
| Quote: |
1. How would you force the optimizer to use the primary key index when querying table SCOTT.EMP(Assume the table is available and EMPNO is the primary key)
2. If the query plan is using the index how would you for the optimizer not to use the index.
|
Sir, that was my answer for OP's query...
What I understand reading doc is that Full table scan is not always bad. Index is preferred when selecting 4%-25% of rows (though there is no magical number!) .Its for the optimizer decide
(if its a Cost based optimizer) to decide whether to use or not to use index...
What I understood is its not always that index should be used.
Ofcourse, if there are indexes other than index on primary key, and
if the index is being skipped and optimizer uses other index instead then we can force the optimizer to use index on primary key by using INDEX hint. If version 9i or below then one can see the plan using rule hint (bcoz using rule hint forces the optimizer to use the index).
If statistics are up to date, then I believe Optimizer will use the index on PK.
Please provide your valuable suggesion.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
