RE: question about index
- From: "Pratap Singh (c)" <psingh@xxxxxxxxxx>
- To: <asif_oracle@xxxxxxxxx>, <Tom.Terrian.ctr@xxxxxxx>, <TESTAJ3@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 19 May 2008 13:51:41 -0700
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Asif Momen
Sent: Monday, May 19, 2008 1:38 PM
To: Tom.Terrian.ctr@xxxxxxx; TESTAJ3@xxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
