Re: How to speed up index creation

2003-09-13 Thread Tim Gorman
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

2003-09-13 Thread Tanel Poder
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

2003-09-08 Thread Tim Gorman
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

2003-09-08 Thread DENNIS WILLIAMS
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

2003-09-08 Thread Gorbounov,Vadim



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

2003-09-08 Thread Tanel Poder
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

2003-09-08 Thread Tanel Poder
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).