date:20040117

Re: (Non)Unique Index Vs Unique Constraint

2004-01-17 Thread Arup Nanda

Jay,

Remember, both UK and PK are enforced by unique indexes. The important
difference between them is a null value is allowed in UK, not in PK.

In (1), your constraint is specifically named as ct_pk1. Oracle does the for
you:

a) create a unique index ct_pk for you in the default tablespace of the
user.
b) use this index to create the PK.
c) makes this column not null if not already.

In (2) you are doing the step (c) yourself, but you have a very important
flexibility - you have explicitly made the column not null. Oracle creates a
check constraint in the format SYS_Cn. It allows you to enable or
disable it at will. Then you have added the unique constraint yourself,
forcing Oracle to create a unique index. So, in effect you have created a
primary key constraint, but still hav some flexibility in enabling/disabling
the indivisual components.

In (3) you have done the same as in (2), except that you have named the
check constraint, which makes it easy in typing etc, as opposed to a name
like SYS_C; but most important in case of migrations to a different
database or rebuilds, where the name is preserved. The system generated
names like SYS_C could change.

All right, now that you can do it, the question is should you define a
primary key this way? The answer is an emphatic NO. A primary key is not
merely to enforce unqiueness and not-null values, but provides information
about the contents of the table - an extension fo the logical design. In the
US, every bank account holder has a Social Security Number (or a Taxpayer's
Identification Number). So you can define a UK + Not Null constraint
combination on the SSN column, which may be recommended, but the PK is the
account_no. Perhaps as of now, only one person is allowed to open only
account in the bank, so the SSN could potentilly be the PK, but that may not
hold true in the future. An account is uniquely identified by the
account_no, and that column should be defined as the PK; anyone else looking
at the table also gets the information.

Hope this helps.

Arup

- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Saturday, January 17, 2004 10:24 PM


> Thanks a lot for your quick response Mr.Jonathan and Mr.Dennis.
>
> One more silly question...
>
> What is the difference(pros & cons) between creating PK Vs (UK+NOT NULL)
Vs
> (UK+check constraint with Not null condition)?
>
> 1)
> drop table constraint_test;
> create table constraint_test(c1 number,c2 varchar2(10));
> alter table constraint_test add constraint ct_pk1 primary key(c1);
>
> 2)
> drop table constraint_test;
> create table constraint_test(c1 number,c2 varchar2(10));
> alter table constraint_test modify(c1 number not null);
> alter table constraint_test add constraint ct_uk1 unique(c1);
>
> 3)
> drop table constraint_test;
> create table constraint_test(c1 number,c2 varchar2(10));
> alter table constraint_test add constraint ct_ck1 check(c1 is not null);
> alter table constraint_test add constraint ct_uk1 unique(c1);
>
> Thanks,
> Jay
>
>
> -Original Message-
> Jonathan Lewis
> Sent: Saturday, January 17, 2004 6:54 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Depends what you want to achieve.
>
> A non-unique index enforcing a unique
> constraint allows the constraint to be
> deferrable - so you could load some
> 'nearly unique' data against it and find
> the duplicates efficiently.
>
> However, a non-unique index requires
> one byte per entry more than the equivalent
> unique index - and some people are very
> fussy about making indexes as small as
> possible.
>
>
> As far as the optimizer is concerned, the
> unique constraint guarantees uniqueness
> of data - which allows the 'single row'
> optimisation to be used, and also results
> in an equality on the index to be costed
> at the 'unique index' cost, rather than the
> 'non unique index' cost.   (But the cost thing
> changes again if the constraint is deferrable)
>
> Bottom line - if you know that you never
> need to play silly games with the constraint,
> then a unique index is more efficient, and
> helps the optimizer more than a non-unique
> index.
>
>
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
>   The educated person is not the person
>   who can answer the questions, but the
>   person who can question the answers -- T. Schick Jr
>
>
> Next public appearance2:
>  March 2004 Hotsos Symposium - Keynote
>  March 2004 Charlotte NC - OUG Tutorial
>  April 2004 Iceland
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> UK___February
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> - Original Message - 
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Saturday, January 17, 2004 3:14 AM
>
>
> > All,
> >
> > Please enlighten this Junior DBA.
> >
> > Which method is more

RE: (Non)Unique Index Vs Unique Constraint

2004-01-17 Thread Jay

Thanks a lot for your quick response Mr.Jonathan and Mr.Dennis.

One more silly question...

What is the difference(pros & cons) between creating PK Vs (UK+NOT NULL) Vs
(UK+check constraint with Not null condition)?

1)
drop table constraint_test;
create table constraint_test(c1 number,c2 varchar2(10));
alter table constraint_test add constraint ct_pk1 primary key(c1);

2)
drop table constraint_test;
create table constraint_test(c1 number,c2 varchar2(10));
alter table constraint_test modify(c1 number not null);
alter table constraint_test add constraint ct_uk1 unique(c1);

3)
drop table constraint_test;
create table constraint_test(c1 number,c2 varchar2(10));
alter table constraint_test add constraint ct_ck1 check(c1 is not null);
alter table constraint_test add constraint ct_uk1 unique(c1);

Thanks,
Jay


-Original Message-
Jonathan Lewis
Sent: Saturday, January 17, 2004 6:54 PM
To: Multiple recipients of list ORACLE-L



Depends what you want to achieve.

A non-unique index enforcing a unique
constraint allows the constraint to be 
deferrable - so you could load some 
'nearly unique' data against it and find
the duplicates efficiently.

However, a non-unique index requires
one byte per entry more than the equivalent
unique index - and some people are very 
fussy about making indexes as small as 
possible.


As far as the optimizer is concerned, the
unique constraint guarantees uniqueness
of data - which allows the 'single row'
optimisation to be used, and also results
in an equality on the index to be costed
at the 'unique index' cost, rather than the
'non unique index' cost.   (But the cost thing
changes again if the constraint is deferrable)

Bottom line - if you know that you never
need to play silly games with the constraint,
then a unique index is more efficient, and
helps the optimizer more than a non-unique
index.




Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person 
  who can answer the questions, but the 
  person who can question the answers -- T. Schick Jr


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___February


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Saturday, January 17, 2004 3:14 AM


> All,
> 
> Please enlighten this Junior DBA.
> 
> Which method is more efficient? When should I go for option (1)?
> 
> 1)NON-UNIQUE index Vs Unique Constraint
> drop table index_test;
> create table index_test(c1 number,c2 varchar2(20));
> create index i1 on index_test(c1);
> alter table index_test add constraint index_test_uk1 UNIQUE(c1);
> 
> 2)UNIQUE index Vs Unique Constraint
> drop table index_test;
> create table index_test(c1 number,c2 varchar2(20));
> create UNIQUE index i1 on index_test(c1);
> alter table index_test add constraint index_test_uk1 UNIQUE(c1);
> 
> Thanks in advance,
> Jay
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Jay
>   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: Jonathan Lewis
  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: Jay
  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 th

Re: (Non)Unique Index Vs Unique Constraint

2004-01-17 Thread Jonathan Lewis


Depends what you want to achieve.

A non-unique index enforcing a unique
constraint allows the constraint to be 
deferrable - so you could load some 
'nearly unique' data against it and find
the duplicates efficiently.

However, a non-unique index requires
one byte per entry more than the equivalent
unique index - and some people are very 
fussy about making indexes as small as 
possible.


As far as the optimizer is concerned, the
unique constraint guarantees uniqueness
of data - which allows the 'single row'
optimisation to be used, and also results
in an equality on the index to be costed
at the 'unique index' cost, rather than the
'non unique index' cost.   (But the cost thing
changes again if the constraint is deferrable)

Bottom line - if you know that you never
need to play silly games with the constraint,
then a unique index is more efficient, and
helps the optimizer more than a non-unique
index.




Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person 
  who can answer the questions, but the 
  person who can question the answers -- T. Schick Jr


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___February


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Saturday, January 17, 2004 3:14 AM


> All,
> 
> Please enlighten this Junior DBA.
> 
> Which method is more efficient? When should I go for option (1)?
> 
> 1)NON-UNIQUE index Vs Unique Constraint
> drop table index_test;
> create table index_test(c1 number,c2 varchar2(20));
> create index i1 on index_test(c1);
> alter table index_test add constraint index_test_uk1 UNIQUE(c1);
> 
> 2)UNIQUE index Vs Unique Constraint
> drop table index_test;
> create table index_test(c1 number,c2 varchar2(20));
> create UNIQUE index i1 on index_test(c1);
> alter table index_test add constraint index_test_uk1 UNIQUE(c1);
> 
> Thanks in advance,
> Jay
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Jay
>   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: Jonathan Lewis
  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: Rename tablespace in 9I

2004-01-17 Thread Kirtikumar Deshpande

No.

It is coming in 10g (from Oracle World presentations I attended last year).

- Kirti

--- [EMAIL PROTECTED] wrote:
> Is there anyway to rename tablespace in Oracle 9202. Thanks
> 
> 
> 

__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kirtikumar Deshpande
  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: (Non)Unique Index Vs Unique Constraint

2004-01-17 Thread DENNIS WILLIAMS

Jay
   That is a good one. The question is: "How is the uniqueness constraint
being enforced when the index is nonunique?" Offhand I would have assumed
your constraint would have been rejected since the index is nonunique --
nope. Then I would have guessed the index would have been converted to a
unique index -- it isn't. Then I was skeptical whether the constraint was
really being enforced, but it is. The next question is how the uniqueness is
being enforced if the index is not unique. 
   Now, if you add some duplicate values to the table, then try to add the
constraint, you receive:

alter table index_test add constraint index_test_uk1 UNIQUE(c1)
  *
ERROR at line 1:
ORA-02299: cannot validate (SILT.INDEX_TEST_UK1) - duplicate keys found 

My guess, and this is only a guess, is that the nonunique index with a
unique constraint executes the code for a unique index. I ran an insert on
each version and the plan looks the same. 
   Of course, if you go around doing funny stuff like this, the person that
eventually takes over your job duties will probably really bless your name
each time they stumble onto something like a unique constraint on a
nonumique index.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Friday, January 16, 2004 9:14 PM
To: Multiple recipients of list ORACLE-L


All,

Please enlighten this Junior DBA.

Which method is more efficient? When should I go for option (1)?

1)NON-UNIQUE index Vs Unique Constraint
drop table index_test;
create table index_test(c1 number,c2 varchar2(20));
create index i1 on index_test(c1);
alter table index_test add constraint index_test_uk1 UNIQUE(c1);

2)UNIQUE index Vs Unique Constraint
drop table index_test;
create table index_test(c1 number,c2 varchar2(20));
create UNIQUE index i1 on index_test(c1);
alter table index_test add constraint index_test_uk1 UNIQUE(c1);

Thanks in advance,
Jay
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jay
  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: any student versions of 'business objects' ab initio or infor

2004-01-17 Thread DENNIS WILLIAMS

Ryan - Some more thoughts for your consideration.
 
1. Consider studying Data Warehousing. These tools are deliberately made
easy to use, so shouldn't take much effort, but understanding how to design
a star schema will make data more accessible to any tool. A good start can
be had at http://www.ralphkimball.com   and
http://www.billinmon.com   
 
2. In spite of the versatility of these tools, sometimes they can't do
everything, particularly in the ETL phase. Consider learning a tool like
perl to fill the gap.
 
3. There are ways to learn about a tool without operating it. Find a group
like this dedicated to the tool. Try to locate user groups. See if you can
get a manual.
 
4. Once you've learned quite a bit about the tool, contact the local sales
rep for these tools and describe your desires. Use your winning charm. Let
them know you are sincere to have learned what you are able. They may have
some creative suggestions for you. Maybe let you sit in the back of a
training class, or slip you an old manual they are ready to toss. Offer
volunteer labor. It is in their best interest to have trained technical
people available



Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Saturday, January 17, 2004 10:44 AM
To: Multiple recipients of list ORACLE-L
informatica?


Something affordable. These are hot right now and I'd like to learn how to
use them. I cant find anything on the web. 

-- 
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: any student versions of 'business objects' ab initio or

2004-01-17 Thread Ryan

Title: Re: any student versions of 'business objects' ab initio or informatica?



does business objects = crystal reports? anyone can 
use crystal reports? 
 
why are employers picky about people with business 
objects knowledge?
 
https://secure.businessobjects.com/default.asp

  - Original Message - 
  From: 
  Tim Gorman 
  
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Saturday, January 17, 2004 1:04 
  PM
  Subject: Re: any student versions of 
  'business objects' ab initio or
  Probably best to 
  contact them directly?I suspect that Ab Initio in particular will be 
  out of reach for this purpose.  My understanding is that they choose 
  their customers based on their capability to deliver a data warehouse 
  succesfully.  Just a rumor, but it makes a certain amount of sense if you 
  want to streamline your post-sales support costs.  I understand that they 
  are more selective of potential customers than the prospective customers are 
  of ETL tools, actually deciding not to sell to certain prospects.Talk 
  about business intelligence!If this is true, then I would suspect that 
  student discounts are not an option.  Of course, it might just be a 
  rumor...on 1/17/04 9:44 AM, Ryan at [EMAIL PROTECTED] 
  wrote:
  Something 
affordable. These are hot right now and I'd like to learn how to use them. I 
cant find anything on the web.

Re: any student versions of 'business objects' ab initio or

2004-01-17 Thread Tim Gorman

Title: Re: any student versions of 'business objects' ab initio or informatica?



Probably best to contact them directly?

I suspect that Ab Initio in particular will be out of reach for this purpose.  My understanding is that they choose their customers based on their capability to deliver a data warehouse succesfully.  Just a rumor, but it makes a certain amount of sense if you want to streamline your post-sales support costs.  I understand that they are more selective of potential customers than the prospective customers are of ETL tools, actually deciding not to sell to certain prospects.

Talk about business intelligence!

If this is true, then I would suspect that student discounts are not an option.  Of course, it might just be a rumor...



on 1/17/04 9:44 AM, Ryan at [EMAIL PROTECTED] wrote:

Something affordable. These are hot right now and I'd like to learn how to use them. I cant find anything on the web.

any student versions of 'business objects' ab initio or informatica?

2004-01-17 Thread Ryan




Something affordable. These are hot right now and 
I'd like to learn how to use them. I cant find anything on the web.

Re: Strange Query Result,... urgent please

2004-01-17 Thread Tanel Poder

Hi!

Use SET TRANSACTION READ ONLY before your run the queries and run them again
(without any commits or rollbacks in the middle). That way you can be sure
that any hidden changes to data won't be reflected.

Tanel.

- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Saturday, January 17, 2004 3:54 PM


>
>
> Dear All,
>
> I need help on the following query, the following has been simplified to
> show you the exact problem. Due to this error, the sql query is giving
> unstable result which affects on production reporting in my company. I
> wonder what's the real problem?
>
> SQL> SELECT COUNT(1) FROM
>   2  (SELECT SO,SUM(QTY_SOLD_SO) AS QTY_SOLD
>   3   FROM IVC_DET IVC
>   4   GROUP BY SO
>   5  );
>
>   COUNT(1)
> --
>  44087
>
> SQL> l
>   1  SELECT COUNT(1) FROM
>   2  (SELECT SO,SUM(QTY_SOLD_SO) AS QTY_SOLD
>   3   FROM IVC_DET IVC
>   4   GROUP BY SO
>   5* )
> SQL> /
>
>   COUNT(1)
> --
>  44085
>
> >From the above query nobody has changed any record in my production
> database but the result is unstable. After I remove group function it
> becomes stable as below.
>
> SQL> SELECT COUNT(1) FROM
>   2  (SELECT SO
>   3   FROM IVC_DET IVC
>   4   GROUP BY SO
>   5  );
>
>   COUNT(1)
> --
>  50770
>
> SQL> /
>
>   COUNT(1)
> --
>  50770
>
> Now my opinion is the sort process has been ruined. I checked alert.log
> but I found no entries regarding this problem. So I check the sort ratio
> through performance manager and it shows almost 100% hit ratio. Well,
> just in case, next I checked on my Temporary tablespace and it still has
> a lot of room to do sorts. Anyone has experience this before? Please
> give me some light, ideas or suggestion to correct this, since I have a
> lot of very complex queries. I feel like a bomb is ticking for other
> complex queries. Thank you all in advance.
>
> Regards,
>
> Wendry.
>
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Wendry
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>


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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Strange Query Result,... urgent please

2004-01-17 Thread Wendry



Dear All,

I need help on the following query, the following has been simplified to
show you the exact problem. Due to this error, the sql query is giving
unstable result which affects on production reporting in my company. I
wonder what's the real problem?

SQL> SELECT COUNT(1) FROM
  2  (SELECT SO,SUM(QTY_SOLD_SO) AS QTY_SOLD
  3   FROM IVC_DET IVC
  4   GROUP BY SO
  5  );

  COUNT(1)
--
 44087

SQL> l
  1  SELECT COUNT(1) FROM
  2  (SELECT SO,SUM(QTY_SOLD_SO) AS QTY_SOLD
  3   FROM IVC_DET IVC
  4   GROUP BY SO
  5* )
SQL> /

  COUNT(1)
--
 44085

>From the above query nobody has changed any record in my production
database but the result is unstable. After I remove group function it
becomes stable as below.

SQL> SELECT COUNT(1) FROM
  2  (SELECT SO
  3   FROM IVC_DET IVC
  4   GROUP BY SO
  5  );

  COUNT(1)
--
 50770

SQL> /

  COUNT(1)
--
 50770

Now my opinion is the sort process has been ruined. I checked alert.log
but I found no entries regarding this problem. So I check the sort ratio
through performance manager and it shows almost 100% hit ratio. Well,
just in case, next I checked on my Temporary tablespace and it still has
a lot of room to do sorts. Anyone has experience this before? Please
give me some light, ideas or suggestion to correct this, since I have a
lot of very complex queries. I feel like a bomb is ticking for other
complex queries. Thank you all in advance.

Regards,

Wendry.

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

free performance tuning books @ Veritas

2004-01-17 Thread Tanel Poder

Hi!

Oracle Performance Tuning 101 eBook (by Gaja, Kirti and John Kostelac) is
available to download in PDF from http://www.veritas.com/offer?a_id=3805

Btw, veritas has a free SQL Server tuning book on their site as well.

Tanel.


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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Oracle Warehouse Builder 9.2.0.2.8 and Workflow 2.6.2 on linux

2004-01-17 Thread Ir Forex

Hi,

I found Workflow 2.6.2 on the 9.2.0.1 Database cdrom set.

You get to it by picking 
Enterprise Edition and then custom.

-moi



__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ir Forex
  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: (Non)Unique Index Vs Unique Constraint

RE: (Non)Unique Index Vs Unique Constraint

Re: (Non)Unique Index Vs Unique Constraint

Re: Rename tablespace in 9I

RE: (Non)Unique Index Vs Unique Constraint

RE: any student versions of 'business objects' ab initio or infor

Re: any student versions of 'business objects' ab initio or

Re: any student versions of 'business objects' ab initio or

any student versions of 'business objects' ab initio or informatica?

Re: Strange Query Result,... urgent please

Strange Query Result,... urgent please

free performance tuning books @ Veritas

Re: Oracle Warehouse Builder 9.2.0.2.8 and Workflow 2.6.2 on linux

13 matches

Site Navigation

Mail list logo

Footer information