RE: Indexing SYS tables
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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: Connor McDonald [mailto:[EMAIL PROTECTED]] > 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 > > --- [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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 --- [EMAIL PROTECTED] 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" <[EMAIL PROTECTED]>@fatcity.com > on 10/01/2002 04:23:22 > PM > > Please respond to [EMAIL PROTECTED] > > Sent by:[EMAIL PROTECTED] > > > To:Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > > > > Yes it does, at least on my test instance. > > Thanks! > Rich > > Rich Jesse System/Database > Administrator > [EMAIL PROTECTED] Quad/Tech > International, Sussex, WI > USA > > > -Original Message----- > > From: Naveen Nahata > [mailto:[EMAIL PROTECTED]] > > 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 > > [EMAIL PROTECTED] Quad/Tech > International, > > Sussex, WI USA > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jesse, Rich > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Indexing SYS tables
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" <[EMAIL PROTECTED]>@fatcity.com on 10/01/2002 04:23:22 PM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Yes it does, at least on my test instance. Thanks! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: Naveen Nahata [mailto:[EMAIL PROTECTED]] > 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 > [EMAIL PROTECTED] Quad/Tech International, > Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Indexing SYS tables
Yes it does, at least on my test instance. Thanks! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: Naveen Nahata [mailto:[EMAIL PROTECTED]] > 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 > [EMAIL PROTECTED] Quad/Tech International, > Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Indexing SYS tables
Thanks, Tom. That's the kind of feedback I'm looking for. Anyone else? Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] > 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Indexing SYS tables
and the latest I have heard is that they no longer will support moving AUD$. Even though it's listed on MetaLink --- Inka Bezdziecka <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] Quad/Tech International, Sussex, > WI USA > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jesse, Rich > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Indexing SYS tables
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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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. 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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: Naveen Nahata [mailto:[EMAIL PROTECTED]] > 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Indexing SYS tables
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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: Naveen Nahata [mailto:[EMAIL PROTECTED]] > 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Indexing SYS tables
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 [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jesse, Rich > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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 -- Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Indexing SYS tables
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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
