FAQ
Hey,

Anyone have any advice on indexing SYS tables? Specifically, I've been
experimenting with putting a reverse index on the TIMESTAMP# column of AUD$,
since almost all of my queries against the AUD$ views end with "ORDER BY
TIMESTAMP DESC".

In 8.1.7.4.0 test DBs on HP/UX, the index -- created under a non-SYS DBA
schema -- works great. I'm looking for pitfalls, but can't come up with any
of my own...

TIA!

Rich

--

Rich Jesse System/Database Administrator
Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.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

  • Naveen Nahata at Oct 1, 2002 at 3:48 pm
    Reverse key index won't help you in ORDER BY DESC, will it?

    AFAIK Reverse-key index will store 'Naveen' as 'neevaN' and 'Rich' as 'hciR'
    and so won't be helpful in ORDER BY DESC

    Regards
    Naveen

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 8:58 PM
    To: Multiple recipients of list ORACLE-L

    Hey,

    Anyone have any advice on indexing SYS tables? Specifically, I've been
    experimenting with putting a reverse index on the TIMESTAMP# column of AUD$,
    since almost all of my queries against the AUD$ views end with "ORDER BY
    TIMESTAMP DESC".

    In 8.1.7.4.0 test DBs on HP/UX, the index -- created under a non-SYS DBA
    schema -- works great. I'm looking for pitfalls, but can't come up with any
    of my own...

    TIA!

    Rich

    --

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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: Naveen Nahata
    INET: naveen_nahata_at_mindtree.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).
  • Igor Neyman at Oct 1, 2002 at 4:38 pm
    You don't need "reverse" index to do: "ORDER BY TIMSTAMP DESC". Regular
    index should do it, even if you want it "DESC".

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, October 01, 2002 11:28 AM
    Hey,

    Anyone have any advice on indexing SYS tables? Specifically, I've been
    experimenting with putting a reverse index on the TIMESTAMP# column of AUD$,
    since almost all of my queries against the AUD$ views end with "ORDER BY
    TIMESTAMP DESC".
    In 8.1.7.4.0 test DBs on HP/UX, the index -- created under a non-SYS DBA
    schema -- works great. I'm looking for pitfalls, but can't come up with any
    of my own...

    TIA!
    Rich

    --

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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: Igor Neyman
    INET: ineyman_at_perceptron.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).
  • Jesse, Rich at Oct 1, 2002 at 5:03 pm
    Yes, you are obviously correct. I really need to RTFM. Too many
    pots on the stove!

    Just a regular index, then. Any other input?

    Thx!
    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Naveen Nahata
    Sent: Tuesday, October 01, 2002 10:49 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Indexing SYS tables


    Reverse key index won't help you in ORDER BY DESC, will it?

    AFAIK Reverse-key index will store 'Naveen' as 'neevaN' and
    'Rich' as 'hciR'
    and so won't be helpful in ORDER BY DESC

    Regards
    Naveen

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 8:58 PM
    To: Multiple recipients of list ORACLE-L


    Hey,

    Anyone have any advice on indexing SYS tables? Specifically,
    I've been
    experimenting with putting a reverse index on the TIMESTAMP#
    column of AUD$,
    since almost all of my queries against the AUD$ views end
    with "ORDER BY
    TIMESTAMP DESC".
    In 8.1.7.4.0 test DBs on HP/UX, the index -- created under a
    non-SYS DBA
    schema -- works great. I'm looking for pitfalls, but can't
    come up with any
    of my own...

    TIA!
    Rich
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).
  • Mercadante, Thomas F at Oct 1, 2002 at 5:18 pm
    Rich,

    I think you're crazy adding indexes SYS tables. I would *never* add
    anything to sys objects. What are you going to do the first time something
    goes wrong and you call Oracle support and you "mention" you added an index
    and they say - hey, you're on your own! Not to mention that when you
    perform an upgrade, that this index may either get lost or be in the way.

    No, a totally bad idea.

    If anything, I would copy the audit records to a local table in your schema,
    and then index that baby anyway I'd like. That way, the system objects are
    the way that Oracle inteneded them to be.

    Just my 2 cents.

    Tom Mercadante
    Oracle Certified Professional

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 11:28 AM
    To: Multiple recipients of list ORACLE-L

    Hey,

    Anyone have any advice on indexing SYS tables? Specifically, I've been
    experimenting with putting a reverse index on the TIMESTAMP# column of AUD$,
    since almost all of my queries against the AUD$ views end with "ORDER BY
    TIMESTAMP DESC".

    In 8.1.7.4.0 test DBs on HP/UX, the index -- created under a non-SYS DBA
    schema -- works great. I'm looking for pitfalls, but can't come up with any
    of my own...

    TIA!

    Rich

    --

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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: Mercadante, Thomas F
    INET: NDATFM_at_labor.state.ny.us

    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).
  • Naveen Nahata at Oct 1, 2002 at 5:28 pm
    Again since the indexes store the row in ordered fashion, I guess a normal
    index should be able to do ORDER BY DESC by reading backwards

    Not sure though

    Regards
    Naveen

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 10:33 PM
    To: Multiple recipients of list ORACLE-L

    Yes, you are obviously correct. I really need to RTFM. Too many
    pots on the stove!

    Just a regular index, then. Any other input?

    Thx!
    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Naveen Nahata
    Sent: Tuesday, October 01, 2002 10:49 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Indexing SYS tables


    Reverse key index won't help you in ORDER BY DESC, will it?

    AFAIK Reverse-key index will store 'Naveen' as 'neevaN' and
    'Rich' as 'hciR'
    and so won't be helpful in ORDER BY DESC

    Regards
    Naveen

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 8:58 PM
    To: Multiple recipients of list ORACLE-L


    Hey,

    Anyone have any advice on indexing SYS tables? Specifically,
    I've been
    experimenting with putting a reverse index on the TIMESTAMP#
    column of AUD$,
    since almost all of my queries against the AUD$ views end
    with "ORDER BY
    TIMESTAMP DESC".
    In 8.1.7.4.0 test DBs on HP/UX, the index -- created under a
    non-SYS DBA
    schema -- works great. I'm looking for pitfalls, but can't
    come up with any
    of my own...

    TIA!
    Rich
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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: Naveen Nahata
    INET: naveen_nahata_at_mindtree.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).
  • Inka Bezdziecka at Oct 1, 2002 at 6:25 pm
    Oracle Corporation has a peculiar habit of providing scripts and solutions which it does not support officially. One of them is moving sys.aud$ out of SYSTEM tablespace. See note 1019377.6 on MetaLink.

    inka

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 1:18 PM
    To: Multiple recipients of list ORACLE-L

    Rich,

    I think you're crazy adding indexes SYS tables. I would *never* add
    anything to sys objects. What are you going to do the first time something
    goes wrong and you call Oracle support and you "mention" you added an index
    and they say - hey, you're on your own! Not to mention that when you
    perform an upgrade, that this index may either get lost or be in the way.

    No, a totally bad idea.

    If anything, I would copy the audit records to a local table in your schema,
    and then index that baby anyway I'd like. That way, the system objects are
    the way that Oracle inteneded them to be.

    Just my 2 cents.

    Tom Mercadante
    Oracle Certified Professional

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 11:28 AM
    To: Multiple recipients of list ORACLE-L

    Hey,

    Anyone have any advice on indexing SYS tables? Specifically, I've been
    experimenting with putting a reverse index on the TIMESTAMP# column of AUD$,
    since almost all of my queries against the AUD$ views end with "ORDER BY
    TIMESTAMP DESC".

    In 8.1.7.4.0 test DBs on HP/UX, the index -- created under a non-SYS DBA
    schema -- works great. I'm looking for pitfalls, but can't come up with any
    of my own...

    TIA!

    Rich

    --

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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: Mercadante, Thomas F
    INET: NDATFM_at_labor.state.ny.us

    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: Inka Bezdziecka
    INET: IBezdziecka_at_cupe.ca

    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).
  • Jesse, Rich at Oct 1, 2002 at 6:38 pm
    Thanks, Tom. That's the kind of feedback I'm looking for. Anyone else?

    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Mercadante, Thomas F
    Sent: Tuesday, October 01, 2002 12:18 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Indexing SYS tables


    Rich,

    I think you're crazy adding indexes SYS tables. I would *never* add
    anything to sys objects. What are you going to do the first
    time something
    goes wrong and you call Oracle support and you "mention" you
    added an index
    and they say - hey, you're on your own! Not to mention that when you
    perform an upgrade, that this index may either get lost or be
    in the way.

    No, a totally bad idea.

    If anything, I would copy the audit records to a local table
    in your schema,
    and then index that baby anyway I'd like. That way, the
    system objects are
    the way that Oracle inteneded them to be.
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).
  • Rachel Carmichael at Oct 1, 2002 at 6:48 pm
    and the latest I have heard is that they no longer will support moving
    AUD$. Even though it's listed on MetaLink

    Inka Bezdziecka wrote:
    Oracle Corporation has a peculiar habit of providing scripts and
    solutions which it does not support officially. One of them is
    moving sys.aud$ out of SYSTEM tablespace. See note 1019377.6 on
    MetaLink.

    inka

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 1:18 PM
    To: Multiple recipients of list ORACLE-L


    Rich,

    I think you're crazy adding indexes SYS tables. I would *never* add
    anything to sys objects. What are you going to do the first time
    something
    goes wrong and you call Oracle support and you "mention" you added an
    index
    and they say - hey, you're on your own! Not to mention that when you
    perform an upgrade, that this index may either get lost or be in the
    way.

    No, a totally bad idea.

    If anything, I would copy the audit records to a local table in your
    schema,
    and then index that baby anyway I'd like. That way, the system
    objects are
    the way that Oracle inteneded them to be.

    Just my 2 cents.

    Tom Mercadante
    Oracle Certified Professional


    -----Original Message-----
    Sent: Tuesday, October 01, 2002 11:28 AM
    To: Multiple recipients of list ORACLE-L


    Hey,

    Anyone have any advice on indexing SYS tables? Specifically, I've
    been
    experimenting with putting a reverse index on the TIMESTAMP# column
    of AUD$,
    since almost all of my queries against the AUD$ views end with "ORDER
    BY
    TIMESTAMP DESC".
    In 8.1.7.4.0 test DBs on HP/UX, the index -- created under a non-SYS
    DBA
    schema -- works great. I'm looking for pitfalls, but can't come up
    with any
    of my own...

    TIA!
    Rich

    --

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex,
    WI USA
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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: Mercadante, Thomas F
    INET: NDATFM_at_labor.state.ny.us

    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: Inka Bezdziecka
    INET: IBezdziecka_at_cupe.ca

    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).
    Do you Yahoo!?
    New DSL Internet Access from SBC & Yahoo!
    http://sbc.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_yahoo.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).
  • Jesse, Rich at Oct 1, 2002 at 8:23 pm
    Yes it does, at least on my test instance.

    Thanks!
    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Naveen Nahata
    Sent: Tuesday, October 01, 2002 12:28 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Indexing SYS tables


    Again since the indexes store the row in ordered fashion, I
    guess a normal
    index should be able to do ORDER BY DESC by reading backwards

    Not sure though

    Regards
    Naveen

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 10:33 PM
    To: Multiple recipients of list ORACLE-L


    Yes, you are obviously correct. I really need to RTFM.
    Too many
    pots on the stove!

    Just a regular index, then. Any other input?

    Thx!
    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International,
    Sussex, WI USA
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).
  • Chaim.Katz_at_Completions.Bombardier.com at Oct 2, 2002 at 9:18 pm
    but there is a "create index ... desc"?

    ASC | DESC

    specifies whether the index should be created in ascending or descending
    order.
    Oracle treats descending indexes as if they were function-based indexes.
    You do not need the QUERY REWRITE or GLOBAL QUERY REWRITE privileges to
    create them, as you do with other function-based indexes.

    Chaim

    "Jesse, Rich" @fatcity.com on 10/01/2002 04:23:22
    PM

    Please respond to ORACLE-L_at_fatcity.com

    Sent by: root_at_fatcity.com

    To: Multiple recipients of list ORACLE-L
    cc:

    Yes it does, at least on my test instance.

    Thanks!
    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI

    USA
    -----Original Message-----
    From: Naveen Nahata
    Sent: Tuesday, October 01, 2002 12:28 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Indexing SYS tables


    Again since the indexes store the row in ordered fashion, I
    guess a normal
    index should be able to do ORDER BY DESC by reading backwards

    Not sure though

    Regards
    Naveen

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 10:33 PM
    To: Multiple recipients of list ORACLE-L


    Yes, you are obviously correct. I really need to RTFM.
    Too many
    pots on the stove!

    Just a regular index, then. Any other input?

    Thx!
    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International,
    Sussex, WI USA
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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:
    INET: Chaim.Katz_at_Completions.Bombardier.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).
  • Connor McDonald at Oct 3, 2002 at 9:28 am
    Depends on the version I think. Oracle has always
    allowed DESC indexes, but only in 9(?) is the keyword
    actually used in the index build.

    hth
    connor

    Chaim.Katz_at_Completions.Bombardier.com wrote: >
    but there is a "create index ... desc"?

    ASC | DESC
    specifies whether the index should be created in
    ascending or descending
    order.
    Oracle treats descending indexes as if they were
    function-based indexes.
    You do not need the QUERY REWRITE or GLOBAL QUERY
    REWRITE privileges to
    create them, as you do with other function-based
    indexes.

    Chaim




    "Jesse, Rich" @fatcity.com
    on 10/01/2002 04:23:22
    PM

    Please respond to ORACLE-L_at_fatcity.com

    Sent by: root_at_fatcity.com


    To: Multiple recipients of list ORACLE-L

    cc:



    Yes it does, at least on my test instance.

    Thanks!
    Rich

    Rich Jesse System/Database
    Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech
    International, Sussex, WI
    USA
    -----Original Message-----
    From: Naveen Nahata
    Sent: Tuesday, October 01, 2002 12:28 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Indexing SYS tables


    Again since the indexes store the row in ordered
    fashion, I
    guess a normal
    index should be able to do ORDER BY DESC by
    reading backwards
    Not sure though

    Regards
    Naveen

    -----Original Message-----
    Sent: Tuesday, October 01, 2002 10:33 PM
    To: Multiple recipients of list ORACLE-L


    Yes, you are obviously correct. I really need to RTFM.
    Too many
    pots on the stove!

    Just a regular index, then. Any other input?

    Thx!
    Rich

    Rich Jesse
    System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech
    International,
    Sussex, WI USA
    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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:
    INET: Chaim.Katz_at_Completions.Bombardier.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).
    Connor McDonald
    http://www.oracledba.co.uk
    http://www.oaktable.net

    "Remember amateurs built the ark - Professionals built the Titanic"

    Do You Yahoo!?
    Everything you'll ever need on one web page
    from News and Sport to Email and Music Charts
    http://uk.my.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: =?iso-8859-1?q?Connor=20McDonald?=
    INET: hamcdc_at_yahoo.co.uk

    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).
  • Jesse, Rich at Oct 3, 2002 at 2:33 pm
    I'm not so sure. I created a DESC index, but the optimizer refused to use
    it in a simple query containing an ORDER BY DESC on the indexed DATE column
    without hinting, even after I analyzed it (8.1.7.4 and CBO).

    Without spending too much time on it, I created a good ol' fashioned b-tree
    and it works like a charm. I just need to address the issue of "Should I?"

    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
    -----Original Message-----
    From: Connor McDonald
    Sent: Thursday, October 03, 2002 4:29 AM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Indexing SYS tables


    Depends on the version I think. Oracle has always
    allowed DESC indexes, but only in 9(?) is the keyword
    actually used in the index build.

    hth
    connor

    --- Chaim.Katz_at_Completions.Bombardier.com wrote: >
    but there is a "create index ... desc"?

    ASC | DESC
    specifies whether the index should be created in
    ascending or descending
    order.
    Oracle treats descending indexes as if they were
    function-based indexes.
    You do not need the QUERY REWRITE or GLOBAL QUERY
    REWRITE privileges to
    create them, as you do with other function-based
    indexes.
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 1, '02 at 3:28p
activeOct 3, '02 at 2:33p
posts13
users8
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase