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:[EMAIL PROTECTED]] > 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:[EMAIL PROTECTED]] > 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:[EMAIL PROTECTED]] > 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
