Home » RDBMS Server » Server Administration » outline issue (11.2.0.1 windows xp)
outline issue [message #542551] Wed, 08 February 2012 01:08 Go to next message
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 #542554 is a reply to message #542551] Wed, 08 February 2012 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
An outline applies to query that has the SAME text.

Regards
Michel
Re: outline issue [message #542557 is a reply to message #542554] Wed, 08 February 2012 01:32 Go to previous messageGo to next message
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 #542567 is a reply to message #542557] Wed, 08 February 2012 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is the same as

Is it?
Not even the same number of characters!

Regards
Michel
Re: outline issue [message #542579 is a reply to message #542557] Wed, 08 February 2012 02:10 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #542620 is a reply to message #542579] Wed, 08 February 2012 04:00 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks!
Re: outline issue [message #542623 is a reply to message #542620] Wed, 08 February 2012 04:10 Go to previous message
John Watson
Messages: 8922
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!!!
Previous Topic: foreground process starts before PMON
Next Topic: duplicate issue about asm system
Goto Forum:
  


Current Time: Fri Mar 29 01:36:36 CDT 2024