Re: How to speed up index creation
Tanel, Tried it out -- I stand corrected! Thanks for the heads up! -Tim on 9/8/03 9:14 AM, Tanel Poder at [EMAIL PROTECTED] wrote: Tim, AFAIK, when doing a rebuild the whole index is read using fast full scan, the branch blocks are just ignored. And sorting does occur, since fast full scan doesn't guarantee all keys to be returned in an order. (yes the keys come in ordered chunks because they are stored in ordered way inside leaf blocks, that might help the ordering alghoritm to complete faster). The benefit comes from that indexes are usually smaller in size than tables because they index only subset of a row, thus there are less IO operations needed when scanning data. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 08, 2003 4:49 PM Gunnar, Using ALTER SESSION to increase SORT_AREA_SIZE to some obscene number (i.e. several hundred Mb? A Gb?) may help, as long as you are running 8i or below or (if running 9i) the parameter WORKAREA_SIZE_POLICY is set to MANUAL. If WORKAREA_SIZE_POLICY is set to AUTO, then you may have to bump PGA_AGGREGATE_TARGET to something enormous instead... Are you making use of ALTER INDEX REBUILD to rebuild an index, or are you dropping and then creating? ALTER INDEX REBUILD runs much faster than a CREATE INDEX for two reasons: 1) the REBUILD reads the previous index¹s leaf nodes so there is less I/O since it is smaller and 2) there is no sort operation needed since the previous index¹s leaf entries are already sorted= -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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: How to speed up index creation
This is nothing compared to your work's contribution to my knowledge, but you're welcome, any time :) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, September 13, 2003 6:49 PM Tanel, Tried it out -- I stand corrected! Thanks for the heads up! -Tim on 9/8/03 9:14 AM, Tanel Poder at [EMAIL PROTECTED] wrote: Tim, AFAIK, when doing a rebuild the whole index is read using fast full scan, the branch blocks are just ignored. And sorting does occur, since fast full scan doesn't guarantee all keys to be returned in an order. (yes the keys come in ordered chunks because they are stored in ordered way inside leaf blocks, that might help the ordering alghoritm to complete faster). The benefit comes from that indexes are usually smaller in size than tables because they index only subset of a row, thus there are less IO operations needed when scanning data. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 08, 2003 4:49 PM Gunnar, Using ALTER SESSION to increase SORT_AREA_SIZE to some obscene number (i.e. several hundred Mb? A Gb?) may help, as long as you are running 8i or below or (if running 9i) the parameter WORKAREA_SIZE_POLICY is set to MANUAL. If WORKAREA_SIZE_POLICY is set to AUTO, then you may have to bump PGA_AGGREGATE_TARGET to something enormous instead... Are you making use of ALTER INDEX REBUILD to rebuild an index, or are you dropping and then creating? ALTER INDEX REBUILD runs much faster than a CREATE INDEX for two reasons: 1) the REBUILD reads the previous index¹s leaf nodes so there is less I/O since it is smaller and 2) there is no sort operation needed since the previous index¹s leaf entries are already sorted= -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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.net -- Author: Tanel Poder 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: How to speed up index creation
Gunnar, Using ALTER SESSION to increase SORT_AREA_SIZE to some obscene number (i.e. several hundred Mb? A Gb?) may help, as long as you are running 8i or below or (if running 9i) the parameter WORKAREA_SIZE_POLICY is set to MANUAL. If WORKAREA_SIZE_POLICY is set to AUTO, then you may have to bump PGA_AGGREGATE_TARGET to something enormous instead... Are you making use of ALTER INDEX REBUILD to rebuild an index, or are you dropping and then creating? ALTER INDEX REBUILD runs much faster than a CREATE INDEX for two reasons: 1) the REBUILD reads the previous index¹s leaf nodes so there is less I/O since it is smaller and 2) there is no sort operation needed since the previous index¹s leaf entries are already sorted
RE: How to speed up index creation
Gunnar Give SORT_AREA_SIZE a try. I think you will be pleasantly pleased with the results. Remember you can set it with ALTER SESSION, particularly useful for creating indexes. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 08, 2003 7:49 AM To: Multiple recipients of list ORACLE-L Hi all, we have a couple of huge tables and we have some performance problems while creating indexes. Here are some methods we already have tried, is there something else: Here's the list of features we already know: - Using NOLOGGING mode - Using PARALLEL option = PARALLEL 4 (the server has 4 CPU's) - Using NOSORT option = not applicable because the rows are not stored in the right order - Creating an index first as unusable and then rebuilding it = this was tested after the QA cutover but the duration remained unchanged - Increasing sort_area_size = has not yet been tested Something else, any help will be appreciated... rgds g _ Want to chat instantly with your online friends? http://uk.rd.yahoo.com/mail/tagline_messenger/*http://uk.messenger.yahoo.co m/ Get the FREE Yahoo! Messenger -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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: How to speed up index creation
Gunnar, It depends on where you are spending most of the wait time. db file scattered read , for example, can be reduced by setting higher db_file_multiblock_read_count. Use event 10046 to collect wait profile. HTH Vadim -Original Message-From: Gunnar Berglund [mailto:[EMAIL PROTECTED]Sent: Monday, September 08, 2003 8:49 AMTo: Multiple recipients of list ORACLE-LSubject: How to speed up index creation Hi all, we have a couple of huge tables and we have some performance problems while creating indexes. Here are some methods we already have tried, is there something else: Here's the list of features we already know: - Using NOLOGGING mode - Using PARALLEL option = PARALLEL 4(the server has 4 CPU's) - Using NOSORT option = not applicable because the rows are not stored in the right order - Creating an index first as unusable and then rebuilding it = this was tested after the QA cutover but the duration remained unchanged - Increasing sort_area_size = has not yet been tested Something else, any help will be appreciated... rgds g Want to chat instantly with your online friends?Get the FREE Yahoo! Messenger
Re: How to speed up index creation
Tim, AFAIK, when doing a rebuild the whole index is read using fast full scan, the branch blocks are just ignored. And sorting does occur, since fast full scan doesn't guarantee all keys to be returned in an order. (yes the keys come in ordered chunks because they are stored in ordered way inside leaf blocks, that might help the ordering alghoritm to complete faster). The benefit comes from that indexes are usually smaller in size than tables because they index only subset of a row, thus there are less IO operations needed when scanning data. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 08, 2003 4:49 PM Gunnar, Using ALTER SESSION to increase SORT_AREA_SIZE to some obscene number (i.e. several hundred Mb? A Gb?) may help, as long as you are running 8i or below or (if running 9i) the parameter WORKAREA_SIZE_POLICY is set to MANUAL. If WORKAREA_SIZE_POLICY is set to AUTO, then you may have to bump PGA_AGGREGATE_TARGET to something enormous instead... Are you making use of ALTER INDEX REBUILD to rebuild an index, or are you dropping and then creating? ALTER INDEX REBUILD runs much faster than a CREATE INDEX for two reasons: 1) the REBUILD reads the previous index¹s leaf nodes so there is less I/O since it is smaller and 2) there is no sort operation needed since the previous index¹s leaf entries are already sorted= -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: How to speed up index creation
Metalink note 1011840.102 describes this. Tanel. P.S. I spelled algorithm wrong in my last post, now I'm a bit wiser again ;) - Original Message - To: [EMAIL PROTECTED] Sent: Monday, September 08, 2003 6:13 PM Tim, AFAIK, when doing a rebuild the whole index is read using fast full scan, the branch blocks are just ignored. And sorting does occur, since fast full scan doesn't guarantee all keys to be returned in an order. (yes the keys come in ordered chunks because they are stored in ordered way inside leaf blocks, that might help the ordering alghoritm to complete faster). The benefit comes from that indexes are usually smaller in size than tables because they index only subset of a row, thus there are less IO operations needed when scanning data. Tanel. - Original Message - From: Tim Gorman [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 08, 2003 4:49 PM Subject: Re: How to speed up index creation Gunnar, Using ALTER SESSION to increase SORT_AREA_SIZE to some obscene number (i.e. several hundred Mb? A Gb?) may help, as long as you are running 8i or below or (if running 9i) the parameter WORKAREA_SIZE_POLICY is set to MANUAL. If WORKAREA_SIZE_POLICY is set to AUTO, then you may have to bump PGA_AGGREGATE_TARGET to something enormous instead... Are you making use of ALTER INDEX REBUILD to rebuild an index, or are you dropping and then creating? ALTER INDEX REBUILD runs much faster than a CREATE INDEX for two reasons: 1) the REBUILD reads the previous index¹s leaf nodes so there is less I/O since it is smaller and 2) there is no sort operation needed since the previous index¹s leaf entries are already sorted= -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
