RE: question about index

From: Asif Momen <asif_oracle_at_yahoo.com>
Date: Mon, 19 May 2008 13:38:28 -0700 (PDT)
Message-ID: <4291.54462.qm@web56601.mail.re3.yahoo.com>

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_at_dla.mil> wrote: Function based index......but I am not sure how you would use it.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of TESTAJ3_at_nationwide.com
Sent: Monday, May 19, 2008 1:55 PM
To: oracle-l_at_freelists.org
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        

--

http://www.freelists.org/webpage/oracle-l Received on Mon May 19 2008 - 15:38:28 CDT