| Using Hints in View Definitions - Not woking [message #18417] |
Wed, 30 January 2002 09:18  |
Greg Skakun
Messages: 10
Registered: January 2000
|
Junior Member |
|
|
Oracle 8.1.7:
Table vendor has index I_vendor (vendor_code).
View v_test looks like:
create or replace view v_test as
select /*+ index(vendor I_vendor) */
* from vendor
/
Query:
Select * from v_test where vendor_code = 'XYZ'
/
Problem:
Full table scan is performed on table vendor instead of using index as expected.
Geez ....
I've done some net searching and see references to where hints are not used of the column name does not appear in the where clause of the sql statement, so (thinking I might trick the optmizer) I tried changing the view to look like:
create or replace view v_test as
select /*+ index(vendor I_vendor) */
* from vendor
where vendor_code = vendor_code
/
Still the same full table scan when executing the above query.
getting frustrated .....
Then tried changing the query to include the need hint:
Query:
Select /*+ index(vendor I_vendor) */
* from v_test where vendor_code = 'XYZ'
/
Full table scan again ...
Crap ....
Any ideas out there on hour to get the view to utilize the needed index ?
I can not simply execute the text in the view instead of referencing a view as this is a third party app and I can only control what the view looks like not the calling sql statements.
Oracle 8.1.7
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
