Re: How to pass bind variable value into a view

From: Shakespeare <>
Date: Sat, 10 Oct 2009 10:35:44 +0200
Message-ID: <4ad04765$0$83243$>

UXDBA schreef:
> On Oct 8, 9:58 pm, Maxim Demenko <> 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 ;-)
>> 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 ...


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 Received on Sat Oct 10 2009 - 03:35:44 CDT

Original text of this message