| outline issue [message #542551] |
Wed, 08 February 2012 01:08  |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
Set full access the table
SQL> create or replace OUTLINE
2 test_outline1 FOR CATEGORY cate_outline
3 ON select /*+ full(a)*/
4 * from tb_test a where Id =1
5 ;
Outline created.
SQL> Alter session set use_stored_outlines = True;
Session altered.
SQL> alter Session set USE_STORED_OUTLINES =cate_outline;
Session altered.
SQL> explain plan for
2 select * from tb_test a where Id =1;
Explained.
SQL> Select * From Table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3578346379
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 527 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_TEST | 1 | 527 | 2 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_TEST | 1 | | 1 (0)|
00:00:01 |
Dear all,
I have set the outline to full access the table, but there is index range scan,why?
|
|
|
|
|
|
| Re: outline issue [message #542557 is a reply to message #542554] |
Wed, 08 February 2012 01:32  |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
Thanks Michel,
How can i make the sql to use the sql plan which defined in outline?
select /*+ full(a)*/ * from tb_test a where Id =1
is the same as
select * from tb_test a where Id =1
|
|
|
|
|
|
| Re: outline issue [message #542579 is a reply to message #542557] |
Wed, 08 February 2012 02:10 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
There are mny ways to do this, but the easiest (in this particular case) would be to alter the index to make it invisible, then create the outline for the statement (without any hint), then make the index visible.
|
|
|
|
|
|
| Re: outline issue [message #542623 is a reply to message #542620] |
Wed, 08 February 2012 04:10 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:Thanks!
If you really want to say "thank you", write this up and put it on the blog, and be famous: I remember a few questions about how to create stored outlines with particular plans, and I think many people would find it useful. Of course, you'll have to generalise the solution and work up a few more examples.
Contribute! Cooperate!! Communicate!!!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
