RE: Function based indexes

[Hallas John]

Sergey,   I did a quick test which indicates that no special path is mentioned when using a FBI, instead the execution plan will show that an index is being used, which it would not do if a FBI had not been set up. The example below shows what I mean   HTH   John     SQL> create index john_idx1 on john(spid_type); SQL> select spid_type from john where upper(substr(spid_type,2,1)) = 'Y' SQL> / SPID_TYPE ----------------------------------- Symphony Symphony Symphony Symphony Symphony Symphony Symphony Symphony   Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'JOHN'   SQL> drop index john_idx1; Index dropped. SQL> create index john_idx1 on john(upper(substr(spid_type,2,1))); Index created. SQL> analyze table john compute statistics; Table analyzed.   SQL> select spid_type from john where upper(substr(spid_type,2,1)) = 'Y'; SPID_TYPE ----------------------------------- Symphony Symphony Symphony Symphony Symphony Symphony Symphony Symphony 8 rows selected.   Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=7 Card=96 Bytes= 1344) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'JOHN' (Cost=7 Card=96 By                  *************************FBI in use**************************** tes=1344) 2 1 INDEX (RANGE SCAN) OF 'JOHN_IDX1' (NON-UNIQUE) (Cost=1 C                      *************************FBI in use**************************** ard=96)   -----Original Message----- From: Babich , Sergey [mailto:[EMAIL PROTECTED]] Sent: 26 February 2002 20:51 To: Multiple recipients of list ORACLE-L Subject: Function based indexes Hi, everyone, This may seem very simple to you, but what's the best way to see if a fresh FBI (sorry!) is used during the execution? Are they reported in the same manner to the SQL trace as other ones? Regards, Sergey ========================================================= This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. =========================================================