Well...
Kashif, June 09, 2003 - 4:05 pm UTC
Hi Tom,
Thanks for the response. Some follow-ups:
1) The correlated part of the subquery, i.e.
'bbl1.book_ISBN = bbl2.book_ISBN
AND bbl1.book_name = bbl2.book_name'
appears after the HAVING keyword, which to me implies that the correlation is done after the aggregation. Is that not true? If anything, it seems to me that it would not be very performant, since they aggregate and then apply the filtering correlated join. Why would they not simply do:
SELECT bbl1.*, COUNT(*) DupeCount
FROM branch_book_list bbl1
WHERE bbl1.branch_name = 'Branch A'
GROUP BY bbl1.branch_name, bbl1.book_ISBN, bbl1.book_name
HAVING NOT EXISTS (
SELECT bbl2.*, COUNT(*)
FROM branch_book_list bbl2
WHERE branch_name = 'Branch B'
and bbl1.book_ISBN = bbl2.book_ISBN --Here...
AND bbl1.book_name = bbl2.book_name --And here...
GROUP BY bbl2.branch_name, bbl2.book_ISBN, bbl2.book_name
HAVING COUNT(*) = COUNT(ALL bbl1.book_ISBN))
..where the correlated joins of the subquery appear BEFORE the HAVING clause, and make it more intuitive? Are they not the same thing?
2) Your version of the query, though easier to read, in fact does not produce the same results. Some setup data:
create table branch_book_list
(BRANCH_NAME CHAR(10), BOOK_ISBN CHAR(13), BOOK_NAME CHAR(40))
/
insert into branch_book_list values (
'Branch A', '1-56592-401-0', 'Transact-SQL Programming')
/
insert into branch_book_list values (
'Branch A', '1-56592-578-5', 'Oracle SQL*Plus: The Definitive Guide')
/
insert into branch_book_list values (
'Branch A', '1-56592-756-7', 'Transact-SQL Cookbook')
/
insert into branch_book_list values (
'Branch B', '1-56592-401-0', 'Transact-SQL Programming')
/
insert into branch_book_list values (
'Branch B', '1-56592-756-7', 'Transact-SQL Cookbook')
/
insert into branch_book_list values (
'Branch B', '1-56592-948-9', 'Oracle SQL*Loader: The Definitive Guide')
/
Your query returns no rows, whereas the other query returns one row. Thanks in advance for any feedback.
Kashif
June 09, 2003 - 6:55 pm UTC
I cannot answer "why they did it that way" as I cannot imagine doing it anywhere near that way in the first place.
yes, it is not very performant, in fact, it is one of the slower ways to accomplish this task
use varchar2 and it'll work. or change the decode to use:
ops$tkyte@ORA920LAP> select book_isbn, book_name,
2 sum(decode( branch_name, rpad('Branch A',10), 1, 0 )) branch_a_cnt,
3 sum(decode( branch_name, rpad('Branch B',10), 1, 0 )) branch_b_cnt
4 from branch_book_list
5 where branch_name in ( 'Branch A', 'Branch B' )
6 group by book_isbn, book_name
7 having sum(decode( branch_name, rpad( 'Branch A', 10 ), 1, 0 )) !=
8 sum(decode( branch_name, rpad( 'Branch B', 10 ), 1, 0 ))
9 and sum(decode( branch_name, rpad( 'Branch A', 10 ), 1, 0 )) != 0
10 /
BOOK_ISBN BOOK_NAME BRANCH_A_CNT BRANCH_B_CNT
------------- ---------------------------------------- ------------ ------------
1-56592-578-5 Oracle SQL*Plus: The Definitive Guide 1 0
rpad
char is a hateful datatype -- suggest you avoid it like the plague -- just pretend it does not exist!
Having clause is redundant
Mikito Harakiri, June 09, 2003 - 6:31 pm UTC
select deptno, sum(sal) from emp
group by deptno
having count(1) > 3
is equivalent to:
select * from (
select deptno, sum(sal) sumsal, count(1) cnt
from emp group by deptno
) where cnt > 3
It doesn't matter where subquery filter is written: in inner, or outer query. A smart optimizer should be able to push the predicate down into the inner query.
June 09, 2003 - 7:31 pm UTC
Mikito, welcome back, long time no hear.
lets see, Websters defines redundant as:
Main Entry: re·dun·dant
Pronunciation: -d&nt
Function: adjective
Etymology: Latin redundant-, redundans, present participle of redundare to overflow -- more at REDOUND
Date: 1594
1 a :
exceeding what is necessary or normal : SUPERFLUOUS b : characterized by or containing an excess; specifically : using more words than necessary c : characterized by similarity or repetition <a group of particularly redundant brick buildings> d chiefly British : no longer needed for a job and hence laid off
2 : PROFUSE, LAVISH
3 : serving as a duplicate for preventing failure of an entire system (as a spacecraft) upon failure of a single component
Now, while the two queries above are "equivalent", the only one that contains redundant syntax would be -- well -- yours :)
You have a redundant SELECT
Yours uses more words than necessary
You are correct about the optimizer -- but I would use
having count(*) > 3
(what is UP with count(1) -- why why do people do that. It is so wrong. You want a count of records -- that is by design what count(*) is. count(1) is not any different then count('foobar') or count(55). It takes away meaning.)
Thanks.
Kashif, June 09, 2003 - 11:01 pm UTC
Thanks Tom, it makes more sense now. Though something just cropped up in my mind: when an AND condition is written after the HAVING clause, does it imply that it will be applied after the GROUP BY clause as part of the HAVING clause? Or is the AND condition evaluated before any aggregation takes place?
Also, I think what Mikito was trying to demonstrate was the following, with respect to the above query, and perhaps trying to eliminate the extra lines of code:
select book_isbn, book_name, branch_a_cnt, branch_b_cnt
from
(
select book_isbn, book_name,
sum(decode( branch_name, rpad('Branch A',10), 1, 0 )) branch_a_cnt,
sum(decode( branch_name, rpad('Branch B',10), 1, 0 )) branch_b_cnt
from branch_book_list
where branch_name in ( 'Branch A', 'Branch B' )
group by book_isbn, book_name
)
where branch_a_cnt != branch_b_cnt
and branch_a_cnt != 0
Kashif
June 10, 2003 - 8:09 am UTC
The entire having clause is processed AFTER the aggregation.
What extra lines of code? we have the same amount of "code". The optimizer is smart enough to know that:
select book_isbn, book_name,
sum(decode( branch_name, 'Branch A', 1, 0 )) branch_a_cnt,
sum(decode( branch_name, 'Branch B', 1, 0 )) branch_b_cnt
from branch_book_list
where branch_name in ( 'Branch A', 'Branch B' )
group by book_isbn, book_name
having sum(decode( branch_name, 'Branch A', 1, 0 )) !=
sum(decode( branch_name, 'Branch B', 1, 0 ))
and sum(decode( branch_name, 'Branch A', 1, 0 )) != 0
/
is using the same sum's
Count(*) vs Count(1)
John Kho, June 10, 2003 - 1:11 am UTC
You said:
<
(what is UP with count(1) -- why why do people do that. It is so wrong. You
want a count of records -- that is by design what count(*) is. count(1) is not
any different then count('foobar') or count(55). It takes away meaning.)
>
Well, I read a book called PL/SQL Reference published by O Reilly (can't remember the author) and a tip for faster queries was to use count(1) rather than count(*). But that was for Oracle 7 and 8i. Not too sure if it is still applicable for 9i.
June 10, 2003 - 8:27 am UTC
it was *never true*
did they have a benchmark?
7.3.4:
SELECT COUNT(*) FROM SYS.SOURCE$
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 10
Fetch 10 0.55 2.50 2441 3210 30 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 0.55 2.50 2441 3210 30 20
********************************************************************************
SELECT COUNT(1) FROM SYS.SOURCE$
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 10
Fetch 10 1.00 2.60 2438 3210 30 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 1.00 2.60 2438 3210 30 20
and 7.1.6
SELECT COUNT(*) FROM SYS.SOURCE$
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.01 0.01 0 0 0 10
Fetch 10 0.26 1.44 1302 1460 30 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 0.27 1.45 1302 1460 30 20
Misses in library cache during parse: 1
Optimizer hint: CHOOSE
Parsing user id: 10 (recursive depth: 1)
********************************************************************************
SELECT COUNT(1) FROM SYS.SOURCE$
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 10
Fetch 10 0.43 1.57 1302 1460 30 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 0.43 1.57 1302 1460 30 20
in 8i, they made count(1) and count(*) internally "the same". Probably in reaction to all of the systems "tuning" with count(1)'s ;)
Count(*) Vs Count(1)
kumar, June 10, 2003 - 7:50 am UTC
Tom,
Even I remember reading the same piece of advice ( Use count(1) instead of Count(*) ) in an Oracle Press Book for 8i.
1. Was this advice valid at any point of time ?
2. Is there really any difference in those choices ?
3. Whatever I may choose to use ( Count(*), Count(1) or Count(column) ), will the optimizer use a fast full scan given an unique index exists ?
Thanks
June 10, 2003 - 8:56 am UTC
count(1) is now internally optimized to be count(*) -- probably in reaction to that "advice".
count(*) vs count(1) - A classic Oracle Myth
Paul, June 10, 2003 - 8:02 am UTC
having and sum() not working surprisingly
Asim Naveed, June 12, 2003 - 7:48 am UTC
Hi,
Please consider the follwoing. Why the last query is not
working. Oracle version 9i release 2
SQL> DESC TIME;
Name Null? Type
-------------------------------- -------- -----------------
TIMEID NOT NULL NUMBER(6)
YEAR NUMBER(10)
QUARTER VARCHAR2(10)
MONTH VARCHAR2(10)
WEEK VARCHAR2(20)
DAY VARCHAR2(20)
SQL> DESC SALES;
Name Null? Type
----------------------------------------- -------- --------
SALESID NOT NULL NUMBER(11)
PRODUCTID NOT NULL NUMBER(10)
MARKETID NOT NULL NUMBER(10)
TIMEID NOT NULL NUMBER(6)
CATEGORYID NOT NULL NUMBER(4)
CLASSID NOT NULL NUMBER(4)
DISTRIBUTORID NOT NULL NUMBER(10)
SALESMANID NOT NULL NUMBER(10)
PRICE NOT NULL NUMBER(10)
VOLUME NOT NULL NUMBER(10)
FORECAST NUMBER(10)
TARGET NUMBER(10)
1 select time.quarter
2 from (select distinct time.quarter from time) time
3 where time.quarter
4 in
5 (select time.quarter
6 from sales, time
7 where sales.timeid = time.timeid
8 group by time.year, time.quarter
9* having sum(1*2) > 3)
SQL> /
QUARTER
----------
Qtr1-2000
Qtr2-2000
Qtr3-2000
Qtr4-2000
Qtr1-1998
Qtr2-1998
Qtr3-1998
Qtr4-1998
SQL> ED
Wrote file afiedt.buf
1 select time.quarter
2 from (select distinct time.quarter from time) time
3 where time.quarter
4 in
5 (select time.quarter
6 from sales, time
7 where sales.timeid = time.timeid
8 group by time.year, time.quarter
9* having sum(sales.price*sales.volume) > 3)
SQL> /
select time.quarter
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Why the last query is giving error
June 12, 2003 - 9:20 am UTC
metalink.oracle.com is where you want to go, file a tar please.
having vs where
Scott Wesley, June 16, 2003 - 8:01 pm UTC
G'day
There seems to be performance gains at times, using having instead of the where clause, for reasons I'm still trying to explain, but here is my example.
This is the statement I started with.
1 SELECT /*+ CHOOSE DRIVING_SITE(s) */
2 SUM(sales_excl_tax)
3 ,c.sales_rep_nbr
4 FROM ths_sales s
5 ,trd_sales_rep_customers c
6 WHERE c.acc_nbr = s.acc_nbr
7 AND s.state_code = c.state_code
8 AND c.sales_rep_nbr=5
9 AND s.fin_yr = 2003
10 AND s.state_code = 'WA'
11* GROUP BY c.sales_rep_nbr
This consistantly took about 22 seconds to run on my development box.
This query, however, consistantly took less than 10 seconds to run.
1 SELECT /*+ CHOOSE DRIVING_SITE(s) */
2 SUM(sales_excl_tax)
3 ,c.sales_rep_nbr
4 FROM ths_sales s
5 ,trd_sales_rep_customers c
6 WHERE c.acc_nbr = s.acc_nbr
7 AND s.state_code = c.state_code
8 AND s.fin_yr = 2003
9 AND s.state_code = 'WA'
10 GROUP BY c.sales_rep_nbr
11* HAVING c.sales_rep_nbr=5
Even when I removed the having clause.
So by adding the sales_rep_nbr condition into the where changed the explain plan somewhat, and slowed the query.
Where:
Id Parent Id Object Operation Options Pos
--------- --------- ----------------- ---------------- -------------------
0 Cost: 463 Card:1 SELECT STATEMENT REMOTE 463
1 0 SORT GROUP BY NOSORT 1
2 1 NESTED LOOPS 1
3 2 REMOTE 1
4 2 THS_SALES TABLE ACCESS BY INDEX ROWID 2
5 4 THSSALES_PK INDEX RANGE SCAN 1
Having:
Id Parent Id Object Operation Options Pos
-------------------- ------------------- ------------------ --------------
0 Cost: 10901 Card:1 SELECT STATEMENT REMOTE ####
1 0 FILTER 1
2 1 SORT GROUP BY 1
3 2 HASH JOIN 1
4 3 REMOTE 1
5 3 THS_SALES TABLE ACCESS FULL 2
Interesting....
June 17, 2003 - 7:11 am UTC
on of them pulled the table and joined (big bulky operation) and then filtered.
the other used that nested loops technique row by row by row by row.
do you have statistics on all objects and are they accurate.
HAVING clause of GROUP BY
cbarr, September 22, 2003 - 4:17 pm UTC
select cols , Aggr. from ( select with Group By Having Clause) Group By cols
pasko, September 26, 2003 - 6:19 am UTC
Hi Tom,
Thanks for the followup above.
I have a problem with a Group By Having Clause :
take a look at the following two Queries from a VIEW with a GROUP BY and HAVING CLAUSE
When i do a ' select without a group by ' , then the answer is fine.
But when i add a Group by to this in_line_view with a 'Group By and a Having Clause',
then strange things happen:
In one of our Databases, the connection is suddenly broken and
i get the ORA-03113: error ;
and in the other like in this example, it gives no rows back
although i know for sure that i should get some rows back.
Example :
SQL> SELECT timestart from vw_having_count_star where rownum < 5 ;
TIMESTAR
--------
24.09.03
24.09.03
02.09.03
02.09.03
SQL> SELECT COUNT(*) FROM vw_having_count_star ;
COUNT(*)
----------
0
SQL>SELECT * FROM v$version
BANNER
----
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
5 rows selected
My view was created as :
CREATE OR REPLACE FORCE VIEW VW_HAVING_COUNT_STAR
AS
SELECT
t.number_vals_received
, t.phoneNum
, t.TXid
, t.customerID
, t.entityID
, t.timestart
, COUNT (*) nchargedVALs
---
FROM SUMMARY_TRANS t, SUMMARY_TRANS_CHILD s
WHERE (( t.customerID IN
(
'21212', '6464'
)
AND t.timestart BETWEEN TO_DATE ('20030901 00:00:00','YYYYMMDDHH24:MI:SS')
AND TO_DATE ('20030930 23:59:59', 'YYYYMMDDHH24:MI:SS' )
)
)
AND t.status = 0
AND t.number_vals_received > 0
AND t.phoneNum = s.phoneNum
AND t.TXid = s.TXid
AND s.direction = 1
GROUP BY t.TXid,
t.phoneNum,
t.customerID,
t.entityID,
t.timestart,
t.number_vals_received
HAVING number_vals_received > COUNT (*)
My observation is :
if HAVING is omitted,then this works fine;
It seems we can't do selects of the form :
select aggregate_columns , aggregates( example. count(*) , sum )
from
(
inline_view_with_HAVING_clause
)
GROUP BY group_by_clause
Is that true Tom , or is that a Bug in our Software.
Thanks in advance.
September 26, 2003 - 6:23 am UTC
3113 = contact support, oracle is having a problem.
How to write this select
A reader, October 28, 2003 - 10:37 pm UTC
My data is as follows, and I need to find out the keys having status both 'Y' AND 'N'. How can achieve it..
SQL> CREATE TABLE TTEST ( KEY NUMBER, FLAG VARCHAR2(10));
Table created.
SQL>
SQL>
SQL> INSERT INTO TTEST VALUES( 1 , 'Y');
1 row created.
SQL> INSERT INTO TTEST VALUES( 2,'Y');
1 row created.
SQL> INSERT INTO TTEST VALUES( 2,'Y');
1 row created.
SQL> INSERT INTO TTEST VALUES( 3,'Y');
1 row created.
SQL> INSERT INTO TTEST VALUES( 4, 'Y');
1 row created.
SQL> INSERT INTO TTEST VALUES( 5, 'Y');
1 row created.
SQL> INSERT INTO TTEST VALUES( 5,'N');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM TTEST;
KEY FLAG
--------- ----------
1 Y
2 Y
2 Y
3 Y
4 Y
5 Y
5 N
7 rows selected.
October 29, 2003 - 6:33 am UTC
select key
from ttest
where flag in ( 'Y', 'N' )
group by key
having count(distinct flag) = 2;
More Group By
Vinnie, February 25, 2004 - 8:33 am UTC
Tom if I have the following:
table A
name varchar2
name_data blob
How would a write a query to return
a result set like the following:
select name, sum of dbms_lob.getlength(name_data) for length>4000, sum of dbms_lob.getlength(name_data) for length<=4000, count of the number of name with lob size>4000, count of the number of name with lob size <=4000
group by name
Thanks
February 25, 2004 - 9:46 am UTC
how does "select name, .... " fit into it? name would seem to be "almost unique, if not unique".
did you really want this at the NAME level or should this query return a single row with 4 counts.
regardless -- pretend my inline view is your query against table A, either way one of these two is what you asked for:
scott@ORA920PC> select count( case when dbms_lob_length < 1000 then 1 end ) a,
2 count( case when dbms_lob_length >= 1000 then 1 end ) b,
3 count( distinct case when dbms_lob_length < 1000 then name end ) c,
4 count( distinct case when dbms_lob_length >= 1000 then name end ) d
5 from (
6 select ename name, sal dbms_lob_length
7 from emp
8 )
9 /
A B C D
---------- ---------- ---------- ----------
2 12 2 12
scott@ORA920PC>
scott@ORA920PC>
scott@ORA920PC> select name,
2 count( case when dbms_lob_length < 1000 then 1 end ) over() a,
3 count( case when dbms_lob_length >= 1000 then 1 end ) over() b,
4 count( distinct case when dbms_lob_length < 1000 then name end ) over() c,
5 count( distinct case when dbms_lob_length >= 1000 then name end ) over() d
6 from (
7 select ename name, sal dbms_lob_length
8 from emp
9 )
10 /
NAME A B C D
-------- ---------- ---------- ---------- ----------
ADAMS 2 12 2 12
ALLEN 2 12 2 12
BLAKE 2 12 2 12
CLARK 2 12 2 12
FORD 2 12 2 12
JONES 2 12 2 12
KING 2 12 2 12
MARTIN 2 12 2 12
MILLER 2 12 2 12
SCOTT 2 12 2 12
TURNER 2 12 2 12
WARD 2 12 2 12
JAMES 2 12 2 12
SMITH 2 12 2 12
14 rows selected.
?
Vinnie, February 25, 2004 - 10:07 am UTC
Perhaps I need to explain more:)
I would like to know the size of the blob data for a given name where the LOB is stored inline or out.
Ex. Dataset
name blob size
SMITH 500
SMITH 10000
JONES 5000
JONES 6000
ANDREWS 100
The result I am looking for is:
NAME COUNT<=4000 BYTES COUNT>4000 BYTES
SMITH 1 500 1 10000
JONES 0 0 2 11000
ANDREWS 1 100 0 0
Hope this is a better example
February 25, 2004 - 11:19 am UTC
variation on a theme here.....
scott@ORA920PC> select name,
2 count( case when getlen <= 1000 then 1 end ) "cnt<=1000",
3 sum( case when getlen <= 1000 then getlen end ) "bytes",
4 count( case when getlen > 1000 then 1 end ) "cnt>1000",
5 sum( case when getlen > 1000 then getlen end ) "bytes"
6 from ( select job name, sal getlen from emp )
7 group by name
8 /
NAME cnt<=1000 bytes cnt>1000 bytes
------------------------------ ---------- ---------- ---------- ----------
ANALYST 0 2 6000
CLERK 2 1750 2 2400
MANAGER 0 3 8275
PRESIDENT 0 1 5000
SALESMAN 0 4 5600
Nice
Catherine, February 26, 2004 - 6:38 am UTC
Hi Tom,
Nice to meet you.I have the sample tables as follows and My Query's intention was "Pick up
the enames from emplang table who have all the lang skills listed in the lang table".
SQL> select * from emplang;
ENM LANGS
------------------------------ ------------------------------
Joe C++
Jack Pl/sql
James Java
Joe Java
Joe Pl/sql
Jack C++
Jack Java
Ford PHP
8 rows selected.
SQL> select * from lang;
LANGS
------------------------------
Java
Pl/sql
C++
1)
SQL> select distinct enm from emplang e1
2 where not exists(select * from lang where not exists
(select * from emplang e2 where e1.enm
3 = e2.enm and e2.langs = lang.langs))
4 /
ENM
------------------------------
Jack
Joe
2)
SQL> select distinct enm from emplang e
2 where e.langs = lang.langs
3 group by langs
4 having count(e.langs) = (select count(langs) from lang);
where e.langs = lang.langs
*
ERROR at line 2:
ORA-00904: invalid column name
But the second query fails.The first one is not mine but it works well.
My questions are
1)Could you please explain the first query with the *not exists* condition?
How it fetches the correct data?
2)Why the second query fails?
3)Any diff.ways that this query can be put?
Could you please add any enhancements to the query that can improve performance?
Please do reply.
Thanks in advance.
February 26, 2004 - 10:36 am UTC
homework?
how did you know to write the first query if you cannot explain it?
it would be a good exercise for you to write the psuedo code of that query.
in a nutshell -- it looks at every record in the EMP table and says only KEEP the record if there are not any LANGS in LANGS such that this record does not have that lang. confusing? perhaps, write out the psuedo code for that.
take each record in emplang
if we find a record in LANG such that there is not a record in EMPLANG for that enm/lang then do not keep this emplang record (the not exists on lang won't be satisified)
the 2cnd query fails for the obvious? reason that there is no lang table in scope on line 2. there is a subquery against lang, but there is no lang in the outer query.
perhaps you meant:
select enm
from emplang, lang
where emplang.langs = lang.langs
group by enm
having count(*) = ( select count(distinct langs) from lang )
/
which is probably the best way to code such a query.
Excellent
Catherine, February 27, 2004 - 12:15 am UTC
Hi Tom,
Thanks for your excellent explanation.Do you have any docs.
pertaining to *Oracle market share* in RDBMS market?Any useful link you have?Any help would be appreciated.
Thanks for your followup.
February 27, 2004 - 7:26 am UTC
goto www.oracle.com and search for
market share
Nice To see
Donovan, February 28, 2004 - 1:03 am UTC
Dear Tom,
Regarding Catherine's post,Both the queries work dandy but
I am also not confident in the *Not exists * condition.
How does the query proceed?The innermost subquery selects
rows but when we use not exists while proceeding up the data,Wouldn't that reverse the rows selected?Does it proceed
as
not (
not(true)) => true.
Iam confused with that.Could you please explain the each part of the query?
Thanks in advance.
Please do reply.
February 28, 2004 - 10:35 am UTC
SQL> select distinct enm from emplang e1
2 where not exists(select * from lang where not exists
(select * from emplang e2 where e1.enm
3 = e2.enm and e2.langs = lang.langs))
4 /
for each record in e1
loop
if we cannot find any records in LANG such that
there isn't a matching record for that LANG.LANGS and E1.ENM in the
original emplang table
then
this record in e1 must have all of the LANGS in the LANG table, output
this record
end if;
end loop
don't know how else to say it. just read it like it was a subroutine, maybe that'll help. read it as a big loop and each "where not exists" is just a lookup (not a negation, a selection criteria).
Thanks
Donovan, February 28, 2004 - 11:04 am UTC
Dear Tom,
Thanks for your response.
OK
Ram, March 01, 2004 - 12:53 am UTC
Dear Tom,
I need a query that calculates sum of sal deptwise and the
total of sal for the entire table.Can we use rollup and cube
for this requirement of the grouping function?I mean like
sql>select decode(grouping(deptno),10,..,20..
...
rollup ..
I need a query like this.I can use SQL*PLUS reporting tool
but is there any other way?
Could you please help?
Bye!
March 01, 2004 - 7:40 am UTC
1 select decode( grouping(deptno)+grouping(ename), 1, 'Dept total', 2, 'Total' ),
2 deptno, ename, sum(sal)
3 from emp
4* group by rollup( deptno, ename )
scott@ORA920PC> /
DECODE(GRO DEPTNO ENAME SUM(SAL)
---------- ---------- ---------- ----------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
Dept total 10 8750
20 FORD 3000
20 ADAMS 1100
20 JONES 2975
20 SCOTT 3000
20 SMITH 800
Dept total 20 10875
30 WARD 1250
30 ALLEN 1600
30 BLAKE 2850
30 JAMES 950
30 MARTIN 1250
30 TURNER 1500
Dept total 30 9400
Total 29025
18 rows selected.
Help me please
Nikhil, May 26, 2004 - 11:57 am UTC
HI Tom,
SELECT a.po_dist_id po_dist_id,
SUM(a.amount_received) Amount_received,
SUM(a.amount_invoiced) Amount_invoiced,
SUM(a.journal_amount) Journal_Amount,
period_name ,
a.PROJECT_CODE ,
a.JOURNAL_NAME journal_name
FROM
gl_view1 a,
( SELECT po_dist_id,SUM(gl_view1.amount_received),SUM(gl_view1.amount_invoiced) ,SUM(gl_view1.journal_amount)
FROM gl_view1
GROUP BY po_dist_id
HAVING SUM(gl_view1.amount_received)-SUM(gl_view1.amount_invoiced) != 0) b
WHERE
a.po_dist_id=b.po_dist_id
GROUP BY a.po_dist_id,period_name,PROJECT_CODE,a.journal_name
The above is my query, it is taking 15 mins to execute. How can I reduce the execution time.
"gl_view1" is a view, and is made up of three Unions.
Please advice us on the above query.
May 26, 2004 - 3:30 pm UTC
tongue in cheek answer: replace gl_view1 with
(select 0 po_dist_id, 0 amount_recieved, .... 'abc' journal_name from dual)
in all cases, it'll go fast.
I'm assuming gl_view1 is a view.
Probably, a complex view
I'm not able to assess the size of the underlying table(s). If in the billions of rows/gigabytes in size -- maybe 15 minutes is the speed of light.
my first inclination is to convert everything I see into analytics, so I'll give that a go:
SELECT a.po_dist_id po_dist_id,
SUM(a.amount_received) Amount_received,
SUM(a.amount_invoiced) Amount_invoiced,
SUM(a.journal_amount) Journal_Amount,
period_name ,
a.PROJECT_CODE ,
a.JOURNAL_NAME
FROM (select po_dist_id,
amount_received,
amount_invoiced,
journal_amount,
period_name,
PROJECT_CODE,
JOURNAL_NAME,
sum(amount_received) over (partition by po_dist_id)
sum_amount_received,
sum(amount_invoiced) over (partition by po_dist_id)
sum_amount_invoiced
from gl_view1
)
where sum_amount_received-sum_amount_invoiced <> 0
GROUP BY po_dist_id, period_name, PROJECT_CODE, journal_name
/
what is the optimizer doing?
Andy Knorr, August 13, 2004 - 2:22 am UTC
Tom,
when using grouping sets how do we see what the optimizer is doing? Explain plan only gives part of the story i.e. it does not show the base tables, only the temp ones created by oracle:
SQL*Plus: Release 9.2.0.5.0
control@DWDEV> l
1 select package_code, brand_code
2 from dim_product
3* group by grouping sets ((package_code), (brand_code))
control@DWDEV> /
572 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2911 Bytes=29110)
1 4 RECURSIVE EXECUTION OF 'SYS_LE_4_0'
2 4 RECURSIVE EXECUTION OF 'SYS_LE_4_1'
3 4 RECURSIVE EXECUTION OF 'SYS_LE_4_2'
4 0 TEMP TABLE TRANSFORMATION
5 4 VIEW (Cost=2 Card=1 Bytes=10)
6 5 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6652_7F82854F' (Cost=2 Card=1 Bytes=10)
August 13, 2004 - 10:09 am UTC
scott@ORA9IR2> delete from plan_table;
22 rows deleted.
scott@ORA9IR2>
scott@ORA9IR2> explain plan for
2 select ename, job from emp group by grouping sets( (ename), (job) )
3 /
Explained.
scott@ORA9IR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 1066 | 2 |
| 4 | TEMP TABLE TRANSFORMATION | | | | |
| 1 | RECURSIVE EXECUTION | SYS_LE_4_0 | | | |
| 0 | INSERT STATEMENT | | | | |
| 1 | LOAD AS SELECT | | | | |
| 2 | TABLE ACCESS FULL | EMP | | | |
| 2 | RECURSIVE EXECUTION | SYS_LE_4_1 | | | |
| 0 | INSERT STATEMENT | | | | |
| 1 | LOAD AS SELECT | | | | |
| 2 | SORT GROUP BY | | | | |
| 3 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66..| | | |
| 3 | RECURSIVE EXECUTION | SYS_LE_4_2 | | | |
| 0 | INSERT STATEMENT | | | | |
| 1 | LOAD AS SELECT | | | | |
| 2 | SORT GROUP BY | | | | |
| 3 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66..| | | |
| 5 | VIEW | | 1 | 13 | 2 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66..| 1 | 13 | 2 |
------------------------------------------------------------------------------
Note: cpu costing is off
25 rows selected.
Nice workaround, thanks heaps and keep up the good work.
Andy, August 16, 2004 - 12:40 am UTC
More on Group By
Lalit, October 11, 2004 - 10:14 am UTC
Hi Tom,
Hope you had a nice vacation. Here I am again to trouble you :)
I have similar doubt as Vinnie. Below is the table structure from where I am trying to retrive the data in a particular format.
CREATE TABLE MND_MAC_PARAM_ATTRIBUTE_MASTER (
PARAM_ATTRIBUTE_ID NUMBER(12) NOT NULL,
PARAM_ATTRIBUTE_CODE VARCHAR2(30) NOT NULL,
PARAM_ATTRIBUTE_NAME VARCHAR2(100) NOT NULL
);
ALTER TABLE MND_MAC_PARAM_ATTRIBUTE_MASTER
ADD ( PRIMARY KEY (PARAM_ATTRIBUTE_ID) ) ;
CREATE TABLE MND_MAC_PARAM_CONFIGURATION (
PARAM_CONFIG_ID NUMBER(12) NOT NULL,
PARAMETER_NAME VARCHAR2(200) NULL
);
ALTER TABLE MND_MAC_PARAM_CONFIGURATION
ADD ( PRIMARY KEY (PARAM_CONFIG_ID) ) ;
CREATE TABLE MND_MAC_PARAM_CONFIG_ATTRIBUTE (
PARAM_CONFIG_ID NUMBER(12) NOT NULL,
PARAM_ATTRIBUTE_ID NUMBER(12) NOT NULL,
PARAM_ATTRIBUTE_VALUE VARCHAR2(200) NULL
);
ALTER TABLE MND_MAC_PARAM_CONFIG_ATTRIBUTE
ADD ( PRIMARY KEY (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID) ) ;
ALTER TABLE MND_MAC_PARAM_CONFIG_ATTRIBUTE
ADD ( FOREIGN KEY (PARAM_ATTRIBUTE_ID)
REFERENCES MND_MAC_PARAM_ATTRIBUTE_MASTER ) ;
ALTER TABLE MND_MAC_PARAM_CONFIG_ATTRIBUTE
ADD ( FOREIGN KEY (PARAM_CONFIG_ID)
REFERENCES MND_MAC_PARAM_CONFIGURATION ) ;
insert into MND_MAC_PARAM_ATTRIBUTE_MASTER (PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_NAME, PARAM_ATTRIBUTE_CODE) values (25, 'Input UOM','INPUT_UOM');
insert into MND_MAC_PARAM_ATTRIBUTE_MASTER (PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_NAME, PARAM_ATTRIBUTE_CODE) values (29, 'KPI Indicator', 'KPI_IND');
insert into MND_MAC_PARAM_CONFIGURATION (PARAM_CONFIG_ID, PARAMETER_NAME) values (1, 'Current');
insert into MND_MAC_PARAM_CONFIGURATION (PARAM_CONFIG_ID, PARAMETER_NAME) values (2, 'Temperature 1');
insert into MND_MAC_PARAM_CONFIGURATION (PARAM_CONFIG_ID, PARAMETER_NAME) values (3, 'Temperature 2');
insert into MND_MAC_PARAM_CONFIGURATION (PARAM_CONFIG_ID, PARAMETER_NAME) values (4, 'Flow Rate 1' );
insert into MND_MAC_PARAM_CONFIGURATION (PARAM_CONFIG_ID, PARAMETER_NAME) values (5, 'Voltage XYZ');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (1, 29, 'N');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (2, 29, 'Y');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (3, 29, 'N');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (4, 29, 'Y');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (5, 29, 'N');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (1, 25, 'mA');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (2, 25, 'Degree F');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (3, 25, 'Degree F');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (4, 25, 'Tonne/day');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (5, 25, 'Volts');
When I am trying to run the sql, I am getting the out put in below format
SELECT
a.parameter_name,
decode(c.param_attribute_code,'KPI_IND',b.param_attribute_value,null) KPI_IND,
decode(c.param_attribute_code,'INPUT_UOM',b.param_attribute_value,null) INPUT_UOM
FROM MND_MAC_PARAM_CONFIGURATION a, MND_MAC_PARAM_CONFIG_ATTRIBUTE b, MND_MAC_PARAM_ATTRIBUTE_MASTER c
where a.param_config_id = b.param_config_id
AND c.param_attribute_id = b.param_attribute_id
AND c.param_attribute_code IN ('KPI_IND','INPUT_UOM');
/
PARAMETER_NAME KPI_IND INPUT_UOM
Current mA
Current N
Flow Rate 1 Tonne/day
Flow Rate 1 Y
Voltage XYZ Volts
Voltage XYZ N
Temperature 1 Degree F
Temperature 1 Y
Temperature 2 Degree F
Temperature 2 N
I hope the indentation is still there after submiting the question.
Is there any way I can club these records on the basis of PARAMETER_NAME to generate the output somthing like this:
PARAMETER_NAME KPI_IND INPUT_UOM
Current N mA
Flow Rate 1 Y Tonne/day
Voltage XYZ N Volts
Temperature 1 Y Degree F
Temperature 2 N Degree F
The records are grouped by parameter_name and now KPI_IND & INPUT_UOM for a PARAMETER_NAME are in single record
Appreciate your help.
Thanks
October 11, 2004 - 10:23 am UTC
select parameter_name, max(kpi_ind), max(input_uom)
from (YOUR_QUERY_GOES_RIGHT_HERE)
group by parameter_name;
or
SELECT
a.parameter_name,
max(decode(c.param_attribute_code,'KPI_IND',b.param_attribute_value,null) ) KPI_IND,
max(decode(c.param_attribute_code,'INPUT_UOM',b.param_attribute_value,null) )
INPUT_UOM
FROM MND_MAC_PARAM_CONFIGURATION a, MND_MAC_PARAM_CONFIG_ATTRIBUTE b,
MND_MAC_PARAM_ATTRIBUTE_MASTER c
where a.param_config_id = b.param_config_id
AND c.param_attribute_id = b.param_attribute_id
AND c.param_attribute_code IN ('KPI_IND','INPUT_UOM');
group by parameter_name
/
great
Lalit, October 11, 2004 - 10:34 am UTC
For the similar case, I used sum function for number data and was struggeling for varchar data. I was not sure if I can use max function for varchar fields. As usual you solved my problem.
Thanks for your help.
Refresh Fast On Commit Materialized View
Lalit K, October 18, 2004 - 8:17 am UTC
Tom,
I was trying to create a materialized view for the above query with "REFRESH FAST ON COMMIT" option, but it seems it is not getting refreshed. Below is what I am doing:
CREATE MATERIALIZED VIEW MND_WIDE_PARAM_ATTRIBUTE_MV
REFRESH FAST ON COMMIT
AS
SELECT
a.param_config_id,
a.parameter_name,
MAX(DECODE(c.param_attribute_code,'PARAM_TYPE',b.param_attribute_value,NULL)) PARAM_TYPE,
MAX(DECODE(c.param_attribute_code,'PARAM_VALUE',b.param_attribute_value,NULL)) PARAM_VALUE,
MAX(DECODE(c.param_attribute_code,'INPUT_UOM',b.param_attribute_value,NULL)) INPUT_UOM,
MAX(DECODE(c.param_attribute_code,'DISPLAY_UOM',b.param_attribute_value,NULL)) DISPLAY_UOM,
MAX(DECODE(c.param_attribute_code,'SI_UOM',b.param_attribute_value,NULL)) SI_UOM,
MAX(DECODE(c.param_attribute_code,'DC_METHOD',b.param_attribute_value,NULL)) DC_METHOD,
MAX(DECODE(c.param_attribute_code,'KPI_IND',b.param_attribute_value,NULL)) KPI_IND
FROM
MND_MAC_PARAM_CONFIGURATION a
, MND_MAC_PARAM_CONFIG_ATTRIBUTE b
, MND_MAC_PARAM_ATTRIBUTE_MASTER c
WHERE a.param_config_id = b.param_config_id
AND c.param_attribute_id = b.param_attribute_id
GROUP BY a.parameter_name, a.param_config_id
I also have materialized view logs created for all the 3 base tables. eg:
CREATE MATERIALIZED VIEW LOG ON
MND_MAC_PARAM_ATTRIBUTE_MASTER
WITH ROWID (param_attribute_id, param_attribute_name, default_value_ind, created_by, field_type_ind, created_date, modified_by, modified_date, param_attribute_code)
including new values
I am not sure if there are any restrictions on the materialized views or I am doing somthing wrong. Can you through some light?
Thanks
October 18, 2004 - 9:10 am UTC
full test case please -- exactly what you are doing. REMOVE ANYTHING YOU CAN from it of course (as few columns as possible, just what is needed). a script anyone on the planet would be able to run!
I was going to do it -- but your column names here don't match with anything from above. too much typing.
Details
Lalit K, October 18, 2004 - 11:16 am UTC
Sorry Tom.
Here is the exact script you can run and see the output.
CREATE TABLE MND_MAC_PARAM_ATTRIBUTE_MASTER (
PARAM_ATTRIBUTE_ID NUMBER(12) NOT NULL,
PARAM_ATTRIBUTE_CODE VARCHAR2(30) NOT NULL,
PARAM_ATTRIBUTE_NAME VARCHAR2(100) NOT NULL
);
ALTER TABLE MND_MAC_PARAM_ATTRIBUTE_MASTER
ADD ( PRIMARY KEY (PARAM_ATTRIBUTE_ID) ) ;
CREATE TABLE MND_MAC_PARAM_CONFIGURATION (
PARAM_CONFIG_ID NUMBER(12) NOT NULL,
PARAMETER_NAME VARCHAR2(200) NULL
);
ALTER TABLE MND_MAC_PARAM_CONFIGURATION
ADD ( PRIMARY KEY (PARAM_CONFIG_ID) ) ;
CREATE TABLE MND_MAC_PARAM_CONFIG_ATTRIBUTE (
PARAM_CONFIG_ID NUMBER(12) NOT NULL,
PARAM_ATTRIBUTE_ID NUMBER(12) NOT NULL,
PARAM_ATTRIBUTE_VALUE VARCHAR2(200) NULL
);
ALTER TABLE MND_MAC_PARAM_CONFIG_ATTRIBUTE
ADD ( PRIMARY KEY (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID) ) ;
ALTER TABLE MND_MAC_PARAM_CONFIG_ATTRIBUTE
ADD ( FOREIGN KEY (PARAM_ATTRIBUTE_ID)
REFERENCES MND_MAC_PARAM_ATTRIBUTE_MASTER ) ;
ALTER TABLE MND_MAC_PARAM_CONFIG_ATTRIBUTE
ADD ( FOREIGN KEY (PARAM_CONFIG_ID)
REFERENCES MND_MAC_PARAM_CONFIGURATION ) ;
-- Insert Script
insert into MND_MAC_PARAM_ATTRIBUTE_MASTER (PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_NAME, PARAM_ATTRIBUTE_CODE) values (23, 'Parameter Type (Raw / Calculated)', 'PARAM_TYPE');
insert into MND_MAC_PARAM_ATTRIBUTE_MASTER (PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_NAME, PARAM_ATTRIBUTE_CODE) values (24, 'Paraneter Value', 'PARAM_VALUE');
insert into MND_MAC_PARAM_ATTRIBUTE_MASTER (PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_NAME, PARAM_ATTRIBUTE_CODE) values (25, 'Input UOM', 'INPUT_UOM');
insert into MND_MAC_PARAM_ATTRIBUTE_MASTER (PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_NAME, PARAM_ATTRIBUTE_CODE) values (26, 'Display UOM', 'DISPLAY_UOM');
insert into MND_MAC_PARAM_ATTRIBUTE_MASTER (PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_NAME, PARAM_ATTRIBUTE_CODE) values (27, 'Storage UOM', 'SI_UOM');
insert into MND_MAC_PARAM_ATTRIBUTE_MASTER (PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_NAME, PARAM_ATTRIBUTE_CODE) values (28, 'Data Collection Method (Manual / Automatic)', 'DC_METHOD');
insert into MND_MAC_PARAM_ATTRIBUTE_MASTER (PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_NAME, PARAM_ATTRIBUTE_CODE) values (29, 'KPI Indicator', 'KPI_IND');
insert into MND_MAC_PARAM_CONFIGURATION (PARAM_CONFIG_ID, PARAMETER_NAME) values (1, 'Current');
insert into MND_MAC_PARAM_CONFIGURATION (PARAM_CONFIG_ID, PARAMETER_NAME) values (2, 'Temperature 1');
insert into MND_MAC_PARAM_CONFIGURATION (PARAM_CONFIG_ID, PARAMETER_NAME) values (3, 'Temperature 2');
insert into MND_MAC_PARAM_CONFIGURATION (PARAM_CONFIG_ID, PARAMETER_NAME) values (4, 'Flow Rate 1' );
insert into MND_MAC_PARAM_CONFIGURATION (PARAM_CONFIG_ID, PARAMETER_NAME) values (5, 'Voltage XYZ');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (1, 29, 'N');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (2, 29, 'Y');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (3, 29, 'N');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (4, 29, 'Y');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (5, 29, 'N');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (1, 25, 'mA');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (2, 25, 'Degree F');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (3, 25, 'Degree F');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (4, 25, 'Tonne/day');
insert into MND_MAC_PARAM_CONFIG_ATTRIBUTE (PARAM_CONFIG_ID, PARAM_ATTRIBUTE_ID, PARAM_ATTRIBUTE_VALUE) values (5, 25, 'Volts');
-- Creating MATERIALIZED VIEW LOG for above 3 tables
CREATE MATERIALIZED VIEW LOG ON
MND_MAC_PARAM_ATTRIBUTE_MASTER
WITH ROWID (param_attribute_id, param_attribute_name, param_attribute_code)
including new values;
CREATE MATERIALIZED VIEW LOG ON
mnd_mac_param_configuration
WITH ROWID (param_config_id, parameter_name)
including new values;
CREATE MATERIALIZED VIEW LOG ON
mnd_mac_param_config_attribute
WITH ROWID (param_config_id, param_attribute_id, param_attribute_value)
including new values;
-- Creating MATERIALIZED VIEW MND_WIDE_PARAM_ATTRIBUTE_MV
CREATE MATERIALIZED VIEW MND_WIDE_PARAM_ATTRIBUTE_MV
REFRESH FAST ON COMMIT
AS
SELECT
a.param_config_id,
a.parameter_name,
MAX(DECODE(c.param_attribute_code,'PARAM_TYPE',b.param_attribute_value,NULL)) PARAM_TYPE,
MAX(DECODE(c.param_attribute_code,'PARAM_VALUE',b.param_attribute_value,NULL)) PARAM_VALUE,
MAX(DECODE(c.param_attribute_code,'INPUT_UOM',b.param_attribute_value,NULL)) INPUT_UOM,
MAX(DECODE(c.param_attribute_code,'DISPLAY_UOM',b.param_attribute_value,NULL)) DISPLAY_UOM,
MAX(DECODE(c.param_attribute_code,'SI_UOM',b.param_attribute_value,NULL)) SI_UOM,
MAX(DECODE(c.param_attribute_code,'DC_METHOD',b.param_attribute_value,NULL)) DC_METHOD,
MAX(DECODE(c.param_attribute_code,'KPI_IND',b.param_attribute_value,NULL)) KPI_IND
FROM
MND_MAC_PARAM_CONFIGURATION a
, MND_MAC_PARAM_CONFIG_ATTRIBUTE b
, MND_MAC_PARAM_ATTRIBUTE_MASTER c
WHERE a.param_config_id = b.param_config_id
AND c.param_attribute_id = b.param_attribute_id
GROUP BY a.parameter_name, a.param_config_id;
SQL> select param_config_id, parameter_name, input_uom, kpi_ind from MND_WIDE_PARAM_ATTRIBUTE_MV;
PARAM_CONFIG_ID PARAMETER_NAME INPUT_UOM KPI_IND
--------------- ------------------ ------------ -------
1 Current mA N
4 Flow Rate 1 Tonne/day Y
5 Voltage XYZ Volts N
2 Temperature 1 Degree F Y
3 Temperature 2 Degree F N
SQL>
SQL> update mnd_mac_param_configuration set parameter_name = 'Current Changed' where param_config_id=1;
1 row updated
SQL> commit;
Commit complete
SQL> select param_config_id, parameter_name, input_uom, kpi_ind from MND_WIDE_PARAM_ATTRIBUTE_MV;
PARAM_CONFIG_ID PARAMETER_NAME INPUT_UOM KPI_IND
--------------- ------------------ ------------ -------
1 Current mA N
4 Flow Rate 1 Tonne/day Y
5 Voltage XYZ Volts N
2 Temperature 1 Degree F Y
3 Temperature 2 Degree F N
If you see, even I have commited the changes, they are not reflecting in the materialized view. Not sure if I am doing somthing wrong here. What I expect is, if I modify anthing in the base tables, the materialized view should get refreshed after the base table commit.
October 18, 2004 - 11:49 am UTC
</code>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96520/mv.htm#40978 <code>
<quote>
If the materialized view has one of the following, then fast refresh is supported only on conventional DML inserts and direct loads.
* Materialized views with MIN or MAX aggregates
* Materialized views which have SUM(expr) but no COUNT(expr)
* Materialized views without COUNT(*)
</quote>
the status of fast refresh is DIRLOAD_LIMITEDDML in user_mviews for this - due to the MAX().
The MV staleness became "UNUSABLE" after your insert.
Other Options
Lalit K, October 19, 2004 - 6:36 am UTC
This means I won't be able to use "refresh on commit" materialized views here.
My requirement is, I want to convert data from rows into columns. As the # of records in the base table is going to be huge (100000000K records), I wanted to go with "refresh on commit" materialized view which will store the data itself....resulting in fast retrival of data. Important point here was, for every commit in the base tables, this materialized view should get refreshed "incrementally".
Can you suggest some options which can be looked into, for my problem.
Thanks
October 19, 2004 - 9:24 am UTC
if the goal was/is fast retrieval of data at the expense of the modifications -- you should seriously consider changing your structures to represent that
that is what I would do, have the insert routines use this structure in the first place. (that is what I have done)
deepa, November 03, 2004 - 6:44 am UTC
hi i am having a query. In which v have 2 field like dept name and dept salary.
And i want the depat name whose occurance is maximum.
November 03, 2004 - 7:22 am UTC
sounds like homework.....
it is one of my interview questions for people that say they know sql......
so, search for
interview questions
and you should be able to get it from there (and also learn why the question you asked is ambigous and has three or so answers that return different results ...)
Question about having
Mariana, January 05, 2005 - 2:11 pm UTC
Hi,Tom,
In your previous example:
select book_isbn, book_name,
sum(decode( branch_name, 'Branch A', 1, 0 )) branch_a_cnt,
sum(decode( branch_name, 'Branch B', 1, 0 )) branch_b_cnt
from branch_book_list
where branch_name in ( 'Branch A', 'Branch B' )
group by book_isbn, book_name
having sum(decode( branch_name, 'Branch A', 1, 0 )) !=
sum(decode( branch_name, 'Branch B', 1, 0 ))
and sum(decode( branch_name, 'Branch A', 1, 0 )) != 0
when i use having sum(decode( branch_name, 'Branch A', 1, 0 ) , this function will be executed twice?
If yes,why i can'not put alias like:
select sum(decode( branch_name, 'Branch A', 1, 0 )) AAA
and then ask having AAA...?
Have a nice day
January 05, 2005 - 7:24 pm UTC
it'll optimize what it can, what it needs to. (an alias wouldn't mean "i won't do it twice" regardless, sql is inheritly non-procedural, it'll do whatever it wants to do at the end of the day....)
About having in groups
Mariana, January 06, 2005 - 1:12 am UTC
Hi,Tom,
But if at this select i have to make group by
to 500,000 records,so will
having sum(decode( branch_name, 'Branch A', 1, 0 )) !=
sum(decode( branch_name, 'Branch B', 1, 0 )) make the performance slower or is it better to write:
select * from
(select book_isbn, book_name,
sum(decode( branch_name, 'Branch A', 1, 0 )) branch_a_cnt,
sum(decode( branch_name, 'Branch B', 1, 0 )) branch_b_cnt
from branch_book_list
where branch_name in ( 'Branch A', 'Branch B' )
group by book_isbn, book_name ) AAA
WHERE AAA.branch_a_cnt != BBB.branch_b_cnt
Thank's for the answer.
January 06, 2005 - 10:43 am UTC
6 one way, 1/2 dozen the other -- for the database is going to rewrite your query anyhow.
ops$tkyte@ORA9IR2> create table branch_book_list
2 ( book_isbn number, book_name varchar2(30), branch_name varchar2(30) );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'branch_book_list', numrows => 500000, numblks => 50000 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
4 rows deleted.
ops$tkyte@ORA9IR2> explain plan for
2 select book_isbn, book_name,
3 sum(decode( branch_name, 'Branch A', 1, 0 )) branch_a_cnt,
4 sum(decode( branch_name, 'Branch B', 1, 0 )) branch_b_cnt
5 from branch_book_list
6 where branch_name in ( 'Branch A', 'Branch B' )
7 group by book_isbn, book_name
8 having sum(decode( branch_name, 'Branch A', 1, 0 )) !=
9 sum(decode( branch_name, 'Branch B', 1, 0 ))
10 and sum(decode( branch_name, 'Branch A', 1, 0 )) != 0
11 /
Explained.
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 229K| 7615 |
|* 1 | FILTER | | | | |
| 2 | SORT GROUP BY | | 5000 | 229K| 7615 |
|* 3 | TABLE ACCESS FULL | BRANCH_BOOK_LIST | 5000 | 229K| 7590 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM(DECODE("BRANCH_BOOK_LIST"."BRANCH_NAME",'Branch
A',1,0))<>SUM(DECODE("BRANCH_BOOK_LIST"."BRANCH_NAME",'Branch B',1,0))
AND SUM(DECODE("BRANCH_BOOK_LIST"."BRANCH_NAME",'Branch A',1,0))<>0)
3 - filter("BRANCH_BOOK_LIST"."BRANCH_NAME"='Branch A' OR
"BRANCH_BOOK_LIST"."BRANCH_NAME"='Branch B')
Note: cpu costing is off
20 rows selected.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
4 rows deleted.
ops$tkyte@ORA9IR2> explain plan for
2 select *
3 from (
4 select book_isbn, book_name,
5 sum(decode( branch_name, 'Branch A', 1, 0 )) branch_a_cnt,
6 sum(decode( branch_name, 'Branch B', 1, 0 )) branch_b_cnt
7 from branch_book_list
8 where branch_name in ( 'Branch A', 'Branch B' )
9 group by book_isbn, book_name
10 )
11 where branch_a_cnt != branch_b_cnt
12 and branch_a_cnt != 0
13 /
Explained.
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 229K| 7615 |
|* 1 | FILTER | | | | |
| 2 | SORT GROUP BY | | 5000 | 229K| 7615 |
|* 3 | TABLE ACCESS FULL | BRANCH_BOOK_LIST | 5000 | 229K| 7590 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM(DECODE("BRANCH_BOOK_LIST"."BRANCH_NAME",'Branch
A',1,0))<>SUM(DECODE("BRANCH_BOOK_LIST"."BRANCH_NAME",'Branch B',1,0))
AND SUM(DECODE("BRANCH_BOOK_LIST"."BRANCH_NAME",'Branch A',1,0))<>0)
3 - filter("BRANCH_BOOK_LIST"."BRANCH_NAME"='Branch A' OR
"BRANCH_BOOK_LIST"."BRANCH_NAME"='Branch B')
Note: cpu costing is off
20 rows selected.
<b>see, no difference in the filters, plans, anything. they are the same. </b>
Help
Richard, January 06, 2005 - 12:45 pm UTC
Hi Tom,
Any other way to put this query?
SELECT
s.prod_id,
AVG(s.amount_sold/s.quantity_sold)
AVG_selling_price
FROM sales s
GROUP BY s.prod_id
HAVING EXISTS ( SELECT p.prod_id FROM products p
WHERE s.prod_id = p.prod_id AND
AVG(s.amount_sold/s.quantity_sold)
> p.prod_min_price*1.10)
ORDER BY 1;
January 06, 2005 - 12:51 pm UTC
select s.prod_id, a.avg_selling_price
from ( select prod_id,
avg( decode(quantity_sold,
0,to_number(null),
amount_sold/quantity_sold) ) avg_selling_price
from sales
group by prod_id ) S,
products P
where s.prod_id = p.prod_id
and avg_selling_price > p.prod_min_price * 1.1
order by s.prod_id;
assuming prod_id is the primary key of products
Having clause works only on Left Hand Side
Ravi, January 21, 2005 - 8:10 am UTC
Looks like Having clause only works if the Grouped Column is on the Left Hand side. Is that right?
A practical Example in 8i:
1 select dummy from dual
2 where (Select dummy from dual) = dummy
3 group by dummy
4* having dummy =(select dummy from dual)
PRODsql -> /
D
-
X
1 select dummy from dual
2 where (Select dummy from dual) = dummy
3 group by dummy
4* having (select dummy from dual) = dummy
PRODsql -> /
having (select dummy from dual) = dummy
*
ERROR at line 4:
ORA-00979: not a GROUP BY expression
2) Is having not similar to Where clause in the way it works:
1 select dummy from dual
2 where (Select dummy from dual) = dummy
3 group by dummy
4* having (select dummy from dual) = (Select dummy from dual)
PRODsql -> /
having (select dummy from dual) = (Select dummy from dual)
*
ERROR at line 4:
ORA-00979: not a GROUP BY expression
alias for the group by clause
Thiru, January 27, 2005 - 3:38 pm UTC
If I have an IN paramter in a proc that acts as a groupby column, is there a way to
use an alias for the group by clause in the select statement?
for eg:
p_groupby is the IN parameter and the group by is from different tables.
select ....,
decode(
p_groupby,
'column 1',
t1.column_1,
'column 2',
t2.column_2,
'column 3',
t3.column_3) <alias if possible over here>
from t1,t2,t3
where...
group by decode(
p_groupby,
'column 1',
t1.column_1,
'column 2',
t2.column_2,
'column 3',
t3.column_3) <use alias here instead of the whole decode>
January 27, 2005 - 4:46 pm UTC
select ......
from ( select ...., decode( ..... ) X from t1, t2, t3 where ... )
group by X
will work. apply the aggregates to the inline view
q on potential use of having clause
A reader, November 04, 2005 - 4:46 pm UTC
Tom this may be a simple q for you.
Consider the table t with following data:
scott@ora92> drop table t;
Table dropped.
scott@ora92> create table t( x varchar2(10), y varchar2(10), z varchar2(10), total_qty number);
Table created.
scott@ora92>
scott@ora92> insert into t values( 'x1', 'y1', 'z1', 1 );
1 row created.
scott@ora92> insert into t values( 'x1', 'y1', 'z2', 5 );
1 row created.
scott@ora92> insert into t values( 'x2', 'y2', 'z3', 6 );
1 row created.
scott@ora92> insert into t values( 'x3', 'y3', 'z4', 2 );
1 row created.
scott@ora92>
scott@ora92> commit;
Commit complete.
scott@ora92> select * From t;
X Y Z TOTAL_QTY
---------- ---------- ---------- ----------
x1 y1 z1 1
x1 y1 z2 5
x2 y2 z3 6
x3 y3 z4 2
The requirement is to get all rows such that sum of total_qty for same values of x and y are > 5.
That means the results of the select would be
x1 y1 z1 1
x1 y1 z2 5
x2 y2 z3 6
The first two are selected even though each row
does not have individually a value of total_qty > 5
but when we group them by x and y, the sum is 6.
November 04, 2005 - 5:41 pm UTC
ops$tkyte@ORA10GR2> select *
2 from (
3 select t.*, sum(total_qty) over (partition by x,y) stq
4 from t
5 )
6 where stq > 5;
X Y Z TOTAL_QTY STQ
------ ------ ------ ---------- ----------
x1 y1 z1 1 6
x1 y1 z2 5 6
x2 y2 z3 6 6
thanx!
Menon, November 04, 2005 - 6:06 pm UTC
I was going to post the following query
select t.*
from
(
select x, y, z, total_qty
from t
) t,
(
select x, y, sum(total_qty)
from t
group by x, y
having sum(total_qty) > 5
)tt
where t.x = tt.x
and t.y = tt.y;
And say that this was not the answer I was looking for - I was looking for analytics - which is what you provided:)
Thanx!
Btw, the previous question was by me (Menon) - forgot to sign my name.
Issue with HAVING clause in Stored Procedure
Priya, August 28, 2006 - 8:04 pm UTC
Hi Tom,
This is the situation.I was hoping to get your status to ask a question and I couldnt get through. so I am posting here.
SQL> spool on;
SQL> create table cb_intfc(
2 OPPORTUNITY_ID VARCHAR2(45),
3 CBRE_LEGACY_SRC_ID VARCHAR2(60)
4 );
Table created.
SQL> INSERT INTO CB_INTFC VALUES('118304','15546');
1 row created.
SQL>
SQL> InSERT INTO CB_INTFC VALUES('118305','15546');
1 row created.
SQL>
SQL> INSERT INTO CB_INTFC VALUES('118306','15462');
1 row created.
SQL>
SQL> INSERT INTO CB_INTFC VALUES('118307','15785');
1 row created.
SQL> commit;
Commit complete.
SQL> CREATE TABLE CB_INTFC_ERROR_CHK
2 (
3 OPPORTUNITY_ID VARCHAR2(45),
4 MSG VARCHAR2(2000),
5 LASTUPDATED DATE
6 )
7 ;
Table created.
Now I have a procedure that basically gets the data from this table in a cursor
and I want the procedure to raise an exception when there is more than one cbre_legacy_src_id
in the table cb_intfc. So I am doing this
SQL> CREATE OR REPLACE PROCEDURE Cb_INTFC_CHK
2 IS
3 BEGIN
4 DECLARE
5 LSID VARCHAR2(60);
6 LSID_COUNT NUMBER(2);
7 propertyid_cnt NUMBER (2);
8 msg VARCHAR2 (2000);
9 well_error EXCEPTION;
10 CURSOR opp_list
11 IS
12 SELECT a.opportunity_id, a.cbre_legacy_src_id
13 FROM CB_INTFC a;
14 BEGIN
15
16 DELETE FROM CB_INTFC_ERROR_CHK;
17 COMMIT;
18 INSERT INTO CB_INTFC_ERROR_CHK(MSG) VALUES('Procedure started at '||TO_CHAR(SYSDATE,'mm-dd-y
yyy hh:mi:ss am'));
19
20 FOR c1 IN opp_list
21 LOOP
22 BEGIN
23 LSID :='';
24 LSID_COUNT :=0;
25 propertyid_cnt :=0;
26
27 --Check if the CBRE Legacy Src ID is not null.If not null, then update the OppID for the corres
ponding propertyid in the well.
28 IF c1.CBRE_LEGACY_SRC_ID <> ' ' THEN
29 SELECT COUNT(1), CBRE_LEGACY_SRC_ID INTO LSID_COUNT, LSID FROM CB_INTFC
30 WHERE CBRE_LEGACY_SRC_ID = c1.CBRE_LEGACY_SRC_ID
31 GROUP BY CBRE_LEGACY_SRC_ID;
32
33 IF LSID_COUNT > 1 THEN
34 msg :=
35 'Legacy Source ID - '
36 || LSID
37 || ' - appears twice in the interface';
38 RAISE well_error;
39 ELSE
40 SELECT COUNT(1) INTO propertyid_cnt
41 FROM CB_PROPERTY WHERE PROPERTYID = TO_NUMBER(c1.cbre_legacy_src_id);
42
43 IF propertyid_cnt > 0 THEN
44 UPDATE CB_PROPERTY SET OPPORTUNITY_ID = c1.opportunity_id WHERE PROPERTYID = TO_NUMBER(c1.
cbre_legacy_src_id);
45 ELSE
46 msg :=
47 'Legacy Source ID - '
48 || c1.cbre_legacy_Src_id
49 || ' - is not a valid id';
50 RAISE well_error;
51 END IF;
52 END IF;
53 END IF;
54 EXCEPTION
55 WHEN well_error
56 THEN
57 INSERT INTO CB_INTFC_ERROR_CHK
58 (opportunity_id, msg, lastupdated
59 )
60 VALUES (c1.opportunity_id, msg, SYSDATE
61 );
62 END;
63 END LOOP;
64 INSERT INTO CB_INTFC_ERROR_chk(MSG) VALUES('Procedure ended at '||TO_CHAR(SYSDATE,'mm-dd-yyy
y hh:mi:ss am'));
65
66 COMMIT;
67 END;
68 END Cb_INTFC_CHK;
69 /
Procedure created.
SQL> exec cb_intfc_chk;
PL/SQL procedure successfully completed.
This is the log from the error table which is fine.
SQL> select * from cb_intfc_error_chk;
OPPORTUNITY_ID
---------------------------------------------
MSG
--------------------------------------------------------------------------------
LASTUPDAT
---------
Procedure started at 08-28-2006 04:32:46 pm
118304
Legacy Source ID - 15546 - appears twice in the interface
28-AUG-06
OPPORTUNITY_ID
---------------------------------------------
MSG
--------------------------------------------------------------------------------
LASTUPDAT
---------
118305
Legacy Source ID - 15546 - appears twice in the interface
28-AUG-06
118306
Legacy Source ID - 15462 - is not a valid id
OPPORTUNITY_ID
---------------------------------------------
MSG
--------------------------------------------------------------------------------
LASTUPDAT
---------
28-AUG-06
Procedure ended at 08-28-2006 04:32:46 pm
SQL>
However, this procedure runs fine if I dont have the Having count(1) > 1 in the procedure. If I include that, it gives the following
SQL> CREATE OR REPLACE PROCEDURE Cb_INTFC_CHK
2 IS
3 BEGIN
4 DECLARE
5 LSID VARCHAR2(60);
6 LSID_COUNT NUMBER(2);
7 propertyid_cnt NUMBER (2);
8 msg VARCHAR2 (2000);
9 well_error EXCEPTION;
10 CURSOR opp_list
11 IS
12 SELECT a.opportunity_id, a.cbre_legacy_src_id
13 FROM CB_INTFC a;
14 BEGIN
15
16 DELETE FROM CB_INTFC_ERROR_CHK;
17 COMMIT;
18 INSERT INTO CB_INTFC_ERROR_CHK(MSG) VALUES('Procedure started at '||TO_CHAR(SYSDATE,'mm-dd-y
yyy hh:mi:ss am'));
19
20 FOR c1 IN opp_list
21 LOOP
22 BEGIN
23 LSID :='';
24 LSID_COUNT :=0;
25 propertyid_cnt :=0;
26
27 --Check if the CBRE Legacy Src ID is not null.If not null, then update the OppID for the corres
ponding propertyid in the well.
28 IF c1.CBRE_LEGACY_SRC_ID <> ' ' THEN
29 SELECT COUNT(1), CBRE_LEGACY_SRC_ID INTO LSID_COUNT, LSID FROM CB_INTFC
30 WHERE CBRE_LEGACY_SRC_ID = c1.CBRE_LEGACY_SRC_ID
31 GROUP BY CBRE_LEGACY_SRC_ID
32 having count(1) > 1;
33
34 IF LSID_COUNT > 1 THEN
35 msg :=
36 'Legacy Source ID - '
37 || LSID
38 || ' - appears twice in the interface';
39 RAISE well_error;
40 ELSE
41 SELECT COUNT(1) INTO propertyid_cnt
42 FROM CB_PROPERTY WHERE PROPERTYID = TO_NUMBER(c1.cbre_legacy_src_id);
43
44 IF propertyid_cnt > 0 THEN
45 UPDATE CB_PROPERTY SET OPPORTUNITY_ID = c1.opportunity_id WHERE PROPERTYID = TO_NUMBER(c1.
cbre_legacy_src_id);
46 ELSE
47 msg :=
48 'Legacy Source ID - '
49 || c1.cbre_legacy_Src_id
50 || ' - is not a valid id';
51 RAISE well_error;
52 END IF;
53 END IF;
54 END IF;
55 EXCEPTION
56 WHEN well_error
57 THEN
58 INSERT INTO CB_INTFC_ERROR_CHK
59 (opportunity_id, msg, lastupdated
60 )
61 VALUES (c1.opportunity_id, msg, SYSDATE
62 );
63 END;
64 END LOOP;
65 INSERT INTO CB_INTFC_ERROR_chk(MSG) VALUES('Procedure ended at '||TO_CHAR(SYSDATE,'mm-dd-yyy
y hh:mi:ss am'));
66
67 COMMIT;
68 END;
69 END Cb_INTFC_CHK;
70 /
Procedure created.
SQL> exec cb_intfc_chk;
BEGIN cb_intfc_chk; END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "DEVWELL.CB_INTFC_CHK", line 29
ORA-06512: at line 1
Also, if I execute the query from the procedure with the having clause, it executes perfectly fine
SQL> SELECT COUNT(1), CBRE_LEGACY_SRC_ID FROM CB_INTFC
2 WHERE CBRE_LEGACY_SRC_ID = '15546'
3 GROUP BY CBRE_LEGACY_SRC_ID
4 having count(1) > 1;
COUNT(1) CBRE_LEGACY_SRC_ID
---------- ------------------------------------------------------------
2 15546
My question to you would be, can we not use a having clause in the procedure or what is that I am doing incorrectly here.
As always, your feedback is highly appreciated.
Thanks,
August 29, 2006 - 6:49 am UTC
yes you can use a having clause.
you are using a having clause.
the having clause however is causing ZERO RECORDS TO BE RETURNED for some value of cbre_legacy_src_id
A select into wants
a) at least one
b) at most one
you have violated a.
therefore, you code:
....
begin
select count(*), ... INTO ..... having count(*) > 1;
exception
when no_data_found then
whatever;
end;
remember, using count(1) makes it look like you don't know SQL - so always use count(*) since we all know that count(*) means "count the number of records" whereas count(1) says "not really sure whats going on but if I count a bunch of one's it seems to work"
Oops forgot to mention the version
Priya, August 28, 2006 - 8:09 pm UTC
Oracle version I am using is 9.2.0.1.0
Thanks,
Priya.
Thanks as always. You are the best
Priya, August 29, 2006 - 1:24 pm UTC
Thanks Tom..I should have thought of that earlier. Keep up the good work.
However, one thing keeps me wondering. You said that count(*) and count(1) both are the same. So what is the impact when you count(1) instead of count(*) or how different is it when used in a stored procedure or in general?
August 29, 2006 - 4:26 pm UTC
it is a matter of semantics, remember I wrote:
remember, using count(1) makes it look like you don't know SQL - so always use
count(*) since we all know that count(*) means "count the number of records"
whereas count(1) says "not really sure whats going on but if I count a bunch of
one's it seems to work"
count(*) semantically meaningful and correct
count(1) founded in mythology (go ahead, ask someone, 50% or better chance they'll say "count(1) is faster than count(*)" - it never was, never has been - in fact in older releases it was actually slower until the optimizer rewrote count(<constant>) as count(*) internally for you to fix the mistake)
Using
Harschil, April 03, 2009 - 2:17 pm UTC
Hi Tom,
I have data in table T :
col1 col2
---- ----
a KK
a LL
a MM
b NN
b OO
c PP
now i want to select count of col2 for each value of col1 alongwith col1 & col2.
i.e.
I want the output
col1 cnt col2
---- ---- -----
a 3 KK
LL
MM
b 2 NN
OO
c 1 PP
Kindly suggest.
Regards
Harschil
April 03, 2009 - 3:20 pm UTC
suggest you give create tables and insert into's if you want working sql.
totally off of the cuff, since I don't have your tables/inserts
select decode( row_number() over (partition by col1 order by col2),
1, col1 ) col1,
decode( row_number() over (partition by col1 order by col2),
1, count(*) over (partition by col1)),
col2
from t
order by col1, col2
/
Giving create/insert statments
Harschil, April 03, 2009 - 2:47 pm UTC
Hi Tom
I missed to give create table / insert into statement for the previous question.
create table T ( col1 varchar2(10),
col2 varchar2(10));
INSERT INTO T ( COL1, COL2 ) VALUES (
'a', 'KK');
INSERT INTO T ( COL1, COL2 ) VALUES (
'a', 'LL');
INSERT INTO T ( COL1, COL2 ) VALUES (
'a', 'MM');
INSERT INTO T ( COL1, COL2 ) VALUES (
'b', 'NN');
INSERT INTO T ( COL1, COL2 ) VALUES (
'b', 'OO');
INSERT INTO T ( COL1, COL2 ) VALUES (
'c', 'PP');
COMMIT;
Regards
Harschil
April 03, 2009 - 3:31 pm UTC
much better, minor modification on the ordering was needed...actually the column alias...
ops$tkyte%ORA10GR2> select decode( row_number() over (partition by col1 order by col2),
2 1, col1 ) new_col1,
3 decode( row_number() over (partition by col1 order by col2),
4 1, count(*) over (partition by col1)) cnt,
5 col2
6 from t
7 order by col1, col2
8 /
NEW_COL1 CNT COL2
---------- ---------- ----------
a 3 KK
LL
MM
b 2 NN
OO
c 1 PP
6 rows selected.
Harschil, April 03, 2009 - 3:37 pm UTC
Many Thanks Tom.
Regards
Harschil
Identifying unique set of data
Rajasekar, May 08, 2009 - 7:24 am UTC
Hi Tom,
I am using the script given by a reader above. My requirement is similar to his, except for I need the Key which has status 'Y' and 'N' only.
In the data below key 3 has 'Y','N' and 'X' and 5 has 'Y and 'N' and here I need only 5 *not* 3, because 3 has status 'X' also
CREATE TABLE TTEST ( KEY NUMBER, FLAG VARCHAR2(10));
INSERT INTO TTEST VALUES( 1,'Y');
INSERT INTO TTEST VALUES( 2,'Y');
INSERT INTO TTEST VALUES( 2,'Y');
INSERT INTO TTEST VALUES( 3,'Y');
INSERT INTO TTEST VALUES( 3,'N');
INSERT INTO TTEST VALUES( 3,'X');
INSERT INTO TTEST VALUES( 4,'Y');
INSERT INTO TTEST VALUES( 5,'Y');
INSERT INTO TTEST VALUES( 5,'N');
COMMIT;
SELECT * FROM TTEST;
KEY FLAG
------------ ----------
1 Y
2 Y
2 Y
4 Y
5 Y
5 N
3 Y
3 N
3 X
You provided the query below which provides me 3 also:
SQL> select key from ttest where flag in ('Y','N') group by key having count(distinct flag) = 2;
KEY
------------
3
5
I modified your query as below and I get the correct answer:
SQL> select distinct key from (select * from ttest where key in (select key from ttest group by key having count(distinct flag) = 2) ) where flag in ('Y','N');
KEY
------------
5
But I feel there is better way to write the query, can you please suggest a better alternate way to query?
Thanks!
Rajasekar
May 11, 2009 - 3:22 pm UTC
this is a huge page with lots of "reader" entries.
ops$tkyte%ORA11GR1> select key from ttest group by key having count(*) = 2 and count(DISTINCT case when flag in('Y','N') then flag end ) = 2;
KEY
----------
5
group by clause
satin satin, March 05, 2010 - 2:11 pm UTC
I am having some issue on below query . How do you make the query run quickly
select product_cd, product_typ,product_locn,sum(prod_cost),sum(product_qty) from products group by product_cd, product_typ,product_locn
the total number of rows in this table 24 mil . All these records are i have to update to another tables
Could you please suggest me how do we improve the performanace on this .We are using oracle 10g
Thanks
March 05, 2010 - 2:50 pm UTC
parallel query is one option.
having an index on product_cd, product_typ, procduct_locn, prod_cost, product_qty would be another (it would be smaller than the table is, faster to scan - assuming at least one of the columns is NOT NULL)
doing the update with the select - not running a select and then an update to another table - would be best as well.
satin satin, March 05, 2010 - 3:45 pm UTC
We are using the ETL tool to update this .Can you provide some syntax please
March 08, 2010 - 7:30 am UTC
sure, here is some syntax:
for( x = 0; x < 10; x++ ) printf( "%d\n", x );
that is C. Not sure what you are looking for - but if it was the create index statement for creating an index and you don't know how to do that yourself, you have some *serious* reading to do - now. I don't care what tool you are using, if you don't know that....
check out the data warehousing guide for parallel query and other options:
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14223/toc.htm
Having Clause
selva, November 15, 2012 - 7:24 am UTC
Hi Tom,
select * from student;
Id Name
1 XXX
2 YYY
3 ZZZ
select * from marks;
Id PHY CHE
1 90 89
2 93 94
3 54 90
I need to display the name, who is highest mark in CHE.
I tried but I'm unable to get the answer.
Thanks in Advance,
Selva
November 19, 2012 - 9:41 am UTC
no create
no inserts
no do your homework for you...
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:122801500346829407 that shows you how to get the values of a row with the "max" value.
bear in mind that your marks table could return lots of ID's - there isn't just "a single name" to be presented where - there is a set of names of people scoring the highest.
select id from marks where che = (select max(che) from marks)
would be one very very simple way to get the set of ids you want, just use that as a subquery against student.
having count (distinct ....) =
Debashis Mukherjee, September 06, 2013 - 7:37 am UTC
Hi Tom,
In an earlier question in the same thread, the following solution was provided.
select key
from ttest
where flag in ( 'Y', 'N' )
group by key
having count(distinct flag) = 2;
My Question is for a Group which has a distinct count of 3, while the counting is being done by SQL Server/ Oracle, the counter will first be 1; then 2, when finds the second distinct record and meet the criteria; hence the loop breaks.
Thats why, having count (distinct ....) = 2 will return all groups that have count => 2.
I have many queries where I needed to do count (distinct ....) = 1 and it did not work. I tried following method to get desired results.
(All rows)
except
( group by ....
count (distinct ...) > 1)
but the results have false positive. Please let me know your thoughts. This will help me solve many problems.
September 09, 2013 - 11:33 am UTC
it does not - this will return ONLY the set of keys such that there is at least one flag = Y and at least one flag = N
that is what this query does, period. that is what this query was designed to do.
this will return ONLY rows such that there is at least one Y and at least one N records
this is the original spec:
"My data is as follows, and I need to find out the keys having status both 'Y' AND 'N'. How can "
now, how about you tell us what YOU are trying to achieve and then we can help you write your specific query for your apparently different question?
while grouping one of the group row should have specific value
AG, January 22, 2015 - 8:43 am UTC
create table t1 (c1 varchar2(4),
c2 varchar2(4),
c3 number,
c4 varchar2(2)
);
insert into t1 (c1, c2, c3, c4) values ('J1', 'M1', 100, 'L1');
insert into t1 (c1, c2, c3, c4) values ('J1', 'M2', 100, 'L2');
insert into t1 (c1, c2, c3, c4) values ('J2', 'M3', 200, 'L2');
insert into t1 (c1, c2, c3, c4) values ('J2', 'M1', 400, 'L3');
insert into t1 (c1, c2, c3, c4) values ('J2', 'M5', 400, 'L4');
insert into t1 (c1, c2, c3, c4) values ('J3', 'M3', 200, 'L1');
insert into t1 (c1, c2, c3, c4) values ('J3', 'M1', 400, 'L2');
insert into t1 (c1, c2, c3, c4) values ('J3', 'M5', 400, 'L3');
insert into t1 (c1, c2, c3, c4) values ('J4', 'M1', 400, 'L1');
insert into t1 (c1, c2, c3, c4) values ('J4', 'M2', 400, 'L2');
insert into t1 (c1, c2, c3, c4) values ('J4', 'M3', 400, 'L3');
now I want to find out the rows having same value of column C3 and and of those rows one row should have Column C4 as value L1.
The result should be
'J1', 'M1', 100, 'L1'
'J1', 'M2', 100, 'L2'
'J4', 'M1', 400, 'L1'
'J4', 'M2', 400, 'L2'
'J4', 'M3', 400, 'L3'
and second result set should be
c1 Total Rows
---- ---------
'J1', 2
'J4' 3