FAQ
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.

Search Discussions

  • Terrian, Thomas J Mr CTR DLA J6DIB at May 19, 2008 at 6:39 pm
    Function based index......but I am not sure how you would use it.

    -----Original Message-----
    From: 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
  • TESTAJ3_at_nationwide.com at May 19, 2008 at 6:40 pm
    thats what was confusing me, if it had a function involved, i'd understand
    but this just seems stupid to me :)

    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.

    "Terrian, Thomas J Mr CTR DLA J6DIB"


    05/19/2008 02:39 PM

    From
    "Terrian, Thomas J Mr CTR DLA J6DIB"
    To,
    cc

    Subject
    RE: question about index

    Function based index......but I am not sure how you would use it.

    -----Original Message-----
    From: 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
  • Mark Brinsmead at May 20, 2008 at 2:56 am
    Your confusion is understandable.

    The problem, I think, is that "Function Based Index" is an unfortunate
    misnomer. If Oracle had called this an "Expression Based Index"
    (which is what it really is), these would probably be much more
    intuitive.

    'X1' is an expression with a constant value. If you build an
    "Expression Based Index" on this expression, what would you expect to
    get? :-)

    I'm pretty sure that there is no way you will ever be able to get the
    CBO to use this index for any purpose. Well, except maybe something
    like:

    SELECT COUNT('X1') FROM Z1;

    or perhaps one or two other similarly useless queries. :-)
    On 5/19/08, TESTAJ3_at_nationwide.com wrote:
    thats what was confusing me, if it had a function involved, i'd understand
    but this just seems stupid to me :)

    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.



    "Terrian, Thomas J Mr CTR DLA J6DIB"





    05/19/2008 02:39 PM

    From
    "Terrian, Thomas J Mr CTR DLA J6DIB"
    To
    ,
    cc

    Subject
    RE: question about index






    Function based index......but I am not sure how you would use it.

    -----Original Message-----
    From: 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.

    --
    Cheers,
    -- Mark Brinsmead
    Senior DBA,
    The Pythian Group
    http://www.pythian.com/blogs
    --
    http://www.freelists.org/webpage/oracle-l
  • Asif Momen at May 19, 2008 at 8:38 pm
    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" wrote: Function based index......but I am not sure how you would use it.

    -----Original Message-----
    From: 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
  • Pratap Singh (c) at May 19, 2008 at 8:51 pm
    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 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" wrote:

    Function based index......but I am not sure how you would use it.


    -----Original Message-----
    From: 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
  • David Aldridge at May 19, 2008 at 8:49 pm
    It's just an index built on the literal string 'X1' -- yes, very odd. Developer got wrong index-creation syntax and forgot to drop it afterwards?

    Original Message ----
    From: "TESTAJ3_at_nationwide.com"
    To: "Terrian, Thomas J Mr CTR DLA J6DIB"
    Cc: oracle-l_at_freelists.org
    Sent: Monday, May 19, 2008 2:40:49 PM
    Subject: RE: question about index

    thats what was confusing me, if it had a function involved, i'd understand but this just seems stupid to me :)

    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.

    "Terrian, Thomas J Mr CTR DLA J6DIB"


    05/19/2008 02:39 PM From"Terrian, Thomas J Mr CTR DLA J6DIB"
    To,
    cc
    SubjectRE: question about index

    Function based index......but I am not sure how you would use it.

    -----Original Message-----
    From: 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
  • James Howerton at May 19, 2008 at 10:01 pm
    DBA's,

    Any idea why jdbc connection pools would stop working from occasionally to three times per day on an 11G db???

    TIA

    ...JIM...
  • GovindanK at May 19, 2008 at 10:44 pm
    Can you provide more info? Which driver are you using? The one
    for 11g? Also, is this a New 11g db or one migrated from 10g or
    lower? Is the connection pooling mechanism written well to reuse
    connections?. Is this the first time the connection pool being
    used or you started getting this problem after migrating? If that
    be the case, i would suspect the driver. Does the log show
    anything unusual?

    Provide these info and we will go from there.

    Govindan
    On Mon, 19 May 2008 17:01:19 -0500, [1]"James Howerton"
    said:
    DBA's,

    Any idea why jdbc connection pools would stop working from
    occasionally
    to three times per day on an 11G db???

    TIA
    ...JIM...


    --
    [2]www.freelists.org/webpage/oracle-l
    References

    mailto:jhowerton_at_uabmc.edu
    file://localhost/tmp/linkstmp/www.freelists.org/webpage/oracle
  • TESTAJ3_at_nationwide.com at May 20, 2008 at 9:42 am
    your idea is probably the likely candidate, thanks everyone.

    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.

    David Aldridge


    Sent by: oracle-l-bounce_at_freelists.org

    05/19/2008 04:49 PM
    Please respond to david_at_david-aldridge.com

    From
    David Aldridge
    To
    TESTAJ3_at_nationwide.com, "Terrian, Thomas J Mr CTR DLA J6DIB"

    cc
    oracle-l_at_freelists.org
    Subject
    Re: question about index

    It's just an index built on the literal string 'X1' -- yes, very odd.
    Developer got wrong index-creation syntax and forgot to drop it
    afterwards?

    Original Message ----
    From: "TESTAJ3_at_nationwide.com"
    To: "Terrian, Thomas J Mr CTR DLA J6DIB"
    Cc: oracle-l_at_freelists.org
    Sent: Monday, May 19, 2008 2:40:49 PM
    Subject: RE: question about index

    thats what was confusing me, if it had a function involved, i'd understand
    but this just seems stupid to me :)

    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.

    "Terrian, Thomas J Mr CTR DLA J6DIB"


    05/19/2008 02:39 PM

    From
    "Terrian, Thomas J Mr CTR DLA J6DIB"
    To,
    cc

    Subject
    RE: question about index

    Function based index......but I am not sure how you would use it.

    -----Original Message-----
    From: 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
  • Sylvester, Peter A. at May 19, 2008 at 10:38 pm
    What is meant by connection pools "stop working"?
    Some connection pools will automatically close down connections during
    idle periods.


    If you are getting hard errors, many pools give you the ability to
    specify SQL which is run to test out the connection (i.e. "select 1
    from dual") before letting the application use it. This can usually get
    around issues associated DB restarts etc.


    --Peter

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 19, '08 at 5:54p
activeMay 20, '08 at 9:42a
posts11
users9
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase