FAQ
I have the following statement that I would like to create a function-based
index for:

SELECT MIN(tran_dt)
FROM ach_tran
WHERE cnfr_no = :b1

I'd like the index to include columns cnfr_no and tran_dt (in that order).
The examples I've seen are
just single-column indexes. Is it possible to have this additional
column in a function-based index that
is not involved in the actual function? If yes, what would the index
creation statement look like?

This is version 9.2.0.1 on Sun Solaris 2.6.

Thanks in advance for any assistance.

Cherie Machler
Gelco DBA

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Cherie_Machler_at_gelco.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • Babu Nagarajan at Dec 11, 2002 at 10:34 pm
    why would you want to create function based index on a column that you are
    not using in the where clause.

    for your sql statement a index on cnfr_no would work great...

    babu
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, December 11, 2002 4:09 PM
    I have the following statement that I would like to create a
    function-based
    index for:

    SELECT MIN(tran_dt)
    FROM ach_tran
    WHERE cnfr_no = :b1


    I'd like the index to include columns cnfr_no and tran_dt (in that order).
    The examples I've seen are
    just single-column indexes. Is it possible to have this additional
    column in a function-based index that
    is not involved in the actual function? If yes, what would the index
    creation statement look like?

    This is version 9.2.0.1 on Sun Solaris 2.6.


    Thanks in advance for any assistance.

    Cherie Machler
    Gelco DBA


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Cherie_Machler_at_gelco.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Babu Nagarajan
    INET: orclbabu_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Whittle Jerome Contr NCI at Dec 11, 2002 at 10:49 pm
    Cherie,

    AFAIK, a plain old index works just fine with Min and Max functions. However, you might need tran_dt first in the index or have its own index.

    Jerry Whittle
    ASIFICS DBA

    NCI Information Systems Inc.
    jerome.whittle_at_scott.af.mil
    618-622-4145
    -----Original Message-----
    From: Cherie_Machler_at_gelco.com [SMTP:Cherie_Machler_at_gelco.com]

    I have the following statement that I would like to create a function-based
    index for:

    SELECT MIN(tran_dt)
    FROM ach_tran
    WHERE cnfr_no = :b1

    I'd like the index to include columns cnfr_no and tran_dt (in that order).
    The examples I've seen are
    just single-column indexes. Is it possible to have this additional
    column in a function-based index that
    is not involved in the actual function? If yes, what would the index
    creation statement look like?

    This is version 9.2.0.1 on Sun Solaris 2.6.

    Thanks in advance for any assistance.

    Cherie Machler
    Gelco DBA
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Whittle Jerome Contr NCI
    INET: Jerome.Whittle_at_scott.af.mil

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Yechiel Adar at Dec 12, 2002 at 8:28 am
    I created a test table and build an index on columns t1 and t2.

    Explain plan for: select min(t1) from test where t2=4;

    was resolved using the index only so I think that will be very efficient.

    Yechiel Adar
    Mehish
    ----- Original Message -----
    To: Multiple recipients of list ORACLE-L
    Sent: Thursday, December 12, 2002 12:34 AM
    why would you want to create function based index on a column that you are
    not using in the where clause.

    for your sql statement a index on cnfr_no would work great...

    babu
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, December 11, 2002 4:09 PM

    I have the following statement that I would like to create a
    function-based
    index for:

    SELECT MIN(tran_dt)
    FROM ach_tran
    WHERE cnfr_no = :b1


    I'd like the index to include columns cnfr_no and tran_dt (in that
    order).
    The examples I've seen are
    just single-column indexes. Is it possible to have this additional
    column in a function-based index that
    is not involved in the actual function? If yes, what would the index
    creation statement look like?

    This is version 9.2.0.1 on Sun Solaris 2.6.


    Thanks in advance for any assistance.

    Cherie Machler
    Gelco DBA


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Cherie_Machler_at_gelco.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Babu Nagarajan
    INET: orclbabu_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Yechiel Adar
    INET: adar76_at_inter.net.il

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jamadagni, Rajendra at Dec 12, 2002 at 1:18 pm
    Cherie,

    you could you a MV (with query_rewrite) ro do what you want ... make sure
    you use refresh on commit ... FBI won't be of much use in this scenario ...

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    -----Original Message-----
    Sent: 11 December 2002 21:09
    To: Multiple recipients of list ORACLE-L

    I have the following statement that I would like to create a function-based
    index for:

    SELECT MIN(tran_dt)
    FROM ach_tran
    WHERE cnfr_no = :b1

    I'd like the index to include columns cnfr_no and tran_dt (in that order).
    The examples I've seen are
    just single-column indexes. Is it possible to have this additional
    column in a function-based index that
    is not involved in the actual function? If yes, what would the index
    creation statement look like?

    This is version 9.2.0.1 on Sun Solaris 2.6.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jamadagni, Rajendra
    INET: Rajendra.Jamadagni_at_espn.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    text/plain attachment: ESPN_Disclaimer.txt

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 11, '02 at 9:09p
activeDec 12, '02 at 1:18p
posts5
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase