Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feed.tweaknews.nl!194.109.133.84.MISMATCH!newsfeed.xs4all.nl!newsfeed5.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail
Date: Sat, 10 Oct 2009 10:35:44 +0200
From: Shakespeare <whatsin@xs4all.nl>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.server
Subject: Re: How to pass bind variable value into a view
References: <6af2dd8d-422b-43e1-ab51-99f76861b827@31g2000vbf.googlegroups.com> 	<4ace4b1b$0$83245$e4fe514c@news.xs4all.nl> <4ACE5291.7000808@gmail.com> <cd5059ad-b0db-489a-9d2e-ad502f97748c@x37g2000yqj.googlegroups.com>
In-Reply-To: <cd5059ad-b0db-489a-9d2e-ad502f97748c@x37g2000yqj.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 58
Message-ID: <4ad04765$0$83243$e4fe514c@news.xs4all.nl>
NNTP-Posting-Host: 82.95.215.210
X-Trace: 1255163749 news.xs4all.nl 83243 [::ffff:82.95.215.210]:51237
X-Complaints-To: abuse@xs4all.nl
Xref:  news.cambrium.nl

UXDBA schreef:
> On Oct 8, 9:58 pm, Maxim Demenko <mdeme...@gmail.com> wrote:
>>   > You could hint your query to use the index on t2.col2. There is a
>> way to
>>
>>> 'push' the hint to the view, can't exactly remember how though.
>>> Shakespeare
>> Me too, but i could remember, where i've seen it ;-)http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsr...
>>
>> Best regards
>>
>> Maxim
> 
> I tried hint. But its not helping in my case.
> I can do hint this way:
> 
> create or replace view V1
> (col1,col2)
> as
> select /*+HINT_NAME*/
> as select col1,col2 from t1 where col3=?
> 
> regards.

No. The hint should not be in your create statement, but in your select 
from the view, so something like (have no database to test right now, 
just cleared my development system):

SELECT /*+ INDEX(v1.t1 <your_index_name_on_table1>) */  <your columns here>
   FROM v1
where ...

etc..

In this way you tell your select statement to 'push' the hint in the 
select statement to v1.t1

By the way: note the space between /*+ and INDEX. It has to be there, or 
else your hint will be ignored. Furthermore, if you alias your viewname 
in the query, you should use the same alias, so not the actual viewname 
in your hint, like

SELECT /*+ INDEX(my_alias.t1 <your_index_name_on_table1>) */  <your 
columns here>
   FROM v1 my_alias
where ...


You could experiment with this a little.

I've used this in the past to optimize queries for a program we had no 
control over. Views were dynamically generated by the program, so all we 
could do was hint the queries this way. It helped.

Shakespeare