Re: indexing expressions instead of columns / clipper style indexes

In article <6hvq7n$vb8$1_at_nnrp1.dejanews.com>,   ssharma_at_clearnet.com wrote:
>
> Hello
> Is this possible directly in Oracle 7.3?
>

I should have been more explicit in this question, here's a second attempt.

as an example
select emp_id from all_employees where :emp1 = emp_number +1 and  lower(substr(emp_name,10,10) ) like 'steve%' ;

as I understand it, as soon as one performs any calculation using the column names emp_name and emp_number ORACLE refuses to use indexes on those columns. to have ORACLE use indexes One must rewrite the using only bare column names or write the calculation results to a new column, index this new column and this column's bare name in the query.

In the above instance rewriting 'where :emp1 -1 = emp_number' may cause ORACLE to use an index on emp_number. AFAIK no amount of manipulation would allow that second expression to be transformed this way.

In Clipper we used to create indexes based on expressions of arbitrary complexity. bareness of column names not necessary. When Clipper saw a SEEK using that exact expression it used that index. In the example above, one could build a Clipper index that took the sin, cos, abs, substr, lower, upper, sqrt, exp, rot64, yada yada of a column, and that index would be used if a 'query' required it.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Mon Apr 27 1998 - 09:54:53 CDT