RE: question about index

From: Pratap Singh (c) <psingh_at_vmware.com>
Date: Mon, 19 May 2008 13:51:41 -0700
Message-ID: <C388F0160F8F6A4DA25614D019619C8F30A096@PA-EXCH21.vmware.com>

If you put " in place of ' it will work correctly. I ran into same issues sometime back.
In the script generated by Query advisor, in text mode some places it put ' in place ".  

Thanks,
PB Singh

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

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

---

PB Singh
DW Architect and Sr Data Modeler
VMware    

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Asif Momen
Sent: Monday, May 19, 2008 1:38 PM
To: Tom.Terrian.ctr_at_dla.mil; TESTAJ3_at_nationwide.com; oracle-l_at_freelists.org Subject: 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_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:51:41 CDT