Home » SQL & PL/SQL » SQL & PL/SQL » Using Hints in View Definitions - Not woking
Using Hints in View Definitions - Not woking [message #18417] Wed, 30 January 2002 09:18 Go to next message
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
Re: Using Hints in View Definitions - Not woking [message #18426 is a reply to message #18417] Wed, 30 January 2002 19:50 Go to previous messageGo to next message
dinakar shetty
Messages: 29
Registered: January 2002
Junior Member
HI
u dont need a index and trick the optimiser
if the index is there then it should take the index u query is a straight forward one

/*+ INDEX(INDEX_NAME) */
Re: Using Hints in View Definitions - Not woking [message #18459 is a reply to message #18417] Thu, 31 January 2002 11:37 Go to previous message
Mary Townsend
Messages: 1
Registered: January 2002
Junior Member
1. How many distinct vendors are there? If there are only a few vendors Oracle might be making the right decision in not using the index.

2. I assume you performed an analyze. If not Oracle can behave in unpredictable ways even with a hint.

Mary Townsend
Previous Topic: Problems when trying to use SYS_GUID()
Next Topic: training or student database?
Goto Forum:
  


Current Time: Sat Apr 04 23:31:11 CDT 2020