Full table scan

[Aljaz]

I would like to know why do I get full table scans, yet better I would like to know how not to get full table scans.

Here is the query

select
PROP_VALUE
from
RIS_APPA
where
upper(APPNAME) = upper('RIS4I_GOST') and
upper(PROP_SECTION) = upper('INIT') and
upper(PROP_IDENT) = upper('DEFAULT_TREE') and
USR_NAME = '%';

Primary key is set to columns APPNAME, PROP_SECTION, PROP_IDENT, USR_NAME. There are no other indexes on the table.

select COLUMN_NAME
from user_ind_columns
where index_name='RIS_APPA_PK';

COLUMN_NAME
------------------------
APPNAME
PROP_IDENT
PROP_SECTION
USR_NAME

And here is what I get from plan_table

Query Plan
---------------------------------
SELECT STATEMENT Cost =
TABLE ACCESS FULL RIS_APPA


Thank you all in advance

Aljaz

[DaPi]

The index can not be used if the column is the argument of a function - all the UPPER's in your case.

Do you need them? Is the data really a random mix of upper & lower case? Frankly I doubt it.

If you must, then look at "Function Based Indexes" in the doc.

[Aljaz]

OK I have elimenated uppers and I like the explain plan.

Thank you DaPi.