select in select
A reader, January 02, 2003 - 4:17 pm UTC
Thank you! TOM!
How long has this syntax been legal?
John Gilmore, January 03, 2003 - 4:06 am UTC
I knew that you could have Query Table expressions in the "from" clause but I was not aware that Oracle allowed you to have these in the "select" clause also.
Indeed, I checked the Oracle 8.1.7 SQL Reference manual (A85397-01) and from what I can see from the examples and the syntax diagrams of the "select" statement it does not seem to allow this syntax. However, a quick test in SQL*Plus proved to me that this works.
How long has this been available?
January 03, 2003 - 8:16 am UTC
815..
they are called "scalar subqueries" and yeah, I agree, it isn't obvious that they can be used there. But most anywhere you could use a CONSTANT in sql before, you can use a scalar subquery.
Select-a-select
Mike, February 21, 2003 - 3:03 pm UTC
Tom:
This "select a select"/"select in select" example looks very much like a correlated sub query which I tend to avoid by replacing with in-line views in the FROM clause. Are there similar correlated sub-query performance penalties of using "select a select"?
Interestingly, I just learned of in-house use of "select a select" where approx. 16 of 20 columns selected in a query are sub-selects similar to the first example in this thread. In our case, "select a select" is used to avoid outer joins between tables with loose RI enforcement/null foreign key values. We found that "select a select" performs quite well (1 sec query time) whereas the outer join query with all "select a selects" removed was much slower (minutes).
Thanks for any general opinions you may have on performance penalties/benefits of "select a select".
-Mike
February 21, 2003 - 3:30 pm UTC
why are you afraid of correlated subqueries in the first place?
consider this, which query would YOU rather run:
ops$tkyte@ORA817DEV> set autotrace traceonly statistics;
ops$tkyte@ORA817DEV> set echo on
ops$tkyte@ORA817DEV> @test
ops$tkyte@ORA817DEV> select username from all_users
2 where exists (select null from all_objects where owner = username);
40 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1070 consistent gets
4 physical reads
0 redo size
1602 bytes sent via SQL*Net to client
647 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
40 rows processed
ops$tkyte@ORA817DEV> select username
2 from all_users, (select distinct owner from all_objects)
3 where username = owner;
40 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
105444 consistent gets
300 physical reads
0 redo size
1602 bytes sent via SQL*Net to client
647 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40 rows processed
ops$tkyte@ORA817DEV>
Me, I would prefer the correlated subquery. <b>If they were always evil, always bad, they would not exist</b>...
There are NO performance penalties with corrlated subqueries. Just as there no none with inline views -- they just do things differently and under different circumstances are better then the other.
<b>All of these are techniques, tools, things to be used.
One of my favorite tuning tools is -- well -- rownum. Go figure eh.. Try this little test case someday:</b>
drop TABLE T1;
CREATE TABLE t1
( prc_chk_key NUMBER(9) NOT NULL,
prod_key NUMBER(12) NOT NULL,
cmpt_loc_key NUMBER(5) NOT NULL,
loc_key NUMBER(5) NOT NULL,
prc_chk_dt DATE
)
/
insert into t1 select 2, 3, 4, 5, sysdate from all_objects where rownum <= 50;
pause
drop TABLE T2;
CREATE TABLE T2
( prc_chk_key NUMBER(9) NOT NULL,
prc_chk_typ_desc VARCHAR2(35) NOT NULL,
cmpt_loc_key NUMBER(5),
loc_key NUMBER(5) NOT NULL
)
/
insert into t2 select 2, 'x', 4, 5 from all_objects where rownum <= 50;
pause
create or replace context my_ctx using F
/
pause
CREATE OR REPLACE function F
(v_prod_key IN number default NULL,
v_prc_chk_key IN number default NULL,
v_return IN varchar2 default NULL,
v_want_sr IN varchar2 default NULL,
v_offset in number default NULL) RETURN varchar2
as
begin
if ( v_offset = -1 )
then
dbms_session.set_context('my_ctx', 'cnt', -1 );
end if;
dbms_session.set_context
( 'my_ctx', 'cnt', nvl(sys_context('my_ctx','cnt'),0)+1 );
return 1;
end;
/
exec dbms_output.put_line( F( v_offset => -1 ) );
pause
set autotrace traceonly
select /*+ USE_HASH( a11, a12 ) */ a12.PRC_CHK_TYP_DESC PRC_CHK_TYP_DESC,
a11.PRC_CHK_DT PRC_CHK_DT,
a11.CMPT_LOC_KEY CMPT_LOC_KEY,
a11.PROD_KEY UPC_PROD_KEY,
a11.LOC_KEY LOC_KEY,
max(F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'QTY', 'D', 1) ) COMPPREVIOUS,
max(F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'AMT', 'D',1) ) COMPPREVIOUS1,
max(F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'CODE', 'D', 1) ) COMPPREVIOUS2,
max(F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'PRC', 'D',1) ) COMPPREVIOUS3
from T1 a11,
T2 a12
where a11.CMPT_LOC_KEY = a12.CMPT_LOC_KEY
and a11.LOC_KEY = a12.LOC_KEY
and a11.PRC_CHK_KEY = a12.PRC_CHK_KEY
group by a12.PRC_CHK_TYP_DESC, a11.PRC_CHK_DT,
a11.CMPT_LOC_KEY, a11.PROD_KEY, a11.LOC_KEY;
set autotrace off
pause
column data format a20
select sys_context( 'my_ctx', 'cnt' ) data from dual;
exec dbms_output.put_line( F( v_offset => -1 ) );
pause
set autotrace traceonly
select /*+ USE_HASH( a11, a12 ) */ a12.PRC_CHK_TYP_DESC PRC_CHK_TYP_DESC,
a11.PRC_CHK_DT PRC_CHK_DT,
a11.CMPT_LOC_KEY CMPT_LOC_KEY,
a11.PROD_KEY UPC_PROD_KEY,
a11.LOC_KEY LOC_KEY,
max(COMPPREVIOUS),
max(COMPPREVIOUS1),
max(COMPPREVIOUS2),
max(COMPPREVIOUS3)
from (select a11.*,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'QTY', 'D', 1 ) COMPPREVIOUS,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'AMT', 'D',1 ) COMPPREVIOUS1,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'CODE', 'D', 1 ) COMPPREVIOUS2,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'PRC', 'D',1 ) COMPPREVIOUS3,
ROWNUM r
from t1 a11 ) a11,
T2 a12
where a11.CMPT_LOC_KEY = a12.CMPT_LOC_KEY
and a11.LOC_KEY = a12.LOC_KEY
and a11.PRC_CHK_KEY = a12.PRC_CHK_KEY
group by a12.PRC_CHK_TYP_DESC, a11.PRC_CHK_DT,
a11.CMPT_LOC_KEY, a11.PROD_KEY, a11.LOC_KEY;
set autotrace off
pause
select sys_context( 'my_ctx', 'cnt' ) data from dual;
spool off
<b>the hints are there to emulate a customer issue they were having -- try the second query with and without rownum and see what happens</b>
A reader, February 21, 2003 - 4:59 pm UTC
Tom,
Please explain to me in detail what each step is supposed to do...what will rownum do? it suddently brought down the cnt from 10,000 to 200? what exactly is happening? what is the purpose of rownum?
February 21, 2003 - 6:45 pm UTC
it is a side effect of view merging:
</code>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96533/opt_ops.htm#1009807 <code>
this "view"
(select a11.*,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'QTY', 'D', 1 ) COMPPREVIOUS,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'AMT', 'D',1 ) COMPPREVIOUS1,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'CODE', 'D', 1 ) COMPPREVIOUS2,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'PRC', 'D',1 ) COMPPREVIOUS3,
ROWNUM r
from t1 a11 ) a11
cannot be merged into since it includes rownum -- so the optimizer "materialized" it off to the side into temp and then joined to it.
So the function F was called 4*50 times = 200 in order to create this temp object.
In the other case, where the rownum was not used -- the function f() was called over and over and over in order to do the MAX processing (sort of like sorting). The function was evaluated and re-evaluated many times.
Rownum let us call the function 4 times per row (50 rows) and be done with it.
Thanks!
Mike, February 22, 2003 - 9:53 am UTC
Tom:
Thanks for your detailed response and examples. You have dispelled yet another myth or bad habit I've learned in the past (avoid corellated subqueries).
Thank you,
-Mike
February 22, 2003 - 10:57 am UTC
if you do this:
o ignore all advice that sounds like a rule of thumb, pretend it doesn't exist, was never said. rule of thumb = ROT and ROT is as bad as it sounds
o ignore all advice that says "feature X" or "approach Y" is bad
o ask for the science behind the claim, facts to support the advice and if you get none, ignore it
I know that means that many performance books on your shelves are now obsolete, to be put in the circular filing cabinet -- but that is the way it is. If they just say "don't use not in", "don't do this" "don't do that" and do not have a scientific proof as to why -- they are wrong.
That and every feature/function has a time and place so anyone that says "never use X" is wrong -- period -- no if and or buts about it -- they are just wrong.
A reader, February 22, 2003 - 11:42 am UTC
Thanks for explaining in detail Tom...
Thanks again
Mike, February 22, 2003 - 12:06 pm UTC
Tom:
It's challenging when respected expert opinions differ. My older Guy H. and Gaja V. tuning books and IOUG-Live hand-outs warn of performance issues with correlated sub-queries so I've avoided them as a general rule. That's probably my fault for taking something in their work out of context or generalizing it.
However, you always stress that the proof is in the numbers. Tracing and tkprof seem like the only way to know for sure which query is the best choice for a situation.
Thanks again,
-Mike
February 22, 2003 - 12:51 pm UTC
Things change over time -- I know Gaja (he is at Oracle now, a very good thing for us!)
With the RBO, it could have been true -- lots less optimization paths, less intelligent optimizer.
It is a generalization however, correlated subqueries can be great. They can be horrible. But -- so can anything and everything else!
There are people who avoid "not in" like the plague. With the CBO and anti-joins, they can blow away the other options -- totally.
Great guy
Mike, February 22, 2003 - 4:10 pm UTC
Tom:
> Things change over time -- I know Gaja (he is at
> Oracle now, a very good thing for us!)
He's a great guy. Please say hi to him from the St. Louis Oracle User Group. He spoke twice to our group and took time out for a book signings and off-line conversations. Hmmmm, now if we could only lure you to St. Louis for a day ;-))
-Mike
A reader, March 07, 2003 - 9:49 am UTC
M.b. I'm wrong putting this question here. But this one about tuning.
I have written this sql, it takes pretty much time.
Is there other sql that can change this one:
select t1.name name1, t2.name name2
from t t1, t t2, r
where r.id=t1.type1_id
and r.id=t2.type2_id
and t1.type='type1'
and t2.type='type2'
order by 1;
thank you.
March 07, 2003 - 10:13 am UTC
if you add "and rownum = 1" it'll probably go lots faster.
is either of t1.type or t2.type "selective".
if so, is either of t1.type or t2.type "indexed".
An index on t1(type,name) could be useful if the goal is to get the first row back ASAP as the "t1.type='type1' " and "order by 1" would be done quickly.
Great information!
Robert, March 07, 2003 - 12:24 pm UTC
Tom,
Thanks for the great information...
o The ROWNUM trick
o The reminder (again) on ROT!
Thanks,
Robert.
March 07, 2003 - 1:27 pm UTC
I hope everyone knows the "and rownum = 1" was a joke, sort of tongue in cheek.
Here is another:
question: how can I make this query go faster
answer: change that tablename to DUAL, queries against dual go really fast.
Dual...
Mark A. Williams, March 07, 2003 - 1:57 pm UTC
Yeah, but what about x$dual??? You know that one has super secret magical powers that make your database run with really_fast=true in the spfile, right?
- Mark
What is @test?
Reader, March 07, 2003 - 2:13 pm UTC
In the above:
ops$tkyte@ORA817DEV> set autotrace traceonly statistics;
ops$tkyte@ORA817DEV> set echo on
ops$tkyte@ORA817DEV> @test
What is @test?
Thanks!
March 07, 2003 - 2:58 pm UTC
it was a script that had the subsequent sql queries in it.
Here we go
Mike, March 07, 2003 - 2:19 pm UTC
Ahhh Tom, your sarcasm always makes my day. Keep your _tk_smart_ass parameter set to 100 or higher... Anticipating your response to "What is @test" ;-)
Doh!
Reader, March 07, 2003 - 3:30 pm UTC
Do Scalar Subqueries Work in PL/SQL?
John Gilmore, March 19, 2003 - 5:33 am UTC
In SQL*Plus
select (select dummy from dual) from dual;
works fine. But in PL/SQL I get the error message
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following
Is this feature not yet supported by PL/SQL?
March 19, 2003 - 7:07 am UTC
in 8i, you need to "hide" that construct from plsql -- in 9i, it is ok
either:
o use a view
o use dynamic sql
open ref_cursor for 'select (select dummy from dual) from dual';
Ik, March 19, 2003 - 8:09 pm UTC
Tom,
Would these scalar subqueries be merged with the rest of the query the same way as complex view merging.
When we do an explain plan we do not see these scalar sub queries. So, iam wondering whether the plan iam seeing would differ from what would actually get executed.
Thanks in advance.
March 20, 2003 - 8:21 am UTC
in the same way? probably not, in some way? yes.
why dont this work
Asim Naveed, May 22, 2003 - 5:38 am UTC
Hi,
This problem is related to
scalar subqueries and WITH clause
Please Consider following queries.
SQL> L
1 with aquery as (select 1 from dual)
2* select 1,2 from aquery
SQL> /
1 2
---------- ----------
1 2
SQL> ED
Wrote file afiedt.buf
1 with aquery as (select 1 from dual)
2* select 1,2, (select 1 from dual) from aquery
SQL> /
1 2 (SELECT1FROMDUAL)
---------- ---------- -----------------
1 2 1
SQL> ed
Wrote file afiedt.buf
1 with aquery as (select 1 from dual)
2* select 1,2, aquery from aquery
SQL> /
select 1,2, aquery from aquery
*
ERROR at line 2:
ORA-00904: "AQUERY": invalid identifier
SQL> ed
Wrote file afiedt.buf
1 with aquery as (select 1 from dual)
2* select 1,2, (aquery) from aquery
SQL> /
select 1,2, (aquery) from aquery
*
ERROR at line 2:
ORA-00904: "AQUERY": invalid identifier
why did the third and fourth query faild. May be third
query failed due to absence of brackets but why fourth
failed.
May 23, 2003 - 7:59 am UTC
aquery is NOT a scalar subquery -- it is a "result set" if you will. it cannot be used there anymore then:
select 1, all_objects from dual
could be expected to work -- you can use aquery where you can use a view, period.
Selects within selects
Richard, June 02, 2003 - 9:11 am UTC
Hi,
Does the following make sense, with respect to Oracle?:
==============================================================================
The queries:
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2
WHERE table1.id=table2.id);
Can be rewritten as:
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;
Using a LEFT [OUTER] JOIN is generally much faster than an equivalent subquery.
===============================================================================
Thanks
June 02, 2003 - 9:27 am UTC
no, because NOT IN and NOT EXISTS are <b>NOT EQUIVALENT</b>. They give different answsers depending on the NULLALITY of ID in T2.
That, and when left to its job, the CBO will do an infinitely better job all by itself.
for example:
ops$tkyte@ORA920> create table t1 as select * from all_objects where rownum <= 10000;
Table created.
ops$tkyte@ORA920> create table t2 as select * from all_objects where rownum <= 9950;
Table created.
ops$tkyte@ORA920> insert /*+ APPEND */ into t2 select * from t2;
9950 rows created.
ops$tkyte@ORA920> commit;
Commit complete.
ops$tkyte@ORA920> insert /*+ APPEND */ into t2 select * from t2;
19900 rows created.
ops$tkyte@ORA920> commit;
Commit complete.
ops$tkyte@ORA920> insert /*+ APPEND */ into t2 select * from t2;
39800 rows created.
ops$tkyte@ORA920> commit;
Commit complete.
ops$tkyte@ORA920> insert /*+ APPEND */ into t2 select * from t2;
79600 rows created.
ops$tkyte@ORA920> commit;
Commit complete.
ops$tkyte@ORA920> create index t2_idx on t2(object_id);
Index created.
ops$tkyte@ORA920> analyze table t1 compute statistics for table;
Table analyzed.
ops$tkyte@ORA920> analyze table t2 compute statistics for table for all indexes for all indexed columns;
Table analyzed.
Now, TKPROF shows us:
select * from t1 where object_id not in ( select object_id from t2 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 1.05 1.06 126 490 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 1.05 1.06 126 490 0 50
Rows Row Source Operation
------- ---------------------------------------------------
50 HASH JOIN ANTI (cr=490 r=126 w=0 time=1060428 us)
10000 TABLE ACCESS FULL T1 (cr=138 r=126 w=0 time=23315 us)
159200 INDEX FAST FULL SCAN T2_IDX (cr=352 r=0 w=0 time=230111 us)(object id 37263)
********************************************************************************
select * from
t1 where not exists ( select null from t2 where t2.object_id = t1.object_id )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.20 0.20 126 20142 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.20 0.20 126 20142 0 50
Rows Row Source Operation
------- ---------------------------------------------------
50 FILTER (cr=20142 r=126 w=0 time=204251 us)
10000 TABLE ACCESS FULL T1 (cr=142 r=126 w=0 time=29849 us)
9950 INDEX RANGE SCAN T2_IDX (cr=20000 r=0 w=0 time=103559 us)(object id 37263)
********************************************************************************
select t1.*
from
t1 left join t2 on (t1.object_id=t2.object_id) where t2.object_id is null
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 1.48 1.72 126 490 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 1.48 1.72 126 490 0 50
Rows Row Source Operation
------- ---------------------------------------------------
50 FILTER (cr=490 r=126 w=0 time=1725038 us)
159250 HASH JOIN OUTER (cr=490 r=126 w=0 time=1472166 us)
10000 TABLE ACCESS FULL OBJ#(37261) (cr=138 r=126 w=0 time=25879 us)
159200 INDEX FAST FULL SCAN OBJ#(37263) (cr=352 r=0 w=0 time=260624 us)(object id 37263)
The superior one is the one that says what you "mean". The outer join was inferior in all cases here.
Cheers!
Richard, June 02, 2003 - 9:50 am UTC
Thank you, that's great!
About the ROWNUM trick
A reader, December 15, 2003 - 5:59 pm UTC
Hi Tom,
Is there other way that I can achieve the same result as the ROWNUM trick, such as HINT or (hidden) parameters?
December 16, 2003 - 7:02 am UTC
same result as "what" exactly?
ROWNUM
A reader, December 16, 2003 - 8:17 am UTC
In this query, you used ROWNUM to change the behavior of CBO. I am wondering if I can do the same by other (more obvious) ways.
set autotrace traceonly
select /*+ USE_HASH( a11, a12 ) */ a12.PRC_CHK_TYP_DESC PRC_CHK_TYP_DESC,
a11.PRC_CHK_DT PRC_CHK_DT,
a11.CMPT_LOC_KEY CMPT_LOC_KEY,
a11.PROD_KEY UPC_PROD_KEY,
a11.LOC_KEY LOC_KEY,
max(COMPPREVIOUS),
max(COMPPREVIOUS1),
max(COMPPREVIOUS2),
max(COMPPREVIOUS3)
from (select a11.*,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'QTY', 'D', 1 ) COMPPREVIOUS,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'AMT', 'D',1 ) COMPPREVIOUS1,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'CODE', 'D', 1 ) COMPPREVIOUS2,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'PRC', 'D',1 ) COMPPREVIOUS3,
ROWNUM r
from t1 a11 ) a11,
T2 a12
where a11.CMPT_LOC_KEY = a12.CMPT_LOC_KEY
and a11.LOC_KEY = a12.LOC_KEY
and a11.PRC_CHK_KEY = a12.PRC_CHK_KEY
group by a12.PRC_CHK_TYP_DESC, a11.PRC_CHK_DT,
a11.CMPT_LOC_KEY, a11.PROD_KEY, a11.LOC_KEY;
Thanks
December 16, 2003 - 11:28 am UTC
NO_MERGE might be useful -- works a little differently, but can work.
WITH subquery factoring can be used as well.
GROUP BY or ORDER BY could have done it.
I did not get it!!
ZS, April 12, 2004 - 11:36 pm UTC
Tom,
From reading the answers I did not get what do you think of Scalar subqueries compared to mergin them to the main query.
I am sorry if you answered it already but I acan't see it.
ZS
April 13, 2004 - 7:24 am UTC
I did not get the question you are asking?
Sorry
ZS, April 14, 2004 - 11:02 am UTC
I mean, is there any performance benefits for merging the "scalar subqueries" into the main query.
e.g.
1)
SELECT t1.a,
(select t2.b from t2 where t2.a=t1.a) b
FROM t1
WHERE blah blah..
compared to:
2)
SELECT t1.a,
t2.b
from t1,t2
where t1.a=t2.b
and blah blah..
Is query 2 any better than query 1?
Thanks,
ZS
April 14, 2004 - 11:33 am UTC
query 2 and query 1 are not comparable.
select t1.a, t2.b
from t1, t2
where t1.a = t2.b(+)
and blah blah blah
would be....
You can use scalar subqueries to remove an outer join sometimes -- I use them as a tuning device from time to time. If you have "Effective Oracle by Design", I go into that in more detail.
Thanks
ZS, April 15, 2004 - 7:59 pm UTC
I will get your book soon.
scalar query
Venkat, April 28, 2004 - 11:55 pm UTC
Tom,
When we tried the given example comparing outer join with scalar query, in the query output under the "cnt/count" column, we get the value '1' while using outer join and the corresponding value as '0' in the scalar query. Query output details are given below:Can you please explain this?
SQL> select a.username,count(*) from
2 all_users a, all_objects b where
3 a.username=b.owner(+) group by a.username;
USERNAME COUNT(*)
------------------------------ ----------
BACKUP 1
CDPARA 105
DBSNMP 1
LOSCD 722
ORBIAFU 1
ORBIBILL 1
ORBICATH 1
ORBICDS 1
ORBICOLLECT 1
ORBIDEBIT 1
ORBIHIST 1
USERNAME COUNT(*)
------------------------------ ----------
ORBIMERC 1
OUTLN 3
SYS 2330
SYSTEM 9
WORKFLOW 114
16 rows selected.
SQL>
SQL> select a.username, (select count(*)
2 from all_objects b where
3 b.owner=a.username) cnt from all_users a
4 /
USERNAME CNT
------------------------------ ----------
SYS 2330
SYSTEM 9
OUTLN 3
DBSNMP 0
ORBIAFU 0
ORBIBILL 0
ORBICATH 0
ORBIMERC 0
ORBICOLLECT 0
LOSCD 722
CDPARA 105
USERNAME CNT
------------------------------ ----------
WORKFLOW 114
BACKUP 0
ORBICDS 0
ORBIDEBIT 0
ORBIHIST 0
16 rows selected.
Regards,
venkat
April 29, 2004 - 7:26 am UTC
yes, look at it -- it should be self evident after a bit....
select ..., count(*)
from ...
counts the records in that result set. with an OUTER JOIN, there will always be *at least one* (the very meaning of outer join)
select ...., (select count(*) ... )
from ...
will report a totally different count.
if you want the outer join to work the same, simply count NON NULL records:
select a.username,count(b.owner) from
all_users a, all_objects b where
a.username=b.owner(+) group by a.username;
that'll count the b records -- just like the scalar subquery does.
Correlated Scalar Subquery?
Reader, June 02, 2004 - 1:48 am UTC
Hi Tom,
I have been trying to understand the execution of a Scalar Subquery.
In the query :-
select c1,(select c2 from t2 where t1.c2=t2.c2) from t1
1. How does the execution of the aove query takes place.Does it go like this =>
a. Retrive 1st row from table t1.
b. Compare the row retrived from t1 with every other row from t2 on the column c2.
c. Continue a and b until all rows in t1 are processed.
Is the above algorithm right?
If 'YES' then should'nt it be called as a Correlated Scalar Subquery ?
If 'NO' can you please explain the algorithm for the same.
June 02, 2004 - 8:22 am UTC
yes, scalar subqueries may be correlated like that.
but they don't have to be.
select (select count(*) from dual)
from t;
rewrite to use 1 inline and/or analytic
karma, March 22, 2005 - 2:59 pm UTC
I have following query and only diff. between 2 inline views in buy_sell_i ( i.e. either 'B' or 'S' ).
Is there a way to re-write this to use one inline view and/or use analytics ? I need to see how many diff. ways i can re-write and then benchmark performance.
If somehow this could be done using 1 sql or moving group by outside inline view it would be better. Can this be done using analytic function?
SELECT buy.trade_d, buy.acct AS b_acct, buy.cusp_n AS cusp_n, buy.dsc AS bdesc,
buy.secgroup_c AS sec_grp_c, buy.sec_typ_c AS sec_typ_c,
buy.rpt_sec_group_c AS rpt_sec_group_c, buy.fund_type_c AS fund_type_c,
buy.buy_sell_i AS bsi, buy.qty AS qty, buy.prin AS prin, buy.tin AS btin,
buy.rcds AS buys, sell.acct AS s_acct, sell.buy_sell_i AS sell_bsi,
sell.qty AS sell_qty, sell.prin AS sell_prin, sell.tin AS sell_tin,
sell.rcds AS sells
FROM (SELECT t.acct_pref_c || '-' || t.acct_id AS acct, t.cusp_n,
t.trade_d, t.buy_sell_i, t.irs_n AS tin,
MAX (TRIM (sec_desc1) || TRIM (sec_desc2)) AS dsc,
MAX (secgroup_c) AS secgroup_c, MAX (sec_typ_c) AS sec_typ_c,
MAX (rpt_secgroup_c) AS rpt_sec_group_c,
MAX (fund_type_c) AS fund_type_c, SUM (t.shr_q) qty,
SUM (t.trd_amt) AS prin, COUNT (*) AS rcds
FROM loader.my_trade t
WHERE t.buy_sell_i = 'B'
AND rpt_secgroup_c <> 'MF'
AND TRIM (t.irs_n) IS NOT NULL
AND ( t.acct_pref_c NOT IN
('ZXX',
'333',
'444',
'222',
)
AND t.acct_pref_c NOT BETWEEN '800' AND '999'
)
AND trade_d = TRUNC (SYSDATE) - 1
GROUP BY t.acct_pref_c || '-' || t.acct_id,
t.cusp_n,
t.trade_d,
t.buy_sell_i,
t.irs_n) buy,
(SELECT t.acct_pref_c || '-' || t.acct_id AS acct, t.cusp_n,
t.trade_d, t.buy_sell_i, t.irs_n AS tin, SUM (t.shr_q)
AS qty,
SUM (t.trd_amt) AS prin, COUNT (*) AS rcds
FROM loader.my_trade t
WHERE t.buy_sell_i = 'S'
AND rpt_secgroup_c <> 'MF'
AND TRIM (t.irs_n) IS NOT NULL
AND ( t.acct_pref_c NOT IN
('ZXX',
'333',
'444',
'222',
)
AND t.acct_pref_c NOT BETWEEN '800' AND '999'
)
AND trade_d = TRUNC (SYSDATE) - 1
GROUP BY t.acct_pref_c || '-' || t.acct_id,
t.cusp_n,
t.trade_d,
t.buy_sell_i,
t.irs_n) sell
WHERE buy.tin = sell.tin
AND buy.cusp_n = sell.cusp_n
AND buy.sec_typ_c IN ('1', '2')
AND ((buy.acct) <> (sell.acct))
convert 2 inline view
karma, March 25, 2005 - 2:03 pm UTC
Is there a way to write above query without using 2 inline views? Can we write this query using analytics ?
Thanks for help
March 25, 2005 - 6:50 pm UTC
probably -- didn't really look -- big big query. short followups and reviews...
How do you do THIS?????
A Friend, May 17, 2005 - 1:36 am UTC
it is a side effect of view merging:
</code>
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/opt_ops.htm#1009807 <code>
this "view"
(select a11.*,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'QTY', 'D', 1 ) COMPPREVIOUS,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'AMT', 'D',1 ) COMPPREVIOUS1,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'CODE', 'D', 1 ) COMPPREVIOUS2,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'PRC', 'D',1 ) COMPPREVIOUS3,
ROWNUM r
from t1 a11 ) a11
cannot be merged into since it includes rownum -- so the optimizer
"materialized" it off to the side into temp and then joined to it.
So the function F was called 4*50 times = 200 in order to create this temp
object.
In the other case, where the rownum was not used -- the function f() was called
over and over and over in order to do the MAX processing (sort of like sorting).
The function was evaluated and re-evaluated many times.
Rownum let us call the function 4 times per row (50 rows) and be done with it.
May 17, 2005 - 8:24 am UTC
what do you mean?
explain plan of scalar sub queries
A reader, March 18, 2006 - 9:14 am UTC
I have seen that there is nothing shown about the
scalar subqueries in the explain plan. why?
March 18, 2006 - 4:38 pm UTC
because you are using a really old version of Oracle and explain plan didn't use to show it.
Using software written this century would fix that up.
.
A reader, March 19, 2006 - 11:34 am UTC
I am using Enterprise Manager Console which comes
with Oracle 9i Release 2, is it very old? :)
By this century you mean its fixed in 10g?
Thanks
March 19, 2006 - 2:15 pm UTC
but what version of the DATABASE are you using that tool against. Databases do explains, enterprise manager just prints them.
ops$tkyte@ORA9IR2> select dname, (select count(*) from emp e where e.deptno = d.deptno) cnt
2 from dept d
3 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 0 TABLE ACCESS (FULL) OF 'DEPT'
scott@ORA8IR3W> select dname, (select count(*) from emp e where e.deptno = d.deptno) cnt
2 from dept d
3 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'
Any suggestions on how I can write this better?
A reader, March 21, 2006 - 10:54 am UTC
Tom,
select student_code , decode ( stud_discharge , null , 'USA' , stud_discharge ) student ,
p.stud_status , p.school_id , p.stud_id , p.code , p.desc
from table t, table p
where ( t.holds = 0
and t.stud_id not in ( select t.stud_id from table_z t where t.university= 'Y' )
and (date_2 >= '10-FEB-2001 12:33:52' or date_1 >= '10-FEB-2001 12:33:52' )
and student_code = 'TBA'
and t.p_code = 'SIG'
and (t.university_id = '0' or t.university_id is null )
and discharge is not null
and student_code in ( '5' , '10' )
and date3 is null
and t.id = p.id )
and ( manager_code ='PAM')
Statistics
----------------------------------------------------------
69834 recursive calls
0 db block gets
153064 consistent gets
0 physical reads
0 redo size
1410 bytes sent via SQL*Net to client
511 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23 rows processed
March 22, 2006 - 2:29 pm UTC
shudder:
and (date_2 >= '10-FEB-2001 12:33:52' or date_1 >= '10-FEB-2001 12:33:52' )
where is the TO_DATE() to convert the string into a date.
Hopefully you are using the CBO and "t.stud_id from table_z t" is such that t.stud_id is defined NOT NULL - else add the predicate "and t.stud_id is not null" to that subquery to ensure it is NOT NULL - otherwise the not in will be done once per row in the outer query (lots of scanning going on)
query plan
abz, January 12, 2007 - 11:35 am UTC
You said that, "in general scalar subquery will be executed
for every row returned by the parent query".
Can you show any example of query plans, one that shows
that scalar subquery is executed for every rows and
one that shows that scalar subquery is not executed for every row.
Thanks.
any body can answer
abz, February 06, 2007 - 2:09 am UTC
Can anybody answer the above.
February 06, 2007 - 11:06 am UTC
the execution plan will not show anything like that - a scalar subquery "in general" will be executed once per row.
however, there are internal optimizations like "scalar subquery caching" (search for that phrase on this site to learn about that) that can make it so that the scalar subquery is NOT executed for each row.
Conceptually, you may think of:
select column, column, (subquery)
from t
as:
for x in (select * from t)
loop
execute (subquery) and add it to the output list of columns
output record
end loop
but the subquery results MIGHT be cached so as to execute the subquery only once for a given set of inputs.
merge
A reader, December 17, 2011 - 3:24 am UTC
Hi Tom,
Below the merge query .I wonder if i can use the rownum magic to tune it :
unfortunately we dont have tkprof.
ln_actions_due_gtt : Gtt table
ln_insurance_funcs : package
ln_acct_mast : view of 4 table
ln_acct_schedule_detls : per account avg 20 rows
ln_prod_mast P : 200 rows
ln_acct_stream_mast : per stream 100000 rows
MERGE INTO ln_actions_due_gtt A
USING ( SELECT L.cod_acct_no, L.cod_prod, L.cod_cc_brn, S.date_instal,
bitwise_or( DECODE( ln_insurance_funcs.is_ins_charging(L.cod_acct_no),
'Y',
bitwise_or( DECODE(L.flg_reminder_gen,'Y',
bitwise_or(4,2),4),
134217728),
DECODE(L.flg_reminder_gen,'Y',
bitwise_or(4,2),
4)),
262144) cod_action, L.cod_remitter_acct
FROM ln_acct_mast L, ln_acct_schedule_detls S, ln_prod_mast P,ln_acct_stream_mast M
WHERE L.cod_acct_no = S.cod_acct_no
AND L.cod_acct_no = M.cod_acct_no
AND cod_actions_stream = pi_cod_stream_id
AND L.cod_prod = P.cod_prod
AND P.flg_mnt_status = 'A'
AND P.cod_prod_type != LN_GLOBAL.REVOLVING -- to skip processing of revolving loans
AND S.date_instal >= l_dat_process
AND S.date_instal < l_dat_next_proess
AND L.amt_disbursed > LN_GLOBAL.EPSILON
AND L.cod_acct_stat NOT IN (1,
5,
11,
12,
20,
24,
27) ) B
ON ( A.cod_acct_no = B.cod_acct_no AND A.dat_action = B.date_instal )
WHEN MATCHED THEN UPDATE SET A.cod_action = bitwise_or(A.cod_action, B.cod_action),
A.cod_stream_id = 99
WHEN NOT MATCHED THEN INSERT ( cod_acct_no, cod_prod, cod_cc_brn, dat_action,
cod_action, ctr_srl_no, cod_stream_id, cod_remitter_acct )
VALUES ( B.cod_acct_no, B.cod_prod, B.cod_cc_brn, B.date_instal,
B.cod_action, 1, 99, B.cod_remitter_acct );
i have tried to use use_hash hint which show cpu cost less but while doing bench marking found that it taking more time with hint.they are 10 merge in function like this.This run in 54 streams.
December 17, 2011 - 9:21 am UTC
we can do better in 2011, scalar subquery caching:
http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html I'm assuming bitwise_or is a function you wrote in plsql. If the input to it - l.flg_reminder_gen, has a small number of distinct values - we can avoid calling it a huge number of times easily using that technique.
Same with ln_insurance_funcs.is_in_charging.
Of course, if either of those can be replaced by inline SQL - DO IT. For example, if is_ins_charging is a simple sql lookup, JUST JOIN and don't call that function at all.
rownum
A reader, December 19, 2011 - 5:57 am UTC
Hi Tom,
Yes bitwise_or is our function.
Below query was orginal in function.I have run the function in rollback mode :
MERGE INTO ln_actions_due_gtt A
USING (SELECT L.cod_acct_no,
L.cod_prod,
L.cod_cc_brn,
S.date_instal,
bitwise_or(DECODE(ln_insurance_funcs.is_ins_charging(L.cod_acct_no),
'Y',
bitwise_or(DECODE(L.flg_reminder_gen,
'Y',
bitwise_or(4, 2),
4),
134217728),
DECODE(L.flg_reminder_gen,
'Y',
bitwise_or(4, 2),
4)),
262144) cod_action,
L.cod_remitter_acct
FROM ln_acct_mast L,
ln_acct_schedule_detls S,
ln_prod_mast P,
ln_acct_stream_mast C
WHERE L.cod_acct_no = S.cod_acct_no
AND L.cod_acct_no = C.cod_acct_no
AND C.cod_action_stream = var_pi_stream_id
AND L.cod_prod = P.cod_prod
AND P.flg_mnt_status = 'A'
AND P.cod_prod_type != LN_GLOBAL.REVOLVING
AND S.date_instal >= l_dat_process
AND S.date_instal < l_dat_next_proess
AND L.amt_disbursed > LN_GLOBAL.EPSILON
AND L.cod_acct_stat NOT IN (1, 5, 11, 12, 20, 24, 27)) B
ON (A.cod_acct_no = B.cod_acct_no AND A.dat_action = B.date_instal)
WHEN MATCHED THEN ....
Then ran alter system flush buffer_cache;
alter system flush shared_pool;
After that run the same function with changed query as below :
MERGE INTO ln_actions_due_gtt A
USING (SELECT L.cod_acct_no,
L.cod_prod,
L.cod_cc_brn,
S.date_instal,
(select bitwise_or(DECODE((SELECT CASE
WHEN COUNT(1) > 0 THEN
'Y'
ELSE
'N'
END
FROM ba_insurance_policy_mast
WHERE cod_acct_no =
L.cod_acct_no
AND cod_policy_status !=
l_expired
AND frq_ins_prem =
l_every_due_date
AND flg_mnt_status = 'A'),
'Y',
(select bitwise_or(DECODE(L.flg_reminder_gen,
'Y',
(select bitwise_or(4,
2)
from dual),
4),
134217728)
from dual),
DECODE(L.flg_reminder_gen,
'Y',
(select bitwise_or(4, 2)
from dual),
4)),
262144)
from dual) cod_action,
L.cod_remitter_acct
FROM ln_acct_mast L,
ln_acct_schedule_detls S,
ln_prod_mast P,
ln_acct_stream_mast C
WHERE L.cod_acct_no = S.cod_acct_no
AND L.cod_acct_no = C.cod_acct_no
AND C.cod_action_stream = var_pi_stream_id
AND L.cod_prod = P.cod_prod
AND P.flg_mnt_status = 'A'
AND P.cod_prod_type != l_revolving
AND S.date_instal >= l_dat_process
AND S.date_instal < l_dat_next_proess
AND L.amt_disbursed > l_epsilon
AND L.cod_acct_stat NOT IN (1, 5, 11, 12, 20, 24, 27)) B
ON (A.cod_acct_no = B.cod_acct_no AND A.dat_action = B.date_instal)
WHEN MATCHED THEN ....
As you have suggested i have changed the query to remove the call of package ln_insurance_funcs.is_ins_charging(L.cod_acct_no)
as it was simple sql look up.Then for all the function bitwise_or we have use select f() from dual.
After comparing the time i got benefit of around 4 minutes.But still i am unable to use the rownum magic.
As inside bitwise_or function we using decode and then inside decode again using bitwise_or.is it the correct place i am using rownum ?
I have tried below but error as "L.flg_reminder_gen invalid identifier "
SELECT L.cod_acct_no,
L.cod_prod,
L.cod_cc_brn,
S.date_instal,
(select X from (select bitwise_or(DECODE((SELECT CASE
WHEN COUNT(1) > 0 THEN
'Y'
ELSE
'N'
END
FROM ba_insurance_policy_mast
WHERE cod_acct_no =
L.cod_acct_no
AND cod_policy_status !=
l_expired
AND frq_ins_prem =
l_every_due_date
AND flg_mnt_status = 'A'),
'Y',
(select bitwise_or(DECODE(L.flg_reminder_gen,
'Y',
(select bitwise_or(4,
2)
from dual),
4),
134217728)
from dual),
DECODE(L.flg_reminder_gen,
'Y',
(select bitwise_or(4, 2)
from dual),
4)),
262144)cod_action,ROWNUM
from dual)) ,
L.cod_remitter_acct
FROM ln_acct_mast L,
ln_acct_schedule_detls S,
ln_prod_mast P,
ln_acct_stream_mast C
WHERE L.cod_acct_no = S.cod_acct_no
AND L.cod_acct_no = C.cod_acct_no
AND C.cod_action_stream = var_pi_stream_id
AND L.cod_prod = P.cod_prod
AND P.flg_mnt_status = 'A'
AND P.cod_prod_type != l_revolving
AND S.date_instal >= l_dat_process
AND S.date_instal < l_dat_next_proess
AND L.amt_disbursed > l_epsilon
AND L.cod_acct_stat NOT IN (1, 5, 11, 12, 20, 24, 27)
December 19, 2011 - 8:05 am UTC
After comparing the time i got benefit of around 4 minutes.But still i am
unable to use the rownum magic.
we've already done better than rownum would have done. I said "we can do better in 2011" - we have scalar subqueries and the caching thereof.
rownum will not be useful here.
you can (should) replace bitwise_or( constant, constant ) with a "constant" - you know that bitwise_or(4,2) is a constant.
can we see your bitwise_or code?
rownum
A reader, December 19, 2011 - 8:56 am UTC
Hi Tom,
This the only query which runs for 10 mins.
Below is the code :
CREATE OR REPLACE FUNCTION "BITWISE_OR"(snum1 IN INTEGER, snum2 IN INTEGER)
return INTEGER as
rint INTEGER;
begin
declare
schar1 VARCHAR2(2000);
schar2 VARCHAR2(2000);
bit1 CHAR(1);
bit2 CHAR(1);
i INTEGER;
max_len INTEGER;
begin
schar1 := to_binary(snum1);
schar2 := to_binary(snum2);
IF (length(schar1) > length(schar2)) THEN
max_len := length(schar1);
ELSE
max_len := length(schar2);
END IF;
schar1 := LPAD(schar1, max_len, '0');
schar2 := LPAD(schar2, max_len, '0');
rint := 0;
FOR i IN 1 .. max_len LOOP
bit1 := substr(schar1, i, 1);
bit2 := substr(schar2, i, 1);
if (bit1 = '1') or (bit2 = '1') then
rint := rint + power(2, (max_len - i));
end if;
END LOOP;
return rint;
end;
end;
December 19, 2011 - 4:12 pm UTC
assuming to_binary just turns a number into a bit string of 0's and 1's... You can drop bitwise_or and use a builtin function "bitand"
bitwise_or(x,y) = x+y-bitand(x,y)
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1284001741333
drop table t;
create table t as select object_id id1, data_object_id id2 from all_objects
where data_object_id is not null;
insert /*+ append */ into t select * from t;
commit;
insert /*+ append */ into t select * from t;
commit;
insert /*+ append */ into t select * from t;
commit;
insert /*+ append */ into t select * from t;
commit;
@trace
set termout off
select bitwise_or(id1,id2) from t;
select id1+id2-bitand(id1,id2) from t;
set termout on
@tk "sys=no"
select bitwise_or(id1,id2) from t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 3 1 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 7610 4.66 5.31 79 7820 0 114128
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7612 4.67 5.31 79 7823 1 114128
********************************************************************************
select id1+id2-bitand(id1,id2) from t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 1 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 7610 0.06 0.27 3 7820 0 114128
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7612 0.06 0.27 4 7821 0 114128
It uses a tad bit less cpu to use native builtins.... You might find that useful.
Thanks
A reader, December 20, 2011 - 9:54 am UTC
Hi Tom,
Thanks alot.....You are great.Learn alot from this site.
aSK
A reader, December 20, 2011 - 10:31 pm UTC
Hi Tom,
Suppose i have a table join on permanent table and function based .Can we dual here also.Are going to see benefit here as well ?????
like as below :
select ....
FROM ln_acct_mast L,
TABLE(CAST( PK_LN_ACCT_SCHED_DATES.get_due_dates(L.cod_acct_no, l_dat_process, l_dat_next_proess - 1)AS LN_DATES)) S,
ln_prod_mast P,
ln_acct_stream_mast C
December 21, 2011 - 7:35 am UTC
that is not a scalar subquery, no. You cannot implement a scalar subquery there.
scalar query
A reader, December 23, 2011 - 12:55 am UTC
Hi Tom,
Below is the function we are using in query :
And we can easily replace the use of function by using directly INSTR.The question is how to handle null problem.
I mean in below function we are checking the exe value,if null raise error.What if in query null value is there and want to handle the same way in function
CREATE OR REPLACE FUNCTION "CHARINDEX"(exp IN VARCHAR2, str IN VARCHAR2)
return INTEGER as
null_problem EXCEPTION;
len INTEGER(4);
begin
if exp is null then
begin
raise null_problem;
end;
else
begin
len := nvl(INSTR(str, exp, 1, 1), 0);
return len;
end;
end if;
end;
Below was the original query :
FROM ln_arrears_table
WHERE cod_acct_no = l_tmp_cod_acct_no
AND 1 = DECODE(pi_arrear_type, NULL, 1, cod_arrear_type, 1, 0)
AND CHARINDEX(cod_arrear_type, l_cod_appr_seq) != 0
AND amt_arrears_due > LN_GLOBAL.EPSILON
AND dat_arrear_cancelled = LN_GLOBAL.BLANK_DATE
AND dat_arrears_assessed <= pi_date
Replace with
WHERE cod_acct_no = l_tmp_cod_acct_no
AND 1 = DECODE( pi_arrear_type,NULL,1,cod_arrear_type,1,0 )
AND nvl(INSTR(l_cod_appr_seq, cod_arrear_type, 1, 1), 0) != 0
AND amt_arrears_due > LN_GLOBAL.EPSILON
AND dat_arrear_cancelled = LN_GLOBAL.BLANK_DATE
AND dat_arrears_assessed <= pi_date
December 23, 2011 - 8:42 am UTC
scott%ORA11GR2> select comm, case when comm is null then 1/0 else power(comm,2) end from emp;
select comm, case when comm is null then 1/0 else power(comm,2) end from emp
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
scott%ORA11GR2> select comm, case when comm is null then 1/0 else power(comm,2) end from emp where comm is not null;
COMM CASEWHENCOMMISNULLTHEN1/0ELSEPOWER(COMM,2)END
---------- ---------------------------------------------
300 90000
500 250000
1400 1960000
0 0
I've done that before - let a zero divide creep in when the statement finds a condition that must cause it to fail
explain this query
Ved, December 23, 2011 - 9:29 am UTC
Recently I came across this query..
SELECT * FROM dept a,
(SELECT deptno dno FROM emp
) WHERE deptno IN dno ;
1) I am not sure how this query works.. and also is this semantically correct. Please give an example as to where you would ideally use this.
2) Please explain the syntax and since which release is this available.
3) If possible, please point me the documentation link which explains this.
December 23, 2011 - 10:02 am UTC
where deptno in dno
has been valid syntax forever:
ops$tkyte%ORA11GR2> select * from dual where dummy in 'X';
D
-
X
ops$tkyte%ORA11GR2>
you only need the () for in if you have more than one. where column in literal is a fancy way of saying "where column = literal"
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from dual where dummy in 'X';
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DUMMY"='X')
that query is just a silly way to write:
select dept.*, emp.deptno dno
from emp, dept
where emp.deptn = dept.deptno;
scalar query
A reader, December 29, 2011 - 12:39 am UTC
scalar query
A reader, December 29, 2011 - 12:47 am UTC
Hi Tom,
In our lots of code we use package which have some constant value.And that value get used under some package in some query.
lets say below :
WHERE cod_acct_no = l_tmp_cod_acct_no
AND 1 = DECODE( pi_arrear_type,NULL,1,cod_arrear_type,1,0 )
AND nvl(INSTR(l_cod_appr_seq, cod_arrear_type, 1, 1), 0) != 0
AND amt_arrears_due > LN_GLOBAL.EPSILON
AND dat_arrear_cancelled = LN_GLOBAL.BLANK_DATE
AND dat_arrears_assessed <= pi_date
Here LN_GLOBAL is a package where
EPSILON CONSTANT NUMBER := 0.00000000001;
The main reason behind to use this is if in future we need to change the constant value than only change required in one place.
If we hard code the value 0.00000000001 directly will have better performance than LN_GLOBAL.EPSILON using above query ??
And can we use dual for this as well ????
Also i was thinking about caching as it used so many place in batch process.Can we do that ???
Thanks alot.
December 29, 2011 - 11:15 am UTC
If we hard code the value 0.00000000001 directly will have better performance
than LN_GLOBAL.EPSILON using above query ??
no, it won't. As it is now, ln_global.epsilon is a bind variable in the query and since we'll peek at bind variables during parsing, it is as if it were hard coded (without being hard coded). This is perfectly find.
And can we use dual for this as well ????
no, that would actually be an anti-tuning technique. Ln_global.epsilon is a bind variable, not a scalar subquery.
scalar query
A reader, December 31, 2011 - 5:31 pm UTC
Hi Tom,
Let say in a select query using a package and using 2 different function of package in select query.Should we use dual for both ???
select pk_test.abc(XYZ), pk_test.def(wer) from dual
replace with
select (select pk_test.abc(XYZ) from dual),
(select pk_test.def(wer) from dual)
from dual
Thanks alot.
January 01, 2012 - 10:28 am UTC
yes
A reader, January 01, 2012 - 6:05 am UTC
Hi Tom,
I was trying to use rownum magic on table view which is join for 4 tables and having 10-12 function use in select query.But the impact is negative.
As below
CREATE OR REPLACE VIEW test_view AS
SELECT a.col1 col,f(),f(),f(),f(),rownum rows from a,b,c,d
where a.col1 = b.col1
and a.col1 = c.col1
and a.col1 = d.col1
Can you please explain why ???
January 01, 2012 - 10:30 am UTC
why did you think it would be positive.
In this case, it would have zero effect in a positive sense.
You didn't have anything even remotely similar to the problem above (with the big multi-table join). there I used rownum to materialize (run into temp) a tiny piece of the query that included plsql functions and joined to that. You are not doing anything even remotely similar.
A reader, January 01, 2012 - 11:50 am UTC
Hi Tom,
Let say in a select query using a package and using 2 different function of package in select
query.Should we use dual for both ???
select pk_test.abc(XYZ), pk_test.def(wer) from dual
replace with
select (select pk_test.abc(XYZ) from dual),
(select pk_test.def(wer) from dual)
from dual
Thanks alot.
January 01, 2012 - 12:37 pm UTC
see two comments above - asked and answered already - before you posted this yet again ???
A reader, January 01, 2012 - 1:03 pm UTC
sorry Tom....
Thanks alot...