RE: SQL Query Problem(possilble duplicate send, Sorry!)

[Rohan Karanjawala]

hi,
There is a function RATIO_BY_PERCENT or something very similar to this in 
SQL
just find it out
this gives u individual contributions as compared to the whole thing.

Regds,

Rohan
From: Viktor <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: SQL Query Problem(possilble duplicate send, Sorry!)
Date: Tue, 13 Jan 2004 12:34:35 -0800
Hello all,

I am working with a query that does some counts. I've hit a brick wall and 
can't get passed trying to figure out how I can make the query show 
percentages %.

Here is the query:

select "COUNTRY",
count ("MSS") "COUNT_MSS"
FROM (
 SELECT DISTINCT
 ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,
 m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ 
"NAME_ID",
 NVL(a.country,'USA') "COUNTRY"
 FROM mscript ms, reviewms m, address a, journal j
 WHERE (m.first_return between '01/01/2003' and '12/31/2003'
  or m.second_return between '01/01/2003 and '12/31/2003'
  or m.second_return between '01/01/2003 and '12/31/2003')
  and ms.journal_id = j.journal_id
  and M.MSNUMBER_JCODE  = ms.journal_id
  and M.MSNUMBER_YRISSUE = ms.yr_of_issue
  and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no
  and M.MSNUMBER_CKCHAR = ms.check_char
  and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+)
  and M.RVIEWNUM_INIT_SITE  = a.namekey_init_site(+)
  and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+)
  and a.addtype_addrstyp in ('m', 'p')
  and a.addtype_typeno = 1
  and (a.addr_end_date is null
or a.addr_end_date > sysdate)
  and (first_recommend not in 
('1','2','3','4','5','6','7','8','9','F','P','N','S','u')
or second_recommend not in 
('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))
 order by 1)
GROUP BY ROLLUP(COUNTRY)

And the output:

COUNTRYCOUNT_MSS

AUSTRALIA   1
AUSTRIA   2
BELGIUM  4
CANADA  20
CHILE   1
CHINA  3
CZECH REPUBLIC1
DENMARK   4
ENGLAND   10
   46
Is there a way I can also display a percentage column, that is the 
percentage of the total in the same SQL statement?

As always thanks so much!

Viktor



-
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
_
Contact brides & grooms FREE! http://www.shaadi.com/ptnr.php?ptnr=hmltag 
Only on www.shaadi.com. Register now!

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rohan Karanjawala
 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: SQL Query Problem(possilble duplicate send, Sorry!)

[Bricklen Anderson]

Viktor wrote:

Thanks for your reply! Will try it now. Will this work in 8i?
 
Viktor
 

If it works at all, then it should work in both 8i and 9i, although I 
don't have a version of 8i handy right now to try this on.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bricklen Anderson
 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: SQL Query Problem(possilble duplicate send, Sorry!)

[Viktor]

Thanks for your reply! Will try it now. Will this work in 8i?
 
Viktor
 
Bricklen Anderson <[EMAIL PROTECTED]> wrote:
Viktor wrote:> Hello all,> > I am working with a query that does some counts. I've hit a brick wall > and can't get passed trying to figure out how I can make the query show > percentages %.> > Here is the query:> > select "COUNTRY",> count ("MSS") "COUNT_MSS"> FROM (> SELECT DISTINCT> ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,> m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ > "NAME_ID",> NVL(a.country,'USA') "COUNTRY"> FROM mscript ms, reviewms m, address a, journal j> WHERE (m.first_return between '01/01/2003' and '12/31/2003'> or m.second_return between '01/01/2003 and '12/31/2003'> or m.second_return between '01/01/2003 and '12/31/2003')> and ms.journal_id = j.journal_id> !
and
 M.MSNUMBER_JCO! DE = ms.journal_id> and M.MSNUMBER_YRISSUE = ms.yr_of_issue> and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no> and M.MSNUMBER_CKCHAR = ms.check_char> and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+)> and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+)> and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+)> and a.addtype_addrstyp in ('m', 'p')> and a.addtype_typeno = 1> and (a.addr_end_date is null> or a.addr_end_date > sysdate)> and (first_recommend not in > ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')> or second_recommend not in > ('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))> order by 1)> GROUP BY ROLLUP(COUNTRY)> > And the output:> > COUNTRY COUNT_MSS> > AUSTRALIA 1> AUSTRIA 2> BELGIUM 4> CANADA 20> CHILE 1> CHINA 3> CZECH REPUBLIC 1> !
DENMARK
 4> ENGLAND 10> 46> > Is there a way I can also display a percentage column, that is the > percentage of the total in the same SQL statement?> > As always thanks so much!> > Viktor> Will this work? (ran a simple test case that worked, although this may not):select country,count_mss,round((count_mss/decode(overall_total,0,1,overall_total))*100)||'%' pctfrom (select "COUNTRY",count ("MSS") over (partition by "MSS") "COUNT_MSS",count("MSS") over () overall_totalFROM (SELECT DISTINCTms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ "NAME_ID",NVL(a.country,'USA') "COUNTRY"FROM mscript ms, reviewms m, address a, journal jWHERE (m.first_return between '01/01/2003' and '12/31/2003'or m.second_return between '01/01/2003 and '12/31/2003'or m.second_return!
 between
 '01/01/2003 and '12/31/2003')and ms.journal_id = j.journal_idand M.MSNUMBER_JCO! DE = ms.journal_idand M.MSNUMBER_YRISSUE = ms.yr_of_issueand M.MSNUMBER_MS_SEQNO = ms.ms_sequence_noand M.MSNUMBER_CKCHAR = ms.check_charand m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+)and M.RVIEWNUM_INIT_SITE = a.namekey_init_site(+)and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+)and a.addtype_addrstyp in ('m', 'p')and a.addtype_typeno = 1and (a.addr_end_date is nullor a.addr_end_date > sysdate)and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')or second_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))order by 1)GROUP BY ROLLUP(COUNTRY))(not sure how it will react to a 'group by rollup..' though.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Bricklen AndersonINET: [EMAIL PROTECTED]Fat C!
ity
 Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes

Re: SQL Query Problem(possilble duplicate send, Sorry!)

[Bricklen Anderson]

Viktor wrote:

Hello all,
 
I am working with a query that does some counts. I've hit a brick wall 
and can't get passed trying to figure out how I can make the query show 
percentages %.
 
Here is the query:
 
select "COUNTRY",
count ("MSS") "COUNT_MSS"
FROM (
 SELECT DISTINCT
 ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,
 m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ 
"NAME_ID",
 NVL(a.country,'USA') "COUNTRY"
 FROM mscript ms, reviewms m, address a, journal j
 WHERE (m.first_return between '01/01/2003' and '12/31/2003'
  or m.second_return between '01/01/2003 and '12/31/2003'
  or m.second_return between '01/01/2003 and '12/31/2003')
  and ms.journal_id = j.journal_id
  and M.MSNUMBER_JCO! DE  = ms.journal_id
  and M.MSNUMBER_YRISSUE = ms.yr_of_issue
  and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no
  and M.MSNUMBER_CKCHAR = ms.check_char
  and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+)
  and M.RVIEWNUM_INIT_SITE  = a.namekey_init_site(+)
  and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+)
  and a.addtype_addrstyp in ('m', 'p')
  and a.addtype_typeno = 1
  and (a.addr_end_date is null
or a.addr_end_date > sysdate)
  and (first_recommend not in 
('1','2','3','4','5','6','7','8','9','F','P','N','S','u')
or second_recommend not in 
('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))
 order by 1)
GROUP BY ROLLUP(COUNTRY)
 
And the output:
 
COUNTRYCOUNT_MSS
 
AUSTRALIA   1
AUSTRIA   2
BELGIUM  4
CANADA  20
CHILE   1
CHINA  3
CZECH REPUBLIC1
DENMARK   4
ENGLAND   10
   46
 
Is there a way I can also display a percentage column, that is the 
percentage of the total in the same SQL statement?
 
As always thanks so much!
 
Viktor
 
Will this work? (ran a simple test case that worked, although this may not):

select 
country,count_mss,round((count_mss/decode(overall_total,0,1,overall_total))*100)||'%' 
pct
from (
select "COUNTRY",
count ("MSS") over (partition by "MSS") "COUNT_MSS",
count("MSS") over () overall_total
FROM (
 SELECT DISTINCT
 ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS,
 m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ 
"NAME_ID",
 NVL(a.country,'USA') "COUNTRY"
 FROM mscript ms, reviewms m, address a, journal j
 WHERE (m.first_return between '01/01/2003' and '12/31/2003'
  or m.second_return between '01/01/2003 and '12/31/2003'
  or m.second_return between '01/01/2003 and '12/31/2003')
  and ms.journal_id = j.journal_id
  and M.MSNUMBER_JCO! DE  = ms.journal_id
  and M.MSNUMBER_YRISSUE = ms.yr_of_issue
  and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no
  and M.MSNUMBER_CKCHAR = ms.check_char
  and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+)
  and M.RVIEWNUM_INIT_SITE  = a.namekey_init_site(+)
  and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+)
  and a.addtype_addrstyp in ('m', 'p')
  and a.addtype_typeno = 1
  and (a.addr_end_date is null
or a.addr_end_date > sysdate)
  and (first_recommend not in 
('1','2','3','4','5','6','7','8','9','F','P','N','S','u')
or second_recommend not in 
('1','2','3','4','5','6','7','8','9','F','P','N','S','u'))
 order by 1)
GROUP BY ROLLUP(COUNTRY))

(not sure how it will react to a 'group by rollup..' though.

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

SQL Query Problem(possilble duplicate send, Sorry!)

[Viktor]

Hello all, I am working with a query that does some counts. I've hit a brick wall and can't get passed trying to figure out how I can make the query show percentages %. Here is the query: select "COUNTRY",    count ("MSS") "COUNT_MSS"FROM ( SELECT DISTINCT ms.journal_id||ms.yr_of_issue||ms.ms_sequence_no||ms.check_char MSS, m.RVIEWNUM_INIT_INITSET||M.RVIEWNUM_INIT_SITE||M.RVIEWNUM_INITSEQ "NAME_ID", NVL(a.country,'USA') "COUNTRY" FROM mscript ms, reviewms m, address a, journal j WHERE (m.first_return between '01/01/2003' and '12/31/2003'  or m.second_return between '01/01/2003 and '12/31/2003'  or m.second_return between '01/01/2003 and '12/31/2003')  and ms.journal_id = j.journal_id  and M.MSNUMBER_JCO!
DE 
 = ms.journal_id  and M.MSNUMBER_YRISSUE = ms.yr_of_issue  and M.MSNUMBER_MS_SEQNO = ms.ms_sequence_no  and M.MSNUMBER_CKCHAR = ms.check_char  and m.RVIEWNUM_INIT_INITSET = a.namekey_init_initset(+)  and M.RVIEWNUM_INIT_SITE  = a.namekey_init_site(+)  and M.RVIEWNUM_INITSEQ = a.namekey_initseq(+)  and a.addtype_addrstyp in ('m', 'p')  and a.addtype_typeno = 1  and (a.addr_end_date is null    or a.addr_end_date > sysdate)  and (first_recommend not in ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')    or second_recommend not in
 ('1','2','3','4','5','6','7','8','9','F','P','N','S','u')) order by 1)GROUP BY ROLLUP(COUNTRY) And the output: COUNTRY    COUNT_MSS AUSTRALIA   1 AUSTRIA   2
 BELGIUM  4 CANADA  20 CHILE   1 CHINA      3
 CZECH REPUBLIC    1 DENMARK   4 ENGLAND   10 
   46 Is there a way I can also display a percentage column, that is the percentage of the total in the same SQL statement? As always thanks so much! Viktor 
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes

Re: SQL Query

[Binley Lim]

Oh, right, #2 is refering to point-and-click GUI 
interfaces that some DBAs depend on that they no longer know how to write SQLs 
to navigate the data dictionary.
 
- Original Message - 

  From: 
  [EMAIL PROTECTED] 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Saturday, November 15, 2003 1:19 
  PM
  Subject: Re: SQL Query
  Sorry, don't understand the 
  DBA part ( #2 ). 
  


  
  "Binley Lim" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED] 
 11/14/2003 02:09 PM 
 Please respond to ORACLE-L 
                  To:     
   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 
        cc:   
        
      Subject:        Re: SQL 
QueryWould 
  have thought:   
  1. A developer would have known this - a SQL 
  many-to-many join   
  2. A DBA would have known this - how else would 
  you know what's happening with your tablespaces? (Clickety-pointy answers not 
  allowed)   
  - Original Message - 
  From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Saturday, November 15, 2003 9:54 AM Subject: Re: SQL Query 
  You can't join DBA_EXTENTS and 
  DBA_DATA_FILES based on an 
  equality of tablespace_name, and then 
  add up the bytes of the files 
  for the tablespace. ie. 
    select 
    b.tablespace_name,   
  b.bytes from dba_extents a, dba_data_files b where 
  a.tablespace_name=b.tablespace_name 
  Try running that query, and it may 
  become clear. Your first query correctly aggregates the file 
  sizes. The second query determines tablespace size 
  based on the number of extents allocated to it. Drop all the objects in the tablespace, and your 
  tablespace will no longer appear to have any 
  space. HTH 
  Jared 
  


  
  "Bellow, Bambi" 
<[EMAIL PROTECTED]> Sent by: 
[EMAIL PROTECTED] 
 11/14/2003 09:44 AM Please respond to 
ORACLE-L 
          
       To: 
       Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]> 
       cc: 
        
      
 Subject:        SQL 
  QueryFriends 
  --Why would these two queries return different results?This 
  query works.SQL> l1 
   selecta.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,2)*100 
  pct2  from (select tablespace_name,sum(bytes)/(1024*1024) 
  megs_used3  from dba_extents group by tablespace_name) a,4 
   (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated5 
   from dba_data_files  group by tablespace_name) b6  where 
  a.tablespace_name=b.tablespace_name7* and 
  a.tablespace_name='NAUAT'SQL> /TABLESPACE_NAME     
             MEGS_ALLOCATED  MEGS_USED   
       PCT-- -- 
  -- --NAUAT             
                     22924.25 
       11509         50This query 
  does not work1  select a.tablespace_name,sum(b.bytes)/(1024*1024) 
  megs_allocated,2  sum(a.bytes)/(1024*1024) megs_used,3 
   round(sum(a.bytes)/sum(b.bytes),4)*100 pct4  from dba_extents 
  a, dba_data_files b5  where a.tablespace_name=b.tablespace_name6 
   and a.tablespace_name='NAUAT'7* group by 
  a.tablespace_name,b.tablespace_nameSQL> /TABLESPACE_NAME   
               MEGS_ALLOCATED  MEGS_USED 
         PCT-- 
  -- -- --NAUAT         
                      
   31773010.5      23018       
   .07Bambi.-- Please see the official ORACLE-L FAQ: 
  http://www.orafaq.net-- Author: Bellow, BambiINET: 
  [EMAIL PROTECTED]Fat City Network Services    -- 
  858-538-5051 http://www.fatcity.comSan Diego, California     
     -- Mailing list and web hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from).  You mayalso send the HELP command 
  for other information (like subscribing).

Re: SQL Query

[Jared . Still]

Sorry, don't understand the DBA part ( #2 ).







"Binley Lim" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 11/14/2003 02:09 PM
 Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Re: SQL Query


Would have thought:
 
1. A developer would have known this - a SQL many-to-many join
 
2. A DBA would have known this - how else would you know what's happening with your tablespaces? (Clickety-pointy answers not allowed)
 
- Original Message - 
From: [EMAIL PROTECTED] 
To: Multiple recipients of list ORACLE-L 
Sent: Saturday, November 15, 2003 9:54 AM
Subject: Re: SQL Query


You can't join DBA_EXTENTS and DBA_DATA_FILES based on an 
equality of tablespace_name, and then add up the bytes of the files 
for the tablespace. 

ie.   

select 
   b.tablespace_name, 
   b.bytes 
from dba_extents a, dba_data_files b 
where a.tablespace_name=b.tablespace_name 

Try running that query, and it may become clear. 

Your first query correctly aggregates the file sizes. 

The second query determines tablespace size based 
on the number of extents allocated to it. 

Drop all the objects in the tablespace, and your tablespace 
will no longer appear to have any space. 

HTH 

Jared 








"Bellow, Bambi" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED] 
 11/14/2003 09:44 AM 
 Please respond to ORACLE-L 
        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 
        cc:         
        Subject:        SQL Query



Friends --

Why would these two queries return different results?

This query works.

SQL> l
 1  select
a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,
2)*100 pct
 2  from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
 3  from dba_extents group by tablespace_name) a,
 4  (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
 5  from dba_data_files  group by tablespace_name) b
 6  where a.tablespace_name=b.tablespace_name
 7* and a.tablespace_name='NAUAT'
SQL> /

TABLESPACE_NAME                MEGS_ALLOCATED  MEGS_USED        PCT
-- -- -- --
NAUAT                                22924.25      11509         50


This query does not work

 1  select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,
 2  sum(a.bytes)/(1024*1024) megs_used,
 3  round(sum(a.bytes)/sum(b.bytes),4)*100 pct
 4  from dba_extents a, dba_data_files b
 5  where a.tablespace_name=b.tablespace_name
 6  and a.tablespace_name='NAUAT'
 7* group by a.tablespace_name,b.tablespace_name
SQL> /

TABLESPACE_NAME                MEGS_ALLOCATED  MEGS_USED        PCT
-- -- -- --
NAUAT                              31773010.5      23018        .07


Bambi.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bellow, Bambi
 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: SQL Query

[Jared . Still]

Sorry, no, group by has always worked that way.

Thanks, I'll try to have a great weekend.  :)








"Bellow, Bambi" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 11/14/2003 01:54 PM
 Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: SQL Query


Jared --

That is expected behavior for this query.  What was weird is that I expected the aggregation in the group by to apply to multiple fileids making GROUP BY a.tablespace_name, b.tablespace_name to be able, then, to join a.tablespace_name to b.tablespace_name as a 1-1 join rather than a many-many.  I could have sworn that has worked in the past, but, regardless, my other query returns what I need and the mystery is solved so I don't really need to pursue this with Oracle as a bug... and who really wants to start a bug report on a Friday afternoon for something wimpy?
 
Hope you have a great weekend!
Bambi.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, November 14, 2003 2:54 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: SQL Query


You can't join DBA_EXTENTS and DBA_DATA_FILES based on an 
equality of tablespace_name, and then add up the bytes of the files 
for the tablespace. 

ie.   

select 
   b.tablespace_name, 
   b.bytes 
from dba_extents a, dba_data_files b 
where a.tablespace_name=b.tablespace_name 

Try running that query, and it may become clear. 

Your first query correctly aggregates the file sizes. 

The second query determines tablespace size based 
on the number of extents allocated to it. 

Drop all the objects in the tablespace, and your tablespace 
will no longer appear to have any space. 

HTH 

Jared 








"Bellow, Bambi" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED] 
 11/14/2003 09:44 AM 
 Please respond to ORACLE-L 
        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 
        cc:         
        Subject:        SQL Query



Friends --

Why would these two queries return different results?

This query works.

SQL> l
 1  select
a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,
2)*100 pct
 2  from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
 3  from dba_extents group by tablespace_name) a,
 4  (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
 5  from dba_data_files  group by tablespace_name) b
 6  where a.tablespace_name=b.tablespace_name
 7* and a.tablespace_name='NAUAT'
SQL> /

TABLESPACE_NAME                MEGS_ALLOCATED  MEGS_USED        PCT
-- -- -- --
NAUAT                                22924.25      11509         50


This query does not work

 1  select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,
 2  sum(a.bytes)/(1024*1024) megs_used,
 3  round(sum(a.bytes)/sum(b.bytes),4)*100 pct
 4  from dba_extents a, dba_data_files b
 5  where a.tablespace_name=b.tablespace_name
 6  and a.tablespace_name='NAUAT'
 7* group by a.tablespace_name,b.tablespace_name
SQL> /

TABLESPACE_NAME                MEGS_ALLOCATED  MEGS_USED        PCT
-- -- -- --
NAUAT                              31773010.5      23018        .07


Bambi.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bellow, Bambi
 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: SQL Query

[Binley Lim]

Would have thought:
 
1. A developer would have known this - a SQL 
many-to-many join
 
2. A DBA would have known this - how else would you 
know what's happening with your tablespaces? (Clickety-pointy answers not 
allowed)
 

  - Original Message - 
  From: 
  [EMAIL PROTECTED] 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Saturday, November 15, 2003 9:54 
  AM
  Subject: Re: SQL Query
  You can't join DBA_EXTENTS and 
  DBA_DATA_FILES based on an equality of 
  tablespace_name, and then add up the bytes of the files for the tablespace. ie.   select   
   b.tablespace_name,   
   b.bytes from dba_extents a, 
  dba_data_files b where 
  a.tablespace_name=b.tablespace_name Try running that query, and it may become clear. Your first query correctly aggregates the file sizes. 
  The second query determines 
  tablespace size based on the number of 
  extents allocated to it. Drop all 
  the objects in the tablespace, and your tablespace will no longer appear to have any space. 
  HTH Jared 
  


  
  "Bellow, Bambi" 
<[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 
 11/14/2003 09:44 AM 
 Please respond to ORACLE-L 
                  To:     
   Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>         cc:       
        
  Subject:        SQL 
  QueryFriends --Why would these two queries return different 
  results?This query works.SQL> l 1 
   selecta.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,2)*100 
  pct 2  from (select tablespace_name,sum(bytes)/(1024*1024) 
  megs_used 3  from dba_extents group by tablespace_name) 
  a, 4  (select tablespace_name,sum(bytes)/(1024*1024) 
  megs_allocated 5  from dba_data_files  group by 
  tablespace_name) b 6  where 
  a.tablespace_name=b.tablespace_name 7* and 
  a.tablespace_name='NAUAT'SQL> /TABLESPACE_NAME     
             MEGS_ALLOCATED  MEGS_USED   
       PCT-- -- 
  -- --NAUAT             
                     22924.25 
       11509         50This query 
  does not work 1  select 
  a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 
   sum(a.bytes)/(1024*1024) megs_used, 3 
   round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4  from 
  dba_extents a, dba_data_files b 5  where 
  a.tablespace_name=b.tablespace_name 6  and 
  a.tablespace_name='NAUAT' 7* group by 
  a.tablespace_name,b.tablespace_nameSQL> /TABLESPACE_NAME   
               MEGS_ALLOCATED  MEGS_USED 
         PCT-- 
  -- -- --NAUAT         
                      
   31773010.5      23018       
   .07Bambi.-- Please see the official ORACLE-L FAQ: 
  http://www.orafaq.net-- Author: Bellow, Bambi INET: 
  [EMAIL PROTECTED]Fat City Network Services    -- 
  858-538-5051 http://www.fatcity.comSan Diego, California     
     -- Mailing list and web hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from).  You mayalso send the HELP command 
  for other information (like 
subscribing).

RE: SQL Query

[Bellow, Bambi]

Jared 
--
That is expected behavior for 
this query.  What was weird is that I expected the aggregation in the group 
by to apply to multiple fileids making GROUP BY a.tablespace_name, 
b.tablespace_name to be able, then, to join a.tablespace_name to 
b.tablespace_name as a 1-1 join rather than a many-many.  I could have sworn that has 
worked in the past, but, regardless, my other query returns what I need and the 
mystery is solved so I don't really need to pursue this with Oracle as a bug... 
and who really wants to start a bug report 
on a Friday afternoon for something wimpy?
 
Hope you have a great weekend!
Bambi.

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]Sent: Friday, November 14, 2003 
  2:54 PMTo: Multiple recipients of list ORACLE-LSubject: 
  Re: SQL QueryYou can't 
  join DBA_EXTENTS and DBA_DATA_FILES based on an equality of tablespace_name, and then add up the bytes 
  of the files for the 
  tablespace. ie.   
  select    b.tablespace_name,    b.bytes from 
  dba_extents a, dba_data_files b where 
  a.tablespace_name=b.tablespace_name Try running that query, and it may become clear. Your first query correctly aggregates the file sizes. 
  The second query determines 
  tablespace size based on the number of 
  extents allocated to it. Drop all 
  the objects in the tablespace, and your tablespace will no longer appear to have any space. 
  HTH Jared 
  


  
  "Bellow, Bambi" 
<[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 
 11/14/2003 09:44 AM 
 Please respond to ORACLE-L 
                  To:     
   Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>         cc:       
        
  Subject:        SQL 
  QueryFriends --Why would these two queries return different 
  results?This query works.SQL> l 1 
   selecta.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,2)*100 
  pct 2  from (select tablespace_name,sum(bytes)/(1024*1024) 
  megs_used 3  from dba_extents group by tablespace_name) 
  a, 4  (select tablespace_name,sum(bytes)/(1024*1024) 
  megs_allocated 5  from dba_data_files  group by 
  tablespace_name) b 6  where 
  a.tablespace_name=b.tablespace_name 7* and 
  a.tablespace_name='NAUAT'SQL> /TABLESPACE_NAME     
             MEGS_ALLOCATED  MEGS_USED   
       PCT-- -- 
  -- --NAUAT             
                     22924.25 
       11509         50This query 
  does not work 1  select 
  a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated, 2 
   sum(a.bytes)/(1024*1024) megs_used, 3 
   round(sum(a.bytes)/sum(b.bytes),4)*100 pct 4  from 
  dba_extents a, dba_data_files b 5  where 
  a.tablespace_name=b.tablespace_name 6  and 
  a.tablespace_name='NAUAT' 7* group by 
  a.tablespace_name,b.tablespace_nameSQL> /TABLESPACE_NAME   
               MEGS_ALLOCATED  MEGS_USED 
         PCT-- 
  -- -- --NAUAT         
                      
   31773010.5      23018       
   .07Bambi.-- Please see the official ORACLE-L FAQ: 
  http://www.orafaq.net-- Author: Bellow, Bambi INET: 
  [EMAIL PROTECTED]Fat City Network Services    -- 
  858-538-5051 http://www.fatcity.comSan Diego, California     
     -- Mailing list and web hosting 
  services-To 
  REMOVE yourself from this mailing list, send an E-Mail messageto: 
  [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
  BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing 
  list you want to be removed from).  You mayalso send the HELP command 
  for other information (like 
subscribing).

RE: SQL Query

[Bellow, Bambi]

Ah, I see your point, now, and that's quite correct.  Which means I can
either aggregate on fileid, or aggregate by table and join.  Nice catch, and
mystery solved.  No bug report.

Take care and have a great weekend!
Bambi.
-Original Message-
Sent: Friday, November 14, 2003 2:50 PM
To: Multiple recipients of list ORACLE-L


The aggregate function operates last, on a result set. Why I suggested
to suppress the GROUP BY is that then you would have seen that the
number of bytes from each datafile is returnedtoo many times.
If you have F1 and F2 associated to your database, with E1 and E2 in F1
and E3 in F2 (I hope the meaning of my symbols is obvious) by joining on
the tablespace name you get
 F1 E1
 F1 E2
 F1 E3
 F2 E1
 F2 E2
 F2 E3

Quite obviously, when you sum up the result is much too big. For files
in that case its 3 times too big for each, and for extents two times too
big for each.

By contrast, the inline views in the query which works force the
aggregates to be computed _before_ the final calculation.

SF


"Bellow, Bambi" wrote:
> 
> But Stephane, I am aggregating by tablespace for both extents and for
> data_files.  There is nothing here that is separating out anything by
> datafile.  And, if I take away the GROUP BY, I lose the ability to
aggregate
> at all, which is the point of this...
> 
> -Original Message-
> Sent: Friday, November 14, 2003 1:09 PM
> To: Multiple recipients of list ORACLE-L
> 
> Bambi,
> 
> Your second query is wrong because all extents in a tablespace don't
> necessarily belong to the same datafile. Try the query without the
> aggregate functions and the GROUP BY, and you'll understand your
> mistake.
> 
> HTH,
> 
> SF
> 
> "Bellow, Bambi" wrote:
> >
> > Friends --
> >
> > Why would these two queries return different results?
> >
> > This query works.
> >
> > SQL> l
> >   1  select
> >
>
a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,
> > 2)*100 pct
> >   2  from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
> >   3  from dba_extents group by tablespace_name) a,
> >   4  (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
> >   5  from dba_data_files  group by tablespace_name) b
> >   6  where a.tablespace_name=b.tablespace_name
> >   7* and a.tablespace_name='NAUAT'
> > SQL> /
> >
> > TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
> > -- -- -- --
> > NAUAT22924.25  11509 50
> >
> > This query does not work
> >
> >   1  select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,
> >   2  sum(a.bytes)/(1024*1024) megs_used,
> >   3  round(sum(a.bytes)/sum(b.bytes),4)*100 pct
> >   4  from dba_extents a, dba_data_files b
> >   5  where a.tablespace_name=b.tablespace_name
> >   6  and a.tablespace_name='NAUAT'
> >   7* group by a.tablespace_name,b.tablespace_name
> > SQL> /
> >
> > TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
> > -- -- -- --
> > NAUAT  31773010.5  23018.07
> >
> > Bambi.
> > --
> --
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  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: Bellow, Bambi
  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: SQL Query

[Jared . Still]

You can't join DBA_EXTENTS and DBA_DATA_FILES based on an
equality of tablespace_name, and then add up the bytes of the files
for the tablespace.

ie.  

select
   b.tablespace_name,
   b.bytes
from dba_extents a, dba_data_files b
where a.tablespace_name=b.tablespace_name

Try running that query, and it may become clear.

Your first query correctly aggregates the file sizes. 

The second query determines tablespace size based
on the number of extents allocated to it.

Drop all the objects in the tablespace, and your tablespace
will no longer appear to have any space.

HTH

Jared









"Bellow, Bambi" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 11/14/2003 09:44 AM
 Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        SQL Query


Friends --

Why would these two queries return different results?

This query works.

SQL> l
  1  select
a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,
2)*100 pct
  2  from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
  3  from dba_extents group by tablespace_name) a,
  4  (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
  5  from dba_data_files  group by tablespace_name) b
  6  where a.tablespace_name=b.tablespace_name
  7* and a.tablespace_name='NAUAT'
SQL> /

TABLESPACE_NAME                MEGS_ALLOCATED  MEGS_USED        PCT
-- -- -- --
NAUAT                                22924.25      11509         50


This query does not work

  1  select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,
  2  sum(a.bytes)/(1024*1024) megs_used,
  3  round(sum(a.bytes)/sum(b.bytes),4)*100 pct
  4  from dba_extents a, dba_data_files b
  5  where a.tablespace_name=b.tablespace_name
  6  and a.tablespace_name='NAUAT'
  7* group by a.tablespace_name,b.tablespace_name
SQL> /

TABLESPACE_NAME                MEGS_ALLOCATED  MEGS_USED        PCT
-- -- -- --
NAUAT                              31773010.5      23018        .07


Bambi.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bellow, Bambi
  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: SQL Query

[Ron Thomas]

Be aware that this script does not include autoexted info

Ron Thomas
Hypercom, Inc
[EMAIL PROTECTED]
Each new user of a new system uncovers a new class of bugs. -- Kernighan


   
   
  [EMAIL PROTECTED]
 
  Sent by: To:   [EMAIL PROTECTED] 

  [EMAIL PROTECTED]cc: 
   
  .com Subject:  RE: SQL Query 
   
   
   
   
   
  11/14/2003 01:19 
   
  PM   
   
  Please respond to
   
  ORACLE-L 
   
   
   
   
   




Below is a very nice tablespace script, followed by some sample output.
Thought you might like it.  I found in somewhere.


REM name: freespace.sql
REM This script is used to list database freespace, total database
REM space, largest extent, fragments and percent freespace.
REM
REM  Usage sqlplus system/passwd @freespace
REM
REM Date  CreateDescription
REM 30-Oct-96Fan Zhang  Initial creation
REM
REM  dba tool key: freespace.sql -- list database freespace, total space
and percent free
REM

set pau off
set pages 35
set lines 120

col tablespace  heading 'Tablespace'
col freeheading 'Free|(Mb)' format 9.9
col total   heading 'Total|(Mb)'format 99.9
col usedheading 'Used|(Mb)' format 9.9
col pct_freeheading 'Pct|Free'  format 9.9
col pct_nextheading 'Pct|Next'  format 9.9
col largest heading 'Largest|(Mb)'  format 9.9
col nextheading 'Next|Ext(Mb)'  format 9.9
col fragmentheading 'Fragment'  format 999
col extents heading 'Max.|Ext.' format 999
spool freespace.txt


compute sum of total on report
compute sum of free on report
compute sum of used on report

break on report

select  substr(a.tablespace_name,1,13) tablespace,
round(sum(a.total1)/(1024*1024), 1) Total,
round(sum(a.total1)/(1024*1024),
1)-round(sum(a.sum1)/(1024*1024), 1) used,
round(sum(a.sum1)/(1024*1024), 1) free,
round(sum(a.sum1)/(1024*1024),
1)*100/round(sum(a.total1)/(1024*1024), 1) pct_free,
round(sum(a.maxb)/(1024*1024), 1) largest,
round(sum(a.next1)/(1024*1024), 1) Next,
round(sum(a.next1)/(1024*1024),
1)*100/round(sum(a.maxb)/(1024*1024), 1) pct_next,
max(a.max_ext) extents,
max(a.cnt) fragment
from
(select tablespace_name,
0 total1,
sum(bytes) sum1,
max(bytes) MAXB,
count(bytes) cnt,
0 next1,
0 max_ext
fromdba_free_space
group by tablespace_name
union
select  tablespace_name,
sum(bytes) total1,
0,
0,
0,
0,
0
fromdba_data_files
group by tablespace_name
union
select tablespace_name,
0,
0,
0,
0,
max(next_extent) next1,
   

Re: SQL Query

[Stephane Faroult]

The aggregate function operates last, on a result set. Why I suggested
to suppress the GROUP BY is that then you would have seen that the
number of bytes from each datafile is returnedtoo many times.
If you have F1 and F2 associated to your database, with E1 and E2 in F1
and E3 in F2 (I hope the meaning of my symbols is obvious) by joining on
the tablespace name you get
 F1 E1
 F1 E2
 F1 E3
 F2 E1
 F2 E2
 F2 E3

Quite obviously, when you sum up the result is much too big. For files
in that case its 3 times too big for each, and for extents two times too
big for each.

By contrast, the inline views in the query which works force the
aggregates to be computed _before_ the final calculation.

SF


"Bellow, Bambi" wrote:
> 
> But Stephane, I am aggregating by tablespace for both extents and for
> data_files.  There is nothing here that is separating out anything by
> datafile.  And, if I take away the GROUP BY, I lose the ability to aggregate
> at all, which is the point of this...
> 
> -Original Message-
> Sent: Friday, November 14, 2003 1:09 PM
> To: Multiple recipients of list ORACLE-L
> 
> Bambi,
> 
> Your second query is wrong because all extents in a tablespace don't
> necessarily belong to the same datafile. Try the query without the
> aggregate functions and the GROUP BY, and you'll understand your
> mistake.
> 
> HTH,
> 
> SF
> 
> "Bellow, Bambi" wrote:
> >
> > Friends --
> >
> > Why would these two queries return different results?
> >
> > This query works.
> >
> > SQL> l
> >   1  select
> >
> a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,
> > 2)*100 pct
> >   2  from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
> >   3  from dba_extents group by tablespace_name) a,
> >   4  (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
> >   5  from dba_data_files  group by tablespace_name) b
> >   6  where a.tablespace_name=b.tablespace_name
> >   7* and a.tablespace_name='NAUAT'
> > SQL> /
> >
> > TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
> > -- -- -- --
> > NAUAT22924.25  11509 50
> >
> > This query does not work
> >
> >   1  select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,
> >   2  sum(a.bytes)/(1024*1024) megs_used,
> >   3  round(sum(a.bytes)/sum(b.bytes),4)*100 pct
> >   4  from dba_extents a, dba_data_files b
> >   5  where a.tablespace_name=b.tablespace_name
> >   6  and a.tablespace_name='NAUAT'
> >   7* group by a.tablespace_name,b.tablespace_name
> > SQL> /
> >
> > TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
> > -- -- -- --
> > NAUAT  31773010.5  23018.07
> >
> > Bambi.
> > --
> --
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  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: SQL Query

[Smith, Ron L.]

Below is a very nice tablespace script, followed by some sample output.
Thought you might like it.  I found in somewhere.


REM name: freespace.sql
REM This script is used to list database freespace, total database
REM space, largest extent, fragments and percent freespace.
REM
REM  Usage sqlplus system/passwd @freespace
REM
REM Date  CreateDescription
REM 30-Oct-96Fan Zhang  Initial creation
REM
REM  dba tool key: freespace.sql -- list database freespace, total space
and percent free
REM

set pau off
set pages 35
set lines 120

col tablespace  heading 'Tablespace'
col freeheading 'Free|(Mb)' format 9.9
col total   heading 'Total|(Mb)'format 99.9
col usedheading 'Used|(Mb)' format 9.9
col pct_freeheading 'Pct|Free'  format 9.9
col pct_nextheading 'Pct|Next'  format 9.9
col largest heading 'Largest|(Mb)'  format 9.9
col nextheading 'Next|Ext(Mb)'  format 9.9
col fragmentheading 'Fragment'  format 999
col extents heading 'Max.|Ext.' format 999
spool freespace.txt


compute sum of total on report
compute sum of free on report
compute sum of used on report

break on report

select  substr(a.tablespace_name,1,13) tablespace,
round(sum(a.total1)/(1024*1024), 1) Total,
round(sum(a.total1)/(1024*1024),
1)-round(sum(a.sum1)/(1024*1024), 1) used,
round(sum(a.sum1)/(1024*1024), 1) free,
round(sum(a.sum1)/(1024*1024),
1)*100/round(sum(a.total1)/(1024*1024), 1) pct_free,
round(sum(a.maxb)/(1024*1024), 1) largest,
round(sum(a.next1)/(1024*1024), 1) Next,
round(sum(a.next1)/(1024*1024),
1)*100/round(sum(a.maxb)/(1024*1024), 1) pct_next,
max(a.max_ext) extents,
max(a.cnt) fragment
from
(select tablespace_name,
0 total1,
sum(bytes) sum1,
max(bytes) MAXB,
count(bytes) cnt,
0 next1,
0 max_ext
fromdba_free_space
group by tablespace_name
union
select  tablespace_name,
sum(bytes) total1,
0,
0,
0,
0,
0
fromdba_data_files
group by tablespace_name
union
select tablespace_name,
0,
0,
0,
0,
max(next_extent) next1,
max(extents) max_ext
from dba_segments
group by tablespace_name) a
group by a.tablespace_name
order by pct_free
/
spool off;


SQL*Plus: Release 3.3.4.0.0 - Production on Fri Nov 14 14:11:53 2003

Copyright (c) Oracle Corporation 1979, 1996.  All rights reserved.


Connected to:
Oracle7 Server Release 7.3.4.4.0 - Production
With the distributed and parallel query options
PL/SQL Release 2.3.4.4.0 - Production


  Total Used Free  Pct  Largest Next
Pct Max.
Tablespace (Mb) (Mb) (Mb) Free (Mb)  Ext(Mb)
Next Ext. Fragment
- -     
  
PRODIDX 21500.0  20130.4   1369.6  6.4925.8 87.9
9.5   62   13
PROD27400.0  24014.1   3385.9 12.4243.3212.4
87.3   54  197
PRODALT   200.0149.3 50.7 25.4 50.7 19.1
37.741
SYSTEM200.0 92.0108.0 54.0 86.5  4.2
4.9   15   12
TOOLS 100.0 12.5 87.5 87.5 75.1 12.0
16.046
PATROL_DATA20.0  2.0 18.0 90.0 18.0   .3
1.711
RBS  2500.0156.1   2343.9 93.8443.9  8.0
1.82   24
USERS 100.0  3.2 96.8 96.8 93.4  1.0
1.125
BMC_SMGT_TS  1000.0  4.6995.4 99.5500.0  2.0
.41  280
PRODAUD  1000.0  3.4996.6 99.7996.6   .4
.0   201
PATROL_TEMP10.0   .0 10.0100.0 10.0   .0
.001
TEMP 2900.0   .0   2900.0100.0 24.0   .0
.00  165
  -  
sum 56930.0  44567.6  12362.4

12 rows selected.



-Original Message-
Sent: Friday, November 14, 2003 1:54 PM
To: Multiple recipients of list ORACLE-L


But Stephane, I am aggregating by tablespace for both extents and for
data_files.  There is nothing here that is separating out anything by
datafile.  And, if I take away the GROUP BY, I lose the ability to
aggregate at all, which is the point of this...

-Original Message-
Sent: Friday, November 14, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L


Bambi,

Your second query is wrong because all extent

RE: SQL Query

[Bellow, Bambi]

I encountered the same issues.  Also, just to make things interesting, if
you replace DBA_EXTENTS with DBA_FREE_SPACE, the number of MEGS_ALLOCATED is
different.  Still wrong, mind you, but different.

None of this makes any sense to me.  My bet is that I got me a bug.

Bambi.

-Original Message-
Sent: Friday, November 14, 2003 1:39 PM
To: Multiple recipients of list ORACLE-L


Bambi,
 I tried your sql on my test server and the used space is the same.
here are the results. The ALLOCATED and PCT are way out, I'm looking. 

 1  select
a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,
2)*100
pct
 2   from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
 3   from dba_extents group by tablespace_name) a,
 4   (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
 5   from dba_data_files  group by tablespace_name) b
 6   where a.tablespace_name=b.tablespace_name
 7* and a.tablespace_name='AWSR_DATA_01'
inux>
inux>/

ABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
- -- -- --
WSR_DATA_01   40 38 95


  1   select a.tablespace_name,sum(b.bytes)/(1024*1024)
megs_allocated,
  2   sum(a.bytes)/(1024*1024) megs_used,
  3   round(sum(a.bytes)/sum(b.bytes),4)*100 pct
  4   from dba_extents a, dba_data_files b
  5   where a.tablespace_name=b.tablespace_name
  6   and a.tablespace_name='AWSR_DATA_01'
  7* group by a.tablespace_name,b.tablespace_name
linux>/

TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
-- -- -- --
AWSR_DATA_01  760 38  5

linux>

Bambi,
 The problem is a sum of the bytes each tome the tablespace_name is
looked up in the dba_extents table.

With both tables used..
  1   select b.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated
  2from dba_extents a, dba_data_files b
  3   where a.tablespace_name=b.tablespace_name
  4   and a.tablespace_name='AWSR_DATA_01'
  5* group by b.tablespace_name
linux>/

TABLESPACE_NAMEMEGS_ALLOCATED
-- --
AWSR_DATA_01  760

With one table used..
  1   select b.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated
  2from  dba_data_files b
  3   where b.tablespace_name='AWSR_DATA_01'
  4* group by b.tablespace_name
linux>/

TABLESPACE_NAMEMEGS_ALLOCATED
-- --
AWSR_DATA_01   40  
   CORRECT ANSWER.

select count(*) from dba_data_files where tablespace_name
='AWSR_DATA_01'
COUNT(*) = 1
select count(*) from dba_extents where tablespace_name ='AWSR_DATA_01'
COUNT(*) = 19
19 X 40 = 760

Ron
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ron Rogers
  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: Bellow, Bambi
  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: SQL Query

[Bellow, Bambi]

But Stephane, I am aggregating by tablespace for both extents and for
data_files.  There is nothing here that is separating out anything by
datafile.  And, if I take away the GROUP BY, I lose the ability to aggregate
at all, which is the point of this...

-Original Message-
Sent: Friday, November 14, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L


Bambi,

Your second query is wrong because all extents in a tablespace don't
necessarily belong to the same datafile. Try the query without the
aggregate functions and the GROUP BY, and you'll understand your
mistake.

HTH,

SF

"Bellow, Bambi" wrote:
> 
> Friends --
> 
> Why would these two queries return different results?
> 
> This query works.
> 
> SQL> l
>   1  select
>
a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,
> 2)*100 pct
>   2  from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
>   3  from dba_extents group by tablespace_name) a,
>   4  (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
>   5  from dba_data_files  group by tablespace_name) b
>   6  where a.tablespace_name=b.tablespace_name
>   7* and a.tablespace_name='NAUAT'
> SQL> /
> 
> TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
> -- -- -- --
> NAUAT22924.25  11509 50
> 
> This query does not work
> 
>   1  select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,
>   2  sum(a.bytes)/(1024*1024) megs_used,
>   3  round(sum(a.bytes)/sum(b.bytes),4)*100 pct
>   4  from dba_extents a, dba_data_files b
>   5  where a.tablespace_name=b.tablespace_name
>   6  and a.tablespace_name='NAUAT'
>   7* group by a.tablespace_name,b.tablespace_name
> SQL> /
> 
> TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
> -- -- -- --
> NAUAT  31773010.5  23018.07
> 
> Bambi.
> --
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  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: Bellow, Bambi
  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: SQL Query

[Ron Rogers]

Bambi,
 I tried your sql on my test server and the used space is the same.
here are the results. The ALLOCATED and PCT are way out, I'm looking. 

 1  select
a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,2)*100
pct
 2   from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
 3   from dba_extents group by tablespace_name) a,
 4   (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
 5   from dba_data_files  group by tablespace_name) b
 6   where a.tablespace_name=b.tablespace_name
 7* and a.tablespace_name='AWSR_DATA_01'
inux>
inux>/

ABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
- -- -- --
WSR_DATA_01   40 38 95


  1   select a.tablespace_name,sum(b.bytes)/(1024*1024)
megs_allocated,
  2   sum(a.bytes)/(1024*1024) megs_used,
  3   round(sum(a.bytes)/sum(b.bytes),4)*100 pct
  4   from dba_extents a, dba_data_files b
  5   where a.tablespace_name=b.tablespace_name
  6   and a.tablespace_name='AWSR_DATA_01'
  7* group by a.tablespace_name,b.tablespace_name
linux>/

TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
-- -- -- --
AWSR_DATA_01  760 38  5

linux>

Bambi,
 The problem is a sum of the bytes each tome the tablespace_name is
looked up in the dba_extents table.

With both tables used..
  1   select b.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated
  2from dba_extents a, dba_data_files b
  3   where a.tablespace_name=b.tablespace_name
  4   and a.tablespace_name='AWSR_DATA_01'
  5* group by b.tablespace_name
linux>/

TABLESPACE_NAMEMEGS_ALLOCATED
-- --
AWSR_DATA_01  760

With one table used..
  1   select b.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated
  2from  dba_data_files b
  3   where b.tablespace_name='AWSR_DATA_01'
  4* group by b.tablespace_name
linux>/

TABLESPACE_NAMEMEGS_ALLOCATED
-- --
AWSR_DATA_01   40  
   CORRECT ANSWER.

select count(*) from dba_data_files where tablespace_name
='AWSR_DATA_01'
COUNT(*) = 1
select count(*) from dba_extents where tablespace_name ='AWSR_DATA_01'
COUNT(*) = 19
19 X 40 = 760

Ron
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ron Rogers
  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: SQL Query

[Paul Baumgartel]

Odder still, I get inconsistent results.  megs_allocated is always
wrong, but megs_used is right when run against one tablespace, wrong
against another:

TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
-- -- -- --
MEDIUM_DATA361713   3616  1

Wrong!  

select sum(bytes)/1048576 from dba_extents where tablespace_name =
'MEDIUM_DATA'

SUM(BYTES)/1048576
--
  1808


TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
-- -- -- --
SMALL_DATA2783232169.875.01

Right!

  1* select sum(bytes)/1048576 from dba_extents where tablespace_name =
'SMALL_DATA'
SQL> /

SUM(BYTES)/1048576
--
   169.875


As for why the ALLOCATED value is wrong...I don't know, but at least
it's wrong consistently!

PB


--- "Bellow, Bambi" <[EMAIL PROTECTED]> wrote:
[snip]

> 
> Also, note that both the allocated *and* used values are wrong...they
> should
> be
> 
> 
> TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
> -- -- -- --
> NAUAT22924.25  11509 50
> 


__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Paul Baumgartel
  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: SQL Query

[Stephane Faroult]

Bambi,

Your second query is wrong because all extents in a tablespace don't
necessarily belong to the same datafile. Try the query without the
aggregate functions and the GROUP BY, and you'll understand your
mistake.

HTH,

SF

"Bellow, Bambi" wrote:
> 
> Friends --
> 
> Why would these two queries return different results?
> 
> This query works.
> 
> SQL> l
>   1  select
> a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,
> 2)*100 pct
>   2  from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
>   3  from dba_extents group by tablespace_name) a,
>   4  (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
>   5  from dba_data_files  group by tablespace_name) b
>   6  where a.tablespace_name=b.tablespace_name
>   7* and a.tablespace_name='NAUAT'
> SQL> /
> 
> TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
> -- -- -- --
> NAUAT22924.25  11509 50
> 
> This query does not work
> 
>   1  select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,
>   2  sum(a.bytes)/(1024*1024) megs_used,
>   3  round(sum(a.bytes)/sum(b.bytes),4)*100 pct
>   4  from dba_extents a, dba_data_files b
>   5  where a.tablespace_name=b.tablespace_name
>   6  and a.tablespace_name='NAUAT'
>   7* group by a.tablespace_name,b.tablespace_name
> SQL> /
> 
> TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
> -- -- -- --
> NAUAT  31773010.5  23018.07
> 
> Bambi.
> --
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  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: SQL Query

[Bellow, Bambi]

Nice try.  Same result.

 1  select a. tablespace_name, sum(b.user_bytes)/(1024*1024) allocated,
  2  sum(a.bytes)/(1024*1024) used, round(sum(a.bytes)/sum(b.user_bytes),4)
pct
  3  from dba_extents a, dba_data_files b
  4  where a.tablespace_name=b.tablespace_name
  5  and a.tablespace_name='NAUAT'
  6* group by a.tablespace_name,b.tablespace_name
SQL> /

TABLESPACE_NAME ALLOCATED   USEDPCT
-- -- -- --
NAUAT  31772837.3  23018  .0007


Also, note that both the allocated *and* used values are wrong...they should
be


TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
-- -- -- --
NAUAT22924.25  11509 50


-Original Message-
Sent: Friday, November 14, 2003 12:44 PM
To: Multiple recipients of list ORACLE-L


Bambi,
 I think that the query (2) will return the the same count as query (1)
if you use the
column user_bytes from the dba_data_files rather than the column
bytes.
 If I sum the bytes from dba_extents for a tablespace_name xxx
and sum the user_bytes from dba_data_files for tablespace_name xxx I
get the same value.

I my case the tablespace is a partitioned table with LMT . size 38M
with 2 M uniform extents. I did not use 100 % of each extent there fore
the different between bytes and user_bytes.
Ron
> [EMAIL PROTECTED] 11/14/2003 12:44:26 PM >>>
Friends --

Why would these two queries return different results?

This query works.

SQL> l
  1  select
a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,
2)*100 pct
  2  from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
  3  from dba_extents group by tablespace_name) a,
  4  (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
  5  from dba_data_files  group by tablespace_name) b
  6  where a.tablespace_name=b.tablespace_name
  7* and a.tablespace_name='NAUAT'
SQL> /

TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
-- -- -- --
NAUAT22924.25  11509 50


This query does not work

  1  select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,
  2  sum(a.bytes)/(1024*1024) megs_used,
  3  round(sum(a.bytes)/sum(b.bytes),4)*100 pct
  4  from dba_extents a, dba_data_files b
  5  where a.tablespace_name=b.tablespace_name
  6  and a.tablespace_name='NAUAT'
  7* group by a.tablespace_name,b.tablespace_name
SQL> /

TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
-- -- -- --
NAUAT  31773010.5  23018.07


Bambi.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Bellow, Bambi
  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: Ron Rogers
  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: Bellow, Bambi
  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: SQL Query

[Ron Rogers]

Bambi,
 I think that the query (2) will return the the same count as query (1)
if you use the
column user_bytes from the dba_data_files rather than the column
bytes.
 If I sum the bytes from dba_extents for a tablespace_name xxx
and sum the user_bytes from dba_data_files for tablespace_name xxx I
get the same value.

I my case the tablespace is a partitioned table with LMT . size 38M
with 2 M uniform extents. I did not use 100 % of each extent there fore
the different between bytes and user_bytes.
Ron
> [EMAIL PROTECTED] 11/14/2003 12:44:26 PM >>>
Friends --

Why would these two queries return different results?

This query works.

SQL> l
  1  select
a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,
2)*100 pct
  2  from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
  3  from dba_extents group by tablespace_name) a,
  4  (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
  5  from dba_data_files  group by tablespace_name) b
  6  where a.tablespace_name=b.tablespace_name
  7* and a.tablespace_name='NAUAT'
SQL> /

TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
-- -- -- --
NAUAT22924.25  11509 50


This query does not work

  1  select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,
  2  sum(a.bytes)/(1024*1024) megs_used,
  3  round(sum(a.bytes)/sum(b.bytes),4)*100 pct
  4  from dba_extents a, dba_data_files b
  5  where a.tablespace_name=b.tablespace_name
  6  and a.tablespace_name='NAUAT'
  7* group by a.tablespace_name,b.tablespace_name
SQL> /

TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
-- -- -- --
NAUAT  31773010.5  23018.07


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

SQL Query

[Bellow, Bambi]

Friends --

Why would these two queries return different results?

This query works.

SQL> l
  1  select
a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,
2)*100 pct
  2  from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
  3  from dba_extents group by tablespace_name) a,
  4  (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
  5  from dba_data_files  group by tablespace_name) b
  6  where a.tablespace_name=b.tablespace_name
  7* and a.tablespace_name='NAUAT'
SQL> /

TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
-- -- -- --
NAUAT22924.25  11509 50


This query does not work

  1  select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,
  2  sum(a.bytes)/(1024*1024) megs_used,
  3  round(sum(a.bytes)/sum(b.bytes),4)*100 pct
  4  from dba_extents a, dba_data_files b
  5  where a.tablespace_name=b.tablespace_name
  6  and a.tablespace_name='NAUAT'
  7* group by a.tablespace_name,b.tablespace_name
SQL> /

TABLESPACE_NAMEMEGS_ALLOCATED  MEGS_USEDPCT
-- -- -- --
NAUAT  31773010.5  23018.07


Bambi.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bellow, Bambi
  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: Sql query : select max timestamp value from table

[Rothouse, Michael]

Title: Message



select 
ip, max(timestamp) from table
group 
by ip;

  
  -Original Message-From: Johan Muller 
  [mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 10:45 
  AMTo: Multiple recipients of list ORACLE-LSubject: Sql 
  query : select max timestamp value from table
  I have multiple timestamps values  for single ip in a table, I need 
  the max(timestamp)  for each ip I select out. 
  example:
  timestamp  
  ip
  2003-09-29 13:20:23    68.209.182.42003-09-29 
  13:20:44    68.209.182.42003-10-02 
  12:53:38    68.209.182.42003-10-02 
  12:35:06    68.75.94.1582003-10-02 
  12:52:03    68.97.33.69
  Thus
  select distinct ip, max(timestamp) from table
  group by ip, timestamp;
   
  returns every timestamp value per ip.
  Any ideas on how to get only the max(timestamp) for each ip?
   

RE: Sql query : select max timestamp value from table

[Melanie Caffrey]

Johann,

 

Take the timestamp out of your group by.

 

Cheers,

Melanie

 



***

Melanie Caffrey

Proximo Consulting
Services, Inc.

[EMAIL PROTECTED]

(212) 686-6004 Ext. 32



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Johan
Muller
Sent: Thursday, October 02, 2003
10:45 AM
To: Multiple recipients of list
ORACLE-L
Subject: Sql query
: select max timestamp value from table

 

I have multiple timestamps values  for single ip
in a table, I need the max(timestamp)  for each ip I select out. 

example:

timestamp 
ip

2003-09-29 13:20:23    68.209.182.4
2003-09-29 13:20:44    68.209.182.4
2003-10-02 12:53:38    68.209.182.4
2003-10-02 12:35:06    68.75.94.158
2003-10-02 12:52:03    68.97.33.69

Thus

select distinct ip, max(timestamp) from table

group by ip, timestamp;

 

returns every timestamp value per ip.

Any ideas on how to get only the max(timestamp) for
each ip?

 

RE: Sql query : select max timestamp value from table

[Whittle Jerome Contr NCI]

Title: RE: Sql query : select max timestamp value from table







  select ip, max(timestamp) from table

  group by ip;


Jerry Whittle

ASIFICS DBA

NCI Information Systems Inc.

[EMAIL PROTECTED]

618-622-4145


-Original Message-

From:   Johan Muller [SMTP:[EMAIL PROTECTED]


I have multiple timestamps values  for single ip in a table, I need the max(timestamp)  for each ip I select out. 


example:


timestamp  ip


2003-09-29 13:20:23    68.209.182.4
2003-09-29 13:20:44    68.209.182.4
2003-10-02 12:53:38    68.209.182.4
2003-10-02 12:35:06    68.75.94.158
2003-10-02 12:52:03    68.97.33.69


Thus


select distinct ip, max(timestamp) from table

group by ip, timestamp;


returns every timestamp value per ip.


Any ideas on how to get only the max(timestamp) for each ip?

RE: Sql query : select max timestamp value from table

[Khedr, Waleed]

select ip, max(timestamp) from table
group by ip;

  -Original Message-From: Johan Muller 
  [mailto:[EMAIL PROTECTED]Sent: Thursday, October 02, 2003 10:45 
  AMTo: Multiple recipients of list ORACLE-LSubject: Sql 
  query : select max timestamp value from table
  I have multiple timestamps values  for single ip in a table, I need 
  the max(timestamp)  for each ip I select out. 
  example:
  timestamp  
  ip
  2003-09-29 13:20:23    68.209.182.42003-09-29 
  13:20:44    68.209.182.42003-10-02 
  12:53:38    68.209.182.42003-10-02 
  12:35:06    68.75.94.1582003-10-02 
  12:52:03    68.97.33.69
  Thus
  select distinct ip, max(timestamp) from table
  group by ip, timestamp;
   
  returns every timestamp value per ip.
  Any ideas on how to get only the max(timestamp) for each ip?
   

Re: Sql query : select max timestamp value from table

[Daniel Fink]

Johan,

First, you don't need the distinct. The proper query will return
1 row per ip.  Second, take the max(timestamp) out of the group
by. That is causing the problem.

Daniel

Johan Muller wrote:

> I have multiple timestamps values  for single ip in a table, I
> need the max(timestamp)  for each ip I select out.
>
> example:
>
> timestamp  ip
>
> 2003-09-29 13:20:2368.209.182.4
> 2003-09-29 13:20:4468.209.182.4
> 2003-10-02 12:53:3868.209.182.4
> 2003-10-02 12:35:0668.75.94.158
> 2003-10-02 12:52:0368.97.33.69
>
> Thus
>
> select distinct ip, max(timestamp) from table
>
> group by ip, timestamp;
>
>
>
> returns every timestamp value per ip.
>
> Any ideas on how to get only the max(timestamp) for each ip?
>
>
begin:vcard 
n:Fink;Daniel
x-mozilla-html:FALSE
org:Sun Microsystems, Inc.
adr:;;
version:2.1
title:Lead, Database Services
x-mozilla-cpt:;9168
fn:Daniel  W. Fink
end:vcard

Sql query : select max timestamp value from table

[Johan Muller]

I have multiple timestamps values  for single ip in a table, I need the max(timestamp)  for each ip I select out. 
example:
timestamp  ip
2003-09-29 13:20:23    68.209.182.42003-09-29 13:20:44    68.209.182.42003-10-02 12:53:38    68.209.182.42003-10-02 12:35:06    68.75.94.1582003-10-02 12:52:03    68.97.33.69
Thus
select distinct ip, max(timestamp) from table
group by ip, timestamp;
 
returns every timestamp value per ip.
Any ideas on how to get only the max(timestamp) for each ip?
 

Re: SQL Query

[sundeep maini]

Mike,

Just wanted to supplement your example with another one from my
environment (uses nested tables with CAST and MULTISET to achieve the
same):

SELECT c.customer_id
  ,customer_name
  ,utils.code_table_to_string(CAST( MULTISET(
SELECT distinct dlr_dealer_code
  FROM dealer_customers dc
   ,dlr_dealer_master_t
 WHERE dc.customer_id = dc_in.customer_id
   AND dlr_dealer_id = dc.dealer_id
 ORDER BY dlr_dealer_code
) AS code_table_t
  )) spans_these_dealers
 FROM customers c
 ,(
   SELECT customer_id
 FROM dealer_customers 
GROUP BY customer_id
HAVING COUNT(distinct dealer_id) > 1
  ) dc_in
WHERE dc_in.customer_id = c.customer_id;


Does eseentially the same thing. In this case it concatenates the
dealer_codes per customer (identifies customers spanning multiple
dealers) on the same row.  Here is a sample of the output:

2969473096  Mountain Enterprises IncD100,D470,D480
2969473121  K C ConstructionB010,B150,B190
2969473195  GOODFELLOW BROS H140,H330

The TYPE code_table_t is defined as:
TYPE CODE_TABLE_T IS table of varchar2(3000);

And the conversion function is very similar in its functionality to
dbms_utility.comma_to_table procedure:

FUNCTION code_table_to_string (in_table code_table_t, in_rec_sep
varchar2 DEFAULT config.c_rec_sep)
   RETURN delimited_list_t
 IS
   v_list delimited_list_t;
 BEGIN
   FOR i IN 1..in_table.COUNT
   LOOP
 IF i = 1
 THEN
   v_list := in_table(1);
 ELSE
   v_list := v_list||in_rec_sep||in_table(i);
 END IF;
   END LOOP;
   RETURN v_list;
 END code_table_to_string;

As opposed your suggestion of using the following:

SELECT id, CONCAT_LIST(CURSOR(
> SELECT text
>   FROM mike m_in
   ORDER BY ordr
>  WHERE m_in.id = m.id)) list
> FROM ( select distinct id from mike) m;
--- Mike Spalinger <[EMAIL PROTECTED]> wrote:
> Imran,
> 
> Tom Kyte has a thread that might help:
>
http://asktom.oracle.com/pls/ask/f?p=4950:8:1554835115460038644::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:229614022562,
> 
> Essentially, you can do this:
> 
> CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR )
>RETURN  VARCHAR2 IS
>  ret VARCHAR2(32000);
>  tmp VARCHAR2(4000);
> BEGIN
>  loop
>  fetch cur into tmp;
>  exit when cur%NOTFOUND;
>  ret := ret || ' ' || tmp;
>  end loop;
>  RETURN ret;
> END;
> /
> 
> SQL> select * from mike;
> 
>  ID TEXT ORDR
> -- -- --
>   1 B   2
>   1 A   1
>   1 C   3
> 
> SELECT id, SUBSTR(CONCAT_LIST(CURSOR(
> SELECT text
>FROM (select id, text, ordr from mike order by ordr) ee
>WHERE e.id = ee.id)),1,40) list
> FROM ( select distinct id from mike) e;
> 
>  ID LIST
> -- 
>   1  A B C
> 
> Mike
> 
> 
> Imran Ashraf wrote:
> > Hi,
> > 
> > I have the following data , table Temp
> > 
> > ID  Text   Order
> > 1 B2
> > 1 A1
> > 1 C3
> > 
> > 
> > I want to write a query which says: wherever there is more than 1
> occurrence
> > of ID then  concatenate the text in the order specified in the
> order column.
> > So i would get:
> > 
> > ID  Text
> > 1 A B C
> > 
> > Any suggestions?
> > 
> > Thanks
> > 
> > Imran
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Mike Spalinger
>   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).


=

Sundeep Maini 
Consultant 
Currently on Assignement at Caterpillar Peoria
[EMAIL PROTECTED] 

__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: sundeep maini
  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 n

Re: SQL Query

[sundeep maini]

Mike,

Just wanted to supplement your example with another one from my
environment:

SELECT c.customer_id
  ,customer_name
  ,utils.code_table_to_string(CAST( MULTISET(
SELECT distinct dlr_dealer_code
  FROM dealer_customers dc
   ,dlr_dealer_master_t
 WHERE dc.customer_id = dc_in.customer_id
   AND dlr_dealer_id = dc.dealer_id
 ORDER BY dlr_dealer_code
) AS code_table_t
  )) spans_these_dealers
 FROM customers c
 ,(
   SELECT customer_id
 FROM dealer_customers 
GROUP BY customer_id
HAVING COUNT(distinct dealer_id) > 1
  ) dc_in
WHERE dc_in.customer_id = c.customer_id;


Does eseentially the same thing. In this case it concatenates the
dealer_codes per customer (identifies customers spanning multiple
dealers) on the same row.  Here is a sample of the output:

2969473096  Mountain Enterprises IncD100,D470,D480
2969473121  K C ConstructionB010,B150,B190
2969473195  GOODFELLOW BROS H140,H330

--- Mike Spalinger <[EMAIL PROTECTED]> wrote:
> Imran,
> 
> Tom Kyte has a thread that might help:
>
http://asktom.oracle.com/pls/ask/f?p=4950:8:1554835115460038644::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:229614022562,
> 
> Essentially, you can do this:
> 
> CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR )
>RETURN  VARCHAR2 IS
>  ret VARCHAR2(32000);
>  tmp VARCHAR2(4000);
> BEGIN
>  loop
>  fetch cur into tmp;
>  exit when cur%NOTFOUND;
>  ret := ret || ' ' || tmp;
>  end loop;
>  RETURN ret;
> END;
> /
> 
> SQL> select * from mike;
> 
>  ID TEXT ORDR
> -- -- --
>   1 B   2
>   1 A   1
>   1 C   3
> 
> SELECT id, SUBSTR(CONCAT_LIST(CURSOR(
> SELECT text
>FROM (select id, text, ordr from mike order by ordr) ee
>WHERE e.id = ee.id)),1,40) list
> FROM ( select distinct id from mike) e;
> 
>  ID LIST
> -- 
>   1  A B C
> 
> Mike
> 
> 
> Imran Ashraf wrote:
> > Hi,
> > 
> > I have the following data , table Temp
> > 
> > ID  Text   Order
> > 1 B2
> > 1 A1
> > 1 C3
> > 
> > 
> > I want to write a query which says: wherever there is more than 1
> occurrence
> > of ID then  concatenate the text in the order specified in the
> order column.
> > So i would get:
> > 
> > ID  Text
> > 1 A B C
> > 
> > Any suggestions?
> > 
> > Thanks
> > 
> > Imran
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Mike Spalinger
>   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).


=

Sundeep Maini 
Consultant 
Currently on Assignement at Caterpillar Peoria
[EMAIL PROTECTED] 

__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: sundeep maini
  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: SQL Query

[Mike Spalinger]

Imran,

Tom Kyte has a thread that might help:
http://asktom.oracle.com/pls/ask/f?p=4950:8:1554835115460038644::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:229614022562,
Essentially, you can do this:

CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR )
  RETURN  VARCHAR2 IS
ret VARCHAR2(32000);
tmp VARCHAR2(4000);
BEGIN
loop
fetch cur into tmp;
exit when cur%NOTFOUND;
ret := ret || ' ' || tmp;
end loop;
RETURN ret;
END;
/
SQL> select * from mike;

ID TEXT ORDR
-- -- --
 1 B   2
 1 A   1
 1 C   3
SELECT id, SUBSTR(CONCAT_LIST(CURSOR(
   SELECT text
  FROM (select id, text, ordr from mike order by ordr) ee
  WHERE e.id = ee.id)),1,40) list
FROM ( select distinct id from mike) e;
ID LIST
-- 
 1  A B C
Mike

Imran Ashraf wrote:
Hi,

I have the following data , table Temp

ID  Text   Order
1 B2
1 A1
1 C3
I want to write a query which says: wherever there is more than 1 occurrence
of ID then  concatenate the text in the order specified in the order column.
So i would get:
ID  Text
1 A B C
Any suggestions?

Thanks

Imran


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

SQL Query

[Imran Ashraf]

Hi,

I have the following data , table Temp

ID  Text   Order
1 B2
1 A1
1 C3


I want to write a query which says: wherever there is more than 1 occurrence
of ID then  concatenate the text in the order specified in the order column.
So i would get:

ID  Text
1 A B C

Any suggestions?

Thanks

Imran
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Imran Ashraf
  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: SQL Query

[Robson, Peter]

Err, not quite, actually.

The solution presented below will simply generate the cartesian product of
the sum of attributes from the table.

The (grossly pedantic) solution is:

select distinct a.col1, a.col2, a.col3||b.col3||c.col3
from fred a, fred b, fred c
where a.col3 = 'A'
and b.col3 = 'B'
and c.col3='C'

... which really begs the question - 'what IS the question?' ! Although this
solution works, it would be quite impractical to apply it to 'test' if there
were 'n' values of col3.

Imran - try framing your question in general terms.

peter
edinburgh


-Original Message-
Sent: Friday, September 19, 2003 5:30 PM
To: Multiple recipients of list ORACLE-L


Assume your table is name fred:

select a.col1 a.col2 a.col3||b.col3||c.col3
from fred a, fred b, fred c;

Allan
-Original Message-
Sent: Friday, September 19, 2003 10:15 AM
To: Multiple recipients of list ORACLE-L


Hi,

I have the following data in a table:

col1  col2   col3

1  2   A
1  2   B
1  2   C


I want to display this as :

col1  col2   col3

1  2   A B C 

How can i do this?

Regards

Imran
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Imran Ashraf
  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 email is intended solely for the person or entity to which it is
addressed and may contain confidential and/or privileged information.
Copying, forwarding or distributing this message by persons or entities
other than the addressee is prohibited. If you have received this email in
error, please contact the sender immediately and delete the material from
any computer.  This email may have been monitored for policy compliance.
[021216]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nelson, Allan
  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,  and  any  files  transmitted  with  it, are
confidential  and intended  solely for the  use of the  addressee. If
this message was not addressed to  you, you have received it in error
and any  copying,  distribution  or  other use  of any part  of it is
strictly prohibited. Any views or opinions presented are solely those
of the sender and do not necessarily represent  those of the British
Geological  Survey. The  security of e-mail  communication  cannot be
guaranteed and the BGS accepts no liability  for claims arising as a
result of the use of this medium to  transmit messages from or to the
BGS. .http://www.bgs.ac.uk
*

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Robson, Peter
  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: SQL Query

[Nelson, Allan]

Assume your table is name fred:

select a.col1 a.col2 a.col3||b.col3||c.col3
from fred a, fred b, fred c;

Allan
-Original Message-
Sent: Friday, September 19, 2003 10:15 AM
To: Multiple recipients of list ORACLE-L


Hi,

I have the following data in a table:

col1  col2   col3

1  2   A
1  2   B
1  2   C


I want to display this as :

col1  col2   col3

1  2   A B C 

How can i do this?

Regards

Imran
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Imran Ashraf
  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 email is intended solely for the person or entity to which it is addressed and 
may contain confidential and/or privileged information.  Copying, forwarding or 
distributing this message by persons or entities other than the addressee is 
prohibited. If you have received this email in error, please contact the sender 
immediately and delete the material from any computer.  This email may have been 
monitored for policy compliance.  [021216]

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

SQL Query

[Imran Ashraf]

Hi,

I have the following data in a table:

col1  col2   col3

1  2   A
1  2   B
1  2   C


I want to display this as :

col1  col2   col3

1  2   A B C 

How can i do this?

Regards

Imran
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Imran Ashraf
  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: SQL Query Help

[Wolfgang Breitling]

select  from  A)
where 
  and datecreated = (select min(datecreated)
from  b where b.cid = a.cid and b.pid = a.pid)
At 08:14 PM 6/13/2003 -0800, you wrote:
I have a table with records like this

  CID S TO_CHAR(DATECREATED, MESSAGE PID
- -   --
2 N 01-feb-1974 19:45:45 service change1*
3 N 01-feb-1974 19:45:45 service 
change1*
3 N 01-feb-1974 21:45:45 service change1
1 N 01-jan-1974 12:34:45 msisdn change 1*
1 N 01-jan-1974 19:45:45 service change1
2 N 01-jan-1974 19:45:45 service change1
1 N 01-nov-1974 17:45:45 service change1
1 N 01-nov-1974 19:45:45 service change1



I want to display only the records with the *(not a value stored in the 
database.just used as a marker here).
i.e the records which meet the following.

1.earliest date
2.if there are multiple occurances of records with the same cid and pid 
combination i want only the record for
the combination of cid-pid and with the most earliest record(oldest time 
stamp).

i want to achieve this

  CID S TO_CHAR(DATECREATED, MESSAGE PID
- -   --
2 N 01-feb-1974 19:45:45 service change1*
3 N 01-feb-1974 19:45:45 service 
change1*
1 N 01-jan-1974 12:34:45 msisdn change 1*

I need some help in getting the query that can get the results like that.

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

SQL Query Help

[Basavaraja, Ravindra]

I have a table with records like this


  CID S TO_CHAR(DATECREATED, MESSAGE PID
- -   --
2 N 01-feb-1974 19:45:45 service change1*
3 N 01-feb-1974 19:45:45 service change1*   
3 N 01-feb-1974 21:45:45 service change1
1 N 01-jan-1974 12:34:45 msisdn change 1*
1 N 01-jan-1974 19:45:45 service change1
2 N 01-jan-1974 19:45:45 service change1
1 N 01-nov-1974 17:45:45 service change1
1 N 01-nov-1974 19:45:45 service change1


   
I want to display only the records with the *(not a value stored in the database.just 
used as a marker here).
i.e the records which meet the following.

1.earliest date
2.if there are multiple occurances of records with the same cid and pid combination i 
want only the record for 
the combination of cid-pid and with the most earliest record(oldest time stamp).

i want to achieve this


  CID S TO_CHAR(DATECREATED, MESSAGE PID
- -   --
2 N 01-feb-1974 19:45:45 service change1*
3 N 01-feb-1974 19:45:45 service change1*   
1 N 01-jan-1974 12:34:45 msisdn change 1*

I need some help in getting the query that can get the results like that.

Thanks
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Basavaraja, Ravindra
  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: sql query optimization

[rgaffuri]

1. check your sort_area_size when you use distinct you need to sort. It might be too 
small and you may be sorting to the temp tablespace which is very slow.

2. how much does querySeq.use='Y' and otehr where clauses like it limit the number of 
rows you are looking for? I have found that if I craete a another table that just has 
the rows I need, I can often significantly improve performance. 
> 
> From: "Niall Litchfield" <[EMAIL PROTECTED]>
> Date: 2003/06/12 Thu PM 01:15:08 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: sql query optimization
> 
> Given the low elapsed time for each iteration of the query I wonder if
> the problem might be susceptible to either of the following approaches. 
> 
> 1. Calling the query less often. I'm guessing from the object names etc
> that this is some sort of scientific analysis program, and it may be
> that you are repeatedly calling the same logic with different binds when
> you could be doing some sort of batch processing. 
> 
> 2. Creating a Materialized view that will serve the query and could be
> stored if necessary in the keep pool. 
> 
> 
> Niall 
> 
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On 
> > Behalf Of gmei
> > Sent: 10 June 2003 22:59
> > To: Multiple recipients of list ORACLE-L
> > Subject: sql query optimization
> > 
> > 
> > Hi:
> > 
> > I have been trying for two days to see if I could optimize 
> > this query without much success. One of the programs here 
> > calls this query many many times and I hope I could make it 
> > run faster. It typically take about 1 sec to get the result. 
> > I have tried using "exists" to replace "in" and the result is 
> > not good. All the columns involved in the "where" clause have 
> > been indexed. b1 and b2 are bind variables that are passed in.
> > 
> > 
> > 
> > select distinct observationlist.geneid, pval, score,
> > Decode(evidenceCode, 3000900, 'E', 3000902, 'P', 3000906),
> > proteomeRefID, Decode(ReferenceType, 'I', 'Y', 'N'), reftarget
> > frommt.dualblastresults, mt.seqtable querySeq, isi.observationlist,
> > isi.termobs
> > where   subjID = :b1
> > and   queryID = QuerySeq.AASeqID
> > and querySeq.use='Y'
> > and   querySeq.geneID=observationlist.geneid
> > and curationStatus='E'
> > and   evidenceCode in (3000900,3000902,3000906)
> > and observationlist.id=obsID
> > and   target='GeneID'
> > and termobs.termid in (select termid from isi.arc
> >  where arctype in (299,300)
> >  start with termid = :b2
> >  connect by prior 
> > termid=parenttermid) order by mt.blast.pvaltonumber(pval) 
> > asc, score desc, geneid,
> >  decode(proteomerefid, null, 0, 1) desc;
> > 
> > --
> > 
> > This query typically returns 10 or less rows. 
> > mt.dualblastresults is a view, all others are tables. BTW, I 
> > need "distinct" and "order by" in the query.
> > 
> > Here is the explain plan and row counts in tables and their 
> > definition. Anyone has any suggestions to make it run faster?
> > 
> > TIA.
> > 
> > Guang
> > 
> > 
> > Execution Plan
> > --
> >0  SELECT STATEMENT Optimizer=CHOOSE (Cost=715 Card=1 Bytes=124
> >   )
> > 
> >10   SORT (ORDER BY) (Cost=715 Card=1 Bytes=124)
> >21 SORT (UNIQUE) (Cost=662 Card=1 Bytes=124)
> >32   NESTED LOOPS (Cost=609 Card=1 Bytes=124)
> >43 NESTED LOOPS (Cost=553 Card=1 Bytes=118)
> >54   NESTED LOOPS (Cost=550 Card=1 Bytes=106)
> >65 NESTED LOOPS (Cost=280 Card=30 Bytes=1830)
> >76   VIEW OF 'DUALBLASTRESULTS' (Cost=112 Card=168
> >   Bytes=8232)
> > 
> >87 UNION-ALL
> >98   TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES
> >   ULTS' (Cost=102 Card=118 Bytes=2360)
> > 
> >   109 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SUBJ
> >   ID_INDEX' (NON-UNIQUE) (Cost=3 Card=118)
> > 
> >   118   TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES
> >   ULTS' (Cost=10 Card=50 Byte

RE: sql query optimization

[Niall Litchfield]

Given the low elapsed time for each iteration of the query I wonder if
the problem might be susceptible to either of the following approaches. 

1. Calling the query less often. I'm guessing from the object names etc
that this is some sort of scientific analysis program, and it may be
that you are repeatedly calling the same logic with different binds when
you could be doing some sort of batch processing. 

2. Creating a Materialized view that will serve the query and could be
stored if necessary in the keep pool. 


Niall 

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On 
> Behalf Of gmei
> Sent: 10 June 2003 22:59
> To: Multiple recipients of list ORACLE-L
> Subject: sql query optimization
> 
> 
> Hi:
> 
> I have been trying for two days to see if I could optimize 
> this query without much success. One of the programs here 
> calls this query many many times and I hope I could make it 
> run faster. It typically take about 1 sec to get the result. 
> I have tried using "exists" to replace "in" and the result is 
> not good. All the columns involved in the "where" clause have 
> been indexed. b1 and b2 are bind variables that are passed in.
> 
> 
> 
> select distinct observationlist.geneid, pval, score,
>   Decode(evidenceCode, 3000900, 'E', 3000902, 'P', 3000906),
>   proteomeRefID, Decode(ReferenceType, 'I', 'Y', 'N'), reftarget
> from  mt.dualblastresults, mt.seqtable querySeq, isi.observationlist,
> isi.termobs
> where subjID = :b1
> and   queryID = QuerySeq.AASeqID
> and   querySeq.use='Y'
> and   querySeq.geneID=observationlist.geneid
> and   curationStatus='E'
> and   evidenceCode in (3000900,3000902,3000906)
> and   observationlist.id=obsID
> and   target='GeneID'
> and   termobs.termid in (select termid from isi.arc
>  where arctype in (299,300)
>  start with termid = :b2
>  connect by prior 
> termid=parenttermid) order by mt.blast.pvaltonumber(pval) 
> asc, score desc, geneid,
>  decode(proteomerefid, null, 0, 1) desc;
> 
> --
> 
> This query typically returns 10 or less rows. 
> mt.dualblastresults is a view, all others are tables. BTW, I 
> need "distinct" and "order by" in the query.
> 
> Here is the explain plan and row counts in tables and their 
> definition. Anyone has any suggestions to make it run faster?
> 
> TIA.
> 
> Guang
> 
> 
> Execution Plan
> --
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=715 Card=1 Bytes=124
>   )
> 
>10   SORT (ORDER BY) (Cost=715 Card=1 Bytes=124)
>21 SORT (UNIQUE) (Cost=662 Card=1 Bytes=124)
>32   NESTED LOOPS (Cost=609 Card=1 Bytes=124)
>43 NESTED LOOPS (Cost=553 Card=1 Bytes=118)
>54   NESTED LOOPS (Cost=550 Card=1 Bytes=106)
>65 NESTED LOOPS (Cost=280 Card=30 Bytes=1830)
>76   VIEW OF 'DUALBLASTRESULTS' (Cost=112 Card=168
>   Bytes=8232)
> 
>87 UNION-ALL
>98   TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES
>   ULTS' (Cost=102 Card=118 Bytes=2360)
> 
>   109 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SUBJ
>   ID_INDEX' (NON-UNIQUE) (Cost=3 Card=118)
> 
>   118   TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES
>   ULTS' (Cost=10 Card=50 Bytes=1000)
> 
>   12   11 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QUER
>   YID_INDEX' (NON-UNIQUE) (Cost=3 Card=50)
> 
>   136   TABLE ACCESS (BY INDEX ROWID) OF 'SEQTABLE' (C
>   ost=1 Card=57344 Bytes=688128)
> 
>   14   13 INDEX (UNIQUE SCAN) OF 'ST_ASI_UN' (UNIQUE)
>   155 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIS
>   T' (Cost=9 Card=499 Bytes=22455)
> 
>   16   15   INDEX (RANGE SCAN) OF 'OBSERVATIONLISTGENEID'
>   (NON-UNIQUE) (Cost=2 Card=499)
> 
>   174   TABLE ACCESS (BY INDEX ROWID) OF 'TERMOBS' (Cost=3
>Card=2388115 Bytes=28657380)
> 
>   18   17 INDEX (RANGE SCAN) OF 'TERMOBSIDINDEX' (NON-UNIQ
>   UE) (Cost=2 Card=2388115)
> 
>   193 VIEW OF 'VW_NSO_1' (Cost=56 Card=7 Bytes=42)
>   20   19   SORT (UNIQUE) (Cost=56 Card=7 Bytes=126)
>   21   20 FILTE

Re: sql query optimization

[Mark Richard]

Hi,

>From what you have said the cost of distinct and the function call
shouldn't be a big deal.  I did wonder if you can use to_number with an
appropriate mask to avoid the function call but it's probably not even
worth bothering.

Simplifying the connect by sub-query will hopefully provide the boost you
need.  The concatenated index relates to my uncertainty about how Oracle
can use them for recursive SQL.  I did a simple test - creating the
following indexes:

1) Unique index on child
2) Non-unique index on parent
3) Unique index on parent, child
4) Unique index on child, parent

The table only had a handful of rows but Oracle chose to use index 1 and
index 3 for the query instead of index 2.  On a table of significant volume
(I used to work on very large recursive SQL statements at one point) I
would suggest testing the indexing combinations to see what Oracle likes -
then remove the rest.  Also, the requirements are different if you are
traversing the tree in both directions - you seem to only be going down the
tree.

Good luck.



   
   
  Guang Mei
   
  <[EMAIL PROTECTED]>To:   Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>  
  Sent by: cc: 
   
  [EMAIL PROTECTED]    Subject:  Re: sql query optimization
   
  .com 
   
   
   
   
   
  11/06/2003 12:34 
   
  Please respond to
   
  ORACLE-L 
   
   
   
   
   




I just looked:

[EMAIL PROTECTED]> select count(*) from arc where arctype in (299,300);

  COUNT(*)
--
 56932

This is about 27% of the total rows, so I will test to move them into a
new table tomorrow and this should help. I did test each part separatley
and timed them and I found that the sub-query is probably the bottle-neck
because
"start ... connect by ..." requires walk the whole index to get all
possible nodes
(expensive). I can create this new table.


> 2)  Consider a concatenated index (perhaps termid, parenttermid or
> parenttermid,termid - too early for my brain to remember without trying)
>

I don't know why concatenated index would help here, for which part in
where clause it would?



<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   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.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>&g

Re: sql query optimization

[Guang Mei]

Hi:

Thanks for your help and suggestions, Mark.

I have done some of the things you suggested already. Please see my text
below.

On Tue, 10 Jun 2003, Mark Richard wrote:

>
> This is an interesting (and relatively complex) query with what I think are
> several opportunities to tune it.  I'd probably spend some time looking at
> the following to see if they might help you out:
>
> 1)  Look at the sub-select with the connect by clause...  Try executing
> that query on it's own and get an idea of it's execution time and the
> number of rows returned for different bind variables.  Depending on the
> number of distinct values of arctype there may be some scope to optimise
> this component.  Possibly create a table containing on the arctype 299
> and 300 records and then remove this clause from the query - this could
> avoid accessing the table at all.  I have no idea if creating such a table
> is practical for your scenario though.

I just looked:

[EMAIL PROTECTED]> select count(*) from arc where arctype in (299,300);

  COUNT(*)
--
 56932

This is about 27% of the total rows, so I will test to move them into a
new table tomorrow and this should help. I did test each part separatley
and timed them and I found that the sub-query is probably the bottle-neck because
"start ... connect by ..." requires walk the whole index to get all possible nodes
(expensive). I can create this new table.


> 2)  Consider a concatenated index (perhaps termid, parenttermid or
> parenttermid,termid - too early for my brain to remember without trying)
>

I don't know why concatenated index would help here, for which part in
where clause it would?

> 3)  Are the distinct and order by clausing really needed.  Often a distinct
> is included to hide a fault in the query (like a missing join or criteria)
> - distinct can be very expensive at times but since your query runs fairly
> fast you probably aren't removing many rows.  How many rows does the query
> return with versus without the distinct clause?
>

distinct is needed because otherwise it would return duplicates. It's more
like 5 rows with distinct and 12 without (typically).

> 4)  In the order by clause is "mt.blast.pvaltonumber(pval)"  This looks
> like a function call - if you have a way to avoid this function call you
> may see a performance increase.  You could test this by creating a table
> which stores the calculated result already and modify the query (remember
> to index and analyze the same as the original table).  Does this help?  Is
> it practical to store the result?  Again though, the benefit will be
> determined by the number of rows being ordered and the amount of query time
> spent doing this - for large data sets a function call is murder though.
>

Yes, it is a function call (from a package), it basically take care of the
situation that "pval" (for example) can be "2e-56" or "e-37", I need a way
of order them. I tried with  "decode" but found it did not improve the
performance.  I even tried to remove "order by" and the performance is
basically the same. This is probably because the number of rows returned
is small.

> 5)  Finally, I just realised at the last minute that DUALBLASTRESULTS
> appears to be a view.  Try bypassing the view and going straight to the
> base tables with the most restrictive criteria you have.  Sometimes Oracle
> doesn't handle views really well within queries.  I've seen improvements
> where the entire logic of the view was moved within the query - it
> shouldn't have changed anything from a theoretical point of view but it
> did.
>

I did try to replace the view with the base tables and the performance are the same. I
have a bounch of other queries using this view and I always get the same performance 
when
I replace it with base tables. So I think there probably isn't much I can do here.

Again, thanks for your help.

Guang

> Hopefully this gives you some options to look at.
>
> Regards,
>   Mark.
>
>
>
>
>   "gmei"
>   <[EMAIL PROTECTED]>To:   Multiple recipients of 
> list ORACLE-L <[EMAIL PROTECTED]>
>   Sent by: cc:
>   [EMAIL PROTECTED]Subject:  sql query optimization
>   .com
>
>
>   11/06/2003 07:59
>   Please respond to
>   ORACLE-L
>
>
>
>
>
>
> Hi:
>
> I have been trying for two days to see if I could optimize this query
> without much success. One of the programs here calls this query many many
> times and I hope I could make it run faster. It ty

Re: sql query optimization

[Binley Lim]

All very good suggestions, but given the low elapsed time and cost figures,
I suspect most of the time is taken up jumping around buffer cache locating
and pinning blocks.

What would help a lot is to eliminate the "table access by index rowid" by
including all query columns in your indexes. There will be a hit on
inserts/updates/deletes, but presumably an acceptable hit compared to your
"many many" selects.

- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Wednesday, June 11, 2003 11:54 AM


>
> This is an interesting (and relatively complex) query with what I think
are
> several opportunities to tune it.  I'd probably spend some time looking at
> the following to see if they might help you out:
>
> 1)  Look at the sub-select with the connect by clause...  Try executing
> that query on it's own and get an idea of it's execution time and the
> number of rows returned for different bind variables.  Depending on the
> number of distinct values of arctype there may be some scope to optimise
> this component.  Possibly create a table containing on the arctype 299
> and 300 records and then remove this clause from the query - this
could
> avoid accessing the table at all.  I have no idea if creating such a table
> is practical for your scenario though.
>
> 2)  Consider a concatenated index (perhaps termid, parenttermid or
> parenttermid,termid - too early for my brain to remember without trying)
>
> 3)  Are the distinct and order by clausing really needed.  Often a
distinct
> is included to hide a fault in the query (like a missing join or criteria)
> - distinct can be very expensive at times but since your query runs fairly
> fast you probably aren't removing many rows.  How many rows does the query
> return with versus without the distinct clause?
>
> 4)  In the order by clause is "mt.blast.pvaltonumber(pval)"  This looks
> like a function call - if you have a way to avoid this function call you
> may see a performance increase.  You could test this by creating a table
> which stores the calculated result already and modify the query (remember
> to index and analyze the same as the original table).  Does this help?  Is
> it practical to store the result?  Again though, the benefit will be
> determined by the number of rows being ordered and the amount of query
time
> spent doing this - for large data sets a function call is murder though.
>
> 5)  Finally, I just realised at the last minute that DUALBLASTRESULTS
> appears to be a view.  Try bypassing the view and going straight to the
> base tables with the most restrictive criteria you have.  Sometimes Oracle
> doesn't handle views really well within queries.  I've seen improvements
> where the entire logic of the view was moved within the query - it
> shouldn't have changed anything from a theoretical point of view but it
> did.
>
> Hopefully this gives you some options to look at.
>
> Regards,
>   Mark.
>
>
>
>
>   "gmei"
>   <[EMAIL PROTECTED]>To:   Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
>   Sent by: cc:
>   [EMAIL PROTECTED]Subject:  sql query
optimization
>   .com
>
>
>   11/06/2003 07:59
>   Please respond to
>   ORACLE-L
>
>
>
>
>
>
> Hi:
>
> I have been trying for two days to see if I could optimize this query
> without much success. One of the programs here calls this query many many
> times and I hope I could make it run faster. It typically take about 1 sec
> to get the result. I have tried using "exists" to replace "in" and the
> result is not good. All the columns involved in the "where" clause have
> been
> indexed. b1 and b2 are bind variables that are passed in.
>
> 
>
> select distinct observationlist.geneid, pval, score,
>  Decode(evidenceCode, 3000900, 'E', 3000902, 'P', 3000906),
>  proteomeRefID, Decode(ReferenceType, 'I', 'Y', 'N'),
reftarget
> from mt.dualblastresults, mt.seqtable querySeq,
> isi.observationlist,
> isi.termobs
> wheresubjID = :b1
> and   queryID = QuerySeq.AASeqID
> and  querySeq.use='Y'
> and   querySeq.geneID=observationlist.geneid
> and  curationStatus='E'
> and   evidenceCode in (3000900,3000902,3000906)
> and  observationlist.id=obsID
> and   target='GeneID'
> and  termobs.termid in (select termid from 

Re: sql query optimization

[Mark Richard]

This is an interesting (and relatively complex) query with what I think are
several opportunities to tune it.  I'd probably spend some time looking at
the following to see if they might help you out:

1)  Look at the sub-select with the connect by clause...  Try executing
that query on it's own and get an idea of it's execution time and the
number of rows returned for different bind variables.  Depending on the
number of distinct values of arctype there may be some scope to optimise
this component.  Possibly create a table containing on the arctype 299
and 300 records and then remove this clause from the query - this could
avoid accessing the table at all.  I have no idea if creating such a table
is practical for your scenario though.

2)  Consider a concatenated index (perhaps termid, parenttermid or
parenttermid,termid - too early for my brain to remember without trying)

3)  Are the distinct and order by clausing really needed.  Often a distinct
is included to hide a fault in the query (like a missing join or criteria)
- distinct can be very expensive at times but since your query runs fairly
fast you probably aren't removing many rows.  How many rows does the query
return with versus without the distinct clause?

4)  In the order by clause is "mt.blast.pvaltonumber(pval)"  This looks
like a function call - if you have a way to avoid this function call you
may see a performance increase.  You could test this by creating a table
which stores the calculated result already and modify the query (remember
to index and analyze the same as the original table).  Does this help?  Is
it practical to store the result?  Again though, the benefit will be
determined by the number of rows being ordered and the amount of query time
spent doing this - for large data sets a function call is murder though.

5)  Finally, I just realised at the last minute that DUALBLASTRESULTS
appears to be a view.  Try bypassing the view and going straight to the
base tables with the most restrictive criteria you have.  Sometimes Oracle
doesn't handle views really well within queries.  I've seen improvements
where the entire logic of the view was moved within the query - it
shouldn't have changed anything from a theoretical point of view but it
did.

Hopefully this gives you some options to look at.

Regards,
  Mark.



   
   
  "gmei"   
   
  <[EMAIL PROTECTED]>To:   Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>  
  Sent by: cc: 
       
  [EMAIL PROTECTED]Subject:  sql query optimization
   
  .com 
   
   
   
   
   
  11/06/2003 07:59 
   
  Please respond to
   
  ORACLE-L 
   
   
   
   
   




Hi:

I have been trying for two days to see if I could optimize this query
without much success. One of the programs here calls this query many many
times and I hope I could make it run faster. It typically take about 1 sec
to get the result. I have tried using "exists" to replace "in" and the
result is not good. All the columns involved in the "where" clause have
been
indexed. b1 and b2 are bind variables that are passed in.



select distinct observationlist.geneid, pval, score,
 Decode(evidenceCode, 3000900, 'E', 3000902, 'P', 3000906),
 proteomeRefID, Decode(ReferenceType, 'I', 'Y', 'N'), reftarget
from mt.dualblastresults, mt.seqtable querySeq,
isi.observationlist,
is

sql query optimization

[gmei]

Hi:

I have been trying for two days to see if I could optimize this query
without much success. One of the programs here calls this query many many
times and I hope I could make it run faster. It typically take about 1 sec
to get the result. I have tried using "exists" to replace "in" and the
result is not good. All the columns involved in the "where" clause have been
indexed. b1 and b2 are bind variables that are passed in.



select distinct observationlist.geneid, pval, score,
Decode(evidenceCode, 3000900, 'E', 3000902, 'P', 3000906),
proteomeRefID, Decode(ReferenceType, 'I', 'Y', 'N'), reftarget
frommt.dualblastresults, mt.seqtable querySeq, isi.observationlist,
isi.termobs
where   subjID = :b1
and   queryID = QuerySeq.AASeqID
and querySeq.use='Y'
and   querySeq.geneID=observationlist.geneid
and curationStatus='E'
and   evidenceCode in (3000900,3000902,3000906)
and observationlist.id=obsID
and   target='GeneID'
and termobs.termid in (select termid from isi.arc
 where arctype in (299,300)
 start with termid = :b2
 connect by prior termid=parenttermid)
order by mt.blast.pvaltonumber(pval) asc, score desc, geneid,
 decode(proteomerefid, null, 0, 1) desc;

--

This query typically returns 10 or less rows. mt.dualblastresults is a view,
all others are tables. BTW, I need "distinct" and "order by" in the query.

Here is the explain plan and row counts in tables and their definition.
Anyone has any suggestions to make it run faster?

TIA.

Guang


Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=715 Card=1 Bytes=124
  )

   10   SORT (ORDER BY) (Cost=715 Card=1 Bytes=124)
   21 SORT (UNIQUE) (Cost=662 Card=1 Bytes=124)
   32   NESTED LOOPS (Cost=609 Card=1 Bytes=124)
   43 NESTED LOOPS (Cost=553 Card=1 Bytes=118)
   54   NESTED LOOPS (Cost=550 Card=1 Bytes=106)
   65 NESTED LOOPS (Cost=280 Card=30 Bytes=1830)
   76   VIEW OF 'DUALBLASTRESULTS' (Cost=112 Card=168
  Bytes=8232)

   87 UNION-ALL
   98   TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES
  ULTS' (Cost=102 Card=118 Bytes=2360)

  109 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SUBJ
  ID_INDEX' (NON-UNIQUE) (Cost=3 Card=118)

  118   TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES
  ULTS' (Cost=10 Card=50 Bytes=1000)

  12   11 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QUER
  YID_INDEX' (NON-UNIQUE) (Cost=3 Card=50)

  136   TABLE ACCESS (BY INDEX ROWID) OF 'SEQTABLE' (C
  ost=1 Card=57344 Bytes=688128)

  14   13 INDEX (UNIQUE SCAN) OF 'ST_ASI_UN' (UNIQUE)
  155 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIS
  T' (Cost=9 Card=499 Bytes=22455)

  16   15   INDEX (RANGE SCAN) OF 'OBSERVATIONLISTGENEID'
  (NON-UNIQUE) (Cost=2 Card=499)

  174   TABLE ACCESS (BY INDEX ROWID) OF 'TERMOBS' (Cost=3
   Card=2388115 Bytes=28657380)

  18   17 INDEX (RANGE SCAN) OF 'TERMOBSIDINDEX' (NON-UNIQ
  UE) (Cost=2 Card=2388115)

  193 VIEW OF 'VW_NSO_1' (Cost=56 Card=7 Bytes=42)
  20   19   SORT (UNIQUE) (Cost=56 Card=7 Bytes=126)
  21   20 FILTER
  22   21   CONNECT BY
  23   22 INDEX (RANGE SCAN) OF 'ARC_TERMID' (NON-UNIQ
  UE) (Cost=1 Card=2 Bytes=12)

  24   22 TABLE ACCESS (BY USER ROWID) OF 'ARC'
  25   22 INDEX (RANGE SCAN) OF 'ARC_TYPETERMPARENT' (
  UNIQUE) (Cost=3 Card=8 Bytes=144)



SQL> select count(*) from mt.dualblastresults;

  COUNT(*)
--
  22332188

SQL> select count(*) from mt.seqtable ;

  COUNT(*)
--
373505

SQL> select count(*) from isi.observationlist;

  COUNT(*)
--
   2290858

SQL> select count(*) from isi.termobs;

  COUNT(*)
--
   2388115

SQL> select count(*) from isi.arc;

  COUNT(*)
--
207375

SQL> desc mt.dualblastresults
 Name  Null?Type
 -  ---
 ID NUMBER
 QUERYIDNUMBER
 SUBJID NUMBER
 MATCHLEN   NUMBER
 IDENTITY   NUMBER
 POSITIVE   NUMBER
 GAPNUMBER
 PVAL   VARCHAR2(16)
 SCORE  NUMBER
 QUERYSTART NUMBER
 QUERYEND

Re: SQL Query Help

[Mark Richard]

This latest alternative will clearly only work when the latest id is known
- I doubt this is reality.

There was a mention that the actual query is more complex - without seeing
that complexity it's hard to offer further advice.  RWB has provided the
correct approach normally taken.  If the date is indexed then Oracle can
determine the max date very easily - the query looks worse than reality.

Another approach (which is normally worse though) is to order the query by
date then wrap a "select * from (inner_query} where rownum = 1" around it.
It's clunky though and probably isn't what you are after.



   
   
  "Daniel W. Fink" 
   
  <[EMAIL PROTECTED]To:   Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>  
  .com>cc: 
   
      Sent by: Subject:  Re: SQL Query Help
   
  [EMAIL PROTECTED]

   
   
   
   
  29/05/2003 12:54 
   
  Please respond to
   
  ORACLE-L 
   
   
   
   
   




SELECT ID,MAX(LastModDate)
FROM  Tab
where id = 1
group by ID;

--
Daniel W. Fink
http://www.optimaldba.com


Basavaraja, Ravindra wrote:

>I had tried this as per Oracle Metalink Note.When I add the group by ID it
will display both the records like
>2   1/20/2003 2:56:18 AM
>1   4/23/2003 10:26:42 PM
>
>but I want to see the only the record with the latest time stamp like
>1   4/23/2003 10:26:42 PM
>
>-Original Message-
>Sent: Wednesday, May 28, 2003 6:25 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Add "group by ID;" as in:
>
>
>SELECT ID,MAX(LastModDate) FROM  Tab
>group by ID;
>
>
>
>>-Original Message-
>>From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]
>>Sent: Wednesday, May 28, 2003 8:15 PM
>>To: Multiple recipients of list ORACLE-L
>>Subject: SQL Query Help
>>
>>
>>i have a query that returns 2 rows with one column being id
>>and the other
>>being date-time stamp.
>>
>>i want to select the row with the latest timestamp among those two
>>records.they have difference id values
>>
>>SELECT ID,LastModDate
>>FROM  Tab
>>
>>ID LastModDate
>>-- ---
>>2  1/20/2003 2:56:18 AM
>>1  4/23/2003 10:26:42 PM
>>
>>I want to modify the above query to return the row with id=1
>>which has the
>>latest timestamp
>>
>>I tried this
>>
>>SELECT ID,MAX(LastModDate)
>>FROM  Tab
>>
>>getting this error
>>OERR: ORA 937 not a single-group group function
>>
>>how do i get this work.
>>
>>Thanks
>>
>>
>>


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Daniel W. Fink
  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: SQL Query Help

[Daniel W. Fink]

SELECT ID,MAX(LastModDate)
FROM  Tab
where id = 1
group by ID;
--
Daniel W. Fink
http://www.optimaldba.com
Basavaraja, Ravindra wrote:

I had tried this as per Oracle Metalink Note.When I add the group by ID it will 
display both the records like
2   1/20/2003 2:56:18 AM
1   4/23/2003 10:26:42 PM
but I want to see the only the record with the latest time stamp like
1   4/23/2003 10:26:42 PM
-Original Message-
Sent: Wednesday, May 28, 2003 6:25 PM
To: Multiple recipients of list ORACLE-L
Add "group by ID;" as in:

SELECT ID,MAX(LastModDate) FROM  Tab
group by ID;
 

-Original Message-
From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 28, 2003 8:15 PM
To: Multiple recipients of list ORACLE-L
Subject: SQL Query Help
i have a query that returns 2 rows with one column being id 
and the other
being date-time stamp.

i want to select the row with the latest timestamp among those two
records.they have difference id values
SELECT ID,LastModDate
FROM  Tab 

ID  LastModDate
--  ---
2   1/20/2003 2:56:18 AM
1   4/23/2003 10:26:42 PM
I want to modify the above query to return the row with id=1 
which has the
latest timestamp

I tried this

SELECT ID,MAX(LastModDate)
FROM  Tab
getting this error
OERR: ORA 937 not a single-group group function
how do i get this work.

Thanks

   



--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Daniel W. Fink
 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: SQL Query Help

[Basavaraja, Ravindra]

This will work.But the actual query that I have is a join b/w two tables
and not as simple as it looks in the example I had given 

SELECT ID,LastModDate
FROM  Tab

I bascially will have to include my complete main query in the sub query with 
TRUNC(MAX
This might be costly on the performance.

do we have any other approach.

thanks



-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 28, 2003 7:08 PM
To: Multiple recipients of list ORACLE-L



RB:

Try :

SELECT ID,  LastModDate
FROM Tab a
WHERE TRUNC(LastModDate) = (Select MAX(TRUNC(LastModDate)) From  Tab b)


The explanation of the error message follows.

RWB

===
|---+--->
|   |   |
|---+--->
  >---|
  |Error:  ORA 937|
  |   |
  |   |
  |Text:   not a single-group group function  |
  |   |
  |   |
  |---|
  |   |
  |   |
  |Cause:  A SELECT list cannot include both a group function, such as AVG, COUNT,|
  |   |
  |   |
  | MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column  |
  |   |
  |   |
  |expression, unless the individual column expression is included in a   |
  |   |
  |   |
  |GROUP BY clause.   |
  |   |
  |   |
  |Action: Drop either the group function or the individual column expression |
  |   |
  |   |
  |from the SELECT list or add a GROUP BY clause that includes all|
  |   |
  |   |
  |individual column expressions listed.  |
  |   |
  |   |
  |.  |
  >---|






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]  

Mobile.com   To: [EMAIL PROTECTED] 
  
Sent by: cc:   
 
[EMAIL PROTECTED] Subject:     SQ

RE: SQL Query Help

[Basavaraja, Ravindra]

I had tried this as per Oracle Metalink Note.When I add the group by ID it will 
display both the records like
2   1/20/2003 2:56:18 AM
1   4/23/2003 10:26:42 PM

but I want to see the only the record with the latest time stamp like
1   4/23/2003 10:26:42 PM

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


Add "group by ID;" as in:


SELECT ID,MAX(LastModDate) FROM  Tab
group by ID;

> -Original Message-
> From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, May 28, 2003 8:15 PM
> To: Multiple recipients of list ORACLE-L
> Subject: SQL Query Help
> 
> 
> i have a query that returns 2 rows with one column being id 
> and the other
> being date-time stamp.
> 
> i want to select the row with the latest timestamp among those two
> records.they have difference id values
> 
> SELECT ID,LastModDate
> FROM  Tab 
> 
> IDLastModDate
> -----
> 2 1/20/2003 2:56:18 AM
> 1 4/23/2003 10:26:42 PM
> 
> I want to modify the above query to return the row with id=1 
> which has the
> latest timestamp
> 
> I tried this
> 
> SELECT ID,MAX(LastModDate)
> FROM  Tab
> 
> getting this error
> OERR: ORA 937 not a single-group group function
> 
> how do i get this work.
> 
> Thanks
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Murray, Margaret
  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: Basavaraja, Ravindra
  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: SQL Query Help

[Reginald . W . Bailey]

RB:

Try :

SELECT ID,  LastModDate
FROM Tab a
WHERE TRUNC(LastModDate) = (Select MAX(TRUNC(LastModDate)) From  Tab b)


The explanation of the error message follows.

RWB

===
|---+--->
|   |   |
|---+--->
  >---|
  |Error:  ORA 937|
  |   |
  |   |
  |Text:   not a single-group group function  |
  |   |
  |   |
  |---|
  |   |
  |   |
  |Cause:  A SELECT list cannot include both a group function, such as AVG, COUNT,|
  |   |
  |   |
  | MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column  |
  |   |
  |   |
  |expression, unless the individual column expression is included in a   |
  |   |
  |   |
  |GROUP BY clause.   |
  |   |
  |   |
  |Action: Drop either the group function or the individual column expression |
  |   |
  |   |
  |from the SELECT list or add a GROUP BY clause that includes all|
  |   |
  |   |
  |individual column expressions listed.  |
  |   |
  |   |
  |.  |
  >---|






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]  

Mobile.com   To: [EMAIL PROTECTED] 
  
Sent by: cc:   
 
[EMAIL PROTECTED] Subject:     SQL Query Help  
  
   
 
   
 
05/28/2003 07:14 PM
 
Please respond to  
 

RE: SQL Query Help

[Murray, Margaret]

Add "group by ID;" as in:


SELECT ID,MAX(LastModDate) FROM  Tab
group by ID;

> -Original Message-
> From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, May 28, 2003 8:15 PM
> To: Multiple recipients of list ORACLE-L
> Subject: SQL Query Help
> 
> 
> i have a query that returns 2 rows with one column being id 
> and the other
> being date-time stamp.
> 
> i want to select the row with the latest timestamp among those two
> records.they have difference id values
> 
> SELECT ID,LastModDate
> FROM  Tab 
> 
> IDLastModDate
> -----
> 2 1/20/2003 2:56:18 AM
> 1 4/23/2003 10:26:42 PM
> 
> I want to modify the above query to return the row with id=1 
> which has the
> latest timestamp
> 
> I tried this
> 
> SELECT ID,MAX(LastModDate)
> FROM  Tab
> 
> getting this error
> OERR: ORA 937 not a single-group group function
> 
> how do i get this work.
> 
> Thanks
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Murray, Margaret
  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).

SQL Query Help

[Basavaraja, Ravindra]

i have a query that returns 2 rows with one column being id and the other
being date-time stamp.

i want to select the row with the latest timestamp among those two
records.they have difference id values

SELECT ID,LastModDate
FROM  Tab 

ID  LastModDate
--  ---
2   1/20/2003 2:56:18 AM
1   4/23/2003 10:26:42 PM

I want to modify the above query to return the row with id=1 which has the
latest timestamp

I tried this

SELECT ID,MAX(LastModDate)
FROM  Tab

getting this error
OERR: ORA 937 not a single-group group function

how do i get this work.

Thanks

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Basavaraja, Ravindra
  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: SQL Query -- List of managers

[Vladimir Begun]

Vladimir Begun wrote:
 SELECT LTRIM(SUBSTR(SYS_CONNECT_BY_PATH(RPAD(ename, 20, ' '), '/'), 2, 
typo: ^RTRIM
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
 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: SQL Query -- List of managers

[Vladimir Begun]

Hello

9i (ORA-01489! be aware):

 SELECT LTRIM(SUBSTR(SYS_CONNECT_BY_PATH(RPAD(ename, 20, ' '), '/'), 2, 19)) ename
  , ename mgrs
   FROM emp
CONNECT BY PRIOR mgr = empno
/
Eberhard, Jeff wrote:
Using the EMP table as an example I want to create a query that will show a
list of employees and the mgrs above them.  Like this:
ENAME   MGRS 
--- -- 
SMITH   SMITH
SMITH   FORD 
SMITH   JONES  
SMITH   KING  
..
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
 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).

SQL Query -- List of managers

[Eberhard, Jeff]

Using the EMP table as an example I want to create a query that will show a
list of employees and the mgrs above them.  Like this:

ENAME   MGRS 
--- -- 
SMITH   SMITH
SMITH   FORD 
SMITH   JONES  
SMITH   KING  
ALLEN   ALLEN 
ALLEN   BLAKE   
ALLEN   KING
WARDWARD
WARDBLAKE   
WARDKING
JONES   JONES   
JONES   KING
MARTIN  MARTIN  
MARTIN  BLAKE   
MARTIN  KING
BLAKE   BLAKE   
BLAKE   KING
CLARK   CLARK   
CLARK   KING
SCOTT   SCOTT   
SCOTT   JONES   
SCOTT   KING
KINGKING
TURNER  TURNER  
TURNER  BLAKE   
TURNER  KING
ADAMS   ADAMS   
ADAMS   SCOTT   
ADAMS   JONES   
ADAMS   KING
JAMES   JAMES   
JAMES   BLAKE   
JAMES   KING
FORDFORD
FORDJONES   
FORDKING
MILLER  MILLER  
MILLER  CLARK   
MILLER  KING





So far I've got it to this:

  1  select lpad(' ',3*level-3)||ename org_char, leve
  2  empno, mgr
  3  from emp
  4* connect by prior mgr = empno

ORG_CHAR LEVEL  EMPNOMGR
--- -- -- --
SMITH1   7369   7902
   FORD  2   7902   7566
  JONES  3   7566   7839
 KING4   7839
ALLEN1   7499   7698
   BLAKE 2   7698   7839
  KING   3   7839
WARD 1   7521   7698
   BLAKE 2   7698   7839
  KING   3   7839
JONES1   7566   7839
   KING  2   7839
MARTIN   1   7654   7698
   BLAKE 2   7698   7839
  KING   3   7839
BLAKE1   7698   7839
   KING  2   7839
CLARK1   7782   7839
   KING  2   7839
SCOTT1   7788   7566
   JONES 2   7566   7839
  KING   3   7839
KING 1   7839
TURNER   1   7844   7698
   BLAKE 2   7698   7839
  KING   3   7839
ADAMS1   7876   7788
   SCOTT 2   7788   7566
  JONES  3   7566   7839
 KING4   7839
JAMES1   7900   7698
   BLAKE 2   7698   7839
  KING   3   7839
FORD 1   7902   7566
   JONES 2   7566   7839
  KING   3   7839
MILLER   1   7934   7782
   CLARK 2   7782   7839
  KING   3   7839

39 rows selected.



Which brain cell am I missing today that will help me get what I want?  


Thanks,
Jeff Eberhard
Database Administrator
Rolls-Royce Gear Systems


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Eberhard, Jeff
  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: sql query

[DENNIS WILLIAMS]

Reddy
   There are a variety of techniques to accomplish this, and it is difficult
to determine which will work best in your situation. Here is a link to a
classic article on this topic by the great Jonathan Gennick who participates
in this list from time to time.
http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html

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


-Original Message-
Sent: Monday, March 31, 2003 12:09 AM
To: Multiple recipients of list ORACLE-L


hi,

i have a table with indent number and quantity with 10 rows
ex ind_no qty
 1  10
 1  12
 1  30
 1  15
 1  30

 2  12
 2  30
 2  15
 2  30
 2  25

 2
I have to dispay it as rows
like
   1  10   12   30   15   30
   2  12   30   15   30   25

please help,

regds,
Sudhakar

___
Odomos - the only  mosquito protection outside 4 walls -
Click here to know more!
http://r.rediff.com/r?http://clients.rediff.com/odomos/Odomos.htm&&odomos&&w
n

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

sql query

[sudhakar Reddy]

hi,

i have a table with indent number and quantity with 10 rows
ex ind_no qty
 1  10
 1  12
 1  30
 1  15
 1  30

 2  12
 2  30
 2  15
 2  30
 2  25

 2
I have to dispay it as rows
like
   1  10   12   30   15   30
   2  12   30   15   30   25

please help,

regds,
Sudhakar

___
Odomos - the only  mosquito protection outside 4 walls -
Click here to know more!
http://r.rediff.com/r?http://clients.rediff.com/odomos/Odomos.htm&&odomos&&wn

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: sudhakar  Reddy
  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: sql query: to_date() :ORA-01830: date format picture ends bef

[Abdul Aleem]

Ravindra,

All the solutions given by others are correct.

The reason for the error is that: In your where clause the date that you are
converting to char, is re-converted to date for comparison with a date
value. This conversion uses Oracle's implicit date conversion, the implicit
date conversion uses current NLS setting which by default is of two digit
year and without time component.

So if you remove time and its format from your
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS'), it should work. Or if you
change your NLS settings to include time component the same command should
work.

HTH!

Aleem


 -Original Message-
Sent:   Wednesday, March 12, 2003 1:20 AM
To: Multiple recipients of list ORACLE-L
Subject:    sql query: to_date() :ORA-01830: date format picture ends
before 

Hi...

I am getting the following error with a query like this

select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')

ORA-01830: date format picture ends before converting entire input string

What is the problem?

Thanks
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Basavaraja, Ravindra
  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: Abdul Aleem
  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: sql query: to_date() :ORA-01830: date format picture ends before

[Sergey V Dolgov]

Hello Ravindra,
You should use to_char OR to_data not both.

select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') = '01-FEB-03 07:53:28'

Wednesday, March 12, 2003, 2:20:17 AM, you wrote:

BR> Hi...

BR> I am getting the following error with a query like this

BR> select * from cust where
BR> to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
BR> to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')

BR> ORA-01830: date format picture ends before converting entire input string

BR> What is the problem?

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



-- 
Best regards,
 Sergeymailto:[EMAIL PROTECTED]
 ICQ 160079606


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sergey V Dolgov
  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: sql query: to_date() :ORA-01830: date format picture ends before

[Igor Neyman]

Why are trying to compare "date" to "string": to_char(...) = to_date(...) ?

Igor Neyman, OCP DBA
[EMAIL PROTECTED]
  


- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, March 11, 2003 3:20 PM


> Hi...
> 
> I am getting the following error with a query like this
> 
> select * from cust where
> to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
> to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
> 
> ORA-01830: date format picture ends before converting entire input string
> 
> What is the problem?
> 
> Thanks
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Basavaraja, Ravindra
>   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: Igor Neyman
  INET: [EMAIL PROTECTED]

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

Re: sql query: to_date() :ORA-01830: date format picture ends before

[Stephane Faroult]

"Basavaraja, Ravindra" wrote:
> 
> Hi...
> 
> I am getting the following error with a query like this
> 
> select * from cust where
> to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
> to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
> 
> ORA-01830: date format picture ends before converting entire input string
> 
> What is the problem?
> 
> Thanks

You are comparing apples with pears.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  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: sql query: to_date() :ORA-01830: date format picture ends bef

[Weiss, Rick]

You are comparing CHAR to DATE, you would need one of the following
conditions

1- where DATECREATED = to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')

2- where to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') = '01-FEB-03 07:53:28'

to be able to complete the WHERE clause

Rick Weiss
Oracle DBA


-Original Message-
Sent: Tuesday, March 11, 2003 13:20
To: Multiple recipients of list ORACLE-L


Hi...

I am getting the following error with a query like this

select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') = to_date('01-FEB-03
07:53:28','DD-MON-YY HH24:MI:SS')

ORA-01830: date format picture ends before converting entire input string

What is the problem?

Thanks
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Weiss, Rick
  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: sql query: to_date() :ORA-01830: date format picture ends bef

[Richard Ji]

What's the to_char for?

try

select * from cust where
DATECREATED = to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')

-Original Message-
Sent: Tuesday, March 11, 2003 12:20 PM
To: Multiple recipients of list ORACLE-L
before 


Hi...

I am getting the following error with a query like this

select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')

ORA-01830: date format picture ends before converting entire input string

What is the problem?

Thanks
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Basavaraja, Ravindra
  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 Ji
  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: sql query: to_date() :ORA-01830: date format picture ends bef

[Jamadagni, Rajendra]

Title: RE: sql query: to_date() :ORA-01830: date format picture ends before 





Why are you comparing a date to a char? 


select * 
  from cust
 where to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') = '01-FEB-03 07:53:28'
/


should work  it appears that your NLS_DATE_FORMAT kicked in when your query tried to do an implicit char to date conversion (on the LHS part of the where clause)

Raj
-
Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !!



-Original Message-
From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 11, 2003 3:20 PM
To: Multiple recipients of list ORACLE-L
Subject: sql query: to_date() :ORA-01830: date format picture ends
before 



Hi...


I am getting the following error with a query like this


select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')


ORA-01830: date format picture ends before converting entire input string


What is the problem?


Thanks
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Basavaraja, Ravindra
  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.*2

sql query: to_date() :ORA-01830: date format picture ends before

[Basavaraja, Ravindra]

Hi...

I am getting the following error with a query like this

select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')

ORA-01830: date format picture ends before converting entire input string

What is the problem?

Thanks
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Basavaraja, Ravindra
  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: sql query: to_date() :ORA-01830: date format picture ends bef

[Basavaraja, Ravindra]

Correction

I am using 

select * from cust where
to_char(DATECREATED,'DD-MON-YY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')

ORA-01830: date format picture ends before converting entire input string
>  -Original Message-
> From: Basavaraja, Ravindra  
> Sent: Tuesday, March 11, 2003 12:19 PM
> To:   'Multiple recipients of list ORACLE-L'
> Subject:  sql query: to_date() :ORA-01830: date format picture ends before 
> converting entire input string
> 
> Hi...
> 
> I am getting the following error with a query like this
> 
> select * from cust where
> to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
> to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
> 
> ORA-01830: date format picture ends before converting entire input string
> 
> What is the problem?
> 
> Thanks
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Basavaraja, Ravindra
  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: Sql query

[Sony kristanto]

Hi Roland,

I make a little modification to your query below,

SELECT distinct PBK.VARUKORGEANREL_ULAG.ean, 0, 0, 0,
rik2.vare.varenavn, rik2.vare.str,
  PBK.VARUKORGEANREL_ULAG.lagstapris,
rik2.vare.hylletxt2, rik2.art_hierarki.vgrp, rik2.hierarki_tekst.tekst,
  '0', '0', 0, pbk.underlag.period,
'rsm',pbk.sortiment_vgrp.sortiment,rik2.art_hierarki.art_grp,rik2.art_hierar
ki.art_ugrp
FROM PBK.UNDERLAG, PBK.VARUKORGEANREL_ULAG, rik2.vare,
rik2.hierarki_tekst, rik2.art_hierarki,
  pbk.sortiment_vgrp
WHERE PBK.underlag.underlagid=1100
AND   PBK.underlag.varukorgid=PBK.varukorgeanrel_ulag.varukorgid
ANDPBK.VARUKORGEANREL_ulag.varutyp=0

I think you miss to join another four tables,
AND   RIK2.VARE = RIK2.HIERARKI_TEKST
AND   RIK2.HIERARKI_TEKST = RIK2.ART_HIERARKI
AND   RIK2.ART_HIERARKI = PBK..SOTIMENT_VGRP;

Hope this is what you mean.

Rgrds,

Sony


> -Original Message-
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, February 05, 2003 9:53 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  Sql query
> 
> 
> I have  this sql query. I am wondering why this query takes so long time,
> Do I need more conditions to make it run, or it it just that this query
> take so long time to run? Anything wrong with the query?
> 
> Please help me with this.
> 
>  SELECT distinct PBK.VARUKORGEANREL_ULAG.ean, 0, 0, 0, rik2.vare.varenavn,
> rik2.vare.str, PBK.VARUKORGEANREL_ULAG.lagstapris, rik2.vare.hylletxt2,
> rik2.art_hierarki.vgrp, rik2.hierarki_tekst.tekst, '0', '0', 0,
> pbk.underlag.period,
> 'rsm',pbk.sortiment_vgrp.sortiment,rik2.art_hierarki.art_grp,rik2.art_hier
> arki.art_ugrp
>  FROM PBK.UNDERLAG, PBK.VARUKORGEANREL_ULAG, rik2.vare,
> rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp
>  WHERE PBK.underlag.underlagid=1100
>  AND PBK.underlag.varukorgid=PBK.varukorgeanrel_ulag.varukorgid
>  AND PBK.VARUKORGEANREL_ulag.varutyp=0;
> 
> 
> Thanks in advance
> 
> 
> Roland
> 
> 
> 
> -- 
> 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: Sony kristanto
  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: Sql query

[Nicoll, Iain]

Roland,

A quick look suggest you have cartesian joins unless rik2.vare,
rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp all have only one
row.

Iain Nicoll

-Original Message-
Sent: 05 February 2003 14:53
To: Multiple recipients of list ORACLE-L



I have  this sql query. I am wondering why this query takes so long time, Do
I need more conditions to make it run, or it it just that this query take so
long time to run? Anything wrong with the query?

Please help me with this.

 SELECT distinct PBK.VARUKORGEANREL_ULAG.ean, 0, 0, 0, rik2.vare.varenavn,
rik2.vare.str, PBK.VARUKORGEANREL_ULAG.lagstapris, rik2.vare.hylletxt2,
rik2.art_hierarki.vgrp, rik2.hierarki_tekst.tekst, '0', '0', 0,
pbk.underlag.period,
'rsm',pbk.sortiment_vgrp.sortiment,rik2.art_hierarki.art_grp,rik2.art_hierar
ki.art_ugrp
 FROM PBK.UNDERLAG, PBK.VARUKORGEANREL_ULAG, rik2.vare,
rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp
 WHERE PBK.underlag.underlagid=1100
 AND PBK.underlag.varukorgid=PBK.varukorgeanrel_ulag.varukorgid
 AND PBK.VARUKORGEANREL_ulag.varutyp=0;


Thanks in advance


Roland



-- 
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: Nicoll, Iain
  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: Sql query

[Igor Neyman]

There is no "join" condition between first two tables (PBK.UNDERLAG,
PBK.VARUKORGEANREL_ULAG) and last four tables (rik2.vare,
rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp).

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, February 05, 2003 9:52 AM


>
> I have  this sql query. I am wondering why this query takes so long time,
Do I need more conditions to make it run, or it it just that this query take
so long time to run? Anything wrong with the query?
>
> Please help me with this.
>
>  SELECT distinct PBK.VARUKORGEANREL_ULAG.ean, 0, 0, 0, rik2.vare.varenavn,
rik2.vare.str, PBK.VARUKORGEANREL_ULAG.lagstapris, rik2.vare.hylletxt2,
rik2.art_hierarki.vgrp, rik2.hierarki_tekst.tekst, '0', '0', 0,
pbk.underlag.period,
>
'rsm',pbk.sortiment_vgrp.sortiment,rik2.art_hierarki.art_grp,rik2.art_hierar
ki.art_ugrp
>  FROM PBK.UNDERLAG, PBK.VARUKORGEANREL_ULAG, rik2.vare,
rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp
>  WHERE PBK.underlag.underlagid=1100
>  AND PBK.underlag.varukorgid=PBK.varukorgeanrel_ulag.varukorgid
>  AND PBK.VARUKORGEANREL_ulag.varutyp=0;
>
>
> Thanks in advance
>
>
> Roland
>
>
>
> --
> 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: Igor Neyman
  INET: [EMAIL PROTECTED]

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

RE: Sql query

[Charu Joshi]

Hi Roland,

Is the query producing right results in the first place?

If there are n tables in the FROM clause, there must be atleast n-1 joins in
the where clause to avoid the Cartesian product (which definitely screws up
performance and rarely produces correct results.)

In the FROM clause of your query, there are 6 tables, whereas in the WHERE
clause there is only 1 join condition.

HTH.

Regards,
Charu

-Original Message-
[EMAIL PROTECTED]
Sent: Wednesday, February 05, 2003 6:24 PM
To: Multiple recipients of list ORACLE-L

I have  this sql query. I am wondering why this query takes so long time, Do
I need more conditions to make it run, or it it just that this query take so
long time to run? Anything wrong with the query?

Please help me with this.

 SELECT distinct PBK.VARUKORGEANREL_ULAG.ean, 0, 0, 0, rik2.vare.varenavn,
rik2.vare.str, PBK.VARUKORGEANREL_ULAG.lagstapris, rik2.vare.hylletxt2,
rik2.art_hierarki.vgrp, rik2.hierarki_tekst.tekst, '0', '0', 0,
pbk.underlag.period,
'rsm',pbk.sortiment_vgrp.sortiment,rik2.art_hierarki.art_grp,rik2.art_hierar
ki.art_ugrp
 FROM PBK.UNDERLAG, PBK.VARUKORGEANREL_ULAG, rik2.vare,
rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp
 WHERE PBK.underlag.underlagid=1100
 AND PBK.underlag.varukorgid=PBK.varukorgeanrel_ulag.varukorgid
 AND PBK.VARUKORGEANREL_ulag.varutyp=0;

Thanks in advance

Roland

--
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).

*
Disclaimer

This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.

*
Visit us at http://www.mahindrabt.com



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

Sql query

[roland . skoldblom]

I have  this sql query. I am wondering why this query takes so long time, Do I need 
more conditions to make it run, or it it just that this query take so long time to 
run? Anything wrong with the query?

Please help me with this.

 SELECT distinct PBK.VARUKORGEANREL_ULAG.ean, 0, 0, 0, rik2.vare.varenavn, 
rik2.vare.str, PBK.VARUKORGEANREL_ULAG.lagstapris, rik2.vare.hylletxt2, 
rik2.art_hierarki.vgrp, rik2.hierarki_tekst.tekst, '0', '0', 0, pbk.underlag.period,
'rsm',pbk.sortiment_vgrp.sortiment,rik2.art_hierarki.art_grp,rik2.art_hierarki.art_ugrp
 FROM PBK.UNDERLAG, PBK.VARUKORGEANREL_ULAG, rik2.vare, rik2.hierarki_tekst, 
rik2.art_hierarki, pbk.sortiment_vgrp
 WHERE PBK.underlag.underlagid=1100
 AND PBK.underlag.varukorgid=PBK.varukorgeanrel_ulag.varukorgid
 AND PBK.VARUKORGEANREL_ulag.varutyp=0;


Thanks in advance


Roland



-- 
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: SQL query without UNION clause

[Jan Pruner]

I think you can translate it as OR, but you have to use some DISTINCT on 
output rows (because you use UNION and not UNION ALL).
Without DISTINCT it is like:
SELECT 
H1.OID HISTORIEOID
,FAHRZEUG.AMTLICHESKENNZEICHEN
,FAHRZEUG.OID
,H1.PRODUKT
,H1.AUFTRAGSPOSITIONSNR
,H1.MYTECHOBJEKT
FROM FAHRZEUG, HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND (   (TO_DATE(H1.DATUMSTR,'-MM-DD') = (
select max(TO_DATE(H1.DATUMSTR,'-mm-dd')) from historie, FAHRZEUG 
WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT 
AND HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN ('HU', 'AU')
)
OR
H1.PRODUKT IN('DS', 'NFZ','BES', 'DekraSiegel', '70', '700202'))
;

And I think you can change
select max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
to
select TO_DATE(MAX(H1.DATUMSTR),'-MM-DD')
it can now use index on column H1.DATUMSTR (or create function based index)

JP


On Thursday 19 December 2002 08:53, you wrote:
> Hi all,
>
> How do I forumlate the below query without using the UNION clause?
>
> SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
> FAHRZEUG.OID,
> H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
> FROM FAHRZEUG,HISTORIE H1
> WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
> AND TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
> max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
> from historie ,FAHRZEUG WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT AND
> HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
> AND H1.PRODUKT IN('HU', 'AU')
> UNION
> SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
> FAHRZEUG.OID,
> H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
> FROM FAHRZEUG,HISTORIE H1
> WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+) AND H1.PRODUKT IN('DS', 'NFZ',
> 'BES', 'DekraSiegel', '70', '700202');
>
> I can't use the union clause because I am using an Object-Relationship tool
> called Cocobase which doesn't support the UNION clause.  Any help in this
> regard is very much appreciated.
>
> Thanks and Regards,
>
> Ranganath
>
> WARNING: The information in this message is confidential and may be legally
> privileged. It is intended solely for the addressee.  Access to this
> message by anyone else is unauthorised.  If you are not the intended
> recipient, any disclosure, copying, or distribution of the message, or any
> action or omission taken by you in reliance on it, is prohibited and may be
> unlawful. Please immediately contact the sender if you have received this
> message in error. Thank you.

-- 
 Pruner Jan
   [EMAIL PROTECTED]
 http://jan.pruner.cz/
-
Only Robinson Crusoe had all his work done by Friday
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jan Pruner
  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: SQL query without UNION clause

[Naveen Nahata]

Try this,

SELECT DISTINCT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT

FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND
((TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
from historie ,FAHRZEUG WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT AND
HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN('HU', 'AU')) OR H1.PRODUKT IN('DS', 'NFZ',
'BES', 'DekraSiegel', '70', '700202'))

Regards
Naveen

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 1:24 PM
To: Multiple recipients of list ORACLE-L


Hi all,

How do I forumlate the below query without using the UNION clause?

SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
from historie ,FAHRZEUG WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT AND
HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN('HU', 'AU')
UNION
SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+) AND H1.PRODUKT IN('DS', 'NFZ',
'BES', 'DekraSiegel', '70', '700202');

I can't use the union clause because I am using an Object-Relationship tool
called Cocobase which doesn't support the UNION clause.  Any help in this
regard is very much appreciated.

Thanks and Regards,

Ranganath

WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee.  Access to this message
by anyone else is unauthorised.  If you are not the intended recipient, any
disclosure, copying, or distribution of the message, or any action or
omission taken by you in reliance on it, is prohibited and may be unlawful.
Please immediately contact the sender if you have received this message in
error. Thank you.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Krishnaswamy, Ranganath
  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: Naveen Nahata
  INET: [EMAIL PROTECTED]

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

RE: SQL query without UNION clause

[Stephane Faroult]

You would be surprised to discover what you could do with OR and suitably placed 
parentheses. 

>- Original Message -
>From: "Krishnaswamy, Ranganath"
><[EMAIL PROTECTED]>
>To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
>Sent: Wed, 18 Dec 2002 23:53:44
>
>Hi all,
>
>How do I forumlate the below query without using
>the UNION clause?
>
>SELECT H1.OID
>HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
>FAHRZEUG.OID,
>H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
>FROM FAHRZEUG,HISTORIE H1
>WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
>AND TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
>max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
>from historie ,FAHRZEUG WHERE FAHRZEUG.OID =
>HISTORIE.MYTECHOBJEKT AND
>HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
>AND H1.PRODUKT IN('HU', 'AU')
>UNION
>SELECT H1.OID
>HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
>FAHRZEUG.OID,
>H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
>FROM FAHRZEUG,HISTORIE H1
>WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+) AND
>H1.PRODUKT IN('DS', 'NFZ',
>'BES', 'DekraSiegel', '70', '700202');
>
>I can't use the union clause because I am using an
>Object-Relationship tool
>called Cocobase which doesn't support the UNION
>clause.  Any help in this
>regard is very much appreciated.
>
>Thanks and Regards,
>
>Ranganath
>
>WARNING: The information in this message is
>confidential and may be legally
>privileged. It is intended solely for the
>addressee.  Access to this message
>by anyone else is unauthorised.  If you are not the
>intended recipient, any
>disclosure, copying, or distribution of the
>message, or any action or
>omission taken by you in reliance on it, is
>prohibited and may be unlawful.
>Please immediately contact the sender if you have
>received this message in
>error. Thank you.
>-- 
>Please see the official ORACLE-L FAQ:
>http://www.orafaq.net
>-- 
>Author: Krishnaswamy, Ranganath
>  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).
>---
>--


Regards,

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

SQL query without UNION clause

[Krishnaswamy, Ranganath]

Hi all,

How do I forumlate the below query without using the UNION clause?

SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
from historie ,FAHRZEUG WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT AND
HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN('HU', 'AU')
UNION
SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+) AND H1.PRODUKT IN('DS', 'NFZ',
'BES', 'DekraSiegel', '70', '700202');

I can't use the union clause because I am using an Object-Relationship tool
called Cocobase which doesn't support the UNION clause.  Any help in this
regard is very much appreciated.

Thanks and Regards,

Ranganath

WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee.  Access to this message
by anyone else is unauthorised.  If you are not the intended recipient, any
disclosure, copying, or distribution of the message, or any action or
omission taken by you in reliance on it, is prohibited and may be unlawful.
Please immediately contact the sender if you have received this message in
error. Thank you.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Krishnaswamy, Ranganath
  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 with SQL query

[Nicoll, Iain]

Ranganath,

Apologies my reply directly to yourself got bounced for some reason.

A couple of questions.  How good a filter is the 

ZPAA.AUFTRAGSPOSITION.TAGESABSCHLUSSNUMMER  = :TAGESABSCHLUSSNUMMER

line.  And is the subquery done simply to ensure no duplicates.

Generally you want to drive from the table which has the best filter so if
the above was a good filter and is indexed you'd probably want to start with
that first.  I've generally found that if you know the tables data and the
order that you'd ideally like to join them then the use of the ordered hint
along with a use_nl and the correct ordering of the from clause gives the
optimal plan.

Iain Nicoll

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: 17 December 2002 05:29
To: Multiple recipients of list ORACLE-L


Hi all,

I have the following query which is running slow and doing
full table scan on DIENSTLEISTUNGSOBJEKT and FZGBRIEF tables.  I have
created all the neccessary indexes on these two tables apart from other
tables.  In this regard I request you to help me in tuning the below query
so that the full table scan is avoided and the response time is less.
> 
>
>  
> 
> select
> ZPAA.AUFTRAGSPOSITION.POSITIONSNUMMER,
> ZPAA.AUFTRAGSPOSITION.ZAHLUNGSART,
> ZPAA.AUFTRAGSPOSITION.ZAHLUNGSARTTYP,
> ZPAA.AUFTRAGSPOSITION.VORGANGSNUMMER,
> to_char( AUFTRAG.AUFTRAGERTEILTDATUM, 'DD-MM-'),
> ZPAA.AUFTRAG.AUFTRAGSNUMMER,
> ZPAA.FAHRZEUG.AMTLICHESKENNZEICHEN,
> ZPAA.FAHRZEUG.FZGHALTERNAME,
> ZPAA.FZGBRIEF.FAHRZEUGARTTEXT,
> ZPAA.RECHNUNG.PREISGRUPPE,
> NULL BETRAG,
> NULL QUITTUNGSNR,
> ZPAA.PLAKETTENERGEBNIS.PLAKETTENLAUFZEITSTR,
> ZPAA.PLAKETTENERGEBNIS.PLAKETTENZUTEILUNG,
> ZPAA.AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID,
> S.MATERIALNUMMER,
> S.ERZEUGNISNR,
> ZPAA.AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE,
> PARTNER_PRUEFORT.PARTNERNUMMER,
> PARTNER_MHS.PARTNERNUMMER,
> PARTNER_AG.PARTNERNUMMER,
> PARTNER_AG.NAME,
> PARTNER_SV.PARTNERNUMMER,
> S.SVDIENSTLEISTUNGOID,
> to_char(S.ERZEUGNISDATUM, 'DD-MM-'),
> S.ERGEBNIS,
> S.SPERGEBNIS,
> S.SPPLAKETTE,
> ZPAA.AUFTRAGSPOSITION.DIENSTLEISTUNGSGRUPPE,
> ZPAA.AUFTRAGSPOSITION.DIENSTLEISTUNGSBEZEICHNUNG,
> NULL  BETRAGOFFEN,
> NULL PARTNERNUMMER,
> NULL ENDEZEIT,
> NULL TAGESABSCHLUSSNUMMER
> from
> ZPAA.FAHRZEUG,
> ZPAA.AUFTRAG,
> ZPAA.AUFTRAGSPOSITION,
> ZPAA.FZGBRIEF,
> ZPAA.PLAKETTENERGEBNIS,
> ZPAA.AUFTRAGSPOSPOSITIONSSTATUS,
> ZPAA.DIENSTLEISTUNGSOBJEKT D,
> ZPAA.RECHNUNG,
> ZPAA.SVDIENSTLEISTUNG S,
> ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_PRUEFORT,
> ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_MHS,
> ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_AG,
> ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_SV
> where
> 
>  ZPAA.AUFTRAGSPOSITION.TAGESABSCHLUSSNUMMER  =
:TAGESABSCHLUSSNUMMER
> 
>  AND  AUFTRAG.VORGANGSNUMMER  =
AUFTRAGSPOSITION.VORGANGSNUMMER
> 
>  AND  AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID  =
S.AUFTRAGSPOSITIONOID
> 
>  AND  S.SVDIENSTLEISTUNGOID  =  D.SVDIENSTLEISTUNGOID
>  AND  D.DIENSTLEISTUNGSOBJEKTOID  
> FAHRZEUG.DIENSTLEISTUNGSOBJEKTOID
>  AND  D.SEQNO  =  (SELECT  E.SEQNO FROM
>  ZPAA.DIENSTLEISTUNGSOBJEKT E ,ZPAA.SVDIENSTLEISTUNG S1
>  WHERE S1.SVDIENSTLEISTUNGOID = E.SVDIENSTLEISTUNGOID
>  AND S1.SVDIENSTLEISTUNGOID = S.SVDIENSTLEISTUNGOID
>  AND ROWNUM < 2)
>  AND  FAHRZEUG.FAHRZEUGOID  =  FZGBRIEF.FAHRZEUGOID
>  AND  S.ERGEBNIS  =
PLAKETTENERGEBNIS.PLAKETTENERGEBNISOID(+)
>  AND  AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID  
> RECHNUNG.AUFTRAGSPOSITIONOID
>  AND  AUFTRAGSPOSITION.AKTUELLERSTATUS  
> AUFTRAGSPOSPOSITIONSSTATUS.AUFTRAGSPOSITIONSSTATUSOID
>  AND  (AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE  =  '6'
>  OR  AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE  =  '8')
>  AND  AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID  
> PARTNER_PRUEFORT.AUFTRAGSPOSITIONOID(+)
>  AND  PARTNER_PRUEFORT.ROLLENTYP(+)  =  'ZP'
>  AND  AUFTRAGSPOSITI

RE: Problem with SQL query

[Naveen Nahata]

In the explain plan's first line

SELECT STATEMENT Hint=HINT: RULE

But i don't see any rule hint in the SQL statement you have sent.

Analyze the tables/indexes, use a choose hint and run the SQL plan again.
What version you are on? what is the value of optimizer_mode, optimizer_goal

Regards
Naveen

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 17, 2002 10:59 AM
To: Multiple recipients of list ORACLE-L


Hi all,

I have the following query which is running slow and doing
full table scan on DIENSTLEISTUNGSOBJEKT and FZGBRIEF tables.  I have
created all the neccessary indexes on these two tables apart from other
tables.  In this regard I request you to help me in tuning the below query
so that the full table scan is avoided and the response time is less.
> 
>
>  
> 
> select
> ZPAA.AUFTRAGSPOSITION.POSITIONSNUMMER,
> ZPAA.AUFTRAGSPOSITION.ZAHLUNGSART,
> ZPAA.AUFTRAGSPOSITION.ZAHLUNGSARTTYP,
> ZPAA.AUFTRAGSPOSITION.VORGANGSNUMMER,
> to_char( AUFTRAG.AUFTRAGERTEILTDATUM, 'DD-MM-'),
> ZPAA.AUFTRAG.AUFTRAGSNUMMER,
> ZPAA.FAHRZEUG.AMTLICHESKENNZEICHEN,
> ZPAA.FAHRZEUG.FZGHALTERNAME,
> ZPAA.FZGBRIEF.FAHRZEUGARTTEXT,
> ZPAA.RECHNUNG.PREISGRUPPE,
> NULL BETRAG,
> NULL QUITTUNGSNR,
> ZPAA.PLAKETTENERGEBNIS.PLAKETTENLAUFZEITSTR,
> ZPAA.PLAKETTENERGEBNIS.PLAKETTENZUTEILUNG,
> ZPAA.AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID,
> S.MATERIALNUMMER,
> S.ERZEUGNISNR,
> ZPAA.AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE,
> PARTNER_PRUEFORT.PARTNERNUMMER,
> PARTNER_MHS.PARTNERNUMMER,
> PARTNER_AG.PARTNERNUMMER,
> PARTNER_AG.NAME,
> PARTNER_SV.PARTNERNUMMER,
> S.SVDIENSTLEISTUNGOID,
> to_char(S.ERZEUGNISDATUM, 'DD-MM-'),
> S.ERGEBNIS,
> S.SPERGEBNIS,
> S.SPPLAKETTE,
> ZPAA.AUFTRAGSPOSITION.DIENSTLEISTUNGSGRUPPE,
> ZPAA.AUFTRAGSPOSITION.DIENSTLEISTUNGSBEZEICHNUNG,
> NULL  BETRAGOFFEN,
> NULL PARTNERNUMMER,
> NULL ENDEZEIT,
> NULL TAGESABSCHLUSSNUMMER
> from
> ZPAA.FAHRZEUG,
> ZPAA.AUFTRAG,
> ZPAA.AUFTRAGSPOSITION,
> ZPAA.FZGBRIEF,
> ZPAA.PLAKETTENERGEBNIS,
> ZPAA.AUFTRAGSPOSPOSITIONSSTATUS,
> ZPAA.DIENSTLEISTUNGSOBJEKT D,
> ZPAA.RECHNUNG,
> ZPAA.SVDIENSTLEISTUNG S,
> ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_PRUEFORT,
> ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_MHS,
> ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_AG,
> ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_SV
> where
> 
>  ZPAA.AUFTRAGSPOSITION.TAGESABSCHLUSSNUMMER  =
:TAGESABSCHLUSSNUMMER
> 
>  AND  AUFTRAG.VORGANGSNUMMER  =
AUFTRAGSPOSITION.VORGANGSNUMMER
> 
>  AND  AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID  =
S.AUFTRAGSPOSITIONOID
> 
>  AND  S.SVDIENSTLEISTUNGOID  =  D.SVDIENSTLEISTUNGOID
>  AND  D.DIENSTLEISTUNGSOBJEKTOID  
> FAHRZEUG.DIENSTLEISTUNGSOBJEKTOID
>  AND  D.SEQNO  =  (SELECT  E.SEQNO FROM
>  ZPAA.DIENSTLEISTUNGSOBJEKT E ,ZPAA.SVDIENSTLEISTUNG S1
>  WHERE S1.SVDIENSTLEISTUNGOID = E.SVDIENSTLEISTUNGOID
>  AND S1.SVDIENSTLEISTUNGOID = S.SVDIENSTLEISTUNGOID
>  AND ROWNUM < 2)
>  AND  FAHRZEUG.FAHRZEUGOID  =  FZGBRIEF.FAHRZEUGOID
>  AND  S.ERGEBNIS  =
PLAKETTENERGEBNIS.PLAKETTENERGEBNISOID(+)
>  AND  AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID  
> RECHNUNG.AUFTRAGSPOSITIONOID
>  AND  AUFTRAGSPOSITION.AKTUELLERSTATUS  
> AUFTRAGSPOSPOSITIONSSTATUS.AUFTRAGSPOSITIONSSTATUSOID
>  AND  (AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE  =  '6'
>  OR  AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE  =  '8')
>  AND  AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID  
> PARTNER_PRUEFORT.AUFTRAGSPOSITIONOID(+)
>  AND  PARTNER_PRUEFORT.ROLLENTYP(+)  =  'ZP'
>  AND  AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID  
> PARTNER_MHS.AUFTRAGSPOSITIONOID(+)
>  AND  PARTNER_MHS.ROLLENTYP(+)  =  'MHS'
>  AND  AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID  
> PARTNER_AG.AUFTRAGSPOSITIONOID(+)
>  AND  PARTNER_AG.ROLLENTYP(+)  =  'AG'
>  AND  AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID  

Problem with SQL query

[Krishnaswamy, Ranganath]

Hi all,

I have the following query which is running slow and doing
full table scan on DIENSTLEISTUNGSOBJEKT and FZGBRIEF tables.  I have
created all the neccessary indexes on these two tables apart from other
tables.  In this regard I request you to help me in tuning the below query
so that the full table scan is avoided and the response time is less.
> 
>
>  
> 
> select
> ZPAA.AUFTRAGSPOSITION.POSITIONSNUMMER,
> ZPAA.AUFTRAGSPOSITION.ZAHLUNGSART,
> ZPAA.AUFTRAGSPOSITION.ZAHLUNGSARTTYP,
> ZPAA.AUFTRAGSPOSITION.VORGANGSNUMMER,
> to_char( AUFTRAG.AUFTRAGERTEILTDATUM, 'DD-MM-'),
> ZPAA.AUFTRAG.AUFTRAGSNUMMER,
> ZPAA.FAHRZEUG.AMTLICHESKENNZEICHEN,
> ZPAA.FAHRZEUG.FZGHALTERNAME,
> ZPAA.FZGBRIEF.FAHRZEUGARTTEXT,
> ZPAA.RECHNUNG.PREISGRUPPE,
> NULL BETRAG,
> NULL QUITTUNGSNR,
> ZPAA.PLAKETTENERGEBNIS.PLAKETTENLAUFZEITSTR,
> ZPAA.PLAKETTENERGEBNIS.PLAKETTENZUTEILUNG,
> ZPAA.AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID,
> S.MATERIALNUMMER,
> S.ERZEUGNISNR,
> ZPAA.AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE,
> PARTNER_PRUEFORT.PARTNERNUMMER,
> PARTNER_MHS.PARTNERNUMMER,
> PARTNER_AG.PARTNERNUMMER,
> PARTNER_AG.NAME,
> PARTNER_SV.PARTNERNUMMER,
> S.SVDIENSTLEISTUNGOID,
> to_char(S.ERZEUGNISDATUM, 'DD-MM-'),
> S.ERGEBNIS,
> S.SPERGEBNIS,
> S.SPPLAKETTE,
> ZPAA.AUFTRAGSPOSITION.DIENSTLEISTUNGSGRUPPE,
> ZPAA.AUFTRAGSPOSITION.DIENSTLEISTUNGSBEZEICHNUNG,
> NULL  BETRAGOFFEN,
> NULL PARTNERNUMMER,
> NULL ENDEZEIT,
> NULL TAGESABSCHLUSSNUMMER
> from
> ZPAA.FAHRZEUG,
> ZPAA.AUFTRAG,
> ZPAA.AUFTRAGSPOSITION,
> ZPAA.FZGBRIEF,
> ZPAA.PLAKETTENERGEBNIS,
> ZPAA.AUFTRAGSPOSPOSITIONSSTATUS,
> ZPAA.DIENSTLEISTUNGSOBJEKT D,
> ZPAA.RECHNUNG,
> ZPAA.SVDIENSTLEISTUNG S,
> ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_PRUEFORT,
> ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_MHS,
> ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_AG,
> ZPAA.AUFTRAGSPOSITIONSPARTNER PARTNER_SV
> where
> 
>  ZPAA.AUFTRAGSPOSITION.TAGESABSCHLUSSNUMMER  =
:TAGESABSCHLUSSNUMMER
> 
>  AND  AUFTRAG.VORGANGSNUMMER  =
AUFTRAGSPOSITION.VORGANGSNUMMER
> 
>  AND  AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID  =
S.AUFTRAGSPOSITIONOID
> 
>  AND  S.SVDIENSTLEISTUNGOID  =  D.SVDIENSTLEISTUNGOID
>  AND  D.DIENSTLEISTUNGSOBJEKTOID  
> FAHRZEUG.DIENSTLEISTUNGSOBJEKTOID
>  AND  D.SEQNO  =  (SELECT  E.SEQNO FROM
>  ZPAA.DIENSTLEISTUNGSOBJEKT E ,ZPAA.SVDIENSTLEISTUNG S1
>  WHERE S1.SVDIENSTLEISTUNGOID = E.SVDIENSTLEISTUNGOID
>  AND S1.SVDIENSTLEISTUNGOID = S.SVDIENSTLEISTUNGOID
>  AND ROWNUM < 2)
>  AND  FAHRZEUG.FAHRZEUGOID  =  FZGBRIEF.FAHRZEUGOID
>  AND  S.ERGEBNIS  =
PLAKETTENERGEBNIS.PLAKETTENERGEBNISOID(+)
>  AND  AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID  
> RECHNUNG.AUFTRAGSPOSITIONOID
>  AND  AUFTRAGSPOSITION.AKTUELLERSTATUS  
> AUFTRAGSPOSPOSITIONSSTATUS.AUFTRAGSPOSITIONSSTATUSOID
>  AND  (AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE  =  '6'
>  OR  AUFTRAGSPOSPOSITIONSSTATUS.STATUSCODE  =  '8')
>  AND  AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID  
> PARTNER_PRUEFORT.AUFTRAGSPOSITIONOID(+)
>  AND  PARTNER_PRUEFORT.ROLLENTYP(+)  =  'ZP'
>  AND  AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID  
> PARTNER_MHS.AUFTRAGSPOSITIONOID(+)
>  AND  PARTNER_MHS.ROLLENTYP(+)  =  'MHS'
>  AND  AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID  
> PARTNER_AG.AUFTRAGSPOSITIONOID(+)
>  AND  PARTNER_AG.ROLLENTYP(+)  =  'AG'
>  AND  AUFTRAGSPOSITION.AUFTRAGSPOSITIONOID  
> PARTNER_SV.AUFTRAGSPOSITIONOID(+)
>  AND  PARTNER_SV.ROLLENTYP(+)  =  'ZT'
> Operation   Object Name RowsBytes   CostTQ
In/Out
> PStart  PStop
> 
> SELECT STATEMENT Hint=HINT: RULE
> 
>   NESTED LOOPS
> 
> NESTED LOOPS
> 
>   NESTED LOOPS
> 
> NESTED LOOPS
> 
>   

RE: Sql query

[Farnsworth, Dave]

ARE YOU AN IDIOT!  ;o)

-Original Message-
Sent: Friday, October 04, 2002 10:13 AM
To: Multiple recipients of list ORACLE-L


HELP!

-Original Message-
Sent: 04 October 2002 14:53
To: Multiple recipients of list ORACLE-L


sql server 7???
 
and you are trying to find an answer on Oracle board?
 
Igor Neyman, OCP DBA
[EMAIL PROTECTED]  
  



- Original Message - 
To: Multiple recipients of list ORACLE-L   
Sent: Friday, October 04, 2002 2:23 AM

 Hi naveen, 

cannot perform an aggregate function on an expression containing an
aggregate or subquery 

is the error i am getting while i am executing the query. i am executing
this query in sql server 7

santosh

-Original Message-
[mailto:[EMAIL PROTECTED]]On Behalf Of Naveen Nahata
Sent: Thursday, October 03, 2002 9:03 PM
To: Multiple recipients of list ORACLE-L


Santosh, 
 
your query is working. See below
 
SQL> CREATE TABLE CLIENT (
  2  CLIENTID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(10)
  4  );
 
Table created.
 
SQL> CREATE TABLE PROJECT(
  2  PROJECTID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(10),
  4  CLIENTID REFERENCES CLIENT(CLIENTID)
  5  );
 
Table created.
 
SQL> insert into client values(1, 'Naveen');
 
1 row created.
 
SQL> insert into client values(2, 'Santosh');
 
1 row created.
 
SQL> insert into project values(1, 'Oracle', 1);
 
1 row created.
 
SQL> insert into project values(2, 'Java', 1);
 
1 row created.
 
SQL> insert into project values(3, 'SQL', 2);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> ed
Wrote file afiedt.buf
 
  1  SELECT name
  2  FROM (SELECT c.name, COUNT(p.clientid) p_count
  3  FROM client c, project p
  4  WHERE c.clientid = p.clientid
  5  GROUP BY c.name) a,
  6  (SELECT MAX(COUNT(clientid)) p_max
  7  FROM project
  8  GROUP BY clientid) b
  9* WHERE a.p_count = b.p_max
SQL> /
 
NAME
--
Naveen
 
SQL> I can run your query, then what's the problem?
SQL> 
 
Regards
Naveen

-Original Message-
Sent: Thursday, October 03, 2002 2:53 PM
To: Multiple recipients of list ORACLE-L


cannot perform an aggregate function on an expression containing an

aggregate or subquery

is the error i am getting while i am executing the query.

SELECT name

FROM (SELECT c.name, COUNT(p.clientid) p_count

FROM client c, project p

WHERE c.clientid = p.clientid

GROUP BY c.name) a,

(SELECT MAX(COUNT(clientid)) p_max

FROM project

GROUP BY clientid) b

WHERE a.p_count = b.p_max

clientid and name are the columns in client table

and projectid and clientid are the columns in project table.

santosh

-Original Message-

Ignaszak

Sent: Monday, September 30, 2002 6:09 PM

To: Multiple recipients of list ORACLE-L

 

try it:

select

name

from

(select c.name, count(p.id) p_count from clients c, projects p

where c.id = p.cl_id

group by c.name) a,

(select max(count(id)) p_max from projects

group by cl_id) b

where a.p_count = b.p_max

Regards,

Leszek

At 03:23 2002-09-30 -0800, you wrote:

>Hello all,

>

> I have a query ->

>i have 2 tables -> client and project

>

>fields in project table -> clientid/projectid

>fields in client table -> clientid/name

>

>i want to get the maximum orders one client has got. i mean a project

>having the greatest clients

>how to write it in single query ??

>

>

>like

>project 1 client 1

>project 2 client 1

>project 3 client 2

>

>in the above case, the query should return client ( 1 ).

>

>Thanks and regards,

>Santosh

--

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

--

Author: Leszek Ignaszak

INET: [EMAIL PROTECTED]

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

San Diego, California -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may

also send the HELP command for other information (like subscribing).

-- 

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

-- 

Author: Santosh Varma

INET: [EMAIL PROTECTED]

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

San Diego, California -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may

also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thomas, Kevin
  INET: [EMAIL PROTECTED]

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

Re: Sql query

[Ruth Gramolini]

Are you an idiot? 
- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, October 04, 2002 11:13 AM


> HELP!
> 
> -Original Message-
> Sent: 04 October 2002 14:53
> To: Multiple recipients of list ORACLE-L
> 
> 
> sql server 7???
>  
> and you are trying to find an answer on Oracle board?
>  
> Igor Neyman, OCP DBA
> [EMAIL PROTECTED]  
>   
> 
> 
> 
> - Original Message - 
> To: Multiple recipients of list ORACLE-L   
> Sent: Friday, October 04, 2002 2:23 AM
> 
>  Hi naveen, 
> 
> cannot perform an aggregate function on an expression containing an
> aggregate or subquery 
> 
> is the error i am getting while i am executing the query. i am executing
> this query in sql server 7
> 
> santosh
> 
> -Original Message-
> [mailto:[EMAIL PROTECTED]]On Behalf Of Naveen Nahata
> Sent: Thursday, October 03, 2002 9:03 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Santosh, 
>  
> your query is working. See below
>  
> SQL> CREATE TABLE CLIENT (
>   2  CLIENTID NUMBER PRIMARY KEY,
>   3  NAME VARCHAR2(10)
>   4  );
>  
> Table created.
>  
> SQL> CREATE TABLE PROJECT(
>   2  PROJECTID NUMBER PRIMARY KEY,
>   3  NAME VARCHAR2(10),
>   4  CLIENTID REFERENCES CLIENT(CLIENTID)
>   5  );
>  
> Table created.
>  
> SQL> insert into client values(1, 'Naveen');
>  
> 1 row created.
>  
> SQL> insert into client values(2, 'Santosh');
>  
> 1 row created.
>  
> SQL> insert into project values(1, 'Oracle', 1);
>  
> 1 row created.
>  
> SQL> insert into project values(2, 'Java', 1);
>  
> 1 row created.
>  
> SQL> insert into project values(3, 'SQL', 2);
>  
> 1 row created.
>  
> SQL> commit;
>  
> Commit complete.
>  
> SQL> ed
> Wrote file afiedt.buf
>  
>   1  SELECT name
>   2  FROM (SELECT c.name, COUNT(p.clientid) p_count
>   3  FROM client c, project p
>   4  WHERE c.clientid = p.clientid
>   5  GROUP BY c.name) a,
>   6  (SELECT MAX(COUNT(clientid)) p_max
>   7  FROM project
>   8  GROUP BY clientid) b
>   9* WHERE a.p_count = b.p_max
> SQL> /
>  
> NAME
> --
> Naveen
>  
> SQL> I can run your query, then what's the problem?
> SQL> 
>  
> Regards
> Naveen
> 
> -Original Message-
> Sent: Thursday, October 03, 2002 2:53 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> cannot perform an aggregate function on an expression containing an
> 
> aggregate or subquery
> 
> is the error i am getting while i am executing the query.
> 
> SELECT name
> 
> FROM (SELECT c.name, COUNT(p.clientid) p_count
> 
> FROM client c, project p
> 
> WHERE c.clientid = p.clientid
> 
> GROUP BY c.name) a,
> 
> (SELECT MAX(COUNT(clientid)) p_max
> 
> FROM project
> 
> GROUP BY clientid) b
> 
> WHERE a.p_count = b.p_max
> 
> clientid and name are the columns in client table
> 
> and projectid and clientid are the columns in project table.
> 
> santosh
> 
> -Original Message-
> 
> Ignaszak
> 
> Sent: Monday, September 30, 2002 6:09 PM
> 
> To: Multiple recipients of list ORACLE-L
> 
>  
> 
> try it:
> 
> select
> 
> name
> 
> from
> 
> (select c.name, count(p.id) p_count from clients c, projects p
> 
> where c.id = p.cl_id
> 
> group by c.name) a,
> 
> (select max(count(id)) p_max from projects
> 
> group by cl_id) b
> 
> where a.p_count = b.p_max
> 
> Regards,
> 
> Leszek
> 
> At 03:23 2002-09-30 -0800, you wrote:
> 
> >Hello all,
> 
> >
> 
> > I have a query ->
> 
> >i have 2 tables -> client and project
> 
> >
> 
> >fields in project table -> clientid/projectid
> 
> >fields in client table -> clientid/name
> 
> >
> 
> >i want to get the maximum orders one client has got. i mean a project
> 
> >having the greatest clients
> 
> >how to write it in single query ??
> 
> >
> 
> >
> 
> >like
> 
> >project 1 client 1
> 
> >project 2 client 1
> 
> >project 3 client 2
> 
> >
> 
> >in the above case, the query should return client ( 1 ).
> 
> >
> 
> >Thanks and regards,
> 
> >Santosh
> 
> --
> 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> 
> --
> 
> Author: Leszek Ignaszak
> 
> INET: [EMAIL PROTECTED]
> 
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> 
> San Diego, California -- Mailing list and web hosting services
> 
> -
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> 
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> 
> the message BODY, include a line containing: UNSUB ORACLE-L
> 
> (or the name of mailing list you want to be removed from). You may
> 
> also send the HELP command for other information (like subscribing).
> 
> -- 
> 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> 
> -- 
> 
> Author: Santosh Varma
> 
> INET: [EMAIL PROTECTED]
> 
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> 
> San Diego, California -- Mailing list and web hosting services
> 
> -

Re: Sql query

[Igor Neyman]

> HELP!

is this also sql server 7 command? :-)

Igor Neyman, OCP DBA
[EMAIL PROTECTED]
  

- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, October 04, 2002 11:13 AM


> HELP!
> 
> -Original Message-
> Sent: 04 October 2002 14:53
> To: Multiple recipients of list ORACLE-L
> 
> 
> sql server 7???
>  
> and you are trying to find an answer on Oracle board?
>  
> Igor Neyman, OCP DBA
> [EMAIL PROTECTED]  
>   
> 
> 
> 
> - Original Message - 
> To: Multiple recipients of list ORACLE-L   
> Sent: Friday, October 04, 2002 2:23 AM
> 
>  Hi naveen, 
> 
> cannot perform an aggregate function on an expression containing an
> aggregate or subquery 
> 
> is the error i am getting while i am executing the query. i am executing
> this query in sql server 7
> 
> santosh
> 
> -Original Message-
> [mailto:[EMAIL PROTECTED]]On Behalf Of Naveen Nahata
> Sent: Thursday, October 03, 2002 9:03 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Santosh, 
>  
> your query is working. See below
>  
> SQL> CREATE TABLE CLIENT (
>   2  CLIENTID NUMBER PRIMARY KEY,
>   3  NAME VARCHAR2(10)
>   4  );
>  
> Table created.
>  
> SQL> CREATE TABLE PROJECT(
>   2  PROJECTID NUMBER PRIMARY KEY,
>   3  NAME VARCHAR2(10),
>   4  CLIENTID REFERENCES CLIENT(CLIENTID)
>   5  );
>  
> Table created.
>  
> SQL> insert into client values(1, 'Naveen');
>  
> 1 row created.
>  
> SQL> insert into client values(2, 'Santosh');
>  
> 1 row created.
>  
> SQL> insert into project values(1, 'Oracle', 1);
>  
> 1 row created.
>  
> SQL> insert into project values(2, 'Java', 1);
>  
> 1 row created.
>  
> SQL> insert into project values(3, 'SQL', 2);
>  
> 1 row created.
>  
> SQL> commit;
>  
> Commit complete.
>  
> SQL> ed
> Wrote file afiedt.buf
>  
>   1  SELECT name
>   2  FROM (SELECT c.name, COUNT(p.clientid) p_count
>   3  FROM client c, project p
>   4  WHERE c.clientid = p.clientid
>   5  GROUP BY c.name) a,
>   6  (SELECT MAX(COUNT(clientid)) p_max
>   7  FROM project
>   8  GROUP BY clientid) b
>   9* WHERE a.p_count = b.p_max
> SQL> /
>  
> NAME
> --
> Naveen
>  
> SQL> I can run your query, then what's the problem?
> SQL> 
>  
> Regards
> Naveen
> 
> -Original Message-
> Sent: Thursday, October 03, 2002 2:53 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> cannot perform an aggregate function on an expression containing an
> 
> aggregate or subquery
> 
> is the error i am getting while i am executing the query.
> 
> SELECT name
> 
> FROM (SELECT c.name, COUNT(p.clientid) p_count
> 
> FROM client c, project p
> 
> WHERE c.clientid = p.clientid
> 
> GROUP BY c.name) a,
> 
> (SELECT MAX(COUNT(clientid)) p_max
> 
> FROM project
> 
> GROUP BY clientid) b
> 
> WHERE a.p_count = b.p_max
> 
> clientid and name are the columns in client table
> 
> and projectid and clientid are the columns in project table.
> 
> santosh
> 
> -Original Message-
> 
> Ignaszak
> 
> Sent: Monday, September 30, 2002 6:09 PM
> 
> To: Multiple recipients of list ORACLE-L
> 
>  
> 
> try it:
> 
> select
> 
> name
> 
> from
> 
> (select c.name, count(p.id) p_count from clients c, projects p
> 
> where c.id = p.cl_id
> 
> group by c.name) a,
> 
> (select max(count(id)) p_max from projects
> 
> group by cl_id) b
> 
> where a.p_count = b.p_max
> 
> Regards,
> 
> Leszek
> 
> At 03:23 2002-09-30 -0800, you wrote:
> 
> >Hello all,
> 
> >
> 
> > I have a query ->
> 
> >i have 2 tables -> client and project
> 
> >
> 
> >fields in project table -> clientid/projectid
> 
> >fields in client table -> clientid/name
> 
> >
> 
> >i want to get the maximum orders one client has got. i mean a project
> 
> >having the greatest clients
> 
> >how to write it in single query ??
> 
> >
> 
> >
> 
> >like
> 
> >project 1 client 1
> 
> >project 2 client 1
> 
> >project 3 client 2
> 
> >
> 
> >in the above case, the query should return client ( 1 ).
> 
> >
> 
> >Thanks and regards,
> 
> >Santosh
> 
> --
> 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> 
> --
> 
> Author: Leszek Ignaszak
> 
> INET: [EMAIL PROTECTED]
> 
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> 
> San Diego, California -- Mailing list and web hosting services
> 
> -
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> 
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> 
> the message BODY, include a line containing: UNSUB ORACLE-L
> 
> (or the name of mailing list you want to be removed from). You may
> 
> also send the HELP command for other information (like subscribing).
> 
> -- 
> 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> 
> -- 
> 
> Author: Santosh Varma
> 
> INET: [EMAIL PROTECTED]
> 
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> 
> San Diego, California -- Mailing list an

RE: Sql query

[Mercadante, Thomas F]

Obligatory...

ARE YOU AN IDIOT?


-Original Message-
Sent: Friday, October 04, 2002 11:13 AM
To: Multiple recipients of list ORACLE-L


HELP!

-Original Message-
Sent: 04 October 2002 14:53
To: Multiple recipients of list ORACLE-L


sql server 7???
 
and you are trying to find an answer on Oracle board?
 
Igor Neyman, OCP DBA
[EMAIL PROTECTED]  
  



- Original Message - 
To: Multiple recipients of list ORACLE-L   
Sent: Friday, October 04, 2002 2:23 AM

 Hi naveen, 

cannot perform an aggregate function on an expression containing an
aggregate or subquery 

is the error i am getting while i am executing the query. i am executing
this query in sql server 7

santosh

-Original Message-
[mailto:[EMAIL PROTECTED]]On Behalf Of Naveen Nahata
Sent: Thursday, October 03, 2002 9:03 PM
To: Multiple recipients of list ORACLE-L


Santosh, 
 
your query is working. See below
 
SQL> CREATE TABLE CLIENT (
  2  CLIENTID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(10)
  4  );
 
Table created.
 
SQL> CREATE TABLE PROJECT(
  2  PROJECTID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(10),
  4  CLIENTID REFERENCES CLIENT(CLIENTID)
  5  );
 
Table created.
 
SQL> insert into client values(1, 'Naveen');
 
1 row created.
 
SQL> insert into client values(2, 'Santosh');
 
1 row created.
 
SQL> insert into project values(1, 'Oracle', 1);
 
1 row created.
 
SQL> insert into project values(2, 'Java', 1);
 
1 row created.
 
SQL> insert into project values(3, 'SQL', 2);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> ed
Wrote file afiedt.buf
 
  1  SELECT name
  2  FROM (SELECT c.name, COUNT(p.clientid) p_count
  3  FROM client c, project p
  4  WHERE c.clientid = p.clientid
  5  GROUP BY c.name) a,
  6  (SELECT MAX(COUNT(clientid)) p_max
  7  FROM project
  8  GROUP BY clientid) b
  9* WHERE a.p_count = b.p_max
SQL> /
 
NAME
--
Naveen
 
SQL> I can run your query, then what's the problem?
SQL> 
 
Regards
Naveen

-Original Message-
Sent: Thursday, October 03, 2002 2:53 PM
To: Multiple recipients of list ORACLE-L


cannot perform an aggregate function on an expression containing an

aggregate or subquery

is the error i am getting while i am executing the query.

SELECT name

FROM (SELECT c.name, COUNT(p.clientid) p_count

FROM client c, project p

WHERE c.clientid = p.clientid

GROUP BY c.name) a,

(SELECT MAX(COUNT(clientid)) p_max

FROM project

GROUP BY clientid) b

WHERE a.p_count = b.p_max

clientid and name are the columns in client table

and projectid and clientid are the columns in project table.

santosh

-Original Message-

Ignaszak

Sent: Monday, September 30, 2002 6:09 PM

To: Multiple recipients of list ORACLE-L

 

try it:

select

name

from

(select c.name, count(p.id) p_count from clients c, projects p

where c.id = p.cl_id

group by c.name) a,

(select max(count(id)) p_max from projects

group by cl_id) b

where a.p_count = b.p_max

Regards,

Leszek

At 03:23 2002-09-30 -0800, you wrote:

>Hello all,

>

> I have a query ->

>i have 2 tables -> client and project

>

>fields in project table -> clientid/projectid

>fields in client table -> clientid/name

>

>i want to get the maximum orders one client has got. i mean a project

>having the greatest clients

>how to write it in single query ??

>

>

>like

>project 1 client 1

>project 2 client 1

>project 3 client 2

>

>in the above case, the query should return client ( 1 ).

>

>Thanks and regards,

>Santosh

--

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

--

Author: Leszek Ignaszak

INET: [EMAIL PROTECTED]

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

San Diego, California -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may

also send the HELP command for other information (like subscribing).

-- 

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

-- 

Author: Santosh Varma

INET: [EMAIL PROTECTED]

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

San Diego, California -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may

also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thomas, Kevin
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://ww

RE: Sql query

[Robertson Lee - lerobe]

ARE YOU AN IDIOT

-Original Message-
Sent: 04 October 2002 16:13
To: Multiple recipients of list ORACLE-L


HELP!

-Original Message-
Sent: 04 October 2002 14:53
To: Multiple recipients of list ORACLE-L


sql server 7???
 
and you are trying to find an answer on Oracle board?
 
Igor Neyman, OCP DBA
[EMAIL PROTECTED]  
  



- Original Message - 
To: Multiple recipients of list ORACLE-L   
Sent: Friday, October 04, 2002 2:23 AM

 Hi naveen, 

cannot perform an aggregate function on an expression containing an
aggregate or subquery 

is the error i am getting while i am executing the query. i am executing
this query in sql server 7

santosh

-Original Message-
[mailto:[EMAIL PROTECTED]]On Behalf Of Naveen Nahata
Sent: Thursday, October 03, 2002 9:03 PM
To: Multiple recipients of list ORACLE-L


Santosh, 
 
your query is working. See below
 
SQL> CREATE TABLE CLIENT (
  2  CLIENTID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(10)
  4  );
 
Table created.
 
SQL> CREATE TABLE PROJECT(
  2  PROJECTID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(10),
  4  CLIENTID REFERENCES CLIENT(CLIENTID)
  5  );
 
Table created.
 
SQL> insert into client values(1, 'Naveen');
 
1 row created.
 
SQL> insert into client values(2, 'Santosh');
 
1 row created.
 
SQL> insert into project values(1, 'Oracle', 1);
 
1 row created.
 
SQL> insert into project values(2, 'Java', 1);
 
1 row created.
 
SQL> insert into project values(3, 'SQL', 2);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> ed
Wrote file afiedt.buf
 
  1  SELECT name
  2  FROM (SELECT c.name, COUNT(p.clientid) p_count
  3  FROM client c, project p
  4  WHERE c.clientid = p.clientid
  5  GROUP BY c.name) a,
  6  (SELECT MAX(COUNT(clientid)) p_max
  7  FROM project
  8  GROUP BY clientid) b
  9* WHERE a.p_count = b.p_max
SQL> /
 
NAME
--
Naveen
 
SQL> I can run your query, then what's the problem?
SQL> 
 
Regards
Naveen

-Original Message-
Sent: Thursday, October 03, 2002 2:53 PM
To: Multiple recipients of list ORACLE-L


cannot perform an aggregate function on an expression containing an

aggregate or subquery

is the error i am getting while i am executing the query.

SELECT name

FROM (SELECT c.name, COUNT(p.clientid) p_count

FROM client c, project p

WHERE c.clientid = p.clientid

GROUP BY c.name) a,

(SELECT MAX(COUNT(clientid)) p_max

FROM project

GROUP BY clientid) b

WHERE a.p_count = b.p_max

clientid and name are the columns in client table

and projectid and clientid are the columns in project table.

santosh

-Original Message-

Ignaszak

Sent: Monday, September 30, 2002 6:09 PM

To: Multiple recipients of list ORACLE-L

 

try it:

select

name

from

(select c.name, count(p.id) p_count from clients c, projects p

where c.id = p.cl_id

group by c.name) a,

(select max(count(id)) p_max from projects

group by cl_id) b

where a.p_count = b.p_max

Regards,

Leszek

At 03:23 2002-09-30 -0800, you wrote:

>Hello all,

>

> I have a query ->

>i have 2 tables -> client and project

>

>fields in project table -> clientid/projectid

>fields in client table -> clientid/name

>

>i want to get the maximum orders one client has got. i mean a project

>having the greatest clients

>how to write it in single query ??

>

>

>like

>project 1 client 1

>project 2 client 1

>project 3 client 2

>

>in the above case, the query should return client ( 1 ).

>

>Thanks and regards,

>Santosh

--

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

--

Author: Leszek Ignaszak

INET: [EMAIL PROTECTED]

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

San Diego, California -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may

also send the HELP command for other information (like subscribing).

-- 

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

-- 

Author: Santosh Varma

INET: [EMAIL PROTECTED]

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

San Diego, California -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may

also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thomas, Kevin
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, Cali

RE: Sql query

[Thomas, Kevin]

HELP!

-Original Message-
Sent: 04 October 2002 14:53
To: Multiple recipients of list ORACLE-L


sql server 7???
 
and you are trying to find an answer on Oracle board?
 
Igor Neyman, OCP DBA
[EMAIL PROTECTED]  
  



- Original Message - 
To: Multiple recipients of list ORACLE-L   
Sent: Friday, October 04, 2002 2:23 AM

 Hi naveen, 

cannot perform an aggregate function on an expression containing an
aggregate or subquery 

is the error i am getting while i am executing the query. i am executing
this query in sql server 7

santosh

-Original Message-
[mailto:[EMAIL PROTECTED]]On Behalf Of Naveen Nahata
Sent: Thursday, October 03, 2002 9:03 PM
To: Multiple recipients of list ORACLE-L


Santosh, 
 
your query is working. See below
 
SQL> CREATE TABLE CLIENT (
  2  CLIENTID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(10)
  4  );
 
Table created.
 
SQL> CREATE TABLE PROJECT(
  2  PROJECTID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(10),
  4  CLIENTID REFERENCES CLIENT(CLIENTID)
  5  );
 
Table created.
 
SQL> insert into client values(1, 'Naveen');
 
1 row created.
 
SQL> insert into client values(2, 'Santosh');
 
1 row created.
 
SQL> insert into project values(1, 'Oracle', 1);
 
1 row created.
 
SQL> insert into project values(2, 'Java', 1);
 
1 row created.
 
SQL> insert into project values(3, 'SQL', 2);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> ed
Wrote file afiedt.buf
 
  1  SELECT name
  2  FROM (SELECT c.name, COUNT(p.clientid) p_count
  3  FROM client c, project p
  4  WHERE c.clientid = p.clientid
  5  GROUP BY c.name) a,
  6  (SELECT MAX(COUNT(clientid)) p_max
  7  FROM project
  8  GROUP BY clientid) b
  9* WHERE a.p_count = b.p_max
SQL> /
 
NAME
--
Naveen
 
SQL> I can run your query, then what's the problem?
SQL> 
 
Regards
Naveen

-Original Message-
Sent: Thursday, October 03, 2002 2:53 PM
To: Multiple recipients of list ORACLE-L


cannot perform an aggregate function on an expression containing an

aggregate or subquery

is the error i am getting while i am executing the query.

SELECT name

FROM (SELECT c.name, COUNT(p.clientid) p_count

FROM client c, project p

WHERE c.clientid = p.clientid

GROUP BY c.name) a,

(SELECT MAX(COUNT(clientid)) p_max

FROM project

GROUP BY clientid) b

WHERE a.p_count = b.p_max

clientid and name are the columns in client table

and projectid and clientid are the columns in project table.

santosh

-Original Message-

Ignaszak

Sent: Monday, September 30, 2002 6:09 PM

To: Multiple recipients of list ORACLE-L

 

try it:

select

name

from

(select c.name, count(p.id) p_count from clients c, projects p

where c.id = p.cl_id

group by c.name) a,

(select max(count(id)) p_max from projects

group by cl_id) b

where a.p_count = b.p_max

Regards,

Leszek

At 03:23 2002-09-30 -0800, you wrote:

>Hello all,

>

> I have a query ->

>i have 2 tables -> client and project

>

>fields in project table -> clientid/projectid

>fields in client table -> clientid/name

>

>i want to get the maximum orders one client has got. i mean a project

>having the greatest clients

>how to write it in single query ??

>

>

>like

>project 1 client 1

>project 2 client 1

>project 3 client 2

>

>in the above case, the query should return client ( 1 ).

>

>Thanks and regards,

>Santosh

--

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

--

Author: Leszek Ignaszak

INET: [EMAIL PROTECTED]

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

San Diego, California -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may

also send the HELP command for other information (like subscribing).

-- 

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

-- 

Author: Santosh Varma

INET: [EMAIL PROTECTED]

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

San Diego, California -- Mailing list and web hosting services

-

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may

also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thomas, Kevin
  INET: [EMAIL PROTECTED]

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

Re: Sql query

[Igor Neyman]

sql server 7???
 
and you are trying to find an answer on Oracle 
board?
 
Igor Neyman, OCP DBA[EMAIL PROTECTED]  


  - Original Message - 
  From: 
  Santosh 
  Varma 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Friday, October 04, 2002 2:23 
  AM
  Subject: RE: Sql query
  
   Hi 
  naveen, 
  cannot perform an aggregate function 
  on an expression containing an aggregate 
  or subquery 
  is 
  the error i am getting while i am executing the query. i am executing this query in sql server 7
  santosh
  
-Original Message-From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]]On Behalf Of Naveen NahataSent: 
Thursday, October 03, 2002 9:03 PMTo: Multiple recipients of list 
ORACLE-LSubject: RE: Sql query
Santosh, 
 
your query is working. See below
 
SQL> CREATE TABLE CLIENT (  2  CLIENTID NUMBER 
PRIMARY KEY,  3  NAME VARCHAR2(10)  4  
);
 
Table created.
 
SQL> CREATE TABLE PROJECT(  2  PROJECTID NUMBER 
PRIMARY KEY,  3  NAME VARCHAR2(10),  4  CLIENTID 
REFERENCES CLIENT(CLIENTID)  5  );
 
Table created.
 
SQL> insert into client values(1, 'Naveen');
 
1 
row created.
 
SQL> insert into client values(2, 'Santosh');
 
1 
row created.
 
SQL> insert into project values(1, 'Oracle', 
1);
 
1 
row created.
 
SQL> insert into project values(2, 'Java', 1);
 
1 
row created.
 
SQL> insert into project values(3, 'SQL', 2);
 
1 
row created.
 
SQL> commit;
 
Commit complete.
 
SQL> edWrote file afiedt.buf
 
  1  SELECT name  2  FROM (SELECT c.name, 
COUNT(p.clientid) p_count  3  FROM client c, project 
p  4  WHERE c.clientid = p.clientid  5  GROUP BY 
c.name) a,  6  (SELECT MAX(COUNT(clientid)) p_max  
7  FROM project  8  GROUP BY clientid) b  9* 
WHERE a.p_count = b.p_maxSQL> /
 
NAME--Naveen
 
SQL> I can run your query, then what's the problem?SQL> 

 
Regards
Naveen

  -Original Message-From: Santosh Varma 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 03, 2002 
  2:53 PMTo: Multiple recipients of list 
  ORACLE-LSubject: Sql query
  cannot perform an aggregate function on an expression containing an
  aggregate or subquery
  is the error i am getting while i am executing the query.
  SELECT name
  FROM (SELECT c.name, COUNT(p.clientid) p_count
  FROM client c, project p
  WHERE c.clientid = p.clientid
  GROUP BY c.name) a,
  (SELECT MAX(COUNT(clientid)) p_max
  FROM project
  GROUP BY clientid) b
  WHERE a.p_count = b.p_max
  clientid and name are the columns in client table
  and projectid and clientid are the columns in project table.
  santosh
  -Original Message-
  Ignaszak
  Sent: Monday, September 30, 2002 6:09 PM
  To: Multiple recipients of list ORACLE-L
   
  try it:
  select
  name
  from
  (select c.name, count(p.id) p_count from clients c, projects p
  where c.id = p.cl_id
  group by c.name) a,
  (select max(count(id)) p_max from projects
  group by cl_id) b
  where a.p_count = b.p_max
  Regards,
  Leszek
  At 03:23 2002-09-30 -0800, you wrote:
  >Hello all,
  >
  > I have a query ->
  >i have 2 tables -> client and project
  >
  >fields in project table -> clientid/projectid
  >fields in client table -> clientid/name
  >
  >i want to get the maximum orders one client has got. i mean a 
  project
  >having the greatest clients
  >how to write it in single query ??
  >
  >
  >like
  >project 1 client 1
  >project 2 client 1
  >project 3 client 2
  >
  >in the above case, the query should return client ( 1 ).
  >
  >Thanks and regards,
  >Santosh
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  --
  Author: Leszek Ignaszak
  INET: [EMAIL PROTECTED]
  Fat City Network Services -- 858-538-5051 http://www.fatcity.com
  San Diego, California -- Mailing list and web hosting services
  -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from). You may
  also send the HELP command for other information (like 
subscribing).
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  -- 
  Author: Sant

RE: Sql query

[Santosh Varma]

 Hi 
naveen,
cannot perform an aggregate function on 
an expression containing an aggregate or 
subquery 
is 
the error i am getting while i am executing the query. i am executing this query in sql server 7
santosh

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of Naveen NahataSent: 
  Thursday, October 03, 2002 9:03 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Sql query
  Santosh, 
   
  your 
  query is working. See below
   
  SQL> CREATE TABLE CLIENT (  2  CLIENTID NUMBER PRIMARY 
  KEY,  3  NAME VARCHAR2(10)  4  
  );
   
  Table created.
   
  SQL> CREATE TABLE PROJECT(  2  PROJECTID NUMBER 
  PRIMARY KEY,  3  NAME VARCHAR2(10),  4  CLIENTID 
  REFERENCES CLIENT(CLIENTID)  5  );
   
  Table created.
   
  SQL> insert into client values(1, 'Naveen');
   
  1 
  row created.
   
  SQL> insert into client values(2, 'Santosh');
   
  1 
  row created.
   
  SQL> insert into project values(1, 'Oracle', 1);
   
  1 
  row created.
   
  SQL> insert into project values(2, 'Java', 1);
   
  1 
  row created.
   
  SQL> insert into project values(3, 'SQL', 2);
   
  1 
  row created.
   
  SQL> commit;
   
  Commit complete.
   
  SQL> edWrote file afiedt.buf
   
    1  SELECT name  2  FROM (SELECT c.name, 
  COUNT(p.clientid) p_count  3  FROM client c, project p  
  4  WHERE c.clientid = p.clientid  5  GROUP BY c.name) 
  a,  6  (SELECT MAX(COUNT(clientid)) p_max  7  FROM 
  project  8  GROUP BY clientid) b  9* WHERE a.p_count = 
  b.p_maxSQL> /
   
  NAME--Naveen
   
  SQL> I can run your query, then what's the problem?SQL> 
  
   
  Regards
  Naveen
  
-Original Message-From: Santosh Varma 
[mailto:[EMAIL PROTECTED]]Sent: Thursday, October 03, 2002 
2:53 PMTo: Multiple recipients of list 
ORACLE-LSubject: Sql query
cannot perform an aggregate function on an expression containing an
aggregate or subquery
is the error i am getting while i am executing the query.
SELECT name
FROM (SELECT c.name, COUNT(p.clientid) p_count
FROM client c, project p
WHERE c.clientid = p.clientid
GROUP BY c.name) a,
(SELECT MAX(COUNT(clientid)) p_max
FROM project
GROUP BY clientid) b
WHERE a.p_count = b.p_max
clientid and name are the columns in client table
and projectid and clientid are the columns in project table.
santosh
-Original Message-
Ignaszak
Sent: Monday, September 30, 2002 6:09 PM
To: Multiple recipients of list ORACLE-L
 
try it:
select
name
from
(select c.name, count(p.id) p_count from clients c, projects p
where c.id = p.cl_id
group by c.name) a,
(select max(count(id)) p_max from projects
group by cl_id) b
where a.p_count = b.p_max
Regards,
Leszek
At 03:23 2002-09-30 -0800, you wrote:
>Hello all,
>
> I have a query ->
>i have 2 tables -> client and project
>
>fields in project table -> clientid/projectid
>fields in client table -> clientid/name
>
>i want to get the maximum orders one client has got. i mean a 
project
>having the greatest clients
>how to write it in single query ??
>
>
>like
>project 1 client 1
>project 2 client 1
>project 3 client 2
>
>in the above case, the query should return client ( 1 ).
>
>Thanks and regards,
>Santosh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Leszek Ignaszak
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Santosh Varma
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: Sql query

[Naveen Nahata]

Santosh, 
 
your 
query is working. See below
 
SQL> CREATE TABLE CLIENT (  2  CLIENTID NUMBER PRIMARY 
KEY,  3  NAME VARCHAR2(10)  4  
);
 
Table 
created.
 
SQL> CREATE TABLE PROJECT(  2  PROJECTID NUMBER PRIMARY 
KEY,  3  NAME VARCHAR2(10),  4  CLIENTID REFERENCES 
CLIENT(CLIENTID)  5  );
 
Table 
created.
 
SQL> insert into client values(1, 'Naveen');
 
1 row 
created.
 
SQL> insert into client values(2, 'Santosh');
 
1 row 
created.
 
SQL> insert into project values(1, 'Oracle', 1);
 
1 row 
created.
 
SQL> insert into project values(2, 'Java', 1);
 
1 row 
created.
 
SQL> insert into project values(3, 'SQL', 2);
 
1 row 
created.
 
SQL> commit;
 
Commit 
complete.
 
SQL> edWrote file afiedt.buf
 
  
1  SELECT name  2  FROM (SELECT c.name, COUNT(p.clientid) 
p_count  3  FROM client c, project p  4  WHERE 
c.clientid = p.clientid  5  GROUP BY c.name) a,  6  
(SELECT MAX(COUNT(clientid)) p_max  7  FROM project  
8  GROUP BY clientid) b  9* WHERE a.p_count = b.p_maxSQL> 
/
 
NAME--Naveen
 
SQL> I can run your query, then what's the problem?SQL> 

 
Regards
Naveen

  -Original Message-From: Santosh Varma 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 03, 2002 2:53 
  PMTo: Multiple recipients of list ORACLE-LSubject: Sql 
  query
  cannot perform an aggregate function on an expression containing an
  aggregate or subquery
  is the error i am getting while i am executing the query.
  SELECT name
  FROM (SELECT c.name, COUNT(p.clientid) p_count
  FROM client c, project p
  WHERE c.clientid = p.clientid
  GROUP BY c.name) a,
  (SELECT MAX(COUNT(clientid)) p_max
  FROM project
  GROUP BY clientid) b
  WHERE a.p_count = b.p_max
  clientid and name are the columns in client table
  and projectid and clientid are the columns in project table.
  santosh
  -Original Message-
  Ignaszak
  Sent: Monday, September 30, 2002 6:09 PM
  To: Multiple recipients of list ORACLE-L
   
  try it:
  select
  name
  from
  (select c.name, count(p.id) p_count from clients c, projects p
  where c.id = p.cl_id
  group by c.name) a,
  (select max(count(id)) p_max from projects
  group by cl_id) b
  where a.p_count = b.p_max
  Regards,
  Leszek
  At 03:23 2002-09-30 -0800, you wrote:
  >Hello all,
  >
  > I have a query ->
  >i have 2 tables -> client and project
  >
  >fields in project table -> clientid/projectid
  >fields in client table -> clientid/name
  >
  >i want to get the maximum orders one client has got. i mean a 
  project
  >having the greatest clients
  >how to write it in single query ??
  >
  >
  >like
  >project 1 client 1
  >project 2 client 1
  >project 3 client 2
  >
  >in the above case, the query should return client ( 1 ).
  >
  >Thanks and regards,
  >Santosh
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  --
  Author: Leszek Ignaszak
  INET: [EMAIL PROTECTED]
  Fat City Network Services -- 858-538-5051 http://www.fatcity.com
  San Diego, California -- Mailing list and web hosting services
  -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
  the message BODY, include a line containing: UNSUB ORACLE-L
  (or the name of mailing list you want to be removed from). You may
  also send the HELP command for other information (like subscribing).
  -- 
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  -- 
  Author: Santosh Varma
  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: SQL Query

[Naveen Nahata]

Santosh,

Till now you haven't exactly specified what you want and WHY you want such a
thing. 

So I can only make a guess at what you want. If you want the age of the last
record to be shown with the name of the first record and so on, then
following is the query:

SQL> SELECT * FROM test;

NAME  AGE
-- --
ANAND   1
BALU2
CHANDU  3
DAVID   4

SQL> SELECT t1.name, t2.age
  2  FROM (SELECT rownum r1, name FROM test) t1
  3  , (SELECT rownum r2, age FROM test) t2
  4  WHERE t1.r1 + t2.r2 - 1 = (SELECT
  5  count(*) FROM test)
  6  /

NAME  AGE
-- --
DAVID   1
CHANDU  2
BALU3
ANAND   4

SQL> I'm stupid!
SQL> 
SQL> You are stupid!!
unknown command beginning "You are st..." - rest of line ignored.

Regards
Naveen
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Naveen Nahata
  INET: [EMAIL PROTECTED]

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

RE: SQL Query

[Mercadante, Thomas F]

huh?
   new value
> ANAND  1   4-1+1 = 4
> BALU   2   4-2+1 = 3
> CHANDU 3   4-3+1 = 2
> DAVID  4   4-4+1 = 1

looks right to me.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Thursday, October 03, 2002 8:35 AM
To: Multiple recipients of list ORACLE-L


the below query adds/substracts the value in the age column...
SELECT NAME, 4-AGE+1 FROM test;
does not work..


-Original Message-
SALAYMEH
Sent: Thursday, October 03, 2002 5:28 PM
To: Multiple recipients of list ORACLE-L


TRY THIS :

SELECT NAME, 4-AGE+1 FROM test; 
 
--- Naveen Nahata <[EMAIL PROTECTED]> wrote:
> why do u want such a query?
> 
> -Original Message-
> Sent: Thursday, October 03, 2002 1:33 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> I have a table test((NAME VARCHAR2(10),AGE
> NUMBER(2));
>  
> data of the table is 
>  
> NAME AGE
> ---
> ANAND  1
> BALU   2
> CHANDU3
> DAVID4
>  
>  
> I want a query which give me the result as
>  
> NAME AGE
> ---
> ANAND  4
> BALU  3
> CHANDU   2
> DAVID   1
>  
> Can any body pl. help me.
>  
> Anand Kumar
> ITW Signode India Ltd
> 
> 


__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: ASHRAF SALAYMEH
  INET: [EMAIL PROTECTED]

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

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

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

RE: SQL Query

[Abdul Aleem]

Santosh,

If you could tell why do you want the ages be reversed?

Aleem

 -Original Message-
Sent:   Thursday, October 03, 2002 5:35 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: SQL Query

the below query adds/substracts the value in the age column...
SELECT NAME, 4-AGE+1 FROM test;
does not work..


-Original Message-
SALAYMEH
Sent: Thursday, October 03, 2002 5:28 PM
To: Multiple recipients of list ORACLE-L


TRY THIS :

SELECT NAME, 4-AGE+1 FROM test; 
 
--- Naveen Nahata <[EMAIL PROTECTED]> wrote:
> why do u want such a query?
> 
> -Original Message-
> Sent: Thursday, October 03, 2002 1:33 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> I have a table test((NAME VARCHAR2(10),AGE
> NUMBER(2));
>  
> data of the table is 
>  
> NAME AGE
> ---
> ANAND  1
> BALU   2
> CHANDU3
> DAVID4
>  
>  
> I want a query which give me the result as
>  
> NAME AGE
> ---
> ANAND  4
> BALU  3
> CHANDU   2
> DAVID   1
>  
> Can any body pl. help me.
>  
> Anand Kumar
> ITW Signode India Ltd
> 
> 


__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: ASHRAF SALAYMEH
  INET: [EMAIL PROTECTED]

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

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

[Santosh Varma]

the below query adds/substracts the value in the age column...
SELECT NAME, 4-AGE+1 FROM test;
does not work..


-Original Message-
SALAYMEH
Sent: Thursday, October 03, 2002 5:28 PM
To: Multiple recipients of list ORACLE-L


TRY THIS :

SELECT NAME, 4-AGE+1 FROM test; 
 
--- Naveen Nahata <[EMAIL PROTECTED]> wrote:
> why do u want such a query?
> 
> -Original Message-
> Sent: Thursday, October 03, 2002 1:33 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> I have a table test((NAME VARCHAR2(10),AGE
> NUMBER(2));
>  
> data of the table is 
>  
> NAME AGE
> ---
> ANAND  1
> BALU   2
> CHANDU3
> DAVID4
>  
>  
> I want a query which give me the result as
>  
> NAME AGE
> ---
> ANAND  4
> BALU  3
> CHANDU   2
> DAVID   1
>  
> Can any body pl. help me.
>  
> Anand Kumar
> ITW Signode India Ltd
> 
> 


__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: ASHRAF SALAYMEH
  INET: [EMAIL PROTECTED]

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

[ASHRAF SALAYMEH]

TRY THIS :

SELECT NAME, 4-AGE+1 FROM test; 
 
--- Naveen Nahata <[EMAIL PROTECTED]> wrote:
> why do u want such a query?
> 
> -Original Message-
> Sent: Thursday, October 03, 2002 1:33 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> I have a table test((NAME VARCHAR2(10),AGE
> NUMBER(2));
>  
> data of the table is 
>  
> NAME AGE
> ---
> ANAND  1
> BALU   2
> CHANDU3
> DAVID4
>  
>  
> I want a query which give me the result as
>  
> NAME AGE
> ---
> ANAND  4
> BALU  3
> CHANDU   2
> DAVID   1
>  
> Can any body pl. help me.
>  
> Anand Kumar
> ITW Signode India Ltd
> 
> 


__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: ASHRAF SALAYMEH
  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: SQL Query

[Naveen Nahata]

why do 
u want such a query?

  -Original Message-From: Anand Kumar N 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, October 03, 2002 
  1:33 PMTo: Multiple recipients of list ORACLE-LSubject: 
  SQL Query
  I have a table 
  test((NAME VARCHAR2(10),AGE NUMBER(2));
   
  data of the table is 
  
   
  NAME 
  AGE--            
  -ANAND  
  1BALU   2CHANDU3DAVID4
   
   
  I want a query which 
  give me the result as
   
  
  NAME 
  AGE--            
  -ANAND  
  4BALU  3CHANDU   2DAVID   1
   
  Can any body pl. help me.
   
  Anand KumarITW Signode India 
Ltd

Sql query

[Santosh Varma]

cannot perform an aggregate function on an expression containing an
aggregate or subquery
is the error i am getting while i am executing the query.
SELECT name
FROM (SELECT c.name, COUNT(p.clientid) p_count
FROM client c, project p
WHERE c.clientid = p.clientid
GROUP BY c.name) a,
(SELECT MAX(COUNT(clientid)) p_max
FROM project
GROUP BY clientid) b
WHERE a.p_count = b.p_max
clientid and name are the columns in client table
and projectid and clientid are the columns in project table.
santosh
-Original Message-
Ignaszak
Sent: Monday, September 30, 2002 6:09 PM
To: Multiple recipients of list ORACLE-L
 
try it:
select
name
from
(select c.name, count(p.id) p_count from clients c, projects p
where c.id = p.cl_id
group by c.name) a,
(select max(count(id)) p_max from projects
group by cl_id) b
where a.p_count = b.p_max
Regards,
Leszek
At 03:23 2002-09-30 -0800, you wrote:
>Hello all,
>
> I have a query ->
>i have 2 tables -> client and project
>
>fields in project table -> clientid/projectid
>fields in client table -> clientid/name
>
>i want to get the maximum orders one client has got. i mean a project
>having the greatest clients
>how to write it in single query ??
>
>
>like
>project 1 client 1
>project 2 client 1
>project 3 client 2
>
>in the above case, the query should return client ( 1 ).
>
>Thanks and regards,
>Santosh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Leszek Ignaszak
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Santosh Varma
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: SQL Query

[Mikhail Ivanov]

3 ïËÔÑÂÒØ 2002 12:03, ÷Ù ÎÁÐÉÓÁÌÉ:
> I have a table test((NAME VARCHAR2(10),AGE NUMBER(2));
>
> data of the table is
>
> NAME AGE
> -- -
> ANAND  1
> BALU   2
> CHANDU3
> DAVID4
>
>
> I want a query which give me the result as
>
> NAME AGE
> ---
> ANAND  4
> BALU  3
> CHANDU   2
> DAVID   1
>

select NAME, 5-AGE AGE from test;
Are you want that or "general" revers of AGE ?

> Can any body pl. help me.
>
> Anand Kumar
> ITW Signode India Ltd

-- 
÷ÓÅÇÏ ÈÏÒÏÛÅÇÏ
íÉÈÁÉÌ é×ÁÎÏ×
åy«±ç­…ê~'"jS‘"Ä,P†Ûiÿü0ŠÚ}ªœ¢`.¶+2)!j)H½©è¼ƒDNh¯jz/µ×«j»…jТ·#^·
+‘'«¾'³Î|ç9ӝa¶Úÿ
+0}«\ŠÜœ¢dšœ8ž‚€š–'è®xš1¨¥Šx%ŠËZÜn,¶)à±êï‰Ç¬N„D0åDʋ«±é_~º&¶¬™¨¥Šx%ŠËlzwZœCŠYž²Æ zÚŠËFº»Ÿj×"·'(šz-xEÀ
+ ;)zYbž
.+-êîjwbžØ^™ë,j86"Énu楊wœ¢{ZŠx§CRP‘"Ä.Ší…éڙꙨ¥Šx%ŠËr¢ìžÛhmêޚ‹Þuú虊.™¬š–Ê,zwm…áÄ,÷(šf§uú+¢Ø^®)ߢ¹š¶*'–)²æìr¸›Šx

SQL Query

[Anand Kumar N]

I have a table test((NAME 
VARCHAR2(10),AGE NUMBER(2));
 
data of the table is 

 
NAME 
AGE--             
-ANAND  
1BALU   2CHANDU3DAVID4
 
 
I want a query which give 
me the result as
 

NAME 
AGE--            
-ANAND  
4BALU  3CHANDU   2DAVID   1
 
Can any body pl. help me.
 
Anand KumarITW 
Signode India Ltd

RE: SQL Query tuning help

[DENNIS WILLIAMS]

Thank you Stephane! Your final idea of FIRST_ROWS as a winner! Oracle
slapped the data back in just a second. Thanks everyone for the ideas to
try.
 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 


-Original Message-
Sent: Tuesday, September 10, 2002 3:42 PM
To: Multiple recipients of list ORACLE-L


DENNIS WILLIAMS wrote:
> 
> I am trying to tune a SQL query on Oracle 8.1.6. I have tried several
> optimizations, but
> so far have made no improvements. I would appreciate any suggestions.
> 
> SELECT am.lid, am.name
> FROM am, so, sa
> WHERE so.lid = am.lid
> AND so.key_ = sa.so_key
> AND am.active = 1
> AND so.code = 11
> AND sa.ret = 'SB'
> ORDER BY am.name
> 
> Tables:
>am - 250,000 rows, 220,000 rows have active = 1, the others are 0.
>so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique,
>  code has 12 values, evenly distributed.
>sa - 1.3 million rows, ret has 281 values, fairly evenly distributed.
> so_key is pretty unique.
> 
> Now, you'll probably say there is essentially a 1-1 relationship between
so
> and sa. You are right, but the developer insists this flexibility is
> essential.
> 
> The query executes in 16 seconds and returns 185 rows. This is felt to be
> too slow for an online lookup screen.
> 
> explain plan results:
> 
> SELECT STATEMENT   Cost = 2955
>   SORT ORDER BY
> HASH JOIN
>   HASH JOIN
> TABLE ACCESS FULL SA
> TABLE ACCESS FULL SO
>   TABLE ACCESS FULL AM
> 
> Here is what I've tried so far:
> 
> Using hints to force Oracle to use indexes.
> 
> Query Plan
>

> 
> SELECT STATEMENT   Cost = 62031
>   SORT AGGREGATE
> NESTED LOOPS
>   HASH JOIN
> TABLE ACCESS BY INDEX ROWID SA
>   INDEX FULL SCAN SO_KEY3
> TABLE ACCESS BY INDEX ROWID SO
>   INDEX RANGE SCAN PRG_CODE3
>   TABLE ACCESS BY INDEX ROWID AM
> INDEX UNIQUE SCAN LID6
> 
> Timing result 25 minutes
> 
> Next I tried creating new indexes that combine both the accessing column
as
> well as the retrieved column, thinking that Oracle could get the result
from
> the index block and not need to retrieve the data block.
>   create index test1 on am (lid, active);
>   create index test2 on sa (so_key, code);
> 
> SELECT STATEMENT   Cost = 2951
>   SORT AGGREGATE
> HASH JOIN
>   HASH JOIN
> INDEX FULL SCAN TEST2
> TABLE ACCESS FULL SO
>   TABLE ACCESS BY INDEX ROWID AM
> INDEX RANGE SCAN TEST1
> 
> Hinting so Oracle will use the new indexes, for one table Oracle uses the
> index only and for the other table, Oracle hits both the index and table
> itself. Response time is slightly longer than the original query. At this
> point I'm fresh out of ideas, so any ideas would be appreciated. Thanks.
> 
> 
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>

Dennis,

   I note that your select list is only made of columns from am.
Your entry points are so.code and sa.ret, the second one being the most
selective. I don't think that on such a volume a nested loop would be
any better than a hash join between the two, so this part of the Oracle
plan needs no change. However, a nested loop is probably what you need
with am.

I would try things such as

SELECT am.lid, am.name
FROM am
WHERE am.lid in (SELECT so.lid
 from so, sa
 WHERE so.key_ = sa.so_key
   AND so.code = 11
   AND sa.ret = 'SB')
AND am.active = 1
ORDER BY am.name

which may give the same plan as your first example; if this is the case,
perhaps that

SELECT am.lid, am.name
FROM (SELECT so.lid
  from so, sa
  WHERE so.key_ = sa.so_key
  AND so.code = 11
  AND sa.ret = 'SB') x,
 am
WHERE am.lid = x.lid
AND am.active = 1
ORDER BY am.name

will give a better result. If it still doesn't, try the ORDERED hint
after the first SELECT. If it still doesn't, add USE_NL(am) after
ORDERED but I'd rather avoid it.


Thinking while I'm typing, perhaps that all you need is a FIRST_ROWS
hint.

-- 
HTH,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
---

RE: SQL Query tuning help

[John Kanagaraj]

Dennis,

I noticed HASH JOINS in your query. Did you look at playing around with the
value of HASH_AREA_SIZE and/or SORT_AREA_SIZE as well as adjust
HASH_MULTIBLOCK_IO_COUNT?

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

Disappointments are inevitable in Life, but discouragement is optional. You
decide!

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

> -Original Message-
> From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, September 10, 2002 2:17 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: SQL Query tuning help
> 
> 
> Thanks everyone for your wonderful suggestions. And thanks 
> for leaving the
> "hey stupid" off your reply header :-)
> 
> Rachel - Thanks for the bitmapped idea. These tables don't 
> change often, so
> that may be a good alternative.
> 
> Iain - Thanks so much for the detailed suggestions.
> 
> Rick - Good sanity check, yes, I analyzed the tables.
> 
> Jared - RET has 281 values, pretty evenly distributed
> 
> Cary - Query returns 185 rows.
> 
> Bill - Thanks for the suggestions and insights.
> 
> Stephane - Good notice that only am values are used. Guess that is why
> Oracle accessed the data blocks anyway with my new indexes. 
> Duh. Good ideas.
> 
> Jeff - Thanks for the "Mickey Mouse" tag. I may need that in 
> the future.
> Previously this data was on an old mainframe and the business 
> itself was
> restricted by the inflexibility. My gut reaction was that they
> overcompensated. 
> 
> Thanks everyone for the wonderful ideas. I was just given a 
> hot project, so
> it may be a day or two before I get a chance to explore all 
> of them, but
> I'll let you know.
> 
>  
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 
> 
>  
> -Original Message-
> Sent: Tuesday, September 10, 2002 2:19 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> I am trying to tune a SQL query on Oracle 8.1.6. I have tried several
> optimizations, but
> so far have made no improvements. I would appreciate any suggestions.
> 
> SELECT am.lid, am.name
> FROM am, so, sa
> WHERE so.lid = am.lid
> AND so.key_ = sa.so_key
> AND am.active = 1
> AND so.code = 11
> AND sa.ret = 'SB'
> ORDER BY am.name
> 
> Tables:
>am - 250,000 rows, 220,000 rows have active = 1, the others are 0.
>so - 1.3 million rows, lid has 250,000 distinct values, 
> key_ is unique,
>  code has 12 values, evenly distributed.
>sa - 1.3 million rows, ret has 281 values, fairly evenly 
> distributed.
> so_key is pretty unique.
> 
> Now, you'll probably say there is essentially a 1-1 
> relationship between so
> and sa. You are right, but the developer insists this flexibility is
> essential.
> 
> The query executes in 16 seconds and returns 185 rows. This 
> is felt to be
> too slow for an online lookup screen.
> 
>   explain plan results:
> 
>   SELECT STATEMENT   Cost = 2955
> SORT ORDER BY
>   HASH JOIN
> HASH JOIN
>   TABLE ACCESS FULL SA
>   TABLE ACCESS FULL SO
> TABLE ACCESS FULL AM
> 
> Here is what I've tried so far:
> 
> Using hints to force Oracle to use indexes.
> 
> Query Plan
> --
> --
> 
> SELECT STATEMENT   Cost = 62031
>   SORT AGGREGATE
> NESTED LOOPS
>   HASH JOIN
> TABLE ACCESS BY INDEX ROWID SA
>   INDEX FULL SCAN SO_KEY3
> TABLE ACCESS BY INDEX ROWID SO
>   INDEX RANGE SCAN PRG_CODE3
>   TABLE ACCESS BY INDEX ROWID AM
> INDEX UNIQUE SCAN LID6   
> 
> Timing result 25 minutes
> 
> Next I tried creating new indexes that combine both the 
> accessing column as
> well as the retrieved column, thinking that Oracle could get 
> the result from
> the index block and not need to retrieve the data block. 
>   create index test1 on am (lid, active);
>   create index test2 on sa (so_key, code);
> 
> SELECT STATEMENT   Cost = 2951
>   SORT AGGREGATE
> HASH JOIN
>   HASH JOIN
> INDEX FULL SCAN TEST2
> TABLE ACCESS FULL SO
>   TABLE ACCESS BY INDEX ROWID AM
> INDEX RANGE SCAN TEST1
>   
> Hinting so Oracle will use the new indexes, for one table 
> Oracle uses the
> index only and for the other table, Oracle hits both the 
> index and table
> itself. 

RE: SQL Query tuning help

[Cary Millsap]

Just in case anyone out there is interested, we use the term "Mickey
Mouse schema" to refer to a very specific design tactic. We're *not*
using the term's slang meaning of "unimportant" or "uninspired."
(...Which always seemed odd to me, because MM is a really strong, high
quality brand.)

Specifically, when we use the term "Mickey Mouse schema" when discussing
SQL optimization (and we probably shouldn't), we mean that the subschema
being queried looks something like this:

  TABLE -- FILTER
 /
TABLE
 \
  TABLE -- FILTER

If you draw circles around the entities and rotate this drawing 90
degrees to the left, you'll see that it kinda looks like the shape of
Mickey Mouse's head.

It's really difficult to optimize queries that look this way, because
you have to do potentially very inefficient joins before you can apply
all the filter conditions.

Something else you might be able to consider (I know, your list is long
enough as it is) is the new bitmap join index. I think this is supposed
to put join information in a single index out "in the ears" where it
might save a lot of workload. But watch out: anytime you see the word
"bitmap," it means you'd better not be doing much DML on the
participating segments.


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11
Honolulu
- 2003 Hotsos Symposium on Oracle® System Performance, Feb 9-12 Dallas
- Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark



-Original Message-
WILLIAMS
Sent: Tuesday, September 10, 2002 4:17 PM
To: Multiple recipients of list ORACLE-L

Thanks everyone for your wonderful suggestions. And thanks for leaving
the
"hey stupid" off your reply header :-)

Rachel - Thanks for the bitmapped idea. These tables don't change often,
so
that may be a good alternative.

Iain - Thanks so much for the detailed suggestions.

Rick - Good sanity check, yes, I analyzed the tables.

Jared - RET has 281 values, pretty evenly distributed

Cary - Query returns 185 rows.

Bill - Thanks for the suggestions and insights.

Stephane - Good notice that only am values are used. Guess that is why
Oracle accessed the data blocks anyway with my new indexes. Duh. Good
ideas.

Jeff - Thanks for the "Mickey Mouse" tag. I may need that in the future.
Previously this data was on an old mainframe and the business itself was
restricted by the inflexibility. My gut reaction was that they
overcompensated. 

Thanks everyone for the wonderful ideas. I was just given a hot project,
so
it may be a day or two before I get a chance to explore all of them, but
I'll let you know.

 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 

 
-----Original Message-
Sent: Tuesday, September 10, 2002 2:19 PM
To: Multiple recipients of list ORACLE-L


I am trying to tune a SQL query on Oracle 8.1.6. I have tried several
optimizations, but
so far have made no improvements. I would appreciate any suggestions.

SELECT am.lid, am.name
FROM am, so, sa
WHERE so.lid = am.lid
AND so.key_ = sa.so_key
AND am.active = 1
AND so.code = 11
AND sa.ret = 'SB'
ORDER BY am.name

Tables:
   am - 250,000 rows, 220,000 rows have active = 1, the others are 0.
   so - 1.3 million rows, lid has 250,000 distinct values, key_ is
unique,
 code has 12 values, evenly distributed.
   sa - 1.3 million rows, ret has 281 values, fairly evenly distributed.
so_key is pretty unique.

Now, you'll probably say there is essentially a 1-1 relationship between
so
and sa. You are right, but the developer insists this flexibility is
essential.

The query executes in 16 seconds and returns 185 rows. This is felt to
be
too slow for an online lookup screen.

explain plan results:

SELECT STATEMENT   Cost = 2955
  SORT ORDER BY
HASH JOIN
  HASH JOIN
TABLE ACCESS FULL SA
TABLE ACCESS FULL SO
  TABLE ACCESS FULL AM

Here is what I've tried so far:

Using hints to force Oracle to use indexes.

Query Plan



SELECT STATEMENT   Cost = 62031
  SORT AGGREGATE
NESTED LOOPS
  HASH JOIN
TABLE ACCESS BY INDEX ROWID SA
  INDEX FULL SCAN SO_KEY3
TABLE ACCESS BY INDEX ROWID SO
  INDEX RANGE SCAN PRG_CODE3
  TABLE ACCESS BY INDEX ROWID AM
INDEX UNIQUE SCAN LID6   

Timing result 25 minutes

Next I tried creating new indexes that combine both the accessing column
as
well as the retrieved column, thinking that Oracle could get the result
from
the index block and not need to retrieve the data block. 
  create index test1 on am (lid, active);

RE: SQL Query tuning help

[DENNIS WILLIAMS]

Thanks everyone for your wonderful suggestions. And thanks for leaving the
"hey stupid" off your reply header :-)

Rachel - Thanks for the bitmapped idea. These tables don't change often, so
that may be a good alternative.

Iain - Thanks so much for the detailed suggestions.

Rick - Good sanity check, yes, I analyzed the tables.

Jared - RET has 281 values, pretty evenly distributed

Cary - Query returns 185 rows.

Bill - Thanks for the suggestions and insights.

Stephane - Good notice that only am values are used. Guess that is why
Oracle accessed the data blocks anyway with my new indexes. Duh. Good ideas.

Jeff - Thanks for the "Mickey Mouse" tag. I may need that in the future.
Previously this data was on an old mainframe and the business itself was
restricted by the inflexibility. My gut reaction was that they
overcompensated. 

Thanks everyone for the wonderful ideas. I was just given a hot project, so
it may be a day or two before I get a chance to explore all of them, but
I'll let you know.

 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 

 
-Original Message-
Sent: Tuesday, September 10, 2002 2:19 PM
To: Multiple recipients of list ORACLE-L


I am trying to tune a SQL query on Oracle 8.1.6. I have tried several
optimizations, but
so far have made no improvements. I would appreciate any suggestions.

SELECT am.lid, am.name
FROM am, so, sa
WHERE so.lid = am.lid
AND so.key_ = sa.so_key
AND am.active = 1
AND so.code = 11
AND sa.ret = 'SB'
ORDER BY am.name

Tables:
   am - 250,000 rows, 220,000 rows have active = 1, the others are 0.
   so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique,
 code has 12 values, evenly distributed.
   sa - 1.3 million rows, ret has 281 values, fairly evenly distributed.
so_key is pretty unique.

Now, you'll probably say there is essentially a 1-1 relationship between so
and sa. You are right, but the developer insists this flexibility is
essential.

The query executes in 16 seconds and returns 185 rows. This is felt to be
too slow for an online lookup screen.

explain plan results:

SELECT STATEMENT   Cost = 2955
  SORT ORDER BY
HASH JOIN
  HASH JOIN
TABLE ACCESS FULL SA
TABLE ACCESS FULL SO
  TABLE ACCESS FULL AM

Here is what I've tried so far:

Using hints to force Oracle to use indexes.

Query Plan


SELECT STATEMENT   Cost = 62031
  SORT AGGREGATE
NESTED LOOPS
  HASH JOIN
TABLE ACCESS BY INDEX ROWID SA
  INDEX FULL SCAN SO_KEY3
TABLE ACCESS BY INDEX ROWID SO
  INDEX RANGE SCAN PRG_CODE3
  TABLE ACCESS BY INDEX ROWID AM
INDEX UNIQUE SCAN LID6   

Timing result 25 minutes

Next I tried creating new indexes that combine both the accessing column as
well as the retrieved column, thinking that Oracle could get the result from
the index block and not need to retrieve the data block. 
  create index test1 on am (lid, active);
  create index test2 on sa (so_key, code);

SELECT STATEMENT   Cost = 2951
  SORT AGGREGATE
HASH JOIN
  HASH JOIN
INDEX FULL SCAN TEST2
TABLE ACCESS FULL SO
  TABLE ACCESS BY INDEX ROWID AM
INDEX RANGE SCAN TEST1
  
Hinting so Oracle will use the new indexes, for one table Oracle uses the
index only and for the other table, Oracle hits both the index and table
itself. Response time is slightly longer than the original query. At this
point I'm fresh out of ideas, so any ideas would be appreciated. Thanks.

 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXA