InStr cannot use a variable in a parameter to create a function index using the return value [to be summarized]__ function

InStr cannot use a variable in a parameter to create a function index to use the return value
When the table content has been set, the input value is fixed by the output value obtained by the function. Input value cannot be a variable

========================

How does the InStr function create a function index?

Create index index name on table name (InStr (individual parameters))

Ask

CREATE INDEX custname_test on TEST (INSTR (Cust_name, '??? '), the key is ' ... ' Part is a variable, may enter John, also may enter the dick, I constructs the index to use what value.

Reply

This is not a fixed value can not be confused.

===================

How the InStr function establishes the function index ...

CREATE TABLE Test (name VARCHAR2 (30));

INSERT into test values (' I love Oracle ');
INSERT into test values (' I love Oracle ');
INSERT into test values (' I love Oracle ');
INSERT into test values (' I hate Oracle ');
INSERT into test values (' I hate Oracle ');
INSERT into test values (' I hate Oracle ');

CREATE INDEX idx_test on test (InStr (Name, ' h '));

Select/*+index (test) */* from Test where InStr (name, ' h ') = 2;

SELECT STATEMENT, GOAL = CHOOSE consumption = 1 cardinality = 1 bytes =17
TABLE ACCESS by INDEX ROWID object owner =yaoyp Object Name =test = 1 cardinality = 1 bytes =17
INDEX RANGE SCAN Object owner =yaoyp Object name =idx_test consumption = 1 cardinality =1

I want to use InStr instead of like, so I can't make a string to include, so how do I build the index,
For example, the above example, I sometimes look for the ' h ' sometimes looking for ' C ', the string to contain the uncertainty of this situation.

That's not going to work. You think about the structure of the index and you know it's not going to be like you.
If you want to search Full-text, Oracle has tools of its own, as if Oracle text

=======================

create index idx_lot_id on rpt_lotinfo a inner join br>                        (select a.lot_id                         from rpt_lotinfo a,rpt _wipstep b                       where a.lot_id = b.lotnum                       group by  a.lot_id)  b   (InStr (a.lot_id,b.lot_id)); Can I ask if this index can be written in this way?

is useful to me [0] throw a brick [0] quote | report | Admin

Jiewo Boat Grade:

#9 Score: 0 back to: 2009-10-21 12:35:15 estimate the index type of landlord plus is wrong. You can try: 1, function Index. 2, I give a (like 10g). Create INDEX mytext_id_x on MyText (thetext) Indextype is Ctxsys. context; SELECT * from MyText where contains (thetext, ' I ') >0;