Re: Function-Based Index not working

There are quite a few restrictions on function-based indexes.

The Oracle SQL guide lists them all. Have you checked to ensure that you're following all the rules?

Jared

On Saturday 31 August 2002 07:53, Marul Mehta wrote:
> Even after giving the hint its not working.
> I guess you can't have IS clause and Like with function-based index.
>
> Marul.
> ----- Original Message -----
> From: Naveen Nahata
> To: Multiple recipients of list ORACLE-L
> Sent: Saturday, August 31, 2002 7:28 PM
> Subject: RE: Function-Based Index not working
>
>
> I think everythying is fine. Did you try index hint? try that and see.
>
> if that also doesn't work, then either we are missing something or the
> Optimizer thinks so
>
> Naveen
> -----Original Message-----
> From: Marul Mehta [mailto:marul_at_zycus.com]
> Sent: Saturday, August 31, 2002 6:33 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Function-Based Index not working
>
>
> Hi Naveen,
> Thanks a lot for the efforts you are putting in for me for such a
> simple problem, but unfortunately, for me all the tips and tricks are not
> solving the problem. Now these are my current statistics :-
>
> + alter session set QUERY_REWRITE_ENABLED=TRUE;
> + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> + alter session set optimizer_mode=FIRST_ROWS;
> + alter session set DB_FILE_MULTIBLOCK_READ_COUNT=1;
>
> This procedure writes 180,000 records in employeees table
> + execute bulk_insert
>
> Analyzing table and rebuilding index (though its not necessary)
> + analyze table employees compute statistics;
> + alter index upper_ix rebuild;
>
> Making autotrace on
> + set autotrace traceonly explain
>
> Fired the query:
> SELECT last_name FROM employees WHERE UPPER(last_name) IS NOT NULL
> ORDER BY UPPER(last_name); Elapsed: 00:00:00.00
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=4001 Bytes=2
> 0005)
>
> 1 0 SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005)
> 2 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=38 Card=4001 By
> tes=20005)
>
> Any clues what is happening? Should I insert more records in the table.
>
> TIA,
> Marul.
>
>
>
>
>
>
> ----- Original Message -----
> From: Naveen Nahata
> To: Multiple recipients of list ORACLE-L
> Sent: Saturday, August 31, 2002 4:58 PM
> Subject: RE: Function-Based Index not working
>
>
> See the table's size is very small. Till it atleast 2 times the value
> of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use index.
>
> Set the value of DB_FILE_MULTIBLOCK_READ_COUNT to one.
>
> Insert lots of values in the table. You can make a procedure to
> insert random characters into the table, and then put it in a big loop.
> Analyze table and thn run the same query.
>
> It should work
>
> naveen
>
> -----Original Message-----
> From: Marul Mehta [mailto:marul_at_zycus.com]
> Sent: Saturday, August 31, 2002 4:03 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Function-Based Index not working
>
>
> Thanks a lot Naveen,
>
> Even after executing the following the execution plan shows full
> table scan :-
>
> + alter session set QUERY_REWRITE_ENABLED=TRUE;
> + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> + alter session set optimizer_mode=FIRST_ROWS;
> + Insert into employees values('A');
> + Insert into employees values('B');
> + analyze table employees compute statistics;
> +
> select last_name
> FROM employees WHERE UPPER(last_name) IS NOT NULL
> ORDER BY UPPER(last_name); 2 3
> Elapsed: 00:00:00.00
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=2
> Bytes=2 )
>
> 1 0 SORT (ORDER BY) (Cost=3 Card=2 Bytes=2)
> 2 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=2
> Bytes= 2)
>
> Even after using the hint no change in the plan :-
> + select /* INDEX employees(upper_ix) */ last_name FROM employees
> WHERE UPPER(last_name) IS NOT NULL;
>
>
> Please tell me what else should I do to make this query use the
> index which is created.
>
>
> TIA,
> Marul.
>
> ----- Original Message -----
> From: Naveen Nahata
> To: Multiple recipients of list ORACLE-L
> Sent: Saturday, August 31, 2002 3:03 PM
> Subject: RE: Function-Based Index not working
>
>
> Marul,
>
> 1. you don't have table analyzed in which case Rule based
> optimizer will be used. CBO is used if atleast one of the tables in the
> query is ANALYZED 2. There is no data in your table. Optimizer goes for a
> full tablescan if it thinks that it will be moer advisable to do a full
> table scan. e.g. You will not use the INDEX if your book has only one page.
>
> The decision of going for a full tablescan is based on
> DB_BLOCK_SIZE * DB_FILE_MULTI_BLOCK_READCOUNT, which tells how much data
> Oracle fetches at one time. If your entire table can be fetched in atleast
> 2 fetches, full table scan will be done instead of INDEX scan, to avoid
> doubling of work.
>
>
> Naveen
> -----Original Message-----
> From: Marul Mehta [mailto:marul_at_zycus.com]
> Sent: Saturday, August 31, 2002 2:18 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Function-Based Index not working
>
>
> Hi,
>
> Can you please help me out in solving this weird problem of
> funcation-based index not being used when I query the table. This is the
> comand I fired and the result it returned me.
>
> 1. SQL> create table employees (last_name varchar2(20));
> Table created.
>
> 2. SQL> CREATE INDEX upper_ix ON employees (UPPER(last_name));
> Index created.
>
> Made the autotrace on and than:-
>
> 3. SELECT last_name FROM employees WHERE UPPER(last_name) IS
> NOT NULL ORDER BY UPPER(last_name); no rows selected.
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 SORT (ORDER BY)
> 2 1 TABLE ACCESS (FULL) OF 'EMPLOYEES'
>
>
> I fired without order by clause also but no use.
>
> Now can any body please let tell me why this Oracle is having a
> full scan of the employee table.
>
> TIA,
> Marul.

Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description: 
----------------------------------------

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).