Re: index usage---

It certainly looks as though it should use it. I am assuming 36000 rows is a small % of the total size of the table. if it is larger than about 4 or 5% then I believe the CBO will choose a FTS.

If it is a small proportion

1. Is the optimiser mode set to choose? If it is set to rule the function based index cannot be used.
2. you might experiment with db_multiblock_read_count and block size in a test system. the higher these are set the more the CBO is biased towards FTS.
3. try using an index hint eg

select /*+ index (test.files test.filesname) */ FILEID,NODEID,ONAME,SSIZE,PSIZE,DATUM from FILES where upper(oname) like 'TEST%';

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Andre Weber" <WeberAndre_at_t-online.de> wrote in message
news:9c76lb$685$03$1_at_news.t-online.com...


> Ok.. here he is:-)

> the table has the following structure ...

>

> > descr files;

> Name                                      Null?    Typ

>  ----------------------------------------- -------- ----------------------

 --


> ----

>  FILEID                                    NOT NULL NUMBER(12) (primary

 key)


>  NODEID                                    NOT NULL NUMBER(12)

>  ONAME                                              VARCHAR2(256)

>  SSIZE                                              NUMBER(38)

>  PSIZE                                              NUMBER(38)

>  DATUM                                              NUMBER(12)

>  DESCR                                              CLOB

>  DATAPOOL                                           BLOB

>

> the index is created with the following statement:

> create index test.filesname on test.files (upper(oname));

> ok.

>

> the plan for the simple select is:

>

>  select FILEID,NODEID,ONAME,SSIZE,PSIZE,DATUM  from FILES where

 upper(oname)


> like 'TEST%';

> (DESCR and DATAPOOL -- will i select only if it is required)

>

> ---->

>

> EXPLAIN PLAN

>     INTO PLAN_TABLE FOR

>          select FILEID,NODEID,ONAME,SSIZE,PSIZE,DATUM  from FILES where

> upper(oname) like 'TEST%';

>

> and the result is ....

>

> Plan Table

> --------------------------------------------------------------------------

 --


> ----

> | Operation                                |  Name    |  Rows | Bytes|

 Cost


> | Pstart| Pstop |

> --------------------------------------------------------------------------

 --


> ----

> | SELECT STATEMENT         |               |    36K|    1M|    637 |

 


> |

> |    TABLE ACCESS FULL       |FILES     |    36K|    1M|    637 |       |

> |

> --------------------------------------------------------------------------

 --


> ----

>

> so i hope thats all?

>

>

> --------------------------------------------------------------------------

 --


> ------------------------------------

> an other select useing the column nodeid as query criteria (also indexed,

> but no function)

> results in the following expected execution plan ---

>

> EXPLAIN PLAN

>     INTO PLAN_TABLE FOR

>          select FILEID,NODEID,ONAME,SSIZE,PSIZE,DATUM  from FILES where

> NODEID=353454;

>

> Plantable

> --------------------------------------------------------------------------

 --


> ----

> | Operation                                                   |  Name    |

> Rows | Bytes|  Cost  | Pstart| Pstop |

> --------------------------------------------------------------------------

 --


> ----

> | SELECT STATEMENT                             |          |    17 |  731 |

 4 |       |       |


> |  TABLE ACCESS BY INDEX ROW        |FILES     |    17 |  731 |      4 |

> |       |

> |   INDEX RANGE SCAN                          |FILES_NOD |    17 |      |

> 3 |       |       |

> --------------------------------------------------------------------------

 --


> ----

>

>

>