RE: question about index

Hi,

Its a function based index and unfortunately, it will never be used. Below is a 
detailed test case:


SQL> desc t
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- 
--------------------------------
 ID                                                             NUMBER
 NAME                                                           VARCHAR2(4000)

SQL> create index t_idx1 on t('garbage');

Index created.
    
SQL> set line 10000
SQL> exec dbms_stats.gather_table_stats( user, 't');

PL/SQL procedure successfully completed.

SQL> select index_name, INDEX_TYPE, num_rows from user_indexes where table_name 
= 'T';

INDEX_NAME                     INDEX_TYPE                    NUM_ROWS
------------------------------ --------------------------- ----------
T_IDX1                         FUNCTION-BASED NORMAL             6000


SQL> select index_name, column_expression from user_ind_expressions where 
table_name = 'T';

INDEX_NAME                     COLUMN_EXPRESSION
------------------------------ 
-----------------------------------------------------------------
-
T_IDX1                         'garbage'


Regards

Asif Momen
http://momendba.blogspot.com

"Terrian, Thomas J Mr CTR DLA J6DIB" <Tom.Terrian.ctr@xxxxxxx> wrote: Function 
based index......but I am not sure how you would use it. 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
TESTAJ3@xxxxxxxxxxxxxx
Sent: Monday, May 19, 2008 1:55 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: question about index


Ok i guess i'm having a brain fart, someone asked me about this and for
the life of me i can't figure it out.  The 2nd index, what exactly is it
doing with single quotes around the column name?? 

SQL> create table z1(x1 number, x2 date); 

Table created. 

SQL> CREATE INDEX X1 ON Z1 (X1); 

Index created. 

SQL> CREATE INDEX X2 ON Z1 ('X1'); 

Index created. 


Thanks,  Joe

---------------------------------------
You can have it: Fast, Right or Cheap, pick 2 of the 3.
Fast + Right is Expensive
Fast + Cheap will be incorrect.
Right + Cheap will take a while.

--
http://www.freelists.org/webpage/oracle-l

Other related posts: