date:20030529

Got it this is going to buy me exactly nothing given my app and
database design and database size..


--- Mladen Gogala <[EMAIL PROTECTED]> wrote:
> I tried it and what it does is, essentially, a fast full index scan
> on the
> remaining columns of the index. To resolve the query, oracle does a
> full 
> sequential scan on the index instead on the table. If your index is
> one third
> size of the table, you saved quite a few IOs but don't expect
> anything like
> search on unique key performance.
> 
> On 2003.05.28 07:54 Mark Leith wrote:
> > Rachel,
> > 
> >
>
http://technet.oracle.com/oramag/webcolumns/2003/techarticles/schumacher_ski
> > pscan.html
> > http://www.oracle-base.com/Articles/9i/IndexSkipScanning.asp
> > 
> > I don't have any personal experience with them myself :( The first
> link
> > gives a pretty good overview though..
> > 
> > Mark
> > 
> > -Original Message-
> > Carmichael
> > Sent: 28 May 2003 12:00
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > Okay, I have a developer here who has been reading the docs (this
> can
> > be dangerous!)
> > 
> > we are adding functionality to one of our applications, this will
> > involve using multiple fulfillment houses, so we'll be adding the
> > fulfillment vendor id to the order table. Easy, this is not a
> problem.
> > We want to be able to search by order date and by fulfillment
> vendor
> > id/order date
> > 
> > Traditional design would be to add two indexes: one on order date,
> and
> > a concatenated one on fulfillment vendor id/order date.
> > 
> > The developer is telling me to create a "skip scan index" instead
> of
> > two different ones. MY reading in the FM tells me that skip scan
> index
> > is not a type of index, but rather a way Oracle uses to use an
> index
> > even if the leftmost column is not in the query.
> > 
> > Is there any benefit in my building only the one index? Our order
> > volume is not so high (and never will be) that there is a visible
> > performance impact if I have the two indices.
> > 
> > This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near
> future.
> > Solaris
> > 
> > Any suggestions/comments/war stories would be appreciated. I know
> I've
> > seen Jonathan post on skip scan indexes before but I can't find the
> > specific reference at the moment.
> > 
> > Rachel
> > 
> > __
> > Do you Yahoo!?
> > Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> > http://calendar.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > 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).
> > 
> > ---
> > Incoming mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> > 
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> > 
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > -- 
> > Author: Mark Leith
> >   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).
> > 
> 
> -- 
> Mladen Gogala
> Oracle DBA
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Mladen Gogala
>   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

Re: skip scan index

2003-05-29 Thread Wolfgang Breitling

At 02:59 AM 5/28/2003 -0800, you wrote:
Okay, I have a developer here who has been reading the docs (this can
be dangerous!)
we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date
Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.
The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.
Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.
This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris
Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.
As others already said, it is a "index skip scan" access method, not a 
"skip scan" index. It is like an implicit OR where the optimizer looks up 
all distinct values for the missing prefix column(s) and augments the 
predicate (sort of) with these values and then does traditional index 
scans, ORing the results. It may not happen exactly that way, but 
conceptually that is what happens. From this you can deduce that it is an 
option only when there are relatively few distinct prefix values. In your 
case I doubt that the optimizer would ever choose a skip scan. Unless you 
have only a handfull (literally 5 or less) of fullfilment vendors. I don't 
have hard numbers as to the number of distinct prefix values beyond which a 
skip scan becomes too expensive compared to an FTS but during my tests in 
preparation for my IOUG presentation I had a hard time constructing an 
example where the optimizer would choose a skip scan - and I had tables 
with just 1 distinct prefix value.
My vote goes for your proposed two indices.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 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: skip scan index

2003-05-29 Thread Goulet, Dick

Rachel,

I'll send you a baseball bat to club that duhveloper over the head with.  Your 
right, skip scan is a method that Oracle uses to make use of an index when logically 
it should not.  You cannot specify it that way.  Darn duhvelopers who read things into 
manuals, it's dangerous for them.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA 

-Original Message-
Sent: Wednesday, May 28, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L


Okay, I have a developer here who has been reading the docs (this can
be dangerous!)

we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Goulet, Dick
  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: skip scan index

2003-05-29 Thread Richard Foote

Hi Rachel,

Correct, "Skip Scan Index" is not a type of index but a method whereby
Oracle can eliminate the need to visit leaf nodes by determining whether the
leading column(s) have changed by sussing out only the branch nodes. It's
possibly useful in situations where previously Oracle would not consider a
concatenated index if the leading column of the index is unknown whereas now
the optimizer might determine that sufficient leaf nodes can be avoided for
the index to be of benefit. It's a kinda improved version of the full index
scan (or not so full if you know what I mean),

However this requires the leading column to have *low* cardinality, low
enough for the same repeated column from one leaf node to extent across all
values of it's neighbouring leaf node. If the leading column changes from
one leaf node to the next, then that leaf node must be at least visited
(although subsequent inspection of the index values may enable Oracle to
"pull out early" from having to read all index values, if a subsequent
change in the leading column rules out all remaining entries).

A quick (and nasty) formula would be to consider the ratio of leaf nodes to
distinct values (LN/DV). The higher the ratio the better with any value
somewhat greater than 1 giving a skip scan index path a chance with the
number representing an approximate number of leaf nodes that could be
"skipped" per leading index value. This obviously assumes evenish
distribution of leading column(s) index values.

However, getting back to your actual situation, if table access is only to
be made via the order date or by order date and order id (and not
necessarily by order id only), then you may find a single index order date
|| order id would meet all your requirements.

Cheers

Richard Foote


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, May 28, 2003 8:59 PM


> Okay, I have a developer here who has been reading the docs (this can
> be dangerous!)
>
> we are adding functionality to one of our applications, this will
> involve using multiple fulfillment houses, so we'll be adding the
> fulfillment vendor id to the order table. Easy, this is not a problem.
> We want to be able to search by order date and by fulfillment vendor
> id/order date
>
> Traditional design would be to add two indexes: one on order date, and
> a concatenated one on fulfillment vendor id/order date.
>
> The developer is telling me to create a "skip scan index" instead of
> two different ones. MY reading in the FM tells me that skip scan index
> is not a type of index, but rather a way Oracle uses to use an index
> even if the leftmost column is not in the query.
>
> Is there any benefit in my building only the one index? Our order
> volume is not so high (and never will be) that there is a visible
> performance impact if I have the two indices.
>
> This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
> Solaris
>
> Any suggestions/comments/war stories would be appreciated. I know I've
> seen Jonathan post on skip scan indexes before but I can't find the
> specific reference at the moment.
>
> Rachel
>
> __
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> 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).
>
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Richard Foote
  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: skip scan index

I don't doubt that it works I just doubt that I NEED it :)


--- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote:
> Rachel,
> 
> Skip scan index is not a index type, it is a index scan type. Maybe
> the
> developer should re-read the relevant portion of the manual. If your
> order
> volume is low, you probably won't see much performance impact by
> having two
> indexes (like you need to hear this from ME), but one should suffice
> ...
> 
> BTW, it works, really.
> Raj
>

> 
> Rajendra dot Jamadagni at nospamespn dot com
> All Views expressed in this email are strictly personal.
> QOTD: Any clod can have facts, having an opinion is an art !
> 
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 7:00 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Okay, I have a developer here who has been reading the docs (this can
> be dangerous!)
> 
> [ much stuff deleted to conserve electrons ]
> >
This
> e-mail message is confidential, intended only for the named
> recipient(s) above and may contain information that is privileged,
> attorney work product or exempt from disclosure under applicable law.
> If you have received this message in error, or are not the named
> recipient(s), please immediately notify corporate MIS at (860)
> 766-2000 and delete this e-mail message from your computer, Thank
>
you.*2
> 


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: skip scan index

Richard,

the access would be order date or vendor id/order date (since it's
possible to look up by vendor id alone as well)

very low cardinality on vendor id -- right now I have all of two.

personal opinion is that the developer read something "cool" and
decided to tell the DBA how to do things, especially since the
statement was "create a skip scan index" :)

Rachel
--- Richard Foote <[EMAIL PROTECTED]> wrote:
> Hi Rachel,
> 
> Correct, "Skip Scan Index" is not a type of index but a method
> whereby
> Oracle can eliminate the need to visit leaf nodes by determining
> whether the
> leading column(s) have changed by sussing out only the branch nodes.
> It's
> possibly useful in situations where previously Oracle would not
> consider a
> concatenated index if the leading column of the index is unknown
> whereas now
> the optimizer might determine that sufficient leaf nodes can be
> avoided for
> the index to be of benefit. It's a kinda improved version of the full
> index
> scan (or not so full if you know what I mean),
> 
> However this requires the leading column to have *low* cardinality,
> low
> enough for the same repeated column from one leaf node to extent
> across all
> values of it's neighbouring leaf node. If the leading column changes
> from
> one leaf node to the next, then that leaf node must be at least
> visited
> (although subsequent inspection of the index values may enable Oracle
> to
> "pull out early" from having to read all index values, if a
> subsequent
> change in the leading column rules out all remaining entries).
> 
> A quick (and nasty) formula would be to consider the ratio of leaf
> nodes to
> distinct values (LN/DV). The higher the ratio the better with any
> value
> somewhat greater than 1 giving a skip scan index path a chance with
> the
> number representing an approximate number of leaf nodes that could be
> "skipped" per leading index value. This obviously assumes evenish
> distribution of leading column(s) index values.
> 
> However, getting back to your actual situation, if table access is
> only to
> be made via the order date or by order date and order id (and not
> necessarily by order id only), then you may find a single index order
> date
> || order id would meet all your requirements.
> 
> Cheers
> 
> Richard Foote
> 
> 
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, May 28, 2003 8:59 PM
> 
> 
> > Okay, I have a developer here who has been reading the docs (this
> can
> > be dangerous!)
> >
> > we are adding functionality to one of our applications, this will
> > involve using multiple fulfillment houses, so we'll be adding the
> > fulfillment vendor id to the order table. Easy, this is not a
> problem.
> > We want to be able to search by order date and by fulfillment
> vendor
> > id/order date
> >
> > Traditional design would be to add two indexes: one on order date,
> and
> > a concatenated one on fulfillment vendor id/order date.
> >
> > The developer is telling me to create a "skip scan index" instead
> of
> > two different ones. MY reading in the FM tells me that skip scan
> index
> > is not a type of index, but rather a way Oracle uses to use an
> index
> > even if the leftmost column is not in the query.
> >
> > Is there any benefit in my building only the one index? Our order
> > volume is not so high (and never will be) that there is a visible
> > performance impact if I have the two indices.
> >
> > This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near
> future.
> > Solaris
> >
> > Any suggestions/comments/war stories would be appreciated. I know
> I've
> > seen Jonathan post on skip scan indexes before but I can't find the
> > specific reference at the moment.
> >
> > Rachel
> >
> > __
> > Do you Yahoo!?
> > Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> > http://calendar.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > 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).
> >
> >
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Richard Foote
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -

RE: skip scan index

2003-05-29 Thread Kevin Toepke

Rachel

My experience with index skip scans can be summed up as follows. If you know
the app will be doing a particular scan, create the index. 

Index Skip Scans should be thought of a means to help optimize those pesky
ad-hoc queries only.

I haven't been able to get a skip-can to work unless there is a simple
restriction (>, <, =) on the non-leading column. My experience tells me they
don't help when you are joining against a non-leading column or you are
using an IN condition (either static or sub-query)

HTH
Kevin

-Original Message-
Sent: Wednesday, May 28, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L


Okay, I have a developer here who has been reading the docs (this can
be dangerous!)

we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kevin Toepke
  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).

Hidden Columns

2003-05-29 Thread Bill Buchan

Hi all,

I know this is RTFM but I can't find the right part in the FM to R.  So any 
help would be appreciated:

In the context of:

 DBMS_STATS.GATHER_DATABASE_STATS(method_opt=>'FOR ALL HIDDEN COLUMNS');

what is a "hidden column".  I tried gathering stats FOR ALL HIDDEN COLUMNS 
and it didn't seem to gather any stats for any columns.  What is it meant 
to do?

Thanks
- Bill.
PS. The complete syntax for the column is: FOR ALL [INDEXED | HIDDEN] 
COLUMNS [size_clause] 

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bill Buchan
 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: skip scan index

Dick,

I club him regularly... doesn't seem to get through. As someone (Bill
Thater) once said on the OT list.. this guy wouldn't be able to spot a
clue, in a clue field, during clue mating season while drenched in clue
pheronomes.

I fight all the time with him. I win :).   My basic premise is "It's MY
database, keep your hands off it and let the DBA do her job"

Rachel

--- "Goulet, Dick" <[EMAIL PROTECTED]> wrote:
> Rachel,
> 
>   I'll send you a baseball bat to club that duhveloper over the head
> with.  Your right, skip scan is a method that Oracle uses to make use
> of an index when logically it should not.  You cannot specify it that
> way.  Darn duhvelopers who read things into manuals, it's dangerous
> for them.
> 
> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA 
>

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: Space Remaining in Current Extent - Done

2003-05-29 Thread Kevin Lange

Thanks for everyone for responding.   I think I have enough to get as close
as I can to what was requested.

Kevin

-Original Message-
Sent: Tuesday, May 27, 2003 5:40 PM
To: Multiple recipients of list ORACLE-L


I'll beg/borrow & steal from John Beresniewicz's book on Oracle Built-in
Packages by O'Reilly & RevealNet to give you an answer.  Here is the script
that John provides with the book.  I use it in a somewhat different form,
but it appears to tell the truth.

DECLARE
   free_blocks   NUMBER;
BEGIN
   DBMS_OUTPUT.PUT_LINE(RPAD('TABLE NAME',30)||' FREELIST BLOCKS');

   FOR user_tables_rec IN
  (SELECT table_name 
 FROM user_tables)
   LOOP
  DBMS_SPACE.FREE_BLOCKS
 (segment_owner => USER
 ,segment_name  => user_tables_rec.table_name
 ,segment_type  => 'TABLE'
 ,freelist_group_id  => 0
 ,free_blks => free_blocks
 ,scan_limit => NULL);
  
  DBMS_OUTPUT.PUT_LINE(RPAD(user_tables_rec.table_name,30)||' '||
  TO_CHAR(free_blocks));
   END LOOP;
END;
/


Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA 

-Original Message-
Sent: Tuesday, May 27, 2003 4:10 PM
To: Multiple recipients of list ORACLE-L


As to what this is for .

I have a request to come up with a way to guage space remaining on an on
going basis  i.e.If I look at the space available between 2
different user transactions I will see a difference.  Most of the methods I
have seen so far either rely on Analyze or show simply the amount of space
that has been allocated to the table at this time (not the actual "This is
what you have allocated" and "This is what you have free").

I have tried using dbms_space but it again shows (at least I interpret it
that way) the amount of space allocated , not neccessarily exactly what is
in use.

If there is an obvious v$ or x$ view out there someplace where I can get
this info, it would be great.

-Original Message-
Sent: Tuesday, May 27, 2003 2:40 PM
To: Multiple recipients of list ORACLE-L


Kevin - Since you haven't received any replies, here goes.
Within an extent, Oracle uses blocks. I haven't seen a way to find the used
space within a block. There are methods to find the number of empty blocks
underneath the high water mark. Analyze does that, but you've ruled that
out. It might help if you could explain what you are trying to accomplish. 
Other ideas are:
  - Write a program that will scan the table, read each row and count the
bytes as it reads it. This would be very accurate, but time-consuming.
  - An approximate answer could be arrived at by doing an analyze and
getting average row length. This shouldn't change much unless some operation
is performed that would alter that. Then by getting the number of rows in
the table you could get a very close estimate of the table size at any time.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Tuesday, May 27, 2003 11:15 AM
To: Multiple recipients of list ORACLE-L


Hey guys;
  Does anyone know where I can look to find the space remaining in an
individual extent ??  I know that you can get the freespace from
dba_free_space but that seems to be based only on unallocated extents.  I
have been asked to find out, down to the byte, how much free space is
available . on the fly (which means not only after every analyze) 

Any suggestions ??? I  am sure Oracle has a table with the information
somewhere .

Thanks

Kevin
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kevin Lange
  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: 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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kevin Lange
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.

RE: Alternative way to write delete query

2003-05-29 Thread Chelur, Jayadas {PBSG}

delete 
where  exists
   (
   select 1
   from   (
  select  from 
  union all
  select  from 
  ) a
   where  a. = .
   );

-Original Message-
Sent: Wednesday, May 28, 2003 9:10 AM
To: Multiple recipients of list ORACLE-L


If anybody knows, it must be Lex...

Lex, you genius of geniuses and SQL Logician of them all - can you help
here?

Mogens

Mark Richard wrote:


This is quite close to what I was thinking of however it doesn't seem to

work for Oracle.  Does anyone know if there is similar syntax available in

the Oracle world?  I've looked at the Oracle (8.1.7) doco but can't see how

I can achieve what I want to do.







 


  "Igor Neyman"


  <[EMAIL PROTECTED]To:   Multiple recipients
of list ORACLE-L    <[EMAIL PROTECTED]>


  on.com>  cc:


  Sent by: Subject:  RE: Alternative way
to write delete query  

   [EMAIL PROTECTED] 


 


 


  28/05/2003 02:34


  Please respond to


  ORACLE-L


 


 










Small correction for SQL Server / Sybase, if anyone cares, of course -:)



Delete table1

  from table_a

where column1 = col_a

or column1 = col_b



Igor Neyman, OCP DBA

[EMAIL PROTECTED]  







-Original Message-

OLLIG

Sent: Tuesday, May 27, 2003 9:35 AM

To: Multiple recipients of list ORACLE-L



Mark -



you could also do it with 2 deletes something like this:



delete

  from (select column1

  from table1

 , table_a

 where column1 = col_a)



delete

  from (select column1

  from table1

 , table_b

 where column1 = col_b)





couldn't find a way to avoid the "ORA-01752: cannot delete from view

without

exactly one key-preserved table" with the or condition.  perhaps someone

who

isn't still clearing the brain cobwebs after a long weekend can see a

solution there.



FWIW - i'm pretty sure SQL Server & Sybase will let you get by with

this:



delete

  from table1

 , table_a

 , table_b

 where column1 = col_a

or column1 = col_b



(don't have a sandbox handy to confirm though)  maybe that's what you

were

thinking of?





-Original Message-

Sent: Tuesday, May 27, 2003 12:27 AM

To: Multiple recipients of list ORACLE-L





Hi List,



I'm having a mental blank and looking for suggestions...  I'm trying to

remember alternative ways to write the below query:



delete from table

where column in (select col_a

  from table_b

  union all

  select col_b

  from table_b);



Having said that, I don't mind if you simplify the nested query down to

"select col_a from table_b" even.  I just have a feeling that I've seen

an

equivalent query written totally differently but I can't remember how.

I

have a feeling it effectively allows more than one table to be mentioned

in

the delete without needing a nested query (like the options available in

normal selects).



Any suggestions?



Thanks,

  Mark.



<<>>

  

  

   Privileged/Confidential information may be contained in this message.

  If you are not the addressee indicated in this message

   (or responsible for delivery of the message to such person),

you may not copy or deliver this message to anyone.

In such case, you should destroy this message and kindly notify the

sender

   by reply e-mail or by telephone on (61 3) 9612-6999.

   Please advise immediately if you or your employer does not consent to

Internet e-mail for messages of this kind.

Opinions, conclusions and other information in this message

  that do not relate to the official business of

 Transurban City Link Ltd

 shall be understood as neither given nor endorsed by it.

<<<>

  

  





--

Please see the official ORACLE-L FAQ:  http://www.orafaq.net
 

--

Author: Mark Richard

  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

Fwd: Hidden Columns

2003-05-29 Thread Bill Buchan

Sorry for wasting bandwidth - found the answer 5 minutes later (found it by 
looking up "histograms" in the manual, not "DBMS_STATS"):

For those who want to know - a hidden column is an expression for a 
function based index.  Therefore you can use this option to gather stats 
when using function based indexes.

- Bill.


Date: Wed, 28 May 2003 14:42:31 +0100
To: [EMAIL PROTECTED]
From: Bill Buchan <[EMAIL PROTECTED]>
Subject: Hidden Columns
Hi all,

I know this is RTFM but I can't find the right part in the FM to R.  So 
any help would be appreciated:

In the context of:

 DBMS_STATS.GATHER_DATABASE_STATS(method_opt=>'FOR ALL HIDDEN COLUMNS');

what is a "hidden column".  I tried gathering stats FOR ALL HIDDEN COLUMNS 
and it didn't seem to gather any stats for any columns.  What is it meant 
to do?

Thanks
- Bill.
PS. The complete syntax for the column is: FOR ALL [INDEXED | HIDDEN] 
COLUMNS [size_clause]
--
Intasys Billing Technologies Ltd.   www.intasysbilling.com
74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX
tel (0)131 625 8200 fax (0)131 625 8201 email [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bill Buchan
 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: would imp speed affect by setting indexes to NOLOGGING?

2003-05-29 Thread gmei

I have a question about index creation. When I run

create index xyz ... tablespace indx_ts ...

at what stage "sort_area_size" (and temp space if sort_area_size is not
large enough) is used during the process? Is my understanding correct that
the index will be created in  "indx_ts" (not temp tablespace). As long as we
have enough space in "indx_ts" we are fine. I thought sort_area_size and
temp space is used for sorting only.

Guang


-- Original Message:
In respone to someone's question about improving
exp/imp speed I had posted the following quite some
time ago. Most of it may help you.

- Kirti



9) After the import is completed, set sort_area_size,
sort_area_retained_size to a higher value (whatever is
adequate and
possible) to speed up index build process. Also,
consider TEMPORARY type
temp tablespace with properly configured initial and
next (multiples of
sort_area_size) extents. Make sure temp tablespace has
ample room, should
index build processes perform disk sorts. Also, make
sure quota is okay on
tablespaces for primary key constraint indexes.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: gmei
  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).

[no subject]

2003-05-29 Thread antonio . belloni

We have two servers: Server A running iAS ; Server B running IDS 9 and
serving files.

We map one drive from server B to server A and we are trying to access the
forms on the remote driver:

http://host:port/forms90/f90servlet?form=f:\form_name.fmx

But we receive an error saying that the servlet could not read the form
file.

If we copy the file from f:\ to a local drive , ie , c:\temp , the form can
be executed succesfully.

We are aware of a known bug issue in the old OAS that prevent using forms
apps running on remote drivers . but we think that it could (and should) be
fixed on iAS. Does anyone facing the same problem or trying to do something
similar on IAS ??

Both server are running Win2K.

Thanks in advance,
Antonio Belloni


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: where is info stored when using the ANALYZE cmd?

2003-05-29 Thread Reginald . W . Bailey


Answer to question #1:  The plan_table owned by system or sys.
#2: Try the following:  V$BUFFER_POOL, V$LIBRARYCACHE, V$DB_OBJECT_CACHE,
V$MYSTAT, V$SESSION_WAIT, V$SESSTAT

Good Hunting.



Reginald W. Bailey
IBM Global Services - ETS SW GDSD - Database Management
Your Friendly Neighborhood DBA
713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager)



   
 
[EMAIL PROTECTED]  

oo.com   To: [EMAIL PROTECTED] 
  
Sent by: cc:   
 
[EMAIL PROTECTED]   Subject: where is info stored when 
using the ANALYZE cmd?  
om 
 
   
 
   
 
05/28/2003 
 
08:15 AM   
 
Please respond 
 
to ORACLE-L
 
   
 
   
 




1. If I analyze the code using the explain plan,
where is info created from this explain plan stored at?

2. If I added more buffers to the buffer-cache, where should
I go check if they made any positive difference?

thx,
maa

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
  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: 
  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: skip scan index

I'll take your vote! Especially since you have hard evidence that you
can't always "get there from here" and even with one value (we'll have
two at the beginning) get an index skip scan to occur


--- Wolfgang Breitling <[EMAIL PROTECTED]> wrote:
> At 02:59 AM 5/28/2003 -0800, you wrote:
> >Okay, I have a developer here who has been reading the docs (this
> can
> >be dangerous!)
> >
> >we are adding functionality to one of our applications, this will
> >involve using multiple fulfillment houses, so we'll be adding the
> >fulfillment vendor id to the order table. Easy, this is not a
> problem.
> >We want to be able to search by order date and by fulfillment vendor
> >id/order date
> >
> >Traditional design would be to add two indexes: one on order date,
> and
> >a concatenated one on fulfillment vendor id/order date.
> >
> >The developer is telling me to create a "skip scan index" instead of
> >two different ones. MY reading in the FM tells me that skip scan
> index
> >is not a type of index, but rather a way Oracle uses to use an index
> >even if the leftmost column is not in the query.
> >
> >Is there any benefit in my building only the one index? Our order
> >volume is not so high (and never will be) that there is a visible
> >performance impact if I have the two indices.
> >
> >This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near
> future.
> >Solaris
> >
> >Any suggestions/comments/war stories would be appreciated. I know
> I've
> >seen Jonathan post on skip scan indexes before but I can't find the
> >specific reference at the moment.
> 
> As others already said, it is a "index skip scan" access method, not
> a 
> "skip scan" index. It is like an implicit OR where the optimizer
> looks up 
> all distinct values for the missing prefix column(s) and augments the
> 
> predicate (sort of) with these values and then does traditional index
> 
> scans, ORing the results. It may not happen exactly that way, but 
> conceptually that is what happens. From this you can deduce that it
> is an 
> option only when there are relatively few distinct prefix values. In
> your 
> case I doubt that the optimizer would ever choose a skip scan. Unless
> you 
> have only a handfull (literally 5 or less) of fullfilment vendors. I
> don't 
> have hard numbers as to the number of distinct prefix values beyond
> which a 
> skip scan becomes too expensive compared to an FTS but during my
> tests in 
> preparation for my IOUG presentation I had a hard time constructing
> an 
> example where the optimizer would choose a skip scan - and I had
> tables 
> with just 1 distinct prefix value.
> My vote goes for your proposed two indices.
> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
> http://www.centrexcc.com
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Wolfgang Breitling
>   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!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: skip scan index

2003-05-29 Thread Goulet, Dick

Rachel,

For skip scan to work you'll need statistics, namely CBO, which if that's the 
case, create the one index if needed due to a unique constraint and forget about it.  
Chances are that the CBO will decide on a FTS anyway.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA 

-Original Message-
Sent: Wednesday, May 28, 2003 10:15 AM
To: Multiple recipients of list ORACLE-L


Got it this is going to buy me exactly nothing given my app and
database design and database size..


--- Mladen Gogala <[EMAIL PROTECTED]> wrote:
> I tried it and what it does is, essentially, a fast full index scan
> on the
> remaining columns of the index. To resolve the query, oracle does a
> full 
> sequential scan on the index instead on the table. If your index is
> one third
> size of the table, you saved quite a few IOs but don't expect
> anything like
> search on unique key performance.
> 
> On 2003.05.28 07:54 Mark Leith wrote:
> > Rachel,
> > 
> >
>
http://technet.oracle.com/oramag/webcolumns/2003/techarticles/schumacher_ski
> > pscan.html
> > http://www.oracle-base.com/Articles/9i/IndexSkipScanning.asp
> > 
> > I don't have any personal experience with them myself :( The first
> link
> > gives a pretty good overview though..
> > 
> > Mark
> > 
> > -Original Message-
> > Carmichael
> > Sent: 28 May 2003 12:00
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > Okay, I have a developer here who has been reading the docs (this
> can
> > be dangerous!)
> > 
> > we are adding functionality to one of our applications, this will
> > involve using multiple fulfillment houses, so we'll be adding the
> > fulfillment vendor id to the order table. Easy, this is not a
> problem.
> > We want to be able to search by order date and by fulfillment
> vendor
> > id/order date
> > 
> > Traditional design would be to add two indexes: one on order date,
> and
> > a concatenated one on fulfillment vendor id/order date.
> > 
> > The developer is telling me to create a "skip scan index" instead
> of
> > two different ones. MY reading in the FM tells me that skip scan
> index
> > is not a type of index, but rather a way Oracle uses to use an
> index
> > even if the leftmost column is not in the query.
> > 
> > Is there any benefit in my building only the one index? Our order
> > volume is not so high (and never will be) that there is a visible
> > performance impact if I have the two indices.
> > 
> > This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near
> future.
> > Solaris
> > 
> > Any suggestions/comments/war stories would be appreciated. I know
> I've
> > seen Jonathan post on skip scan indexes before but I can't find the
> > specific reference at the moment.
> > 
> > Rachel
> > 
> > __
> > Do you Yahoo!?
> > Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> > http://calendar.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > 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).
> > 
> > ---
> > Incoming mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> > 
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003
> > 
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > -- 
> > Author: Mark Leith
> >   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).
> > 
> 
> -- 
> Mladen Gogala
> Oracle DBA
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Mladen Gogala
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing

RE: Oracle, IBM fight of whose DB is more manageable

2003-05-29 Thread Mark Leith

What, like:

Oracle, Unbreakable?

;)

-Original Message-
Nørgaard
Sent: 28 May 2003 14:05
To: Multiple recipients of list ORACLE-L

It's an oxymoron, I'd say.

Gogala, Mladen wrote:

>What is a predictable corporate environment? I know of no such
>thing.
>
>Mladen Gogala
>Oracle DBA
>Phone:(203) 459-6855
>Email:[EMAIL PROTECTED]
>
>
>-Original Message-
>Sent: Tuesday, May 27, 2003 4:50 PM
>To: Multiple recipients of list ORACLE-L
>"Both will do most jobs, with Oracle excelling in the high-end, highly
>available marketplace. MSSQL costs far less, both in purchase and TCO
>and excels in the 2-4 processor predictable corporate environment
>without a HA requirement."
>
>Cheers
>
>Niall
>
>
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  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).

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.481 / Virus Database: 277 - Release Date: 13/05/2003

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Leith
  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: skip scan index

Kevin,

Thanks these will NOT be ad-hoc queries but part of the app -- for
the admin and customer service users. 

I'm leaning more and more towards setting things up so that we either
do a full table scan or use two indexes.

I just did a query -- since the app was released in December, we have
had just over 24,000 rows added to the order table. 

Trifling even if we do an FTS

Rachel

--- Kevin Toepke <[EMAIL PROTECTED]> wrote:
> Rachel
> 
> My experience with index skip scans can be summed up as follows. If
> you know
> the app will be doing a particular scan, create the index. 
> 
> Index Skip Scans should be thought of a means to help optimize those
> pesky
> ad-hoc queries only.
> 
> I haven't been able to get a skip-can to work unless there is a
> simple
> restriction (>, <, =) on the non-leading column. My experience tells
> me they
> don't help when you are joining against a non-leading column or you
> are
> using an IN condition (either static or sub-query)
> 
> HTH
> Kevin
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 7:00 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Okay, I have a developer here who has been reading the docs (this can
> be dangerous!)
> 
> we are adding functionality to one of our applications, this will
> involve using multiple fulfillment houses, so we'll be adding the
> fulfillment vendor id to the order table. Easy, this is not a
> problem.
> We want to be able to search by order date and by fulfillment vendor
> id/order date
> 
> Traditional design would be to add two indexes: one on order date,
> and
> a concatenated one on fulfillment vendor id/order date.
> 
> The developer is telling me to create a "skip scan index" instead of
> two different ones. MY reading in the FM tells me that skip scan
> index
> is not a type of index, but rather a way Oracle uses to use an index
> even if the leftmost column is not in the query.
> 
> Is there any benefit in my building only the one index? Our order
> volume is not so high (and never will be) that there is a visible
> performance impact if I have the two indices.
> 
> This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
> Solaris
> 
> Any suggestions/comments/war stories would be appreciated. I know
> I've
> seen Jonathan post on skip scan indexes before but I can't find the
> specific reference at the moment.
> 
> Rachel
> 
> __
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> 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).
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Kevin Toepke
>   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!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: skip scan index

Rachel,

First, I'd strongly suggest you look at 9.2.0.3... there are a number of bug
fixes in it and we have been running it here for a couple of months now I
would say with few problems.

With regards to the skip scans on indexes, your assumptions are correct.
I've seen some cases where skip scans made rather inefficient SQL much more
efficient and of course I've seen the reverse. Of course, when using 2
indexes, you have the potential for more IO, depending on the structure of
the indexes, how Oracle does the join, etc Depending on the order and
cardinality of the column in question that would be skipped, it index may
perform just fine via skip scan, or it might be a dog. We had one untuned
once-a-day, untuned SQL statement runing in production start performing like
lighting when we moved the DB to 9i from 8i. After looking at the explain
plan I found out that it started doing a skip scan on an index that
previously was unavailable to Oracle because of some date function
manipulation in the WHERE clause. Once Oracle could just "skip" that date
column, wammo, we got single index usage and great response times.

I generally prefer single index lookups vs. multipule index lookups, and my
experience is that single index scans perform better than say AND_EQUAL
operations.

My opinion, could always be WRONG (and have been in the past).

Robert


-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 9:40 AM

Richard,

the access would be order date or vendor id/order date (since it's
possible to look up by vendor id alone as well)

very low cardinality on vendor id -- right now I have all of two.

personal opinion is that the developer read something "cool" and
decided to tell the DBA how to do things, especially since the
statement was "create a skip scan index" :)

Rachel
--- Richard Foote <[EMAIL PROTECTED]> wrote:
> Hi Rachel,
> 
> Correct, "Skip Scan Index" is not a type of index but a method
> whereby
> Oracle can eliminate the need to visit leaf nodes by determining
> whether the
> leading column(s) have changed by sussing out only the branch nodes.
> It's
> possibly useful in situations where previously Oracle would not
> consider a
> concatenated index if the leading column of the index is unknown
> whereas now
> the optimizer might determine that sufficient leaf nodes can be
> avoided for
> the index to be of benefit. It's a kinda improved version of the full
> index
> scan (or not so full if you know what I mean),
> 
> However this requires the leading column to have *low* cardinality,
> low
> enough for the same repeated column from one leaf node to extent
> across all
> values of it's neighbouring leaf node. If the leading column changes
> from
> one leaf node to the next, then that leaf node must be at least
> visited
> (although subsequent inspection of the index values may enable Oracle
> to
> "pull out early" from having to read all index values, if a
> subsequent
> change in the leading column rules out all remaining entries).
> 
> A quick (and nasty) formula would be to consider the ratio of leaf
> nodes to
> distinct values (LN/DV). The higher the ratio the better with any
> value
> somewhat greater than 1 giving a skip scan index path a chance with
> the
> number representing an approximate number of leaf nodes that could be
> "skipped" per leading index value. This obviously assumes evenish
> distribution of leading column(s) index values.
> 
> However, getting back to your actual situation, if table access is
> only to
> be made via the order date or by order date and order id (and not
> necessarily by order id only), then you may find a single index order
> date
> || order id would meet all your requirements.
> 
> Cheers
> 
> Richard Foote
> 
> 
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, May 28, 2003 8:59 PM
> 
> 
> > Okay, I have a developer here who has been reading the docs (this
> can
> > be dangerous!)
> >
> > we are adding functionality to one of our applications, this will
> > involve using multiple fulfillment houses, so we'll be adding the
> > fulfillment vendor id to the order table. Easy, this is not a
> problem.
> > We want to be able to search by order date and by fulfillment
> vendor
> > id/order date
> >
> > Traditional design would be to add two indexes: one on order date,
> and
> > a concatenated one on fulfillment vendor id/order date.
> >
> > The developer is telling me to create a "skip scan index" instead
> of
> > two different ones. MY reading in the FM tells me that skip scan
> index
> > is not a type of index, but rather a way Oracle uses to use an
> index
> > even if the leftmost column is not in the query.
> >
> > Is there any benefit in my building only the one index? Our order
> > volume is not so high (and never will be) that there is a visible
> > performance impact if I have the two indices.
> >
> > This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in

Problem running form app in 9iAS

2003-05-29 Thread antonio . belloni

We have two servers: Server A running iAS ; Server B running IDS 9 and
serving files.

We map one drive from server B to server A and we are trying to access the
forms on the remote driver:

http://host:port/forms90/f90servlet?form=f:\form_name.fmx

But we receive an error saying that the servlet could not read the form
file.

If we copy the file from f:\ to a local drive , ie , c:\temp , the form can
be executed succesfully.

We are aware of a known bug issue in the old OAS that prevent using forms
apps running on remote drivers . but we think that it could (and should) be
fixed on iAS. Does anyone facing the same problem or trying to do something
similar on IAS ??

Both server are running Win2K.

Thanks in advance,
Antonio Belloni


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: skip scan index

List - If I wanted to know whether my query was taking advantage of index
skip scans, how would I know? Is there something different in the EXPLAIN
PLAN that I should look for? The discussion just made me curious.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Wednesday, May 28, 2003 9:30 AM
To: Multiple recipients of list ORACLE-L

At 02:59 AM 5/28/2003 -0800, you wrote:
>Okay, I have a developer here who has been reading the docs (this can
>be dangerous!)
>
>we are adding functionality to one of our applications, this will
>involve using multiple fulfillment houses, so we'll be adding the
>fulfillment vendor id to the order table. Easy, this is not a problem.
>We want to be able to search by order date and by fulfillment vendor
>id/order date
>
>Traditional design would be to add two indexes: one on order date, and
>a concatenated one on fulfillment vendor id/order date.
>
>The developer is telling me to create a "skip scan index" instead of
>two different ones. MY reading in the FM tells me that skip scan index
>is not a type of index, but rather a way Oracle uses to use an index
>even if the leftmost column is not in the query.
>
>Is there any benefit in my building only the one index? Our order
>volume is not so high (and never will be) that there is a visible
>performance impact if I have the two indices.
>
>This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
>Solaris
>
>Any suggestions/comments/war stories would be appreciated. I know I've
>seen Jonathan post on skip scan indexes before but I can't find the
>specific reference at the moment.

As others already said, it is a "index skip scan" access method, not a 
"skip scan" index. It is like an implicit OR where the optimizer looks up 
all distinct values for the missing prefix column(s) and augments the 
predicate (sort of) with these values and then does traditional index 
scans, ORing the results. It may not happen exactly that way, but 
conceptually that is what happens. From this you can deduce that it is an 
option only when there are relatively few distinct prefix values. In your 
case I doubt that the optimizer would ever choose a skip scan. Unless you 
have only a handfull (literally 5 or less) of fullfilment vendors. I don't 
have hard numbers as to the number of distinct prefix values beyond which a 
skip scan becomes too expensive compared to an FTS but during my tests in 
preparation for my IOUG presentation I had a hard time constructing an 
example where the optimizer would choose a skip scan - and I had tables 
with just 1 distinct prefix value.
My vote goes for your proposed two indices.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  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: 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).

Which method is more efficient

2003-05-29 Thread Rodrigues, Bryan

Hello everyone,

I have a question for the group of which method is more efficient. 

To set the stage my company has a process to load part changes from vendors
into the tables in an 8.1.7.4 Oracle database with archiving on and this
database has a standby database at disaster recovery site, so nologging is
not an option. 

There is a discussion going on as to which method is more effective for
updating the information in a table. In looking at effectiveness, I am
looking at reducing the amount of redo information produced and having the
database do the least amount of work.

1)  Method 1 is to update the information only for the fields that have
changed, 1 field at a time.
2)  Method 2 is to update the information for all the fields in the
record whether they have changed or not, 1 record at a time.

The size of the record is 1843 bytes and the distribution of field sizes:
 2 fields varchar2(240).
 1 field varchar2(150)
15 fields varchar2(50)
1 field varchar2(3)
2 fields varchar2(20)
4 fields varchar2(40)
3 fields varchar2(1)
2 fields varchar2(25)
2 fields number(10,2)
1 field number(13,2)
1 field number(1)
1 field number
1 field varchar2(6)
1 field number (17,2)
1 field varchar2(4)
3 fields that are date.

In the past couple of months the average number of fields changed per record
was 3 to 4 fields per record.

Thanks for your help,

Bryan Rodrigues
Oracle DBA
Elcom, Inc.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rodrigues, Bryan
  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).

Unbreakable, my buns. Upgrade to 9.2/w2k

Title: Unbreakable, my buns.  Upgrade to 9.2/w2k






For those of you who have upgraded to 9.2 on w2k:


How many of you have ended up with such a hosed server that you had to completely wipe Oracle off of it and start with a fresh install (9.2)?  It was spectacular!  I ended up in this situation and I sure hope it's something I did or didn't do.  I sure don't have time for this when I upgrade my production databases.  How I long for those days wading through problems in unix... it was so much easier and more enjoyable...  I will say this was definately a learning experience. 

And Tom Mercadante, your document worked like a charm, and saved my a$$...  Muchas Gracias!


Lisa Koivu

Oracle Database Administrator

Fairfield Resorts, Inc.

5259 Coconut Creek Parkway

Ft. Lauderdale, FL, USA  33063

Office: 954-935-4117  

Fax:    954-935-3639

Cell:    954-683-4459




--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Koivu, Lisa
  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: Hidden Columns

Bill - Try Google. A quick search yielded the fact this has something to do
with creating columns of objects, nested table, varray, or REF type.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Wednesday, May 28, 2003 9:45 AM
To: Multiple recipients of list ORACLE-L



Hi all,

I know this is RTFM but I can't find the right part in the FM to R.  So any 
help would be appreciated:

In the context of:

  DBMS_STATS.GATHER_DATABASE_STATS(method_opt=>'FOR ALL HIDDEN COLUMNS');

what is a "hidden column".  I tried gathering stats FOR ALL HIDDEN COLUMNS 
and it didn't seem to gather any stats for any columns.  What is it meant 
to do?

Thanks
- Bill.

PS. The complete syntax for the column is: FOR ALL [INDEXED | HIDDEN] 
COLUMNS [size_clause] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bill Buchan
  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: 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).

maxextents unlimited - value in dba_tables, dba_indexes

2003-05-29 Thread Cunningham, Gerald

Title: Message



Hi 
all,
 
I have a database 
where all tables and indexes have maxextents set to unlimited. Why, in 
dba_tables, do some tables have a null value for maxetents and some have a value 
of 2147483645?
 
An index I just 
modified (previous value of maxextents was 249) now has a null value for 
maxextents:
 

SQL> alter index app_owner.index1 storage (maxextents 
unlimited);
Index altered.
SQL> select owner, index_name, max_extents 
from dba_indexes  2  where owner = 'APP_OWNER' and index_name = 'INDEX1';
OWNER  
INDEX_NAME 
MAX_EXTENTS-- -- 
---APP_OWNER   INDEX1
 
It seems that some 
of the objects that have maxextents = null are partitioned, but not all. Anybody 
have an explanation??
 
Thanks!
 
- 
Jerry
 
 
Jerry Cunningham
Principal Database Engineer
Enterprise Applications
USi
[EMAIL PROTECTED]
 
Office    
410.897.3084
Cell  443.994.7359
SMS   [EMAIL PROTECTED]
AIM   cunninghamjerryc

Re: Hidden Columns

2003-05-29 Thread Wolfgang Breitling

Hidden columns are used if you use certain features of Oracle, e.g. 
materialized views, replication, objects

At 06:44 AM 5/28/2003 -0800, you wrote:

Hi all,

I know this is RTFM but I can't find the right part in the FM to R.  So 
any help would be appreciated:

In the context of:

 DBMS_STATS.GATHER_DATABASE_STATS(method_opt=>'FOR ALL HIDDEN COLUMNS');

what is a "hidden column".  I tried gathering stats FOR ALL HIDDEN COLUMNS 
and it didn't seem to gather any stats for any columns.  What is it meant 
to do?
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
 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: skip scan index

A short cut to test the new feature is using the hint index_ss(table,index).

Index skip scan is not an index scan or fast full scan.

Regards,

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L


Okay, I have a developer here who has been reading the docs (this can
be dangerous!)

we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  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: Problem running form app in 9iAS

2003-05-29 Thread Kevin Toepke

Yes, the OPERATION column in PLAN_TABLE will show something along the lines
of index access - skip. Easy to overlook, but its there.

-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 28, 2003 11:50 AM
To: Multiple recipients of list ORACLE-L


We have two servers: Server A running iAS ; Server B running IDS 9 and
serving files.

We map one drive from server B to server A and we are trying to access the
forms on the remote driver:

http://host:port/forms90/f90servlet?form=f:\form_name.fmx

But we receive an error saying that the servlet could not read the form
file.

If we copy the file from f:\ to a local drive , ie , c:\temp , the form can
be executed succesfully.

We are aware of a known bug issue in the old OAS that prevent using forms
apps running on remote drivers . but we think that it could (and should) be
fixed on iAS. Does anyone facing the same problem or trying to do something
similar on IAS ??

Both server are running Win2K.

Thanks in advance,
Antonio Belloni


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kevin Toepke
  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).

index-organized table question

2003-05-29 Thread Pardee, Roy E

Greetings all,

Say I have two main tables in a M:M relationship, and a junction table
resolving the relationship, like so:

create table staff
   (staff_id number PRIMARY KEY
   , name varchar2(50)
   , <>) ;

create table projects
   (proj_id number PRIMARY KEY
   , name varchar2(50)
   , mgr_id number
   , <>) ;

create table staff_projects
   (staff_id number
   , proj_id number
   , CONSTRAINT staff_projects_pk 
 PRIMARY KEY (staff_id, proj_id)
   , FOREIGN KEY (staff_id) REFERENCES staff(staff_id)
   , FOREIGN KEY (proj_id) REFERENCES projects(proj_id)
   ) ;

Queries that join staff_projects to projects to pull project info for a
given staff_id should be really fast, since staff_id is the leading column
in the index created to enforce the PK on staff_projects (right?)--and in
fact, the staff_projects table itself shouldn't need to be touched, since
all the needed info is in this index (also right?).

If I want to speed lookups of staff info for a given proj_id, I can create
another unique index on staff_projects(proj_id, staff_id).  There again, the
staff_projects table shouldn't need to be touched, b/c all the info is in
the index.

At this point, it seems like the table is sort of superflous--all the info
in it is better accessible in the two indexes.  If that's right--is there an
advantage in making staff_projects an index organized table?

Thanks!

-Roy

Roy Pardee
Programmer/Analyst/DBA
SWFPAC Lockheed Martin IT
Extension 8487
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pardee, Roy E
  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: skip scan index

2003-05-29 Thread Hengen, Brian

I've done a little bit research and testing on this and one thing that I've
found is that the optimizer will only choose a skip-scan route if the
leading column of the index is relatively non-selective.  I haven't been
able to pin down how non-selective it has to be, but I've never had one kick
in with a unique leading column, and it always seems to choose one if the
leading column of the index has only a few distinct values in it.

--Brian

-Original Message-
Sent: Wednesday, May 28, 2003 5:00 AM
To: Multiple recipients of list ORACLE-L


Okay, I have a developer here who has been reading the docs (this can
be dangerous!)

we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hengen, Brian
  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: Which method is more efficient

2003-05-29 Thread Jamadagni, Rajendra

Title: RE: Which method is more efficient





I vote for the whole record, you'd be spending more time and resources in finding _which_ field has changed.


Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !



-Original Message-
From: Rodrigues, Bryan [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 28, 2003 11:50 AM
To: Multiple recipients of list ORACLE-L
Subject: Which method is more efficient



Hello everyone,


I have a question for the group of which method is more efficient. 


To set the stage my company has a process to load part changes from vendors
into the tables in an 8.1.7.4 Oracle database with archiving on and this
database has a standby database at disaster recovery site, so nologging is
not an option. 


There is a discussion going on as to which method is more effective for
updating the information in a table. In looking at effectiveness, I am
looking at reducing the amount of redo information produced and having the
database do the least amount of work.


1)  Method 1 is to update the information only for the fields that have
changed, 1 field at a time.
2)  Method 2 is to update the information for all the fields in the
record whether they have changed or not, 1 record at a time.


The size of the record is 1843 bytes and the distribution of field sizes:
 2 fields varchar2(240).
 1 field varchar2(150)
15 fields varchar2(50)
1 field varchar2(3)
2 fields varchar2(20)
4 fields varchar2(40)
3 fields varchar2(1)
2 fields varchar2(25)
2 fields number(10,2)
1 field number(13,2)
1 field number(1)
1 field number
1 field varchar2(6)
1 field number (17,2)
1 field varchar2(4)
3 fields that are date.


In the past couple of months the average number of fields changed per record
was 3 to 4 fields per record.


Thanks for your help,


Bryan Rodrigues
Oracle DBA
Elcom, Inc.



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rodrigues, Bryan
  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).



*This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*1

Re: Unbreakable, my buns. Upgrade to 9.2/w2k

2003-05-29 Thread Thomas Day


I was not able to upgrade an 8.1.7.0 database to 9.2.0.1.0 on Win2K.  I had
to create a new Oracle home, install 9.2, and clone the database in 9.2.
OUI did not upgrade successfully.



   

  "Koivu, Lisa"

  
  @Fairfieldresort cc: 

  s.com>   Subject: Unbreakable, my buns.  Upgrade 
to 9.2/w2k  
  Sent by: root

   

   

  05/28/2003 12:25 

  PM   

  Please respond   

  to ORACLE-L  

   

   





For those of you who have upgraded to 9.2 on w2k:


How many of you have ended up with such a hosed server that you had to
completely wipe Oracle off of it and start with a fresh install (9.2)?  It
was spectacular!  I ended up in this situation and I sure hope it's
something I did or didn't do.  I sure don't have time for this when I
upgrade my production databases.  How I long for those days wading through
problems in unix... it was so much easier and more enjoyable...  I will say
this was definately a learning experience.


And Tom Mercadante, your document worked like a charm, and saved my a$$...
Muchas Gracias!


Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA  33063
Office: 954-935-4117
Fax:954-935-3639
Cell:954-683-4459


-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author:
Koivu, Lisa 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: Thomas Day
  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: Unbreakable, my buns. Upgrade to 9.2/w2k

2003-05-29 Thread Seefelt, Beth

Title: Message



I upgaded 2 
production servers.  One running 9 instances, one running 3.  
Both are serving data warehouses.  Had no problem.
 
 

-Original Message-From: Koivu, Lisa 
[mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 
2003 12:25 PMTo: Multiple recipients of list 
ORACLE-LSubject: Unbreakable, my buns. Upgrade to 
9.2/w2k
For those of you who have upgraded to 9.2 on 
w2k: 
How many of you have ended up with such a hosed 
server that you had to completely wipe Oracle off of it and start with a fresh 
install (9.2)?  It was spectacular!  I ended up in this situation and 
I sure hope it's something I did or didn't do.  I sure don't have time for 
this when I upgrade my production databases.  How I long for those days 
wading through problems in unix... it was so much easier and more 
enjoyable...  I will say this was definately a learning experience. 

And Tom Mercadante, your document worked like a 
charm, and saved my a$$...  Muchas Gracias! 
Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut 
Creek Parkway Ft. Lauderdale, FL, USA  
33063 Office: 954-935-4117  
Fax:    954-935-3639 
Cell:    954-683-4459 -- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Koivu, 
Lisa 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: Which method is more efficient

2003-05-29 Thread rgaffuri

1. to totally eliminate redo, load your staging records into a global temp table. it 
has absolutely no redo and is very fast. 

2. if your in 9i, use an external table and a merge command and update the table 
directly from the file. dont even load the records to a staging table. Do a search for 
this on asktom. He explains it in detail. 

3. This is the fastest update statement you can right...

UPDATE(select a.col1 a_col1, b.col1 b_col1
 from tab1 a, tab2 b
where a.pk = b.pk)
set a_col1 = b_col1;

need primary keys on both tables or it wont work. This is hands down the fastest 
update you can do. 
> 
> From: "Rodrigues, Bryan" <[EMAIL PROTECTED]>
> Date: 2003/05/28 Wed AM 11:49:49 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Which method is more efficient
> 
> Hello everyone,
> 
> I have a question for the group of which method is more efficient. 
> 
> To set the stage my company has a process to load part changes from vendors
> into the tables in an 8.1.7.4 Oracle database with archiving on and this
> database has a standby database at disaster recovery site, so nologging is
> not an option. 
> 
> There is a discussion going on as to which method is more effective for
> updating the information in a table. In looking at effectiveness, I am
> looking at reducing the amount of redo information produced and having the
> database do the least amount of work.
> 
> 1)Method 1 is to update the information only for the fields that have
> changed, 1 field at a time.
> 2)Method 2 is to update the information for all the fields in the
> record whether they have changed or not, 1 record at a time.
> 
> The size of the record is 1843 bytes and the distribution of field sizes:
>  2 fields varchar2(240).
>  1 field varchar2(150)
> 15 fields varchar2(50)
> 1 field varchar2(3)
> 2 fields varchar2(20)
> 4 fields varchar2(40)
> 3 fields varchar2(1)
> 2 fields varchar2(25)
> 2 fields number(10,2)
> 1 field number(13,2)
> 1 field number(1)
> 1 field number
> 1 field varchar2(6)
> 1 field number (17,2)
> 1 field varchar2(4)
> 3 fields that are date.
> 
> In the past couple of months the average number of fields changed per record
> was 3 to 4 fields per record.
> 
> Thanks for your help,
> 
> Bryan Rodrigues
> Oracle DBA
> Elcom, Inc.
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Rodrigues, Bryan
>   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: <[EMAIL PROTECTED]
  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: maxextents unlimited - value in dba_tables, dba_indexes

Jerry - I did a quick survey of some of my databases and only found a single
table that had a null MAX_EXTENTS and it was not partitioned. Its name was
ATEMPTAB$ owned by SYS.



Dennis Williams 
DBA, 80%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Wednesday, May 28, 2003 11:15 AM
To: Multiple recipients of list ORACLE-L


Hi all,
 
I have a database where all tables and indexes have maxextents set to
unlimited. Why, in dba_tables, do some tables have a null value for
maxetents and some have a value of 2147483645?
 
An index I just modified (previous value of maxextents was 249) now has a
null value for maxextents:
 
SQL> alter index app_owner.index1 storage (maxextents unlimited);

Index altered.

SQL> select owner, index_name, max_extents from dba_indexes
  2  where owner = 'APP_OWNER' and index_name = 'INDEX1';

OWNER  INDEX_NAME MAX_EXTENTS
-- -- ---
APP_OWNER   INDEX1

 

It seems that some of the objects that have maxextents = null are
partitioned, but not all. Anybody have an explanation??
 
Thanks!
 
- Jerry
 
 
Jerry Cunningham
Principal Database Engineer
Enterprise Applications
USi
[EMAIL PROTECTED]  
 
Office410.897.3084
Cell  443.994.7359
SMS[EMAIL PROTECTED]  
AIM   cunninghamjerryc
 
 

-- 
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: Which method is more efficient

Bryan - If this is a critical issue, I would try it both ways on a test
database and use log miner to examine the amount of redo that is generated.
My recollection is that you will find that the redo record records the
before and after data for each field. So just updating all fields may
generate significantly more redo. But don't trust my recollection on this
issue, test it yourself.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Wednesday, May 28, 2003 10:50 AM
To: Multiple recipients of list ORACLE-L


Hello everyone,

I have a question for the group of which method is more efficient. 

To set the stage my company has a process to load part changes from vendors
into the tables in an 8.1.7.4 Oracle database with archiving on and this
database has a standby database at disaster recovery site, so nologging is
not an option. 

There is a discussion going on as to which method is more effective for
updating the information in a table. In looking at effectiveness, I am
looking at reducing the amount of redo information produced and having the
database do the least amount of work.

1)  Method 1 is to update the information only for the fields that have
changed, 1 field at a time.
2)  Method 2 is to update the information for all the fields in the
record whether they have changed or not, 1 record at a time.

The size of the record is 1843 bytes and the distribution of field sizes:
 2 fields varchar2(240).
 1 field varchar2(150)
15 fields varchar2(50)
1 field varchar2(3)
2 fields varchar2(20)
4 fields varchar2(40)
3 fields varchar2(1)
2 fields varchar2(25)
2 fields number(10,2)
1 field number(13,2)
1 field number(1)
1 field number
1 field varchar2(6)
1 field number (17,2)
1 field varchar2(4)
3 fields that are date.

In the past couple of months the average number of fields changed per record
was 3 to 4 fields per record.

Thanks for your help,

Bryan Rodrigues
Oracle DBA
Elcom, Inc.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rodrigues, Bryan
  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: 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: Unbreakable, my buns. Upgrade to 9.2/w2k

and THAT is the reason NOT, repeat, NOT to use the OUI to do upgrades. Do
'em manually, I've had a very good success rate that way. Never trust the
GUI's. Never.

Robert

(self-proclaimed GUI biggot)

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 11:56 AM


I was not able to upgrade an 8.1.7.0 database to 9.2.0.1.0 on Win2K.  I
had
to create a new Oracle home, install 9.2, and clone the database in 9.2.
OUI did not upgrade successfully.



 

  "Koivu, Lisa"

  
  @Fairfieldresort cc:

  s.com>   Subject: Unbreakable, my
buns.  Upgrade to 9.2/w2k  
  Sent by: root

 

 

  05/28/2003 12:25

  PM

  Please respond

  to ORACLE-L

 

 





For those of you who have upgraded to 9.2 on w2k:


How many of you have ended up with such a hosed server that you had to
completely wipe Oracle off of it and start with a fresh install (9.2)?
It
was spectacular!  I ended up in this situation and I sure hope it's
something I did or didn't do.  I sure don't have time for this when I
upgrade my production databases.  How I long for those days wading
through
problems in unix... it was so much easier and more enjoyable...  I will
say
this was definately a learning experience.


And Tom Mercadante, your document worked like a charm, and saved my
a$$...
Muchas Gracias!


Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA  33063
Office: 954-935-4117
Fax:954-935-3639
Cell:954-683-4459


-- Please see the official ORACLE-L FAQ: http://www.orafaq.net --
Author:
Koivu, Lisa 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: Thomas Day
  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: Freeman Robert - IL
  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: Which method is more efficient

2003-05-29 Thread Goulet, Dick

Bryan,

First item is to define "efficient".  Are you looking for efficiency in the 
update of data on the production database or the standby?  The code needed to 
determine which columns need updating is going to be a real bear and could create 
several update statements per record.  On the other hand the standby is going to 
expend the same amount of effort one way as the other.  Personally I'd update 
everything, except the primary key.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA 

-Original Message-
Sent: Wednesday, May 28, 2003 11:50 AM
To: Multiple recipients of list ORACLE-L


Hello everyone,

I have a question for the group of which method is more efficient. 

To set the stage my company has a process to load part changes from vendors
into the tables in an 8.1.7.4 Oracle database with archiving on and this
database has a standby database at disaster recovery site, so nologging is
not an option. 

There is a discussion going on as to which method is more effective for
updating the information in a table. In looking at effectiveness, I am
looking at reducing the amount of redo information produced and having the
database do the least amount of work.

1)  Method 1 is to update the information only for the fields that have
changed, 1 field at a time.
2)  Method 2 is to update the information for all the fields in the
record whether they have changed or not, 1 record at a time.

The size of the record is 1843 bytes and the distribution of field sizes:
 2 fields varchar2(240).
 1 field varchar2(150)
15 fields varchar2(50)
1 field varchar2(3)
2 fields varchar2(20)
4 fields varchar2(40)
3 fields varchar2(1)
2 fields varchar2(25)
2 fields number(10,2)
1 field number(13,2)
1 field number(1)
1 field number
1 field varchar2(6)
1 field number (17,2)
1 field varchar2(4)
3 fields that are date.

In the past couple of months the average number of fields changed per record
was 3 to 4 fields per record.

Thanks for your help,

Bryan Rodrigues
Oracle DBA
Elcom, Inc.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rodrigues, Bryan
  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: Goulet, Dick
  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: skip scan index

I've had a couple kick in where full scans were happening before (badly
tuned SQL with out a proper index) and in one case I saw a three index
and-equal become a skip scan. 

I'm about 70/30 against skip scans on performance improvements with hints.

RF

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 11:35 AM

I've done a little bit research and testing on this and one thing that
I've
found is that the optimizer will only choose a skip-scan route if the
leading column of the index is relatively non-selective.  I haven't been
able to pin down how non-selective it has to be, but I've never had one
kick
in with a unique leading column, and it always seems to choose one if
the
leading column of the index has only a few distinct values in it.

--Brian

-Original Message-
Sent: Wednesday, May 28, 2003 5:00 AM
To: Multiple recipients of list ORACLE-L


Okay, I have a developer here who has been reading the docs (this can
be dangerous!)

we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hengen, Brian
  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: Freeman Robert - IL
  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: skip scan index

The execution plan indicates if a skip scan is happening. Can't remember the
exact verbage and I don't have a convienient plan with one handy to pull
out, but you will know it when you see it.

RF

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 10:45 AM

List - If I wanted to know whether my query was taking advantage of
index
skip scans, how would I know? Is there something different in the
EXPLAIN
PLAN that I should look for? The discussion just made me curious.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Wednesday, May 28, 2003 9:30 AM
To: Multiple recipients of list ORACLE-L

At 02:59 AM 5/28/2003 -0800, you wrote:
>Okay, I have a developer here who has been reading the docs (this can
>be dangerous!)
>
>we are adding functionality to one of our applications, this will
>involve using multiple fulfillment houses, so we'll be adding the
>fulfillment vendor id to the order table. Easy, this is not a problem.
>We want to be able to search by order date and by fulfillment vendor
>id/order date
>
>Traditional design would be to add two indexes: one on order date, and
>a concatenated one on fulfillment vendor id/order date.
>
>The developer is telling me to create a "skip scan index" instead of
>two different ones. MY reading in the FM tells me that skip scan index
>is not a type of index, but rather a way Oracle uses to use an index
>even if the leftmost column is not in the query.
>
>Is there any benefit in my building only the one index? Our order
>volume is not so high (and never will be) that there is a visible
>performance impact if I have the two indices.
>
>This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
>Solaris
>
>Any suggestions/comments/war stories would be appreciated. I know I've
>seen Jonathan post on skip scan indexes before but I can't find the
>specific reference at the moment.

As others already said, it is a "index skip scan" access method, not a 
"skip scan" index. It is like an implicit OR where the optimizer looks
up 
all distinct values for the missing prefix column(s) and augments the 
predicate (sort of) with these values and then does traditional index 
scans, ORing the results. It may not happen exactly that way, but 
conceptually that is what happens. From this you can deduce that it is
an 
option only when there are relatively few distinct prefix values. In
your 
case I doubt that the optimizer would ever choose a skip scan. Unless
you 
have only a handfull (literally 5 or less) of fullfilment vendors. I
don't 
have hard numbers as to the number of distinct prefix values beyond
which a 
skip scan becomes too expensive compared to an FTS but during my tests
in 
preparation for my IOUG presentation I had a hard time constructing an 
example where the optimizer would choose a skip scan - and I had tables 
with just 1 distinct prefix value.
My vote goes for your proposed two indices.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  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: 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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  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 t

RE: skip scan index

>> Trifling even if we do an FTS

Until your developers develop a query next month that joins that table to
the 200 million row table they are planning on installing but just forgot to
tell you about.

Developers are funny that way.


"Excuse me, did you plan any indexing on this table??"
"Hints? We don't need no stinking hints, the optimzer is way to smart to
need hints."

Robert

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 10:29 AM

Kevin,

Thanks these will NOT be ad-hoc queries but part of the app -- for
the admin and customer service users. 

I'm leaning more and more towards setting things up so that we either
do a full table scan or use two indexes.

I just did a query -- since the app was released in December, we have
had just over 24,000 rows added to the order table. 

Trifling even if we do an FTS

Rachel

--- Kevin Toepke <[EMAIL PROTECTED]> wrote:
> Rachel
> 
> My experience with index skip scans can be summed up as follows. If
> you know
> the app will be doing a particular scan, create the index. 
> 
> Index Skip Scans should be thought of a means to help optimize those
> pesky
> ad-hoc queries only.
> 
> I haven't been able to get a skip-can to work unless there is a
> simple
> restriction (>, <, =) on the non-leading column. My experience tells
> me they
> don't help when you are joining against a non-leading column or you
> are
> using an IN condition (either static or sub-query)
> 
> HTH
> Kevin
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 7:00 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Okay, I have a developer here who has been reading the docs (this can
> be dangerous!)
> 
> we are adding functionality to one of our applications, this will
> involve using multiple fulfillment houses, so we'll be adding the
> fulfillment vendor id to the order table. Easy, this is not a
> problem.
> We want to be able to search by order date and by fulfillment vendor
> id/order date
> 
> Traditional design would be to add two indexes: one on order date,
> and
> a concatenated one on fulfillment vendor id/order date.
> 
> The developer is telling me to create a "skip scan index" instead of
> two different ones. MY reading in the FM tells me that skip scan
> index
> is not a type of index, but rather a way Oracle uses to use an index
> even if the leftmost column is not in the query.
> 
> Is there any benefit in my building only the one index? Our order
> volume is not so high (and never will be) that there is a visible
> performance impact if I have the two indices.
> 
> This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
> Solaris
> 
> Any suggestions/comments/war stories would be appreciated. I know
> I've
> seen Jonathan post on skip scan indexes before but I can't find the
> specific reference at the moment.
> 
> Rachel
> 
> __
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> 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).
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Kevin Toepke
>   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!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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

RE: skip scan index

A skip scan can be a index scan, full scan or range scan type access. It
simply allows a unusable column to be "deselected" from the index (for lack
of a better word) during these operations.

RF

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 11:15 AM

A short cut to test the new feature is using the hint
index_ss(table,index).

Index skip scan is not an index scan or fast full scan.

Regards,

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L


Okay, I have a developer here who has been reading the docs (this can
be dangerous!)

we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  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: Freeman Robert - IL
  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: RE: Which method is more efficient

2003-05-29 Thread rgaffuri

oh i missed part of it. the question is how do you figure out which fields have 
changed? if you have to do an anti-join on each field, then do an update of every 
field. 

the question is how will you determine which fields have changed? 
> 
> From: DENNIS WILLIAMS <[EMAIL PROTECTED]>
> Date: 2003/05/28 Wed PM 12:59:51 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: Which method is more efficient
> 
> Bryan - If this is a critical issue, I would try it both ways on a test
> database and use log miner to examine the amount of redo that is generated.
> My recollection is that you will find that the redo record records the
> before and after data for each field. So just updating all fields may
> generate significantly more redo. But don't trust my recollection on this
> issue, test it yourself.
> 
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED] 
> 
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 10:50 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hello everyone,
> 
> I have a question for the group of which method is more efficient. 
> 
> To set the stage my company has a process to load part changes from vendors
> into the tables in an 8.1.7.4 Oracle database with archiving on and this
> database has a standby database at disaster recovery site, so nologging is
> not an option. 
> 
> There is a discussion going on as to which method is more effective for
> updating the information in a table. In looking at effectiveness, I am
> looking at reducing the amount of redo information produced and having the
> database do the least amount of work.
> 
> 1)Method 1 is to update the information only for the fields that have
> changed, 1 field at a time.
> 2)Method 2 is to update the information for all the fields in the
> record whether they have changed or not, 1 record at a time.
> 
> The size of the record is 1843 bytes and the distribution of field sizes:
>  2 fields varchar2(240).
>  1 field varchar2(150)
> 15 fields varchar2(50)
> 1 field varchar2(3)
> 2 fields varchar2(20)
> 4 fields varchar2(40)
> 3 fields varchar2(1)
> 2 fields varchar2(25)
> 2 fields number(10,2)
> 1 field number(13,2)
> 1 field number(1)
> 1 field number
> 1 field varchar2(6)
> 1 field number (17,2)
> 1 field varchar2(4)
> 3 fields that are date.
> 
> In the past couple of months the average number of fields changed per record
> was 3 to 4 fields per record.
> 
> Thanks for your help,
> 
> Bryan Rodrigues
> Oracle DBA
> Elcom, Inc.
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Rodrigues, Bryan
>   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: 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).
> 
> 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <[EMAIL PROTECTED]
  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: Oracle Performance Tuning 101 Book now Available!

2003-05-29 Thread Johnson, Michael

Title: Precise Software Solutions



IMHO,  Every DBA should own this book.  It will change 
your way of thinking about how to approach oracle performance 
problems.
 
 Mike

  
-Original Message-From: Kent Mingus 
[mailto:[EMAIL PROTECTED]Sent: 
Wednesday, May 28, 2003 6:50 AMTo: 
[EMAIL PROTECTED]Subject: Oracle Performance 
Tuning 101 Book now Available!

To unsubscribe 
from this Precise Software email 
list click 
here and submit your email address. 
 


  
  

  


  

  
  Free Book from Oracle 
Press
  

  
  


  

  
  
  
  

  

  
  

  
  

  Good 
  Oracle Hit-Ratios Don't Make Users Happy...Good 
  Performance Does!
  Conventional tuning approaches rely heavily 
  on checking the Buffer Cache Hit ratio. But even though 
  DBAs do their best to get a 99% or better hit-ratio they 
  discover that the performance of their database isn't 
  really improving when the hit-ratio gets better.  
  
  More over, 
  "ratio" tuning does not consider what the database is 
  doing for the application, and application performance is 
  what the end-user sees.  
  
  Make your end 
  users happy and reduce your workweek by following these 
  simple steps: 
  -  Download this FREE book, Oracle 
  Performance Tuning 101, and dispel the myths and 
  folklore about performance tuning. 
  -  Register for a 
  webinar on Precise's 
  response-time tuning solutions. 
  -  Visit our 
  website or give us a 
  call for more information on our Oracle tuning solutions - 
  like "Precise/Indepth for Oracle ... the best in a 
  hotly contested market segment for Oracle instance 
  monitoring and tuning tools." - Gartner 
  
  
  
  
  

  


   
   
  


  
Click 
here to Download 
Your FREE Book now! 
 
 
 
 

  
  
  Download 
the Book

  

  

  

  


   
  
 

  

  Precise Software Solutions T: 1 781 461 0700 
W: www.precise.com E: [EMAIL PROTECTED]

RE: Unbreakable, my buns. Upgrade to 9.2/w2k

2003-05-29 Thread Tony Johnson

Title: Unbreakable, my buns. Upgrade to 9.2/w2k



I have 
upgraded 16 databases ( manually using the scripts and procedures in metablink 
as a guide ) with very few problems and only then on the 1st ones I 
did.
We 
were going from 8174 ro 92021 on Win2k SP3.

  -Original Message-From: Koivu, Lisa 
  [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 
  2003 9:25 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Unbreakable, my buns. Upgrade to 
  9.2/w2k
  For those of you who have upgraded to 9.2 on 
  w2k: 
  How many of you have ended up with such a hosed 
  server that you had to completely wipe Oracle off of it and start with a fresh 
  install (9.2)?  It was spectacular!  I ended up in this situation 
  and I sure hope it's something I did or didn't do.  I sure don't have 
  time for this when I upgrade my production databases.  How I long for 
  those days wading through problems in unix... it was so much easier and more 
  enjoyable...  I will say this was definately a learning experience. 
  
  And Tom Mercadante, your document worked like a 
  charm, and saved my a$$...  Muchas Gracias! 
  Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 
  Coconut Creek Parkway Ft. Lauderdale, FL, 
  USA  33063 Office: 954-935-4117  
  Fax:    954-935-3639 
  Cell:    954-683-4459 
  -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 
  Koivu, Lisa 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: Unbreakable, my buns. Upgrade to 9.2/w2k

As much as I prefer the command line do it yourself approach, I did have 
one
occasion recently where I did use the GUI, and it worked fine.

In the process of upgrading several 8.0.4 databases to 8.1.7, I had 
trouble with
one database.  Sorry, can't recall the details, but I went over the 
checklist several
times and am fairly sure I did all the steps correctly. 

For some reason, that one would not upgrade.  ( As I said, don't recall 
the error
that was occurring. )

Used the GUI, upgrade without a problem.

All the others were done via command line, not sure why that one was a 
problem, but
the GUI seemed to know something not included in the manual method.

Kind of like the (unofficial) Bond movie, "Never Say Never Again".  :)

Jared






Freeman Robert - IL <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 05/28/2003 10:20 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: Unbreakable, my buns.  Upgrade to 9.2/w2k


and THAT is the reason NOT, repeat, NOT to use the OUI to do upgrades. Do
'em manually, I've had a very good success rate that way. Never trust the
GUI's. Never.

Robert

(self-proclaimed GUI biggot)

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 11:56 AM


I was not able to upgrade an 8.1.7.0 database to 9.2.0.1.0 on Win2K.  I
had
to create a new Oracle home, install 9.2, and clone the database in 9.2.
OUI did not upgrade successfully.



 

  "Koivu, Lisa"

   
  @Fairfieldresort cc:

  s.com>   Subject: Unbreakable, my
buns.  Upgrade to 9.2/w2k 
  Sent by: root

 

 

  05/28/2003 12:25

  PM

  Please respond

  to ORACLE-L

 

 





For those of you who have upgraded to 9.2 on w2k:


How many of you have ended up with such a hosed server that you had to
completely wipe Oracle off of it and start with a fresh install (9.2)?
It
was spectacular!  I ended up in this situation and I sure hope it's
something I did or didn't do.  I sure don't have time for this when I
upgrade my production databases.  How I long for those days wading
through
problems in unix... it was so much easier and more enjoyable...  I will
say
this was definately a learning experience.


And Tom Mercadante, your document worked like a charm, and saved my
a$$...
Muchas Gracias!


Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA  33063
Office: 954-935-4117
Fax:954-935-3639
Cell:954-683-4459


-- Please see the official ORACLE-L FAQ: http://www.orafaq.net --
Author:
Koivu, Lisa 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: Thomas Day
  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: Freeman Robert - IL
  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: 
  INET: [EMAIL PROTECTED]

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

SAP Hands SAP DB over to MySQL

The past few months I've been wondering when MySQL would start
putting pressure on Oracle in the same way that Linux is putting
pressure on MS.

Maybe sooner than you think:

http://news.com.com/2100-1012_3-1010522.html?tag=fd_top


Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: skip scan index

Actually, it is an index full scan. 

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 12:16 PM
To: Multiple recipients of list ORACLE-L


A short cut to test the new feature is using the hint index_ss(table,index).

Index skip scan is not an index scan or fast full scan.

Regards,

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L


Okay, I have a developer here who has been reading the docs (this can
be dangerous!)

we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  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: Gogala, Mladen
  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: Oracle Performance Tuning 101 Book now Available!

2003-05-29 Thread Charlie_Mengler


I refuse to do business with spammers;  regardless of what their product
may be.



   
   
  "Johnson, Michael "  
   
  <[EMAIL PROTECTED]To:   Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]> 
  fmc.af.mil>   cc:
   
  Sent by:  Subject:  RE: Oracle Performance 
Tuning 101 Book now Available!   
  [EMAIL PROTECTED]

   
   
   
   
  05/28/2003 10:44 AM  
   
  Please respond to
   
  ORACLE-L 
   
   
   
   
   





IMHO,  Every DBA should own this book.  It will change your way of thinking
about how to approach oracle performance problems.

 Mike
-Original Message-
From: Kent Mingus
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 28, 2003 6:50 AM
To: [EMAIL PROTECTED]
Subject: Oracle Performance Tuning 101 Book now Available!

To unsubscribe from this Precise Software email list click here
and submit your email address.

|-|
| |
| |
| |
| |
|(Embedded image moved to file: pic07195.gif) |
|(Embedded image moved to file: pic07825.gif) |
|Free Book from Oracle|
|Press|
|   (E|
|   mb|
|   ed|
|   de|
|   d |
|   im|
|   ag|
|   e |
|   mo|
|   ve|
|   d |
|   to|
|   fi|
|   le|
|   : |
|   pi|
|   c0|
|   38|
|   30|
|   .g|
|   if|
|   ) |
| |
| |
|(Embedded image moved to file: pic15360.gif) |
| |
| |
|

RE: Bulk collect got truncated?

2003-05-29 Thread Gorbounov,Vadim


Hi dear listers, 

Some of you may still remember this thread, bulk collect truncated to 65535
records sometimes.
I've got this case reproduceable and tried all suggestions , 

In a brief, 
 SELECT returns 318847 rows, 
 INSERT INTO FROM SELECT - 318847 rows, 
 PL/SQL plain FOR cr IN (select ..) LOOP - - 318847 rows
 PL/SQL with BULK COLLECT many different code versions - sometimes  returs
65535 records instead, the rest is truncated

What might be interesting, in case when it fails, it doesn't retrieve
requiered rows from disk. I can judge it by much shorter responce time and
10046 trace doesn't show db file sequential read events what always showup
when number of rows is correct.

10046 trace provides interesting details,  65535 records is approximately
the point (=/- 50 records) where it usually does first db file sequential
read in case of successful execution.

So Waleed, apparently there is a problem here, 9.0.1.4 Solaris. Time to TAR
now.

Below is the spool from my recent session.

Have a good day, 
Vadim

set serveroutput on
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index
by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index
by binary_integer;
  4 esubsvcid   t_sub_svc_id;
  5 evalt_subsvcext_key;
  6  BEGIN
  7 SELECT /*+ index(p sub_svc_parm_ix2) */
  8sub_svc_id, val
  9   BULK COLLECT INTO esubsvcid, eval
 10   FROM CBQA4SP.sub_svc_parm p
 11  WHERE parm_id =10;
 12 dbms_output.put_line(esubsvcid.count);
 13  end;
 14  /
318847


PL/SQL procedure successfully completed.

Elapsed: 00:00:12.03
SQL> alter session set events = '10046 trace name context forever, level 8';

Session altered.

Elapsed: 00:00:00.00
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index
by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index
by binary_integer;
  4 esubsvcid   t_sub_svc_id;
  5 evalt_subsvcext_key;
  6  BEGIN
  7 SELECT /*+ index(p sub_svc_parm_ix2) */
  8sub_svc_id, val
  9   BULK COLLECT INTO esubsvcid, eval
 10   FROM CBQA4SP.sub_svc_parm p
 11  WHERE parm_id =10;
 12 dbms_output.put_line(esubsvcid.count);
 13  end;
 14  /
65535


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> alter session set events = '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set sql_trace= true;

Session altered.

Elapsed: 00:00:00.00
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index
by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index
by binary_integer;
  4 esubsvcid   t_sub_svc_id;
  5 evalt_subsvcext_key;
  6  BEGIN
  7 SELECT /*+ index(p sub_svc_parm_ix2) */
  8sub_svc_id, val
  9   BULK COLLECT INTO esubsvcid, eval
 10   FROM CBQA4SP.sub_svc_parm p
 11  WHERE parm_id =10;
 12 dbms_output.put_line(esubsvcid.count);
 13  end;
 14  /
65535


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> alter session set sql_trace= false;

Session altered.

Elapsed: 00:00:00.00
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index
by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index
by binary_integer;
  4 esubsvcid   t_sub_svc_id;
  5 evalt_subsvcext_key;
  6  BEGIN
  7 SELECT /*+ index(p sub_svc_parm_ix2) */
  8sub_svc_id, val
  9   BULK COLLECT INTO esubsvcid, eval
 10   FROM CBQA4SP.sub_svc_parm p
 11  WHERE parm_id =10;
 12 dbms_output.put_line(esubsvcid.count);
 13  end;
 14  /
65535


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> SELECT /*+ index(p sub_svc_parm_ix2) */ count(*)  FROM
CBQA4SP.sub_svc_parm p
  2  WHERE parm_id =10;

  COUNT(*)

--

318847


Elapsed: 00:00:00.03
SQL> 
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF number index by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF varchar2(255) index by
binary_integer;
  4 esubsvcid   t_sub_svc_id;
  5 evalt_subsvcext_key;
  6  BEGIN
  7 SELECT /*+ index(p sub_svc_parm_ix2) */
  8sub_svc_id, val
  9   BULK COLLECT INTO esubsvcid, eval
 10   FROM CBQA4SP.sub_svc_parm p
 11  WHERE parm_id =10;
 12 dbms_output.put_line(esubsvcid.count);
 13  end;
 14  /
65535


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> 
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF number;
  3 TYPE t_subsvcext_key IS TABLE OF varchar2(255) ;
  4 esubsvcid   t_sub_svc_id;
  5 evalt_subsvcext_key;
  6  BEGIN
  7 SELECT /*+ index(p sub_svc_parm_ix2) */
  8sub_svc_id, val
  9   BULK COLLECT INTO esubsvcid, eval
 10

RE: skip scan index

Skip scan will show in the execution plan as "skip scan". Not true that it
will show as regular index scan.

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 1:20 PM
To: Multiple recipients of list ORACLE-L


A skip scan can be a index scan, full scan or range scan type access. It
simply allows a unusable column to be "deselected" from the index (for lack
of a better word) during these operations.

RF

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 11:15 AM

A short cut to test the new feature is using the hint
index_ss(table,index).

Index skip scan is not an index scan or fast full scan.

Regards,

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L


Okay, I have a developer here who has been reading the docs (this can
be dangerous!)

we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  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: Freeman Robert - IL
  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: Khedr, Waleed
  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).

AW: Unbreakable, my buns. Upgrade to 9.2/w2k .... repost

2003-05-29 Thread Stefan Jahnke

Title: Unbreakable, my buns. Upgrade to 9.2/w2k



Maybe 
that's a good opportunity to repost a question before I apply 
patchsets:
 

I actually planned on applying the Patchset to lift 9.2.0.1.0 up to 
9.2.0.3.0.
But while downloading, I saw that 9.2.0.3.0 came out on April, 7th, while 

there was 9.2.0.2.1 from April, 12th. In the Readme, it says:
# Earliest version of the product that this patch may be applied to: 
9.2.0.2.1 
So, what's the way to go ? Apply 9.2.0.2.0 patchset plus patches first ? 
Or forget about 9.2.0xxx and just go with 9.2.0.3.0 ?
 

Stefan

  -Ursprüngliche Nachricht-Von: Tony Johnson 
  [mailto:[EMAIL PROTECTED]Gesendet: Mittwoch, 28. Mai 2003 
  19:57An: Multiple recipients of list ORACLE-LBetreff: 
  RE: Unbreakable, my buns. Upgrade to 9.2/w2k
  I 
  have upgraded 16 databases ( manually using the scripts and procedures in 
  metablink as a guide ) with very few problems and only then on the 1st ones I 
  did.
  We 
  were going from 8174 ro 92021 on Win2k SP3.
  
-Original Message-From: Koivu, Lisa 
[mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 
2003 9:25 AMTo: Multiple recipients of list 
ORACLE-LSubject: Unbreakable, my buns. Upgrade to 
9.2/w2k
For those of you who have upgraded to 9.2 on 
w2k: 
How many of you have ended up with such a hosed 
server that you had to completely wipe Oracle off of it and start with a 
fresh install (9.2)?  It was spectacular!  I ended up in this 
situation and I sure hope it's something I did or didn't do.  I sure 
don't have time for this when I upgrade my production databases.  How I 
long for those days wading through problems in unix... it was so much easier 
and more enjoyable...  I will say this was definately a learning 
experience. 
And Tom Mercadante, your document worked like a 
charm, and saved my a$$...  Muchas Gracias! 
Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 
Coconut Creek Parkway Ft. Lauderdale, 
FL, USA  33063 Office: 
954-935-4117  Fax:    954-935-3639 Cell:    954-683-4459 -- Please see the 
official ORACLE-L FAQ: http://www.orafaq.net -- Author: Koivu, Lisa 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: SAP Hands SAP DB over to MySQL

At http://www.sapdb.org/7.4/pdf/sapdb_letter.pdf SAP offers "clarification:"

SAP: 
"Contrary to erroneous press reports, SAP AG has not given up any rights concerning 
the SAP DB code base nor handed over or even sold SAP DB to MySQL AB."

SAP: 
"SAP AG remains responsible for ongoing development and support."

CNet: 
"MySQL will take over most of the development of SAP DB."



-Original Message-
Sent: Wednesday, May 28, 2003 12:01 PM
To: Multiple recipients of list ORACLE-L
Importance: High


The past few months I've been wondering when MySQL would start
putting pressure on Oracle in the same way that Linux is putting
pressure on MS.

Maybe sooner than you think:

http://news.com.com/2100-1012_3-1010522.html?tag=fd_top


Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Orr, Steve
  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: RE: Which method is more efficient

2003-05-29 Thread Rodrigues, Bryan

The fields that are changed are determined by 
1) A loop would start until all records in parts change table are done
2) Select a part record from the part changes table
3) Select the same part from the existing part table
4) Compare the value in the parts changes table against the corresponding
field in the part table 5) After comparing all fields in the records, create
record in a seperate work table with the values populated with null if the
field values matched and the new value if the values did not.
6) This loop would continue until all parts are done.
7) After any records in the work table where all fields (outside of part
number) are null are deleted.

This process normally will decrease the number of records to be processed
after this point by 75%.

Hope that helps,

Bryan


-Original Message-
Sent: Wednesday, May 28, 2003 1:21 PM
To: Multiple recipients of list ORACLE-L


oh i missed part of it. the question is how do you figure out which fields
have changed? if you have to do an anti-join on each field, then do an
update of every field. 

the question is how will you determine which fields have changed? 
> 
> From: DENNIS WILLIAMS <[EMAIL PROTECTED]>
> Date: 2003/05/28 Wed PM 12:59:51 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: Which method is more efficient
> 
> Bryan - If this is a critical issue, I would try it both ways on a test
> database and use log miner to examine the amount of redo that is
generated.
> My recollection is that you will find that the redo record records the
> before and after data for each field. So just updating all fields may
> generate significantly more redo. But don't trust my recollection on this
> issue, test it yourself.
> 
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED] 
> 
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 10:50 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hello everyone,
> 
> I have a question for the group of which method is more efficient. 
> 
> To set the stage my company has a process to load part changes from
vendors
> into the tables in an 8.1.7.4 Oracle database with archiving on and this
> database has a standby database at disaster recovery site, so nologging is
> not an option. 
> 
> There is a discussion going on as to which method is more effective for
> updating the information in a table. In looking at effectiveness, I am
> looking at reducing the amount of redo information produced and having the
> database do the least amount of work.
> 
> 1)Method 1 is to update the information only for the fields that have
> changed, 1 field at a time.
> 2)Method 2 is to update the information for all the fields in the
> record whether they have changed or not, 1 record at a time.
> 
> The size of the record is 1843 bytes and the distribution of field sizes:
>  2 fields varchar2(240).
>  1 field varchar2(150)
> 15 fields varchar2(50)
> 1 field varchar2(3)
> 2 fields varchar2(20)
> 4 fields varchar2(40)
> 3 fields varchar2(1)
> 2 fields varchar2(25)
> 2 fields number(10,2)
> 1 field number(13,2)
> 1 field number(1)
> 1 field number
> 1 field varchar2(6)
> 1 field number (17,2)
> 1 field varchar2(4)
> 3 fields that are date.
> 
> In the past couple of months the average number of fields changed per
record
> was 3 to 4 fields per record.
> 
> Thanks for your help,
> 
> Bryan Rodrigues
> Oracle DBA
> Elcom, Inc.
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Rodrigues, Bryan
>   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: 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).
> 
> 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.

RE: skip scan index

Easy test case:

CREATE TABLE TEST_SKIP 
(
C1 NUMBER NOT NULL,
C2 NUMBER NOT NULL,
C3 NUMBER NULL
);
 
CREATE UNIQUE INDEX   TESTSKIP1
ON TEST_SKIP(C1,C2);
 
select --+ index_ss(test_skip, )
 c1,c2,c3
from test_skip
where c2 = 10;

OPERATIONOPTIONS OBJECT_NAME
SELECT STATEMENT [NULL]  [NULL]
TABLE ACCESS BY INDEX ROWID  TEST_SKIP
INDEXSKIP SCAN   TESTSKIP1

Regards,

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L


Okay, I have a developer here who has been reading the docs (this can
be dangerous!)

we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  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: skip scan index

True enough, it will show as "index skip scan", but if you take a look at 
the statistics, you'll see that the nubmer of blocks read roughly
corresponds 
to the number of blocks in the index. It is also logical, because without
the first column, the only way to find the desired key is to read the whole
index. Indexes are B*tree structures which are searched using modified
version
of binary search. The ordering is so called lexicographical order, which
means
that the column 1 is compared first, then column 2 if there is equality in
the column 1 and so forth until we reach differing columns. Without knowing
column 1, you MUST read them all and see which ones contain the sought for 
column 2.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 2:17 PM
To: Multiple recipients of list ORACLE-L


Skip scan will show in the execution plan as "skip scan". Not true that it
will show as regular index scan.

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 1:20 PM
To: Multiple recipients of list ORACLE-L


A skip scan can be a index scan, full scan or range scan type access. It
simply allows a unusable column to be "deselected" from the index (for lack
of a better word) during these operations.

RF

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 11:15 AM

A short cut to test the new feature is using the hint
index_ss(table,index).

Index skip scan is not an index scan or fast full scan.

Regards,

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L


Okay, I have a developer here who has been reading the docs (this can
be dangerous!)

we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  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: Freeman Robert - IL
  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 ORAC

RE: Unbreakable, my buns. Upgrade to 9.2/w2k .... repost

Title: Unbreakable, my buns. Upgrade to 9.2/w2k



Hi 
Stefan, 
 
I 
don't know how much  this is, but I've seen several comments on the 
list stating that 9.2.0.3 is the version to be at.  I'm going to that patch 
level from 8.1.7.4.  You are right, the docs are confusing.  

 
I've 
found that comments from our esteemed colleagues can at times be more valuable 
than the doco.  In this case, doubly so.  
 
Go 
boldly forth to 9.2.0.3. 
 
Lisa 
M.

  -Original Message-From: Stefan Jahnke 
  [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 2003 2:18 
  PMTo: Multiple recipients of list ORACLE-LSubject: AW: 
  Unbreakable, my buns. Upgrade to 9.2/w2k  repost
  Maybe that's a good opportunity to repost a question before I apply 
  patchsets:
   
  
  I actually planned on applying the Patchset to lift 9.2.0.1.0 up to 
  9.2.0.3.0.
  But while downloading, I saw that 9.2.0.3.0 came out on April, 7th, while 
  
  there was 9.2.0.2.1 from April, 12th. In the Readme, it 
  says:
  # Earliest version of the product that this patch may be applied to: 
  9.2.0.2.1 
  So, what's the way to go ? Apply 9.2.0.2.0 patchset plus patches first ? 
  
  Or forget about 9.2.0xxx and just go with 9.2.0.3.0 ?
   
  
  Stefan
  
-Ursprüngliche Nachricht-Von: Tony Johnson 
[mailto:[EMAIL PROTECTED]Gesendet: Mittwoch, 28. Mai 2003 
19:57An: Multiple recipients of list ORACLE-LBetreff: 
RE: Unbreakable, my buns. Upgrade to 9.2/w2k
I 
have upgraded 16 databases ( manually using the scripts and procedures in 
metablink as a guide ) with very few problems and only then on the 1st ones 
I did.
We 
were going from 8174 ro 92021 on Win2k SP3.

  -Original Message-From: Koivu, Lisa 
  [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 
  28, 2003 9:25 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Unbreakable, my buns. Upgrade to 
  9.2/w2k
  For those of you who have upgraded to 9.2 on 
  w2k: 
  How many of you have ended up with such a hosed 
  server that you had to completely wipe Oracle off of it and start with a 
  fresh install (9.2)?  It was spectacular!  I ended up in this 
  situation and I sure hope it's something I did or didn't do.  I sure 
  don't have time for this when I upgrade my production databases.  How 
  I long for those days wading through problems in unix... it was so much 
  easier and more enjoyable...  I will say this was definately a 
  learning experience. 
  And Tom Mercadante, your document worked like a 
  charm, and saved my a$$...  Muchas Gracias! 
  Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 
  Coconut Creek Parkway Ft. Lauderdale, 
  FL, USA  33063 Office: 
  954-935-4117  Fax:    954-935-3639 Cell:    954-683-4459 -- Please see the 
  official ORACLE-L FAQ: http://www.orafaq.net -- Author: Koivu, Lisa 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: RE: Which method is more efficient

2003-05-29 Thread rgaffuri

have you run it? isnt that alot slower? you have alot of context switches also. for 
every record to update, you then switch to SQL. 

what kind of efficiency improvement are you going for? Speed or cutting down on redo? 
Are you in archivelog mode and dont want to blow up your archives? 
> 
> From: "Rodrigues, Bryan" <[EMAIL PROTECTED]>
> Date: 2003/05/28 Wed PM 02:40:25 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: RE: Which method is more efficient
> 
> The fields that are changed are determined by 
> 1) A loop would start until all records in parts change table are done
> 2) Select a part record from the part changes table
> 3) Select the same part from the existing part table
> 4) Compare the value in the parts changes table against the corresponding
> field in the part table 5) After comparing all fields in the records, create
> record in a seperate work table with the values populated with null if the
> field values matched and the new value if the values did not.
> 6) This loop would continue until all parts are done.
> 7) After any records in the work table where all fields (outside of part
> number) are null are deleted.
> 
> This process normally will decrease the number of records to be processed
> after this point by 75%.
> 
> Hope that helps,
> 
> Bryan
> 
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 1:21 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> oh i missed part of it. the question is how do you figure out which fields
> have changed? if you have to do an anti-join on each field, then do an
> update of every field. 
> 
> the question is how will you determine which fields have changed? 
> > 
> > From: DENNIS WILLIAMS <[EMAIL PROTECTED]>
> > Date: 2003/05/28 Wed PM 12:59:51 EDT
> > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > Subject: RE: Which method is more efficient
> > 
> > Bryan - If this is a critical issue, I would try it both ways on a test
> > database and use log miner to examine the amount of redo that is
> generated.
> > My recollection is that you will find that the redo record records the
> > before and after data for each field. So just updating all fields may
> > generate significantly more redo. But don't trust my recollection on this
> > issue, test it yourself.
> > 
> > Dennis Williams
> > DBA, 80%OCP, 100% DBA
> > Lifetouch, Inc.
> > [EMAIL PROTECTED] 
> > 
> > 
> > -Original Message-
> > Sent: Wednesday, May 28, 2003 10:50 AM
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > Hello everyone,
> > 
> > I have a question for the group of which method is more efficient. 
> > 
> > To set the stage my company has a process to load part changes from
> vendors
> > into the tables in an 8.1.7.4 Oracle database with archiving on and this
> > database has a standby database at disaster recovery site, so nologging is
> > not an option. 
> > 
> > There is a discussion going on as to which method is more effective for
> > updating the information in a table. In looking at effectiveness, I am
> > looking at reducing the amount of redo information produced and having the
> > database do the least amount of work.
> > 
> > 1)  Method 1 is to update the information only for the fields that have
> > changed, 1 field at a time.
> > 2)  Method 2 is to update the information for all the fields in the
> > record whether they have changed or not, 1 record at a time.
> > 
> > The size of the record is 1843 bytes and the distribution of field sizes:
> >  2 fields varchar2(240).
> >  1 field varchar2(150)
> > 15 fields varchar2(50)
> > 1 field varchar2(3)
> > 2 fields varchar2(20)
> > 4 fields varchar2(40)
> > 3 fields varchar2(1)
> > 2 fields varchar2(25)
> > 2 fields number(10,2)
> > 1 field number(13,2)
> > 1 field number(1)
> > 1 field number
> > 1 field varchar2(6)
> > 1 field number (17,2)
> > 1 field varchar2(4)
> > 3 fields that are date.
> > 
> > In the past couple of months the average number of fields changed per
> record
> > was 3 to 4 fields per record.
> > 
> > Thanks for your help,
> > 
> > Bryan Rodrigues
> > Oracle DBA
> > Elcom, Inc.
> > 
> > 
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > -- 
> > Author: Rodrigues, Bryan
> >   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: DENNIS WILLIAMS
> >   INET: [EMAIL PROTECTED]
> >

which util do I use to see oracle's execution plan?

2003-05-29 Thread Maryann Atkinson

I am so novice it'll probably take me years to learn oracle
like some of you know it in here...
I am just studying a book, and try to learn from some of its questions.

a. Which util do I use to see oracle's execution plan for a SQL statement?
Which statement would you recommend me, so I can actually try it
and see results?
b. If I need to tune the SGA ONLY, should I concentrate on the log buffers,
the library cache or the shared pool?
thx
maa 

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
 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: SAP Hands SAP DB over to MySQL

Reminiscent of the M$/Sybase "partnership?"


-Original Message-
Sent: Wednesday, May 28, 2003 11:40 AM
To: '[EMAIL PROTECTED]'


At http://www.sapdb.org/7.4/pdf/sapdb_letter.pdf SAP offers "clarification:"

SAP: 
"Contrary to erroneous press reports, SAP AG has not given up any rights concerning 
the SAP DB code base nor handed over or even sold SAP DB to MySQL AB."

SAP: 
"SAP AG remains responsible for ongoing development and support."

CNet: 
"MySQL will take over most of the development of SAP DB."



-Original Message-
Sent: Wednesday, May 28, 2003 12:01 PM
To: Multiple recipients of list ORACLE-L
Importance: High


The past few months I've been wondering when MySQL would start
putting pressure on Oracle in the same way that Linux is putting
pressure on MS.

Maybe sooner than you think:

http://news.com.com/2100-1012_3-1010522.html?tag=fd_top


Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Orr, Steve
  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).

Oracle Financials and IBM/AIX Platforms

2003-05-29 Thread Mercadante, Thomas F

All,

Anybody running Oracle Financials on an IBM/AIX platform?  I've been asked
for opinions on running the latest version on an IBM P650 platform running
AIX.  This would be just for the database itself.  Any suggestions for
required memory/disk?  I know I need to provide more info (like how many
users etc), but I'm just looking for a thnumbs up/down on the hardware for
now.

thanks

Tom Mercadante
Oracle Certified Professional

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: skip scan index

Thanks Waleed. Something even I can understand!

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Wednesday, May 28, 2003 1:52 PM
To: Multiple recipients of list ORACLE-L


Easy test case:

CREATE TABLE TEST_SKIP 
(
C1 NUMBER NOT NULL,
C2 NUMBER NOT NULL,
C3 NUMBER NULL
);
 
CREATE UNIQUE INDEX   TESTSKIP1
ON TEST_SKIP(C1,C2);
 
select --+ index_ss(test_skip, )
 c1,c2,c3
from test_skip
where c2 = 10;

OPERATIONOPTIONS OBJECT_NAME
SELECT STATEMENT [NULL]  [NULL]
TABLE ACCESS BY INDEX ROWID  TEST_SKIP
INDEXSKIP SCAN   TESTSKIP1

Regards,

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L


Okay, I have a developer here who has been reading the docs (this can
be dangerous!)

we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  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: 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: Unbreakable, my buns. Upgrade to 9.2/w2k

Thanks everyone for your responses.  wish me luck...

Lisa M.

-Original Message-
Sent: Wednesday, May 28, 2003 1:21 PM
To: Multiple recipients of list ORACLE-L


and THAT is the reason NOT, repeat, NOT to use the OUI to do upgrades. Do
'em manually, I've had a very good success rate that way. Never trust the
GUI's. Never.

Robert

(self-proclaimed GUI biggot)

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 11:56 AM


I was not able to upgrade an 8.1.7.0 database to 9.2.0.1.0 on Win2K.  I
had
to create a new Oracle home, install 9.2, and clone the database in 9.2.
OUI did not upgrade successfully.



 

  "Koivu, Lisa"

  
  @Fairfieldresort cc:

  s.com>   Subject: Unbreakable, my
buns.  Upgrade to 9.2/w2k  
  Sent by: root

 

 

  05/28/2003 12:25

  PM

  Please respond

  to ORACLE-L

 

 





For those of you who have upgraded to 9.2 on w2k:


How many of you have ended up with such a hosed server that you had to
completely wipe Oracle off of it and start with a fresh install (9.2)?
It
was spectacular!  I ended up in this situation and I sure hope it's
something I did or didn't do.  I sure don't have time for this when I
upgrade my production databases.  How I long for those days wading
through
problems in unix... it was so much easier and more enjoyable...  I will
say
this was definately a learning experience.


And Tom Mercadante, your document worked like a charm, and saved my
a$$...
Muchas Gracias!


Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA  33063
Office: 954-935-4117
Fax:954-935-3639
Cell:954-683-4459


-- Please see the official ORACLE-L FAQ: http://www.orafaq.net --
Author:
Koivu, Lisa 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: Thomas Day
  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: Freeman Robert - IL
  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: Koivu, Lisa
  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: Bulk collect got truncated?

2003-05-29 Thread Mark Moynahan

Is it possible that you are running out memory on the OS?

A different question I have is why bulk collect such a large amount at once.
Why not do a cursor with a limit on the fetch? This would allow you to
process in smaller batches instead of one gigantic fetch and insert.

Mark

-Original Message-
Sent: Wednesday, May 28, 2003 11:07 AM
To: Multiple recipients of list ORACLE-L



Hi dear listers, 

Some of you may still remember this thread, bulk collect truncated to 65535
records sometimes.
I've got this case reproduceable and tried all suggestions , 

In a brief, 
 SELECT returns 318847 rows, 
 INSERT INTO FROM SELECT - 318847 rows, 
 PL/SQL plain FOR cr IN (select ..) LOOP - - 318847 rows
 PL/SQL with BULK COLLECT many different code versions - sometimes  returs
65535 records instead, the rest is truncated

What might be interesting, in case when it fails, it doesn't retrieve
requiered rows from disk. I can judge it by much shorter responce time and
10046 trace doesn't show db file sequential read events what always showup
when number of rows is correct.

10046 trace provides interesting details,  65535 records is approximately
the point (=/- 50 records) where it usually does first db file sequential
read in case of successful execution.

So Waleed, apparently there is a problem here, 9.0.1.4 Solaris. Time to TAR
now.

Below is the spool from my recent session.

Have a good day, 
Vadim

set serveroutput on
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index
by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index
by binary_integer;
  4 esubsvcid   t_sub_svc_id;
  5 evalt_subsvcext_key;
  6  BEGIN
  7 SELECT /*+ index(p sub_svc_parm_ix2) */
  8sub_svc_id, val
  9   BULK COLLECT INTO esubsvcid, eval
 10   FROM CBQA4SP.sub_svc_parm p
 11  WHERE parm_id =10;
 12 dbms_output.put_line(esubsvcid.count);
 13  end;
 14  /
318847


PL/SQL procedure successfully completed.

Elapsed: 00:00:12.03
SQL> alter session set events = '10046 trace name context forever, level 8';

Session altered.

Elapsed: 00:00:00.00
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index
by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index
by binary_integer;
  4 esubsvcid   t_sub_svc_id;
  5 evalt_subsvcext_key;
  6  BEGIN
  7 SELECT /*+ index(p sub_svc_parm_ix2) */
  8sub_svc_id, val
  9   BULK COLLECT INTO esubsvcid, eval
 10   FROM CBQA4SP.sub_svc_parm p
 11  WHERE parm_id =10;
 12 dbms_output.put_line(esubsvcid.count);
 13  end;
 14  /
65535


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> alter session set events = '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set sql_trace= true;

Session altered.

Elapsed: 00:00:00.00
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index
by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index
by binary_integer;
  4 esubsvcid   t_sub_svc_id;
  5 evalt_subsvcext_key;
  6  BEGIN
  7 SELECT /*+ index(p sub_svc_parm_ix2) */
  8sub_svc_id, val
  9   BULK COLLECT INTO esubsvcid, eval
 10   FROM CBQA4SP.sub_svc_parm p
 11  WHERE parm_id =10;
 12 dbms_output.put_line(esubsvcid.count);
 13  end;
 14  /
65535


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> alter session set sql_trace= false;

Session altered.

Elapsed: 00:00:00.00
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index
by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index
by binary_integer;
  4 esubsvcid   t_sub_svc_id;
  5 evalt_subsvcext_key;
  6  BEGIN
  7 SELECT /*+ index(p sub_svc_parm_ix2) */
  8sub_svc_id, val
  9   BULK COLLECT INTO esubsvcid, eval
 10   FROM CBQA4SP.sub_svc_parm p
 11  WHERE parm_id =10;
 12 dbms_output.put_line(esubsvcid.count);
 13  end;
 14  /
65535


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> SELECT /*+ index(p sub_svc_parm_ix2) */ count(*)  FROM
CBQA4SP.sub_svc_parm p
  2  WHERE parm_id =10;

  COUNT(*)

--

318847


Elapsed: 00:00:00.03
SQL> 
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF number index by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF varchar2(255) index by
binary_integer;
  4 esubsvcid   t_sub_svc_id;
  5 evalt_subsvcext_key;
  6  BEGIN
  7 SELECT /*+ index(p sub_svc_parm_ix2) */
  8sub_svc_id, val
  9   BULK COLLECT INTO esubsvcid, eval
 10   FROM CBQA4SP.sub_svc_parm p
 11  WHERE parm_id =10;
 12 dbms_output.put_line(esubsvcid.count);
 13  end;
 14  /
65535


PL/SQL procedur

RE: skip scan index

I'm talking about the way it get executed not the statistics or the cost.

The cost is completely dependent on the distribution of the data.

For example if we have table (c1 number, c2 number) and a primary key on
(c1, c2).

And the data looks like this:

c1  c2
A   1
A   2
A   3
A   4
.   .
.   .
A   
A   1
B   1
B   2
B   3
.   .
.   .
.   .
B   
B   1


And I run this sql using skip scan:

select c1,c2
from table
where c2 = 100

This will be almost similar if you execute this (two unique lookups):

select
   c1,c2
from table
where c1 = 'A' and c2 = 100
union all
select
   c1,c2
from table
where c1 = 'B' and c2 = 100

There will be extra cost related to finding the unique value of c1 but will
be much cheaper compared to full index scan.

Regards,

Waleed



-Original Message-
Sent: Wednesday, May 28, 2003 2:52 PM
To: Multiple recipients of list ORACLE-L


True enough, it will show as "index skip scan", but if you take a look at 
the statistics, you'll see that the nubmer of blocks read roughly
corresponds 
to the number of blocks in the index. It is also logical, because without
the first column, the only way to find the desired key is to read the whole
index. Indexes are B*tree structures which are searched using modified
version
of binary search. The ordering is so called lexicographical order, which
means
that the column 1 is compared first, then column 2 if there is equality in
the column 1 and so forth until we reach differing columns. Without knowing
column 1, you MUST read them all and see which ones contain the sought for 
column 2.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 2:17 PM
To: Multiple recipients of list ORACLE-L


Skip scan will show in the execution plan as "skip scan". Not true that it
will show as regular index scan.

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 1:20 PM
To: Multiple recipients of list ORACLE-L


A skip scan can be a index scan, full scan or range scan type access. It
simply allows a unusable column to be "deselected" from the index (for lack
of a better word) during these operations.

RF

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 11:15 AM

A short cut to test the new feature is using the hint
index_ss(table,index).

Index skip scan is not an index scan or fast full scan.

Regards,

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L


Okay, I have a developer here who has been reading the docs (this can
be dangerous!)

we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  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')

RE: RE: Which method is more efficient

2003-05-29 Thread Jamadagni, Rajendra

Title: RE: RE: Which method is more efficient





Bryan,


Can you ...
create table my_work_table as 
select * from changed_parts_table
minus
select * from existing_parts_table
/


The result will give you all the rows where _something_ is different between your existing table and changed table. This will cut down a lot on your processing. Afterwards, you can drop the my_work_table.

Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !



-Original Message-
From: Rodrigues, Bryan [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 28, 2003 2:40 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: RE: Which method is more efficient



The fields that are changed are determined by 
1) A loop would start until all records in parts change table are done
2) Select a part record from the part changes table
3) Select the same part from the existing part table
4) Compare the value in the parts changes table against the corresponding
field in the part table 5) After comparing all fields in the records, create
record in a seperate work table with the values populated with null if the
field values matched and the new value if the values did not.
6) This loop would continue until all parts are done.
7) After any records in the work table where all fields (outside of part
number) are null are deleted.


This process normally will decrease the number of records to be processed
after this point by 75%.


Hope that helps,


Bryan



-Original Message-
Sent: Wednesday, May 28, 2003 1:21 PM
To: Multiple recipients of list ORACLE-L



oh i missed part of it. the question is how do you figure out which fields
have changed? if you have to do an anti-join on each field, then do an
update of every field. 


the question is how will you determine which fields have changed? 
> 
> From: DENNIS WILLIAMS <[EMAIL PROTECTED]>
> Date: 2003/05/28 Wed PM 12:59:51 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: Which method is more efficient
> 
> Bryan - If this is a critical issue, I would try it both ways on a test
> database and use log miner to examine the amount of redo that is
generated.
> My recollection is that you will find that the redo record records the
> before and after data for each field. So just updating all fields may
> generate significantly more redo. But don't trust my recollection on this
> issue, test it yourself.
> 
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED] 
> 
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 10:50 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hello everyone,
> 
> I have a question for the group of which method is more efficient. 
> 
> To set the stage my company has a process to load part changes from
vendors
> into the tables in an 8.1.7.4 Oracle database with archiving on and this
> database has a standby database at disaster recovery site, so nologging is
> not an option. 
> 
> There is a discussion going on as to which method is more effective for
> updating the information in a table. In looking at effectiveness, I am
> looking at reducing the amount of redo information produced and having the
> database do the least amount of work.
> 
> 1)    Method 1 is to update the information only for the fields that have
> changed, 1 field at a time.
> 2)    Method 2 is to update the information for all the fields in the
> record whether they have changed or not, 1 record at a time.
> 
> The size of the record is 1843 bytes and the distribution of field sizes:
>  2 fields varchar2(240).
>  1 field varchar2(150)
> 15 fields varchar2(50)
> 1 field varchar2(3)
> 2 fields varchar2(20)
> 4 fields varchar2(40)
> 3 fields varchar2(1)
> 2 fields varchar2(25)
> 2 fields number(10,2)
> 1 field number(13,2)
> 1 field number(1)
> 1 field number
> 1 field varchar2(6)
> 1 field number (17,2)
> 1 field varchar2(4)
> 3 fields that are date.
> 
> In the past couple of months the average number of fields changed per
record
> was 3 to 4 fields per record.
> 
> Thanks for your help,
> 
> Bryan Rodrigues
> Oracle DBA
> Elcom, Inc.
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Rodrigues, Bryan
>   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 s

RE: Oracle Financials and IBM/AIX Platforms

2003-05-29 Thread Mercadante, Thomas F

All,

Another question.  What does it take to support Oracle Financials?  I know
that a Financials DBA has a different skill set than an Oracle DBA, but how
many people are we talking about here?  How long does a "typical"
installation take to complete?  I know a little about it, but not enough to
provide some answers here.

thanks

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Wednesday, May 28, 2003 2:56 PM
To: Multiple recipients of list ORACLE-L


All,

Anybody running Oracle Financials on an IBM/AIX platform?  I've been asked
for opinions on running the latest version on an IBM P650 platform running
AIX.  This would be just for the database itself.  Any suggestions for
required memory/disk?  I know I need to provide more info (like how many
users etc), but I'm just looking for a thnumbs up/down on the hardware for
now.

thanks

Tom Mercadante
Oracle Certified Professional

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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.net
-- 
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: maxextents unlimited - value in dba_tables, dba_indexes

2003-05-29 Thread Cunningham, Gerald

Thanks, Dennis.

Started snooping around some more, looks like I needed to go to
DBA_SEGMENTS for partitioned objects:

 
SQL> select OWNER, SEGMENT_TYPE, SEGMENT_NAME, MAX_EXTENTS from
dba_segments
  2  where owner = 'APP_OWNER' and SEGMENT_NAME = 'INDEX1';
 
OWNER  SEGMENT_TYPE
-- --
SEGMENT_NAME
MAX_EXTENTS

- ---
APP_OWNER   INDEX PARTITION
INDEX1
2147483645
 
APP_OWNER   INDEX PARTITION
INDEX1
2147483645
 
APP_OWNER   INDEX PARTITION
INDEX1
2147483645
 
APP_OWNER   INDEX PARTITION
INDEX1
2147483645
 

4 rows selected.


-Original Message-
Sent: Wednesday, May 28, 2003 1:06 PM
To: Multiple recipients of list ORACLE-L


Jerry - I did a quick survey of some of my databases and only found a
single table that had a null MAX_EXTENTS and it was not partitioned. Its
name was ATEMPTAB$ owned by SYS.



Dennis Williams 
DBA, 80%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Wednesday, May 28, 2003 11:15 AM
To: Multiple recipients of list ORACLE-L


Hi all,
 
I have a database where all tables and indexes have maxextents set to
unlimited. Why, in dba_tables, do some tables have a null value for
maxetents and some have a value of 2147483645?
 
An index I just modified (previous value of maxextents was 249) now has
a null value for maxextents:
 
SQL> alter index app_owner.index1 storage (maxextents unlimited);

Index altered.

SQL> select owner, index_name, max_extents from dba_indexes
  2  where owner = 'APP_OWNER' and index_name = 'INDEX1';

OWNER  INDEX_NAME
MAX_EXTENTS
-- --
---
APP_OWNER   INDEX1

 

It seems that some of the objects that have maxextents = null are
partitioned, but not all. Anybody have an explanation??
 
Thanks!
 
- Jerry
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Cunningham, Gerald
  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: Oracle Financials and IBM/AIX Platforms

2003-05-29 Thread antonio . belloni


Hi Thomas,

We are running Oracle Applications Release 11.5.8 on an RS6000 F80 with 4
procs , 8 Gb RAM and 12 x 9.1 Gb , RAID 01 , SSA disks. Both application
and database are on the same machine. The database have almost 50 Gb and
there are aprox. 50 concurrent users , using four modules: GL , AP , INV
and PO. We´ve migrated this environment from an RS6000 F50 recently and the
performance increased a lot.

We´ve been running Oracle Applications on IBM machines since release 10.7
and the performance and scalability are very good.

I don´t know the size of your database or the number of users that your´re
planning to support , but the P650 (2-to8 way , 2-to-64Gb RAM , up to 587.2
GB internal storage) is a mid-range solution and I think it can support
high mission-critical applications easily with performace and scalability
(I don´t work for IBM :- ).

HIH,
Antonio Belloni



   
 
  "Mercadante, 
 
  Thomas F"To:   Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>
  <[EMAIL PROTECTED]cc:
  
  te.ny.us>Subject:  Oracle Financials and IBM/AIX 
Platforms
  Sent by: 
 
  [EMAIL PROTECTED]
  
   
 
   
 
  28/05/2003 15:56 
 
  Please respond to
 
  ORACLE-L 
 
   
 
   
 




All,

Anybody running Oracle Financials on an IBM/AIX platform?  I've been asked
for opinions on running the latest version on an IBM P650 platform running
AIX.  This would be just for the database itself.  Any suggestions for
required memory/disk?  I know I need to provide more info (like how many
users etc), but I'm just looking for a thnumbs up/down on the hardware for
now.

thanks

Tom Mercadante
Oracle Certified Professional

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
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.net
-- 
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: skip scan index

I'm not talking about the cost either. The way by which is getting executed 
is by reading the whole index. You may call it fast full scan, you may call 
it index skip scan, but it is still the same thing: sequential read of the
whole index. In other words, the name doesn't matter.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 3:40 PM
To: Multiple recipients of list ORACLE-L


I'm talking about the way it get executed not the statistics or the cost.

The cost is completely dependent on the distribution of the data.

For example if we have table (c1 number, c2 number) and a primary key on
(c1, c2).

And the data looks like this:

c1  c2
A   1
A   2
A   3
A   4
.   .
.   .
A   
A   1
B   1
B   2
B   3
.   .
.   .
.   .
B   
B   1


And I run this sql using skip scan:

select c1,c2
from table
where c2 = 100

This will be almost similar if you execute this (two unique lookups):

select
   c1,c2
from table
where c1 = 'A' and c2 = 100
union all
select
   c1,c2
from table
where c1 = 'B' and c2 = 100

There will be extra cost related to finding the unique value of c1 but will
be much cheaper compared to full index scan.

Regards,

Waleed



-Original Message-
Sent: Wednesday, May 28, 2003 2:52 PM
To: Multiple recipients of list ORACLE-L


True enough, it will show as "index skip scan", but if you take a look at 
the statistics, you'll see that the nubmer of blocks read roughly
corresponds 
to the number of blocks in the index. It is also logical, because without
the first column, the only way to find the desired key is to read the whole
index. Indexes are B*tree structures which are searched using modified
version
of binary search. The ordering is so called lexicographical order, which
means
that the column 1 is compared first, then column 2 if there is equality in
the column 1 and so forth until we reach differing columns. Without knowing
column 1, you MUST read them all and see which ones contain the sought for 
column 2.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 2:17 PM
To: Multiple recipients of list ORACLE-L


Skip scan will show in the execution plan as "skip scan". Not true that it
will show as regular index scan.

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 1:20 PM
To: Multiple recipients of list ORACLE-L


A skip scan can be a index scan, full scan or range scan type access. It
simply allows a unusable column to be "deselected" from the index (for lack
of a better word) during these operations.

RF

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 11:15 AM

A short cut to test the new feature is using the hint
index_ss(table,index).

Index skip scan is not an index scan or fast full scan.

Regards,

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 7:00 AM
To: Multiple recipients of list ORACLE-L


Okay, I have a developer here who has been reading the docs (this can
be dangerous!)

we are adding functionality to one of our applications, this will
involve using multiple fulfillment houses, so we'll be adding the
fulfillment vendor id to the order table. Easy, this is not a problem.
We want to be able to search by order date and by fulfillment vendor
id/order date

Traditional design would be to add two indexes: one on order date, and
a concatenated one on fulfillment vendor id/order date.

The developer is telling me to create a "skip scan index" instead of
two different ones. MY reading in the FM tells me that skip scan index
is not a type of index, but rather a way Oracle uses to use an index
even if the leftmost column is not in the query.

Is there any benefit in my building only the one index? Our order
volume is not so high (and never will be) that there is a visible
performance impact if I have the two indices.

This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
Solaris

Any suggestions/comments/war stories would be appreciated. I know I've
seen Jonathan post on skip scan indexes before but I can't find the
specific reference at the moment.

Rachel

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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

RE: Unbreakable, my buns. Upgrade to 9.2/w2k

and I had no problem at all doing it. But it was a simple database,
with no major connections or anything.

Maybe because it was a dev box? Oracle didn't think it worthwhile to
mess with my mind?

The only thing that happened was I had to leave the migration of the
database running overnight as it took HOURS


--- Freeman Robert - IL <[EMAIL PROTECTED]> wrote:
> and THAT is the reason NOT, repeat, NOT to use the OUI to do
> upgrades. Do
> 'em manually, I've had a very good success rate that way. Never trust
> the
> GUI's. Never.
> 
> Robert
> 
> (self-proclaimed GUI biggot)
> 
> -Original Message-
> To: Multiple recipients of list ORACLE-L
> Sent: 5/28/2003 11:56 AM
> 
> 
> I was not able to upgrade an 8.1.7.0 database to 9.2.0.1.0 on Win2K. 
> I
> had
> to create a new Oracle home, install 9.2, and clone the database in
> 9.2.
> OUI did not upgrade successfully.
> 
> 
> 
>  
> 
>   "Koivu, Lisa"
> 
>recipients of list ORACLE-L <[EMAIL PROTECTED]>   
> 
>   @Fairfieldresort cc:
> 
>   s.com>   Subject: Unbreakable,
> my
> buns.  Upgrade to 9.2/w2k  
>   Sent by: root
> 
>  
> 
>  
> 
>   05/28/2003 12:25
> 
>   PM
> 
>   Please respond
> 
>   to ORACLE-L
> 
>  
> 
>  
> 
> 
> 
> 
> 
> For those of you who have upgraded to 9.2 on w2k:
> 
> 
> How many of you have ended up with such a hosed server that you had
> to
> completely wipe Oracle off of it and start with a fresh install
> (9.2)?
> It
> was spectacular!  I ended up in this situation and I sure hope it's
> something I did or didn't do.  I sure don't have time for this when I
> upgrade my production databases.  How I long for those days wading
> through
> problems in unix... it was so much easier and more enjoyable...  I
> will
> say
> this was definately a learning experience.
> 
> 
> And Tom Mercadante, your document worked like a charm, and saved my
> a$$...
> Muchas Gracias!
> 
> 
> Lisa Koivu
> Oracle Database Administrator
> Fairfield Resorts, Inc.
> 5259 Coconut Creek Parkway
> Ft. Lauderdale, FL, USA  33063
> Office: 954-935-4117
> Fax:954-935-3639
> Cell:954-683-4459
> 
> 
> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net --
> Author:
> Koivu, Lisa 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: Thomas Day
>   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: Freeman Robert - IL
>   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!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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 'List

RE: Oracle Performance Tuning 101 Book now Available!

Interesting, as this book has been out for over a year.

Kirti Deshpande and Gaja Vaidyanatha of this list wrote it


--- "Johnson, Michael " <[EMAIL PROTECTED]> wrote:
> 
> IMHO,  Every DBA should own this book.  It will change your way of
> thinking
> about how to approach oracle performance problems.
> 
>  
>  Mike
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 6:50 AM
> To: [EMAIL PROTECTED]
> 
> 
> 
> To unsubscribe from this Precise Software email list
>

> click
> here and submit your email address. 
>  
> 
>   
>  Precise  
> Free
> Book from Oracle Press
>   
>  Precise - Performance Is Our Business.
> 
>   
>   
> 
> 
>    
>   
>   
> 
> Good Oracle Hit-Ratios Don't Make Users Happy...Good Performance
> Does!
> 
> Conventional tuning approaches rely heavily on checking the Buffer
> Cache Hit
> ratio. But even though DBAs do their best to get a 99% or better
> hit-ratio
> they discover that the performance of their database isn't really
> improving
> when the hit-ratio gets better.  
> 
> More over, "ratio" tuning does not consider what the database is
> doing for
> the application, and application performance is what the end-user
> sees.  
> 
> Make your end users happy and reduce your workweek by following these
> simple
> steps: 
> 
> -   
>  Download this FREE book,
> Oracle
> Performance Tuning 101, and dispel the myths and folklore about
> performance
> tuning. 
> 
> -   
>  Register for a webinar on
> Precise's
> response-time tuning solutions. 
> 
> -   
>  Visit our website or give us
> a call
> for more information on our Oracle tuning solutions - like
> "Precise/Indepth
> for Oracle ... the best in a hotly contested market segment for
> Oracle
> instance monitoring and tuning tools." - Gartner 
> 
>   
>   
>  
> 
> 
>    
>    
> 
> 
>   Click here to Download Your
> FREE
> Book now! 
> 
>    
> 
> 
>  
> 
>  
> 
>  
> 
>  
>   Download
>  the Book 
>   
> 
>   _  
> 
>   
>
>  
> 
>  Precise
>  
> 
>   
> Precise Software Solutions T: 1 781 461 0700 W: www.precise.com
>   E: [EMAIL PROTECTED]
> 
> 
>   
> 
>   
>   
>  
> 
>   
> 
> 


__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: skip scan index

this is the online store. If we EVER have a table with more than
100,000 rows in it, I'll faint.


--- Freeman Robert - IL <[EMAIL PROTECTED]> wrote:
> >> Trifling even if we do an FTS
> 
> Until your developers develop a query next month that joins that
> table to
> the 200 million row table they are planning on installing but just
> forgot to
> tell you about.
> 
> Developers are funny that way.
> 
> 
> "Excuse me, did you plan any indexing on this table??"
> "Hints? We don't need no stinking hints, the optimzer is way to smart
> to
> need hints."
> 
> Robert
> 
> -Original Message-
> To: Multiple recipients of list ORACLE-L
> Sent: 5/28/2003 10:29 AM
> 
> Kevin,
> 
> Thanks these will NOT be ad-hoc queries but part of the app --
> for
> the admin and customer service users. 
> 
> I'm leaning more and more towards setting things up so that we either
> do a full table scan or use two indexes.
> 
> I just did a query -- since the app was released in December, we have
> had just over 24,000 rows added to the order table. 
> 
> Trifling even if we do an FTS
> 
> Rachel
> 
> --- Kevin Toepke <[EMAIL PROTECTED]> wrote:
> > Rachel
> > 
> > My experience with index skip scans can be summed up as follows. If
> > you know
> > the app will be doing a particular scan, create the index. 
> > 
> > Index Skip Scans should be thought of a means to help optimize
> those
> > pesky
> > ad-hoc queries only.
> > 
> > I haven't been able to get a skip-can to work unless there is a
> > simple
> > restriction (>, <, =) on the non-leading column. My experience
> tells
> > me they
> > don't help when you are joining against a non-leading column or you
> > are
> > using an IN condition (either static or sub-query)
> > 
> > HTH
> > Kevin
> > 
> > -Original Message-
> > Sent: Wednesday, May 28, 2003 7:00 AM
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > Okay, I have a developer here who has been reading the docs (this
> can
> > be dangerous!)
> > 
> > we are adding functionality to one of our applications, this will
> > involve using multiple fulfillment houses, so we'll be adding the
> > fulfillment vendor id to the order table. Easy, this is not a
> > problem.
> > We want to be able to search by order date and by fulfillment
> vendor
> > id/order date
> > 
> > Traditional design would be to add two indexes: one on order date,
> > and
> > a concatenated one on fulfillment vendor id/order date.
> > 
> > The developer is telling me to create a "skip scan index" instead
> of
> > two different ones. MY reading in the FM tells me that skip scan
> > index
> > is not a type of index, but rather a way Oracle uses to use an
> index
> > even if the leftmost column is not in the query.
> > 
> > Is there any benefit in my building only the one index? Our order
> > volume is not so high (and never will be) that there is a visible
> > performance impact if I have the two indices.
> > 
> > This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near
> future.
> > Solaris
> > 
> > Any suggestions/comments/war stories would be appreciated. I know
> > I've
> > seen Jonathan post on skip scan indexes before but I can't find the
> > specific reference at the moment.
> > 
> > Rachel
> > 
> > __
> > Do you Yahoo!?
> > Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> > http://calendar.yahoo.com
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > -- 
> > 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).
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > -- 
> > Author: Kevin Toepke
> >   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!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
> --

RE: which util do I use to see oracle's execution plan?

Maryann - No problem, welcome to the list. We only ask that you make a
slight effort to search for the answer first, but these are good questions
that you won't quickly find as a beginner.

a. Oracle provides an explain plan feature to show you how your SQL will be
handled. You must create a table in your schema called plan_table. Here is a
tutorial (you'll have to patch the link back together):
http://www.evolt.org/article/Use_Oracle_s_Explain_Plan_to_Tune_Your_Queries/
17/2986/

b. You can do as much damage by tuning the SGA as anything, so learn how to
do it right from the start. Buy:
Oracle Performance Tuning 101
http://www.amazon.com/exec/obidos/tg/detail/-/0072131454/qid=1054149138/sr=8
-1/ref=sr_8_1/103-6975634-9907034?v=glance&s=books&n=507846
Another good book I've recently found is
The Art and Science of Oracle Performance Tuning:
http://www.amazon.com/exec/obidos/ASIN/1904347010/qid%3D1054149206/sr%3D11-1
/ref%3Dsr%5F11%5F1/103-6975634-9907034

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Wednesday, May 28, 2003 2:15 PM
To: Multiple recipients of list ORACLE-L


I am so novice it'll probably take me years to learn oracle
like some of you know it in here...

I am just studying a book, and try to learn from some of its questions.

a. Which util do I use to see oracle's execution plan for a SQL statement?
Which statement would you recommend me, so I can actually try it
and see results?

b. If I need to tune the SGA ONLY, should I concentrate on the log buffers,
the library cache or the shared pool?

thx
maa 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Maryann Atkinson
  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: 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: Oracle Performance Tuning 101 Book now Available!

this is not spam. Michael is signed up to their email listserv and
passed on the information to us, as a member of this list.

how does that become defined as spam?

and oh by the way, Precise has NO connection to Oracle Press or any of
their books


--- [EMAIL PROTECTED] wrote:
> 
> I refuse to do business with spammers;  regardless of what their
> product
> may be.
> 
> 
> 
>  
> 
>   "Johnson, Michael "
> 
>   <[EMAIL PROTECTED]To:  
> Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>  
>   
>   fmc.af.mil>   cc:  
> 
>   Sent by:  Subject:  RE:
> Oracle Performance Tuning 101 Book now Available!   
>   [EMAIL PROTECTED]   
> 
>  
> 
>  
> 
>   05/28/2003 10:44 AM
> 
>   Please respond to  
> 
>   ORACLE-L   
> 
>  
> 
>  
> 
> 
> 
> 
> 
> 
> IMHO,  Every DBA should own this book.  It will change your way of
> thinking
> about how to approach oracle performance problems.
> 
>  Mike
> -Original Message-
> From: Kent Mingus
> [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, May 28, 2003 6:50 AM
> To: [EMAIL PROTECTED]
> Subject: Oracle Performance Tuning 101 Book now
> Available!
> 
> To unsubscribe from this Precise Software email list
> click here
> and submit your email address.
> 
>
|-|
> |
> |
> |
> |
> |
> |
> |
> |
> |(Embedded image moved to file: pic07195.gif)
> |
> |(Embedded image moved to file: pic07825.gif)
> |
> |Free Book from
> Oracle|
> |   
> Press|
> |  
> (E|
> |  
> mb|
> |  
> ed|
> |  
> de|
> |   d
> |
> |  
> im|
> |  
> ag|
> |   e
> |
> |  
> mo|
> |  
> ve|
> |   d
> |
> |  
> to|
> |  
> fi|
> |  
> le|
> |   :
> |
> |  
> pi|
> |  
> c0|
> |  
> 38|
> |  
> 30|
> |  
> .g|
> |

RE: Oracle Financials and IBM/AIX Platforms

2003-05-29 Thread John Kanagaraj

Tom,

Can you clarify what you meant by 'just the database itself'? Fyi, there are
multiple layers in Apps 11i, in which the DB is just one. If this is
*entirely* only the DB (not including any part of the Concurrent
Manager/other apps layers), then the comparison is as if you are hosting
'just a database' and want to compare IBM/Sun/HP. The only consideration can
be 'interoperability' issues - which can leave you exposed to
finger-pointing between the Apps/CM layer OS vendors and DB level (IBM)
vendor in case of issues. Oracle leans towards Solaris, followed by HP-UX.

I do not currently know of any (fully) AIX based Apps 11i installations.
AFAIK, the list is (1) Solaris (2) HP-UX and (3) AIX [ a distant three ]. If
you are looking for a reason, availability of patches (esp. NLS patches if
you are planning to support multiple languages) and OS level certifications
could be a factor when considering the various flavors. 

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

What would you see if you were allowed to look back at your life at the end
of your journey in this earth?

** The opinions and statements above are entirely my own and not those of my
employer or clients **


> -Original Message-
> From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, May 28, 2003 11:56 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Oracle Financials and IBM/AIX Platforms
> 
> 
> All,
> 
> Anybody running Oracle Financials on an IBM/AIX platform?  
> I've been asked
> for opinions on running the latest version on an IBM P650 
> platform running
> AIX.  This would be just for the database itself.  Any suggestions for
> required memory/disk?  I know I need to provide more info 
> (like how many
> users etc), but I'm just looking for a thnumbs up/down on the 
> hardware for
> now.
> 
> thanks
> 
> Tom Mercadante
> Oracle Certified Professional
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> 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.net
-- 
Author: John Kanagaraj
  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: Unbreakable, my buns. Upgrade to 9.2/w2k

Rachel, thanks for your response.  Did you use the gui?

-Original Message-
Sent: Wednesday, May 28, 2003 4:05 PM
To: Multiple recipients of list ORACLE-L


and I had no problem at all doing it. But it was a simple database,
with no major connections or anything.

Maybe because it was a dev box? Oracle didn't think it worthwhile to
mess with my mind?

The only thing that happened was I had to leave the migration of the
database running overnight as it took HOURS


--- Freeman Robert - IL <[EMAIL PROTECTED]> wrote:
> and THAT is the reason NOT, repeat, NOT to use the OUI to do
> upgrades. Do
> 'em manually, I've had a very good success rate that way. Never trust
> the
> GUI's. Never.
> 
> Robert
> 
> (self-proclaimed GUI biggot)
> 
> -Original Message-
> To: Multiple recipients of list ORACLE-L
> Sent: 5/28/2003 11:56 AM
> 
> 
> I was not able to upgrade an 8.1.7.0 database to 9.2.0.1.0 on Win2K. 
> I
> had
> to create a new Oracle home, install 9.2, and clone the database in
> 9.2.
> OUI did not upgrade successfully.
> 
> 
> 
>  
> 
>   "Koivu, Lisa"
> 
>recipients of list ORACLE-L <[EMAIL PROTECTED]>   
> 
>   @Fairfieldresort cc:
> 
>   s.com>   Subject: Unbreakable,
> my
> buns.  Upgrade to 9.2/w2k  
>   Sent by: root
> 
>  
> 
>  
> 
>   05/28/2003 12:25
> 
>   PM
> 
>   Please respond
> 
>   to ORACLE-L
> 
>  
> 
>  
> 
> 
> 
> 
> 
> For those of you who have upgraded to 9.2 on w2k:
> 
> 
> How many of you have ended up with such a hosed server that you had
> to
> completely wipe Oracle off of it and start with a fresh install
> (9.2)?
> It
> was spectacular!  I ended up in this situation and I sure hope it's
> something I did or didn't do.  I sure don't have time for this when I
> upgrade my production databases.  How I long for those days wading
> through
> problems in unix... it was so much easier and more enjoyable...  I
> will
> say
> this was definately a learning experience.
> 
> 
> And Tom Mercadante, your document worked like a charm, and saved my
> a$$...
> Muchas Gracias!
> 
> 
> Lisa Koivu
> Oracle Database Administrator
> Fairfield Resorts, Inc.
> 5259 Coconut Creek Parkway
> Ft. Lauderdale, FL, USA  33063
> Office: 954-935-4117
> Fax:954-935-3639
> Cell:954-683-4459
> 
> 
> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net --
> Author:
> Koivu, Lisa 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: Thomas Day
>   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: Freeman Robert - IL
>   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!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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
--

RE: Oracle Financials and IBM/AIX Platforms

2003-05-29 Thread April Wells

Title: RE: Oracle Financials and IBM/AIX Platforms





 He's right, Thomas.


We have our middle tier and our Concurrent managers (don't ask) on Win 2K... but our database is on AIX.. and I REALLY wish the rest were too.  50 -80 gig allocated should be fine for the databae layer plus 100 M+- for each user for log and out files from Concurrent managers (depending on how much you plan to keep around).

anohter 20 gig for the apps layer if you are going to put that on AIX as well. 


-Original Message-
From: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
Sent: 5/28/2003 2:55 PM
Subject: Re: Oracle Financials and IBM/AIX Platforms



Hi Thomas,


We are running Oracle Applications Release 11.5.8 on an RS6000 F80 with
4
procs , 8 Gb RAM and 12 x 9.1 Gb , RAID 01 , SSA disks. Both application
and database are on the same machine. The database have almost 50 Gb and
there are aprox. 50 concurrent users , using four modules: GL , AP , INV
and PO. We´ve migrated this environment from an RS6000 F50 recently and
the
performance increased a lot.


We´ve been running Oracle Applications on IBM machines since release
10.7
and the performance and scalability are very good.


I don´t know the size of your database or the number of users that
your´re
planning to support , but the P650 (2-to8 way , 2-to-64Gb RAM , up to
587.2
GB internal storage) is a mid-range solution and I think it can support
high mission-critical applications easily with performace and
scalability
(I don´t work for IBM :- ).


HIH,
Antonio Belloni




 


  "Mercadante,


  Thomas F"    To:   Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>    
  <[EMAIL PROTECTED]    cc:


  te.ny.us>    Subject:  Oracle
Financials and IBM/AIX Platforms    
  Sent by:


  [EMAIL PROTECTED]


 


 


  28/05/2003 15:56


  Please respond to


  ORACLE-L


 


 






All,


Anybody running Oracle Financials on an IBM/AIX platform?  I've been
asked
for opinions on running the latest version on an IBM P650 platform
running
AIX.  This would be just for the database itself.  Any suggestions for
required memory/disk?  I know I need to provide more info (like how many
users etc), but I'm just looking for a thnumbs up/down on the hardware
for
now.


thanks


Tom Mercadante
Oracle Certified Professional


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
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.net
-- 
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).



The information contained in this communication, including attachments, is strictly 
confidential and for the intended use of the addressee only; it may also contain 
proprietary, price sensitive, or legally privileged information. Notice is hereby given that 
any disclosure, distribution, dissemination, use, or copying of the information by anyone 
other than the intended recipient is strictly prohibited and may be illegal. If you have 
received this communication in error, please notify the sender immediately by reply e-mail, 
delete this communication, and destroy all copies.
 

Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to 
this e-mail has been swept for viruses. We specifically disclaim all liability and will 
accept no responsibility for any damage sustained as a result of software viruses and advise 
you to carry out your own virus checks before opening any attachment.

RE: skip scan index

I don't think that fainting is in order when you get stinking rich.
If you get more then 100,000 rows in the table, that means that your
company is doing very, very well and that your stock options and your
bonuses will make it possible you to retire to a cosy little place 
near the Waikiki Beach. Now, nuff dreaming, get back to work!

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 4:05 PM
To: Multiple recipients of list ORACLE-L


this is the online store. If we EVER have a table with more than
100,000 rows in it, I'll faint.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  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).

Search engine

2003-05-29 Thread Tammy Jordan


Could someone give me some information or lead on how I can create a
search engine for usernames.  I am running an 8.1.7.4 database and also
9ias for my web application.  I have received some info such as to
create the code in ColdFusion and link up to the database with ODBC.
Does anyone have any other possibility? 


Thanks,

Tammy R. Jordan
Morehouse College
[EMAIL PROTECTED]


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tammy Jordan
  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: RE: Which method is more efficient

Title: RE: RE: Which method is more efficient



And 
with CTAS you can specify nologging to minimize redo generation. "Cloning" a 
table, renaming/dropping the source, and renaming the clone to the production 
table could be interesting. You would have to recreate indexes. 


  -Original Message-From: Jamadagni, Rajendra 
  [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 2003 
  1:35 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: RE: Which method is more efficient
  Bryan, 
  Can you ... create table my_work_table 
  as select * from changed_parts_table minus select * from existing_parts_table 
  / 
  The result will give you all the rows where _something_ is 
  different between your existing table and changed table. This will cut down a 
  lot on your processing. Afterwards, you can drop the my_work_table.
  Raj  
  Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. 
  QOTD: Any clod can have facts, having an opinion is an art 
  ! 
  -Original Message- From: 
  Rodrigues, Bryan [mailto:[EMAIL PROTECTED]] 
  Sent: Wednesday, May 28, 2003 2:40 PM To: Multiple recipients of list ORACLE-L Subject: RE: RE: Which method is more efficient 
  The fields that are changed are determined by 1) A loop would start until all records in parts change table are 
  done 2) Select a part record from the part changes 
  table 3) Select the same part from the existing part 
  table 4) Compare the value in the parts changes table 
  against the corresponding field in the part table 5) 
  After comparing all fields in the records, create record in a seperate work table with the values populated with null if 
  the field values matched and the new value if the 
  values did not. 6) This loop would continue until all 
  parts are done. 7) After any records in the work table 
  where all fields (outside of part number) are null are 
  deleted. 
  This process normally will decrease the number of records to 
  be processed after this point by 75%. 
  Hope that helps, 
  Bryan 
  -Original Message- Sent: 
  Wednesday, May 28, 2003 1:21 PM To: Multiple 
  recipients of list ORACLE-L 
  oh i missed part of it. the question is how do you figure out 
  which fields have changed? if you have to do an 
  anti-join on each field, then do an update of every 
  field. 
  the question is how will you determine which fields have 
  changed? > > From: 
  DENNIS WILLIAMS <[EMAIL PROTECTED]> > 
  Date: 2003/05/28 Wed PM 12:59:51 EDT > To: Multiple 
  recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: Which method is more efficient > > Bryan - If this is a critical issue, 
  I would try it both ways on a test > database and 
  use log miner to examine the amount of redo that is generated. > My recollection is that you 
  will find that the redo record records the > before 
  and after data for each field. So just updating all fields may 
  > generate significantly more redo. But don't trust my 
  recollection on this > issue, test it 
  yourself. > > Dennis 
  Williams > DBA, 80%OCP, 100% DBA > Lifetouch, Inc. > 
  [EMAIL PROTECTED] > > > -Original Message- 
  > Sent: Wednesday, May 28, 2003 10:50 AM > To: Multiple recipients of list ORACLE-L > > > Hello 
  everyone, > > I have a 
  question for the group of which method is more efficient. > > To set the stage my company has a 
  process to load part changes from vendors 
  > into the tables in an 8.1.7.4 Oracle database with 
  archiving on and this > database has a standby 
  database at disaster recovery site, so nologging is > not an option. > > There is a discussion going on as to which method is more 
  effective for > updating the information in a 
  table. In looking at effectiveness, I am > looking 
  at reducing the amount of redo information produced and having the 
  > database do the least amount of work. > > 1)    Method 1 is to 
  update the information only for the fields that have > changed, 1 field at a time. > 
  2)    Method 2 is to update the information for all the fields 
  in the > record whether they have changed or not, 1 
  record at a time. > > 
  The size of the record is 1843 bytes and the distribution of field 
  sizes: >  2 fields varchar2(240). 
  >  1 field varchar2(150) > 
  15 fields varchar2(50) > 1 field varchar2(3) 
  > 2 fields varchar2(20) > 4 
  fields varchar2(40) > 3 fields varchar2(1) 
  > 2 fields varchar2(25) > 2 
  fields number(10,2) > 1 field number(13,2) 
  > 1 field number(1) > 1 field 
  number > 1 field varchar2(6) > 1 field number (17,2) > 1 field 
  varchar2(4) > 3 fields that are date. 
  > > In the past couple of 
  months the average number of fields changed per record > was 3 to 4 fields per 
  record. > > Thanks for 
  your help, > > Bryan 
  Rodrigues > Oracle DBA > 
  Elcom, Inc. > > 
  > -- > Please see the 
  official ORACLE-L FAQ: http://www.ora

RE: skip scan index

Not true, try this:

create table test_skip1 ( c1 number,c2 number, primary key (c1,c2));

begin
 for i in 1..10 loop
  insert into test_skip1 values (1,i);
  insert into test_skip1 values (2,i);
 end loop;
 end;


alter session set sql_trace = true;

select --+ index_ss(test_skip1, )
 c1,c2
 from test_skip1
 where c2 = 100;

 select blocks from dba_segments where segment_name = 'SYS_C0038241'  -- pk
index
 blocks =  384
---
-- From the tkprof output
---
select --+ index_ss(test_skip1, )
 c1,c2
 from test_skip1
 where c2 = 100

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.01   0.00  0  0  0
0
Execute  1  0.00   0.00  0  0  0
0
Fetch1  0.00   0.00  0  8  0
2
--- --   -- -- -- --
--
total3  0.01   0.00  0  8  0
2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 26  (IA)

Rows Row Source Operation
---  ---
  2  INDEX SKIP SCAN SYS_C0038241 (cr=8 r=0 w=0 time=1226 us)(object id
810709)


Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
  2   INDEX (SKIP SCAN) OF 'SYS_C0038241' (UNIQUE)

---
---
-- This is using index scan
---
---

select --+ index( test_skip1, )
 c1,c2
 from test_skip1
 where c2 = 100

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.00   0.00  0  0  0
0
Execute  1  0.00   0.00  0  0  0
0
Fetch1  0.12   0.11  0331  0
2
--- --   -- -- -- --
--
total3  0.12   0.11  0331  0
2

Regards,

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 4:05 PM
To: Multiple recipients of list ORACLE-L


I'm not talking about the cost either. The way by which is getting executed 
is by reading the whole index. You may call it fast full scan, you may call 
it index skip scan, but it is still the same thing: sequential read of the
whole index. In other words, the name doesn't matter.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 3:40 PM
To: Multiple recipients of list ORACLE-L


I'm talking about the way it get executed not the statistics or the cost.

The cost is completely dependent on the distribution of the data.

For example if we have table (c1 number, c2 number) and a primary key on
(c1, c2).

And the data looks like this:

c1  c2
A   1
A   2
A   3
A   4
.   .
.   .
A   
A   1
B   1
B   2
B   3
.   .
.   .
.   .
B   
B   1


And I run this sql using skip scan:

select c1,c2
from table
where c2 = 100

This will be almost similar if you execute this (two unique lookups):

select
   c1,c2
from table
where c1 = 'A' and c2 = 100
union all
select
   c1,c2
from table
where c1 = 'B' and c2 = 100

There will be extra cost related to finding the unique value of c1 but will
be much cheaper compared to full index scan.

Regards,

Waleed



-Original Message-
Sent: Wednesday, May 28, 2003 2:52 PM
To: Multiple recipients of list ORACLE-L


True enough, it will show as "index skip scan", but if you take a look at 
the statistics, you'll see that the nubmer of blocks read roughly
corresponds 
to the number of blocks in the index. It is also logical, because without
the first column, the only way to find the desired key is to read the whole
index. Indexes are B*tree structures which are searched using modified
version
of binary search. The ordering is so called lexicographical order, which
means
that the column 1 is compared first, then column 2 if there is equality in
the column 1 and so forth until we reach differing columns. Without knowing
column 1, you MUST read them all and see which ones contain the sought for 
column 2.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 2:17 PM
To: Multiple recipients of list ORACLE-L


Skip scan will show in the execution plan as "skip scan". Not true that it
will show as regular index scan.

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 1:20 PM
To: Multiple recipients of list ORACLE-L


A skip scan can be a index scan, full scan or range scan type access. It
simply allows a unusable column t

RE: Bulk collect got truncated?

2003-05-29 Thread Gorbounov,Vadim

Mark, 

No chance I'm running out of memory. I checked max PGA for the session, it
was around 26M after successful execution, what is not something completely
unbareable, and didn't climb up after next executions.

We can probably workaround  this issue, thanks for suggestion.

Regards
Vadim

-Original Message-
Sent: Wednesday, May 28, 2003 3:15 PM
To: Multiple recipients of list ORACLE-L


Is it possible that you are running out memory on the OS?

A different question I have is why bulk collect such a large amount at once.
Why not do a cursor with a limit on the fetch? This would allow you to
process in smaller batches instead of one gigantic fetch and insert.

Mark

-Original Message-
Sent: Wednesday, May 28, 2003 11:07 AM
To: Multiple recipients of list ORACLE-L



Hi dear listers, 

Some of you may still remember this thread, bulk collect truncated to 65535
records sometimes.
I've got this case reproduceable and tried all suggestions , 

In a brief, 
 SELECT returns 318847 rows, 
 INSERT INTO FROM SELECT - 318847 rows, 
 PL/SQL plain FOR cr IN (select ..) LOOP - - 318847 rows
 PL/SQL with BULK COLLECT many different code versions - sometimes  returs
65535 records instead, the rest is truncated

What might be interesting, in case when it fails, it doesn't retrieve
requiered rows from disk. I can judge it by much shorter responce time and
10046 trace doesn't show db file sequential read events what always showup
when number of rows is correct.

10046 trace provides interesting details,  65535 records is approximately
the point (=/- 50 records) where it usually does first db file sequential
read in case of successful execution.

So Waleed, apparently there is a problem here, 9.0.1.4 Solaris. Time to TAR
now.

Below is the spool from my recent session.

Have a good day, 
Vadim

set serveroutput on
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index
by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index
by binary_integer;
  4 esubsvcid   t_sub_svc_id;
  5 evalt_subsvcext_key;
  6  BEGIN
  7 SELECT /*+ index(p sub_svc_parm_ix2) */
  8sub_svc_id, val
  9   BULK COLLECT INTO esubsvcid, eval
 10   FROM CBQA4SP.sub_svc_parm p
 11  WHERE parm_id =10;
 12 dbms_output.put_line(esubsvcid.count);
 13  end;
 14  /
318847


PL/SQL procedure successfully completed.

Elapsed: 00:00:12.03
SQL> alter session set events = '10046 trace name context forever, level 8';

Session altered.

Elapsed: 00:00:00.00
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index
by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index
by binary_integer;
  4 esubsvcid   t_sub_svc_id;
  5 evalt_subsvcext_key;
  6  BEGIN
  7 SELECT /*+ index(p sub_svc_parm_ix2) */
  8sub_svc_id, val
  9   BULK COLLECT INTO esubsvcid, eval
 10   FROM CBQA4SP.sub_svc_parm p
 11  WHERE parm_id =10;
 12 dbms_output.put_line(esubsvcid.count);
 13  end;
 14  /
65535


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> alter session set events = '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set sql_trace= true;

Session altered.

Elapsed: 00:00:00.00
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index
by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index
by binary_integer;
  4 esubsvcid   t_sub_svc_id;
  5 evalt_subsvcext_key;
  6  BEGIN
  7 SELECT /*+ index(p sub_svc_parm_ix2) */
  8sub_svc_id, val
  9   BULK COLLECT INTO esubsvcid, eval
 10   FROM CBQA4SP.sub_svc_parm p
 11  WHERE parm_id =10;
 12 dbms_output.put_line(esubsvcid.count);
 13  end;
 14  /
65535


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> alter session set sql_trace= false;

Session altered.

Elapsed: 00:00:00.00
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF CBQA4SP.sub_svc.sub_svc_id%TYPE index
by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF CBQA4SP.sub_svc_parm.val%TYPE index
by binary_integer;
  4 esubsvcid   t_sub_svc_id;
  5 evalt_subsvcext_key;
  6  BEGIN
  7 SELECT /*+ index(p sub_svc_parm_ix2) */
  8sub_svc_id, val
  9   BULK COLLECT INTO esubsvcid, eval
 10   FROM CBQA4SP.sub_svc_parm p
 11  WHERE parm_id =10;
 12 dbms_output.put_line(esubsvcid.count);
 13  end;
 14  /
65535


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> SELECT /*+ index(p sub_svc_parm_ix2) */ count(*)  FROM
CBQA4SP.sub_svc_parm p
  2  WHERE parm_id =10;

  COUNT(*)

--

318847


Elapsed: 00:00:00.03
SQL> 
SQL> DECLARE
  2 TYPE t_sub_svc_id IS TABLE OF number index by binary_integer;
  3 TYPE t_subsvcext_key IS TABLE OF var

RE: index-organized table question

Roy - This could be a good IOT candidate, but I haven't used them myself. My
suggestion would be to test it. Here is a good paper on how to decide:
http://www.peled.com/white_papers/iot.pdf
A more recent edition is available on http://www.orapub.com (free but must
register)

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Wednesday, May 28, 2003 11:45 AM
To: Multiple recipients of list ORACLE-L


Greetings all,

Say I have two main tables in a M:M relationship, and a junction table
resolving the relationship, like so:

create table staff
   (staff_id number PRIMARY KEY
   , name varchar2(50)
   , <>) ;

create table projects
   (proj_id number PRIMARY KEY
   , name varchar2(50)
   , mgr_id number
   , <>) ;

create table staff_projects
   (staff_id number
   , proj_id number
   , CONSTRAINT staff_projects_pk 
 PRIMARY KEY (staff_id, proj_id)
   , FOREIGN KEY (staff_id) REFERENCES staff(staff_id)
   , FOREIGN KEY (proj_id) REFERENCES projects(proj_id)
   ) ;

Queries that join staff_projects to projects to pull project info for a
given staff_id should be really fast, since staff_id is the leading column
in the index created to enforce the PK on staff_projects (right?)--and in
fact, the staff_projects table itself shouldn't need to be touched, since
all the needed info is in this index (also right?).

If I want to speed lookups of staff info for a given proj_id, I can create
another unique index on staff_projects(proj_id, staff_id).  There again, the
staff_projects table shouldn't need to be touched, b/c all the info is in
the index.

At this point, it seems like the table is sort of superflous--all the info
in it is better accessible in the two indexes.  If that's right--is there an
advantage in making staff_projects an index organized table?

Thanks!

-Roy

Roy Pardee
Programmer/Analyst/DBA
SWFPAC Lockheed Martin IT
Extension 8487
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pardee, Roy E
  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: 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: Search engine

Tammy
   - For a small amount of searching, take a look at function-based indexes
(you just said usernames).
   - You can also create your own method with PL/SQL pretty easily. Strip
individual words into a separate table that can be easily searched.
   - For heavy-duty searching, look at Oracle Text (interMedia Text in
Oracle8i)
http://technet.oracle.com/products/text/content.html
   - Don't use ColdFusion. It is easy but difficult to scale.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Wednesday, May 28, 2003 3:45 PM
To: Multiple recipients of list ORACLE-L



Could someone give me some information or lead on how I can create a
search engine for usernames.  I am running an 8.1.7.4 database and also
9ias for my web application.  I have received some info such as to
create the code in ColdFusion and link up to the database with ODBC.
Does anyone have any other possibility? 


Thanks,

Tammy R. Jordan
Morehouse College
[EMAIL PROTECTED]


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tammy Jordan
  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: 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: Oracle Financials and IBM/AIX Platforms

2003-05-29 Thread John Kanagaraj

Tom,

As one who 'transitioned' from a 'normal' DBA (if there ever was a 'normal!)
to an 'Apps DBA', I went through a self-learning path (as did many others in
this list), and tried to capture this transformation in an article. It is
housed at http://www.geocities.com/john_sharmila/links.htm - feel free to
read/question.

Be prepared to read, and read lots! Hang out at http://www.oaug.org (get
PHBs to purchase a subscription). There is also a Apps-specific listserv
there. Metalink will continue to be a favorite (and constant) friend!

All the best,
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Grace - Getting something we don't deserve; Mercy - NOT getting something we
deserve

Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!

** The opinions and statements above are entirely my own and not those of my
employer or clients **

> -Original Message-
> From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, May 28, 2003 12:55 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Oracle Financials and IBM/AIX Platforms
> 
> 
> All,
> 
> Another question.  What does it take to support Oracle 
> Financials?  I know
> that a Financials DBA has a different skill set than an 
> Oracle DBA, but how
> many people are we talking about here?  How long does a "typical"
> installation take to complete?  I know a little about it, but 
> not enough to
> provide some answers here.
> 
> thanks
> 
> Tom Mercadante
> Oracle Certified Professional
> 
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 2:56 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> All,
> 
> Anybody running Oracle Financials on an IBM/AIX platform?  
> I've been asked
> for opinions on running the latest version on an IBM P650 
> platform running
> AIX.  This would be just for the database itself.  Any suggestions for
> required memory/disk?  I know I need to provide more info 
> (like how many
> users etc), but I'm just looking for a thnumbs up/down on the 
> hardware for
> now.
> 
> thanks
> 
> Tom Mercadante
> Oracle Certified Professional
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> 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.net
> -- 
> 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.net
-- 
Author: John Kanagaraj
  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: Oracle Performance Tuning 101 Book now Available!

2003-05-29 Thread Johnson, Michael

Charlie,

I am not a spammer   I am a struggling DBA trying to find answers just
like you. I was mired in  Hit Ratios for the longest time  scratching my
head trying to figure it all out ... lost in the clouds with no way out so
to speak.  In the end , it got me no where.  I met this guy Gaja a few
years back and he gave me some ideas to help me.Later I met Kirti.
They are first class folks along with alot of folks on this board.
Always willing  to help ... Always willing to answer questions.   

As a  free market capitalist pig I believe folks who work hard and put out
good information should get notice as this book richly deserves.In
addition, they deserve to be compensated for their hard work and knowledge
just as Home Depot is compensating you,   correct ?  For example, if I told
you to buy Synopsis (symbol SNPS) today at $59 and in a year that stock
doubles then that information could be worth some money correct ?

fwiw, Mike

-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 28, 2003 11:10 AM
To: Multiple recipients of list ORACLE-L



I refuse to do business with spammers;  regardless of what their product
may be.



 

  "Johnson, Michael "

  <[EMAIL PROTECTED]To:   Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]> 
  fmc.af.mil>   cc:

  Sent by:  Subject:  RE: Oracle
Performance Tuning 101 Book now Available!   
  [EMAIL PROTECTED]

 

 

  05/28/2003 10:44 AM

  Please respond to

  ORACLE-L

 

 






IMHO,  Every DBA should own this book.  It will change your way of thinking
about how to approach oracle performance problems.

 Mike
-Original Message-
From: Kent Mingus
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 28, 2003 6:50 AM
To: [EMAIL PROTECTED]
Subject: Oracle Performance Tuning 101 Book now Available!

To unsubscribe from this Precise Software email list click here
and submit your email address.

|-|
| |
| |
| |
| |
|(Embedded image moved to file: pic07195.gif) |
|(Embedded image moved to file: pic07825.gif) |
|Free Book from Oracle|
|Press|
|   (E|
|   mb|
|   ed|
|   de|
|   d |
|   im|
|   ag|
|   e |
|   mo|
|   ve|
|   d |
|   to|
|   fi|
|   le|
|   : |
|   pi|
|   c0|
|   38|
|   30|
|   .g|
|   if|
|   ) |
| |
| |
|(Embedded image moved to file: pic15360.gif) |
| |
| |
| |
| |
|

RE: skip scan index

OK. I don't have the 9i instance that I can use for testing right now,
but tonight, at home, I'll give you the counter example. The bottom line 
is that the only way to execute a skip scan with a B*Tree index is to 
go and read it whole. No other way.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 4:40 PM
To: Multiple recipients of list ORACLE-L


Not true, try this:

create table test_skip1 ( c1 number,c2 number, primary key (c1,c2));

begin
 for i in 1..10 loop
  insert into test_skip1 values (1,i);
  insert into test_skip1 values (2,i);
 end loop;
 end;


alter session set sql_trace = true;

select --+ index_ss(test_skip1, )
 c1,c2
 from test_skip1
 where c2 = 100;

 select blocks from dba_segments where segment_name = 'SYS_C0038241'  -- pk
index
 blocks =  384
---
-- From the tkprof output
---
select --+ index_ss(test_skip1, )
 c1,c2
 from test_skip1
 where c2 = 100

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.01   0.00  0  0  0
0
Execute  1  0.00   0.00  0  0  0
0
Fetch1  0.00   0.00  0  8  0
2
--- --   -- -- -- --
--
total3  0.01   0.00  0  8  0
2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 26  (IA)

Rows Row Source Operation
---  ---
  2  INDEX SKIP SCAN SYS_C0038241 (cr=8 r=0 w=0 time=1226 us)(object id
810709)


Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
  2   INDEX (SKIP SCAN) OF 'SYS_C0038241' (UNIQUE)

---
---
-- This is using index scan
---
---

select --+ index( test_skip1, )
 c1,c2
 from test_skip1
 where c2 = 100

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.00   0.00  0  0  0
0
Execute  1  0.00   0.00  0  0  0
0
Fetch1  0.12   0.11  0331  0
2
--- --   -- -- -- --
--
total3  0.12   0.11  0331  0
2

Regards,

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 4:05 PM
To: Multiple recipients of list ORACLE-L


I'm not talking about the cost either. The way by which is getting executed 
is by reading the whole index. You may call it fast full scan, you may call 
it index skip scan, but it is still the same thing: sequential read of the
whole index. In other words, the name doesn't matter.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 3:40 PM
To: Multiple recipients of list ORACLE-L


I'm talking about the way it get executed not the statistics or the cost.

The cost is completely dependent on the distribution of the data.

For example if we have table (c1 number, c2 number) and a primary key on
(c1, c2).

And the data looks like this:

c1  c2
A   1
A   2
A   3
A   4
.   .
.   .
A   
A   1
B   1
B   2
B   3
.   .
.   .
.   .
B   
B   1


And I run this sql using skip scan:

select c1,c2
from table
where c2 = 100

This will be almost similar if you execute this (two unique lookups):

select
   c1,c2
from table
where c1 = 'A' and c2 = 100
union all
select
   c1,c2
from table
where c1 = 'B' and c2 = 100

There will be extra cost related to finding the unique value of c1 but will
be much cheaper compared to full index scan.

Regards,

Waleed



-Original Message-
Sent: Wednesday, May 28, 2003 2:52 PM
To: Multiple recipients of list ORACLE-L


True enough, it will show as "index skip scan", but if you take a look at 
the statistics, you'll see that the nubmer of blocks read roughly
corresponds 
to the number of blocks in the index. It is also logical, because without
the first column, the only way to find the desired key is to read the whole
index. Indexes are B*tree structures which are searched using modified
version
of binary search. The ordering is so called lexicographical order, which
means
that the column 1 is compared first, then column 2 if there is equality in
the column 1 and so forth until we reach differing columns. Without knowing
column 1, you MUST read them all and see which ones contain the sought for 
column 2.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-O

RE: skip scan index

what is this thing you call stock options? My company is already very
rich (Sony) and they don't seem to be passing any of it along to me :(

and it'll be upstate NY or somewhere cold(ish) rather than Waikiki
Beach, I can assure you!

--- "Gogala, Mladen" <[EMAIL PROTECTED]> wrote:
> I don't think that fainting is in order when you get stinking rich.
> If you get more then 100,000 rows in the table, that means that your
> company is doing very, very well and that your stock options and your
> bonuses will make it possible you to retire to a cosy little place 
> near the Waikiki Beach. Now, nuff dreaming, get back to work!
> 
> Mladen Gogala
> Oracle DBA
> Phone:(203) 459-6855
> Email:[EMAIL PROTECTED]
> 
> 
> -Original Message-
> Sent: Wednesday, May 28, 2003 4:05 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> this is the online store. If we EVER have a table with more than
> 100,000 rows in it, I'll faint.
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Gogala, Mladen
>   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!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: skip scan index

2003-05-29 Thread Hengen, Brian

You'd see it in the explain plan -- it should look something like this:
TABLE ACCESS (BY INDEX ROWID) OF 'PHONEBOOK'
INDEX (SKIP SCAN) OF 'I_PHONEBOOK_SKIP' (NON-UNIQUE)

--Brian

-Original Message-
Sent: Wednesday, May 28, 2003 9:45 AM
To: Multiple recipients of list ORACLE-L

List - If I wanted to know whether my query was taking advantage of index
skip scans, how would I know? Is there something different in the EXPLAIN
PLAN that I should look for? The discussion just made me curious.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Wednesday, May 28, 2003 9:30 AM
To: Multiple recipients of list ORACLE-L

At 02:59 AM 5/28/2003 -0800, you wrote:
>Okay, I have a developer here who has been reading the docs (this can
>be dangerous!)
>
>we are adding functionality to one of our applications, this will
>involve using multiple fulfillment houses, so we'll be adding the
>fulfillment vendor id to the order table. Easy, this is not a problem.
>We want to be able to search by order date and by fulfillment vendor
>id/order date
>
>Traditional design would be to add two indexes: one on order date, and
>a concatenated one on fulfillment vendor id/order date.
>
>The developer is telling me to create a "skip scan index" instead of
>two different ones. MY reading in the FM tells me that skip scan index
>is not a type of index, but rather a way Oracle uses to use an index
>even if the leftmost column is not in the query.
>
>Is there any benefit in my building only the one index? Our order
>volume is not so high (and never will be) that there is a visible
>performance impact if I have the two indices.
>
>This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future.
>Solaris
>
>Any suggestions/comments/war stories would be appreciated. I know I've
>seen Jonathan post on skip scan indexes before but I can't find the
>specific reference at the moment.

As others already said, it is a "index skip scan" access method, not a 
"skip scan" index. It is like an implicit OR where the optimizer looks up 
all distinct values for the missing prefix column(s) and augments the 
predicate (sort of) with these values and then does traditional index 
scans, ORing the results. It may not happen exactly that way, but 
conceptually that is what happens. From this you can deduce that it is an 
option only when there are relatively few distinct prefix values. In your 
case I doubt that the optimizer would ever choose a skip scan. Unless you 
have only a handfull (literally 5 or less) of fullfilment vendors. I don't 
have hard numbers as to the number of distinct prefix values beyond which a 
skip scan becomes too expensive compared to an FTS but during my tests in 
preparation for my IOUG presentation I had a hard time constructing an 
example where the optimizer would choose a skip scan - and I had tables 
with just 1 distinct prefix value.
My vote goes for your proposed two indices.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  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: 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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hengen, Brian
  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

RE: SAP Hands SAP DB over to MySQL

I dunno.  Though both want to make a profit ( and rightly so ) SAP 
doesn't seem to have the same mercenary mentality that MS has.

Jared






"Orr, Steve" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 05/28/2003 11:52 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: SAP Hands SAP DB over to MySQL


Reminiscent of the M$/Sybase "partnership?"


-Original Message-
Sent: Wednesday, May 28, 2003 11:40 AM
To: '[EMAIL PROTECTED]'


At http://www.sapdb.org/7.4/pdf/sapdb_letter.pdf SAP offers "clarification:"

SAP: 
"Contrary to erroneous press reports, SAP AG has not given up any rights 
concerning the SAP DB code base nor handed over or even sold SAP DB to 
MySQL AB."

SAP: 
"SAP AG remains responsible for ongoing development and support."

CNet: 
"MySQL will take over most of the development of SAP DB."



-Original Message-
Sent: Wednesday, May 28, 2003 12:01 PM
To: Multiple recipients of list ORACLE-L
Importance: High


The past few months I've been wondering when MySQL would start
putting pressure on Oracle in the same way that Linux is putting
pressure on MS.

Maybe sooner than you think:

http://news.com.com/2100-1012_3-1010522.html?tag=fd_top


Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Orr, Steve
  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: 
  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).

lengthy URL's

Just a slightly OT post here.

It isn't unusual when writing to this list to come up with 
a few URL's to include in an email. 

The problem at times is that the URL's can easily be
200 characters in length, meaning that those reading
your email must cut and paste the URL's to make use 
of them.

An easy solution to this is to make use of www.tinyurl.com

It's easy:  cut your lengthy URL from the browser, go to 
www.tinyurl.com, paste your lnnng URL, and get 
a short one in return. 

Anyone using the tiny URL will be redirected to the original.

It's a free service, but they accept donations.  Personally, I've
been using it so much I gave them $10 the other day. 

Jared






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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: skip scan index

2003-05-29 Thread Thater, William



> -Original Message-
> From: Rachel Carmichael [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, May 28, 2003 5:45 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: skip scan index
> 
> 
> what is this thing you call stock options? My company is already very
> rich (Sony) and they don't seem to be passing any of it along to me :(
> 
> and it'll be upstate NY or somewhere cold(ish) rather than Waikiki
> Beach, I can assure you!

your defination of upstate or mine?;-)
> 
www.mailfiler.com [RC-3H561A2]
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Thater, William
  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: skip scan index

It's like any other execution plan, good in certain data distributions and
bad in others.

But I do not think it's correct that skip scan requires reading the whole
index (it's even clear in this test).

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 5:30 PM
To: Multiple recipients of list ORACLE-L


OK. I don't have the 9i instance that I can use for testing right now,
but tonight, at home, I'll give you the counter example. The bottom line 
is that the only way to execute a skip scan with a B*Tree index is to 
go and read it whole. No other way.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 4:40 PM
To: Multiple recipients of list ORACLE-L


Not true, try this:

create table test_skip1 ( c1 number,c2 number, primary key (c1,c2));

begin
 for i in 1..10 loop
  insert into test_skip1 values (1,i);
  insert into test_skip1 values (2,i);
 end loop;
 end;


alter session set sql_trace = true;

select --+ index_ss(test_skip1, )
 c1,c2
 from test_skip1
 where c2 = 100;

 select blocks from dba_segments where segment_name = 'SYS_C0038241'  -- pk
index
 blocks =  384
---
-- From the tkprof output
---
select --+ index_ss(test_skip1, )
 c1,c2
 from test_skip1
 where c2 = 100

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.01   0.00  0  0  0
0
Execute  1  0.00   0.00  0  0  0
0
Fetch1  0.00   0.00  0  8  0
2
--- --   -- -- -- --
--
total3  0.01   0.00  0  8  0
2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 26  (IA)

Rows Row Source Operation
---  ---
  2  INDEX SKIP SCAN SYS_C0038241 (cr=8 r=0 w=0 time=1226 us)(object id
810709)


Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
  2   INDEX (SKIP SCAN) OF 'SYS_C0038241' (UNIQUE)

---
---
-- This is using index scan
---
---

select --+ index( test_skip1, )
 c1,c2
 from test_skip1
 where c2 = 100

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.00   0.00  0  0  0
0
Execute  1  0.00   0.00  0  0  0
0
Fetch1  0.12   0.11  0331  0
2
--- --   -- -- -- --
--
total3  0.12   0.11  0331  0
2

Regards,

Waleed

-Original Message-
Sent: Wednesday, May 28, 2003 4:05 PM
To: Multiple recipients of list ORACLE-L


I'm not talking about the cost either. The way by which is getting executed 
is by reading the whole index. You may call it fast full scan, you may call 
it index skip scan, but it is still the same thing: sequential read of the
whole index. In other words, the name doesn't matter.

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]


-Original Message-
Sent: Wednesday, May 28, 2003 3:40 PM
To: Multiple recipients of list ORACLE-L


I'm talking about the way it get executed not the statistics or the cost.

The cost is completely dependent on the distribution of the data.

For example if we have table (c1 number, c2 number) and a primary key on
(c1, c2).

And the data looks like this:

c1  c2
A   1
A   2
A   3
A   4
.   .
.   .
A   
A   1
B   1
B   2
B   3
.   .
.   .
.   .
B   
B   1


And I run this sql using skip scan:

select c1,c2
from table
where c2 = 100

This will be almost similar if you execute this (two unique lookups):

select
   c1,c2
from table
where c1 = 'A' and c2 = 100
union all
select
   c1,c2
from table
where c1 = 'B' and c2 = 100

There will be extra cost related to finding the unique value of c1 but will
be much cheaper compared to full index scan.

Regards,

Waleed



-Original Message-
Sent: Wednesday, May 28, 2003 2:52 PM
To: Multiple recipients of list ORACLE-L


True enough, it will show as "index skip scan", but if you take a look at 
the statistics, you'll see that the nubmer of blocks read roughly
corresponds 
to the number of blocks in the index. It is also logical, because without
the first column, the only way to find the desired key is to read the whole
index. Indexes are B*tree structures which are searched using modified
version
of binary search. The ordering is so called lexicographical order, which
me

RE: latch free - library cache

2003-05-29 Thread Michael Wu

Title: RE: latch free - library cache

Raj,
With
Oracle 9.2.0.3, I got one of the following three problems when CF=F or
S.
1)
ORA-03113: end-of-file on communication channel
2)
ORA-01802: Julian date is out of range
3)
performance problems.
My
previous statement is still valid.

-Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Thursday, May 15, 2003
2:52 PMTo: Multiple recipients of list ORACLE-LSubject:
RE: latch free - library cache
Send it to me ... I have it CS=F working on all of my 5
production RAC instances 9202 with 5 patches (for various reasons).
XML/JAMA/Intermedia/CLOBS ... you name it, we have it.
ps: what are the problems you are facing?
Raj
Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art
!
-Original Message- From:
Michael Wu [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 15, 2003 11:59 AM To: Multiple recipients of list ORACLE-L Subject: RE: latch free - library cache
If somebody can make CURSOR_SHARING = SIMILAR|FORCE to work
even with 9iR2 (with the latest patch), I am willing to give him
$1000.
-Original Message- Sent:
Thursday, May 15, 2003 9:42 AM To: Multiple recipients
of list ORACLE-L
Cursor sharing set to FARCE forces constants to be converted
into bind variables and enables SQL statements to be
shared even if the're not identical. It also enables
quite a few very interesting ora-0600 and ora-7445
errors that have very good applications in a DSS
environment. It will work properly in Oracle 19zR20 and not in
oracle 9iR2. Plase, de patient. Your money is very important
to us.

**
This e-mail contains privileged attorney-client communications and/or confidential information, and is only for the use by the intended recipient. Receipt by an unintended recipient does not constitute a waiver of any applicable privilege.

Reading, disclosure, discussion, dissemination, distribution or copying of this information by anyone other than the intended recipient or his or her employees or agents is strictly prohibited. If you have received this communication in error, please immediately notify us and delete the original material from your computer.

Sempra Energy Trading Corp. (SET) is not the same company as SDG&E or SoCalGas, the utilities owned by SET's parent company. SET is not regulated by the California Public Utilities Commission and you do not have to buy SET's products and services to continue to receive quality regulated service from the utilities.
**

Re: skip scan index

2003-05-29 Thread Richard Foote

Hi Mladen,

Not true.

The whole point of the Index Skip Scanning is that Oracle can avoid probes
of leaf pages because it knows for sure that the required index value can't
possibly be found in a leaf node based on the less than values found in the
branch nodes (read my earlier post if it makes any sense).

For a concatenated index artist || album_name and you want all albums called
"ALADDIN SANE"
by any artist.

If a leaf node had "BEATLES || SEG PEPPERS|| as it's maximum value and the
next leaf node had BEATLES || WHITE ALBUM as it's max values there *can't
possible be* an album called ALADDIN SANE in that leaf node and so Oracle
doesn't need to probe it.

If however, the next leaf node had a max value of BOWIE || DIAMOND DOGS then
this node *could* have a value of ALADDIN SANE so would need to be probed.

Note also that BOWIE || AARDVARK also causes a probe of the leaf node as
Oracle can't know for sure that there's no value between BEATLES and BOWIE
(BOLAN ||ALADDIN SANE for example)

Of cause a max value of BEATLES || ABBEY ROAD followed by a max value less
than equal to BEATLES || SGT PEPPERS will also cause a probe.

So it depends on whether Oracle can know for sure that the value it's
looking for can't possibly exist in a leaf node *by just checking the branch
node*.

Suggestions that a full index scan and a index skip scan are just the same
thing are therefore incorrect.

Cheers

Richard




- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, May 29, 2003 7:29 AM


> OK. I don't have the 9i instance that I can use for testing right now,
> but tonight, at home, I'll give you the counter example. The bottom line
> is that the only way to execute a skip scan with a B*Tree index is to
> go and read it whole. No other way.
>
> Mladen Gogala
> Oracle DBA
> Phone:(203) 459-6855
> Email:[EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Wednesday, May 28, 2003 4:40 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Not true, try this:
>
> create table test_skip1 ( c1 number,c2 number, primary key (c1,c2));
>
> begin
>  for i in 1..10 loop
>   insert into test_skip1 values (1,i);
>   insert into test_skip1 values (2,i);
>  end loop;
>  end;
>
>
> alter session set sql_trace = true;
>
> select --+ index_ss(test_skip1, )
>  c1,c2
>  from test_skip1
>  where c2 = 100;
>
>  select blocks from dba_segments where segment_name = 'SYS_C0038241'  --
pk
> index
>  blocks =  384
> ---
> -- From the tkprof output
> ---
> select --+ index_ss(test_skip1, )
>  c1,c2
>  from test_skip1
>  where c2 = 100
>
> call count   cpuelapsed   disk  querycurrent
> rows
> --- --   -- -- -- --
> --
> Parse1  0.01   0.00  0  0  0
> 0
> Execute  1  0.00   0.00  0  0  0
> 0
> Fetch1  0.00   0.00  0  8  0
> 2
> --- --   -- -- -- --
> --
> total3  0.01   0.00  0  8  0
> 2
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 26  (IA)
>
> Rows Row Source Operation
> ---  ---
>   2  INDEX SKIP SCAN SYS_C0038241 (cr=8 r=0 w=0 time=1226 us)(object
id
> 810709)
>
>
> Rows Execution Plan
> ---  ---
>   0  SELECT STATEMENT   GOAL: CHOOSE
>   2   INDEX (SKIP SCAN) OF 'SYS_C0038241' (UNIQUE)
>
> ---
> ---
> -- This is using index scan
> ---
> ---
>
> select --+ index( test_skip1, )
>  c1,c2
>  from test_skip1
>  where c2 = 100
>
> call count   cpuelapsed   disk  querycurrent
> rows
> --- --   -- -- -- --
> --
> Parse1  0.00   0.00  0  0  0
> 0
> Execute  1  0.00   0.00  0  0  0
> 0
> Fetch1  0.12   0.11  0331  0
> 2
> --- --   -- -- -- --
> --
> total3  0.12   0.11  0331  0
> 2
>
> Regards,
>
> Waleed
>
> -Original Message-
> Sent: Wednesday, May 28, 2003 4:05 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I'm not talking about the cost either. The way by which is getting
executed
> is by reading the whole index. You may call it fast full scan, you may
call
> it index skip scan, but it is still the same thing: sequential read of the
> whole index. In other words, the name doesn't matter.
>
> Mladen Gogala
> Oracle DBA
> Phone:(203) 459-6855
> Email:[EMAIL PROTECTED]
>
>
> -Origi

RE: SAP Hands SAP DB over to MySQL

Agreed, it's a stretch, but I was actually thinking it was MySQL who was looking to 
leverage off SAP's code because they said it would reduce development time 2 years. 
Meanwhile SAP folks are saying this partnership gives MySQL customers a robust 
enterprise alternative. Open source hype and "co-opetition?" 

SAP's motivation for SAP DB is to provide a low cost alternative to Oracle, that 
sentiment is widely shared and gaining traction. Time to selling my Oracle stock?

Steve



-Original Message-
Sent: Wednesday, May 28, 2003 3:08 PM
To: [EMAIL PROTECTED]
Cc: Orr, Steve
Importance: High


I dunno.  Though both want to make a profit ( and rightly so ) SAP 
doesn't seem to have the same mercenary mentality that MS has.

Jared






"Orr, Steve" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 05/28/2003 11:52 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: SAP Hands SAP DB over to MySQL


Reminiscent of the M$/Sybase "partnership?"


-Original Message-
Sent: Wednesday, May 28, 2003 11:40 AM
To: '[EMAIL PROTECTED]'


At http://www.sapdb.org/7.4/pdf/sapdb_letter.pdf SAP offers "clarification:"

SAP: 
"Contrary to erroneous press reports, SAP AG has not given up any rights 
concerning the SAP DB code base nor handed over or even sold SAP DB to 
MySQL AB."

SAP: 
"SAP AG remains responsible for ongoing development and support."

CNet: 
"MySQL will take over most of the development of SAP DB."



-Original Message-
Sent: Wednesday, May 28, 2003 12:01 PM
To: Multiple recipients of list ORACLE-L
Importance: High


The past few months I've been wondering when MySQL would start
putting pressure on Oracle in the same way that Linux is putting
pressure on MS.

Maybe sooner than you think:

http://news.com.com/2100-1012_3-1010522.html?tag=fd_top


Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Orr, Steve
  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: Orr, Steve
  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: lengthy URL's