Re: How to pass bind variable value into a view
Date: Sat, 10 Oct 2009 10:35:44 +0200
> On Oct 8, 9:58 pm, Maxim Demenko <mdeme..._at_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
> select /*+HINT_NAME*/
> as select col1,col2 from t1 where col3=?
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>
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
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