Thanks
Dennis Cook, March 15, 2002 - 9:55 am UTC
Looks very efficient. Shame it involves so many lines
of code, though, as I would have thought it quite a
common test.
Thanks for your help.
March 15, 2002 - 2:19 pm UTC
Well, actually, in many many many years of coding I think I could count on one hand how many times I've used this construct.
I don't see the usefulness of this particular construct. The reason -- I don't see the point in branching based on the existence of a row in a table WITHOUT ACTUALLY GETTING the row itself. I mean, don't I need the values in that row? The existence of the row doesn't tell me very much. In my code, I have lots of:
begin
select * into l_Rec from t where....;
process....
exception
when no_data_found then
processs.....
end;
which is the same as your
begin
if exists ( select ... )
then
process...
else
process ...
end if;
end;
but I have access to the row itself.
RE: in many many many years...
Mark A. Williams, March 15, 2002 - 2:37 pm UTC
Aw, c'mon Tom, I've got your book - your picture is on it. It can't be *that* many years!
- Mark
Alternate way
Jagdish Malani, April 15, 2002 - 5:46 pm UTC
I am not sure if we really need to check the count in the for loop.
I think we can do as follows:
for x in ( select * from dual
where exists
(select * from foo where bar)
)
loop
.... processing
end loop;
The body of the loop will execute once if the subquery is satisfied, zero times otherwise. This way we do not need to check x.cnt 's value for 1 or 0.
Please correct me if I am wrong.
April 16, 2002 - 7:43 am UTC
If you want to do an:
if ( exists ..... )
end if;
your approach works ( I use that myself )
If you want to do:
if ( exists .... )
....
ELSE
.....
end if
you need to look at count
Well, well , well
Famy, April 16, 2002 - 12:08 pm UTC
Tom
According to you a 'where exists'
"only runs the subquery as long as it "needs" to, where
exists stops the subquery after hitting the first row)'
But look below, if what you have said is right then only records of only one department should be returned.
The below query tells us that the WHERE EXISTS is returning all the departments.
Kindly clarify.
Wrote file afiedt.buf
1 select ename, deptno from emp
2 where exists( select null from dept
3* where emp.deptno=dept.deptno)
/
ENAME DEPTNO
---------- ---------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
14 rows selected.
April 16, 2002 - 12:31 pm UTC
It runs the subquery for each row in the OUTER query.
It runs the subquery only as much as it needs to. Consider:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int, y char(30) );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select 1, 'x' from all_objects;
22843 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select 2, 'x' from dual;
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly statistics;
ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
2 from dual
3 where exists ( select null from t where x = 1 );
Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
2 consistent gets
63 physical reads
0 redo size
363 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
2 from dual
3 where exists ( select null from t where x = 2 );
Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
125 consistent gets
60 physical reads
0 redo size
363 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off
Notice the consistent gets -- since "1" is near the "front" of T, the subquery stops right away. I put "2" at the "end" of T, the subquery read the entire table in that case.
If dual had N rows -- it would run the subquery N times with N different values if I used a correlated subquery...
re:re:in many many years
Jim, April 16, 2002 - 7:49 pm UTC
Mark,
While I agree with you on your evaluation of Tom's picture on the cover of his book - his youthful appearance - you have to consider things in "Tom Years". For us mortals Tom accomplishes a multiple of what we accomplish. Thus something like 20 years to us Tom accomplishes in just 5.
Hence, the in many many years refers to our time.
:-)
A reader, April 22, 2003 - 10:54 am UTC
Well, here is one occasion where this costruct might be useful. I just realized that I cannot use exists in PL/SQL when I hit a PLS-204 on this block (for validating FK relationship before making an insert).
FOR i IN (SELECT * FROM staging_postal_address)
LOOP
---Many other validation blocks here...
---
---
/************************************
*FK validation.
*Check if pos_add_address_id has parent in address table.
************************************/
IF NOT EXISTS ( SELECT NULL
FROM address
WHERE add_address_id = i.pos_add_address_id)
THEN
v_error_code := 'POS002';
v_error_pk_value := i.pos_address_id;
RAISE e_err;
END IF;
I had to define a variable and select into it as in Dennis' post. I think it will be quite useful to have the exists construct in pl/sql too.
Actually, I am trying to write a generic function for validating the FK relationship. I want to pass as input variables the parent table name, child table name, parent table pk field and child table fk field. Can you please help me write this (it will be a 2min job for you, while may be a day for me!). Or if you have anyother suggestion for validating this relation using an existing oracle fn, please describe. As always thanks verymuch for your time.
I use exists to determine if I need to do an update or insert
Mark Nijhof, July 29, 2004 - 11:20 am UTC
Hi Tom,
I come from using mostly ms sql, but also sybase and mysql. Now I am working in a company where they use oracle, so I am learning a new dialect.
I use exists to determine if I need to do an update or insert in ms sql.
IF EXISTS(SELECT 'True' FROM TABLE WHERE COLUMN = @ID)
BEGIN
UPDATE TABLE;
END
ESLE
BEGIN
INSERT INTO TABLE;
END
Is this a bad way of doing this? And would you use the way you describt on top of the page to do this?
Thanks,
Mark
July 29, 2004 - 1:30 pm UTC
i would:
update table
if ( sql%rowcount = 0 )
then
insert
end if;
the exists just wastes time. the update will do the "read" and if it finds something -- the update, if not the insert will happen.
this would be true in sqlserver as well!
Thanks
Mark Nijhof, July 30, 2004 - 4:56 am UTC
Thanks,
I'll starting to use this approch next time, and will alter my own existing procedures. p.s. I am a application developer (programmes) so this might be a stupid mistake, I appreciate to reply.
Mark
Great thread
A reader, January 05, 2007 - 12:08 pm UTC
Tom,
I would like to do the following...I would like to check
for the existance of a partition on a table and if exists email me but do not create it. Do you have something like that...or anyone here in this thread?
Thank you...
January 06, 2007 - 8:34 am UTC
does not make sense - what does it mean to "check for the existence of a partition", what would trigger this "check", what causes this "check" to happen.
Given there are potentially an infinite number of partitions you might create on a table, how do you know to "check" for any given one?
Thanks for the response
A reader, January 08, 2007 - 10:18 am UTC
does not make sense - what does it mean to "check for the existence of a partition", what would trigger this "check", what causes this "check" to happen.
- Every month we drop and re-create partition part_1234
however, let's say we forget to create it or drop it.
how can I check first if the partition is there(exists)
before we re-create it...it is sort like your example
above about checking for the existance of a table...
January 08, 2007 - 1:14 pm UTC
query user_tab_partitions.
It is more efficient?
sandro, April 27, 2007 - 10:07 am UTC
You have said...
<<...
for x in ( select count(*) cnt
from dual
where exists ( select NULL from foo where bar ) )
loop
if ( x.cnt = 1 )
then
found do something
else
not found
end if;
end loop;
is one way (very fast, only runs the subquery as long as it "needs" to, where exists
stops the subquery after hitting the first row)
That loop always executes at least once and at most once since a count(*) on a table
without a group by clause ALWAYS returns at LEAST one row and at MOST one row (even of
the table itself is empty!)...>>For me is (little) more efficient this approch...
for x in ( select /*+ first_rows(1) */ count(*) cnt
from foo
where bar
and rownum < 2 )
loop
if ( x.cnt = 1 )
then
found do something
else
not found
end if;
end loop;Without query dual......waht do you think about?
April 27, 2007 - 11:16 am UTC
sure, it works with a little less work
once again
Reene, April 07, 2008 - 4:28 am UTC
Hi Tom
sorry I have posted it in another therad too.but i think this thread is more relevent to ask this.
my question is
1) the way you have explined exists and not exists..will a query with exists and not exists always use a nested loop as join method..as it works on one record at a time.
2) if that is true then what is the alternat of not exists..
my problem is the query below which takes only nested loop joins which i think correct -as it works on one record at a time.
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 2 690.28 4899.16 1524771 23494672 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 690.28 4899.16 1524771 23494672 0 12
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 65 (APPS)
SELECT /*+ ordered use_hash(mtp) push_subq */ ooha.org_id "OPERATING UNIT ID"
FROM apps.oe_transaction_types_all ott,
apps.oe_transaction_types_tl ottt_l,
apps.oe_order_lines_all oola,
apps.mtl_parameters mtp,
apps.oe_order_headers_all ooha,
apps.hz_cust_accounts hca
WHERE ooha.header_id = oola.header_id AND
ooha.org_id = oola.org_id AND
ooha.order_source_id = 1263 AND
ott.transaction_type_id = ottt_l.transaction_type_id AND
ott.org_id = ooha.org_id AND
oola.line_type_id = ottt_l.transaction_type_id AND
oola.line_type_id = ott.transaction_type_id AND
mtp.organization_id = oola.ship_from_org_id AND
hca.cust_account_id = ooha.sold_to_org_id AND
(ottt_l.NAME LIKE 'GPO%FE_CONSIGNMENT_LINE' OR
ottt_l.NAME LIKE 'GPO%FE_SHIPMENT_LINE') AND
oola.flow_status_code IN ('SHIPPED', 'CLOSED') AND
hca.attribute9 IS NOT NULL
and NOT EXISTS
( SELECT /*+ NO_UNNEST */ 1
FROM apps.mtl_material_transactions mmt ,
apps.mtl_transaction_lot_numbers mtnl ,
apps.mtl_parameters mp ,
apps.mtl_item_locations mil
WHERE mmt.inventory_item_id = oola.inventory_item_id AND
mmt.transaction_type_id IN (420, 40, 41, 42) AND
mmt.transaction_quantity > 0 AND
mtnl.lot_number = ooha.order_number AND
mtnl.transaction_id = mmt.transaction_id AND
mmt.transaction_date > (TRUNC(SYSDATE) -90) AND
mmt.organization_id = mp.organization_id AND
mp.attribute7 = 'PARTS' AND
mp.attribute11 ='SERVICE' AND
mil.inventory_location_id = hca.attribute9 AND
mil.organization_id = mp.organization_id
) AND
NVL(oola.actual_shipment_date, oola.schedule_ship_date) > SYSDATE - 90
Rows Row Source Operation
------- ---------------------------------------------------
12 NESTED LOOPS (cr=23494672 r=1524771 w=0 time=4640213970 us)
157696 NESTED LOOPS (cr=5730245 r=1402270 w=0 time=3699650845 us)
157697 HASH JOIN (cr=5257151 r=1379549 w=0 time=3576187058 us)
157697 TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL (cr=5257149 r=1379549 w=0 time=3575115599 us)
2789657 NESTED LOOPS (cr=13232 r=12982 w=0 time=90323177 us)
76 HASH JOIN (cr=277 r=82 w=0 time=175347 us)
4597 TABLE ACCESS FULL OE_TRANSACTION_TYPES_ALL (cr=157 r=0 w=0 time=6683 us)
76 TABLE ACCESS FULL OE_TRANSACTION_TYPES_TL (cr=120 r=82 w=0 time=157808 us)
2789580 INDEX RANGE SCAN GEMS_ONT_ORDER_LINES_N99 (cr=12955 r=12900 w=0 time=86301122 us)(object id 1863725)
377 INDEX FULL SCAN MTL_PARAMETERS_U1 (cr=2 r=0 w=0 time=367 us)(object id 9847)
157696 TABLE ACCESS BY INDEX ROWID OE_ORDER_HEADERS_ALL (cr=473094 r=22721 w=0 time=123022055 us)
157697 INDEX UNIQUE SCAN OE_ORDER_HEADERS_U1 (cr=315397 r=1363 w=0 time=11565697 us)(object id 688729)
12 TABLE ACCESS BY INDEX ROWID HZ_CUST_ACCOUNTS (cr=17764427 r=122501 w=0 time=1114177945 us)
157696 INDEX UNIQUE SCAN HZ_CUST_ACCOUNTS_U1 (cr=315395 r=274 w=0 time=3770326 us)(object id 715003)
157621 TABLE ACCESS BY INDEX ROWID MTL_TRANSACTION_LOT_NUMBERS (cr=17291336 r=121205 w=0 time=1096619120 us)
6897874 NESTED LOOPS (cr=14562731 r=114663 w=0 time=1018837983 us)
3448931 NESTED LOOPS (cr=7488435 r=112057 w=0 time=936487677 us)
157633 NESTED LOOPS (cr=788165 r=581 w=0 time=10022817 us)
157633 INDEX RANGE SCAN MTL_ITEM_LOCATIONS_U1 (cr=472899 r=581 w=0 time=7984520 us)(object id 9761)
157633 TABLE ACCESS BY INDEX ROWID MTL_PARAMETERS (cr=315266 r=0 w=0 time=1667395 us)
157633 INDEX UNIQUE SCAN MTL_PARAMETERS_U1 (cr=157633 r=0 w=0 time=770942 us)(object id 9847)
3448931 TABLE ACCESS BY INDEX ROWID MTL_MATERIAL_TRANSACTIONS (cr=6700270 r=111476 w=0 time=924106849 us)
8273225 INDEX RANGE SCAN MTL_MATERIAL_TRANSACTIONS_N1 (cr=695175 r=32781 w=0 time=303092226 us)(object id 6205511)
3448931 INDEX RANGE SCAN MTL_TRANSACTION_LOT_NUMBERS_N1 (cr=7074296 r=2606 w=0 time=74416944 us)(object id 9976)
is there a way to improve this query.
Thanks
April 07, 2008 - 9:31 am UTC
1) the way you have explined exists and not exists..will a query with exists
and not exists always use a nested loop as join method..as it works on one
record at a time.
no, it will not - the CBO knows how to rewrite both constructs as semi-joins.
2) lose the hints, just loose them.
sometime hints helps too
Reene, April 07, 2008 - 9:50 am UTC
Hi Tom
database is oracle 9iR2.oracle apps.
i have seen that in most of the queries where there is too many tables and /or too many where clauses are there.the appropriate hints have improved the performance..why did you say that loose the hints.as sometime they do improve the performance.
consider this
select /*+ parallel(ooh,4) parallel(ool,4) ordered use_hash(ooh ool) */ ooh.order_number,count(ool.line_id)
from ont.oe_order_headers_all ooh,
ont.oe_system_parameters_all osp,
ont.oe_order_lines_all ool,
wsh.wsh_delivery_Details wdd
where ooh.org_id = osp.org_id
and ooh.order_type_id = osp.attribute1
and ooh.order_source_id = 1203
and ooh.header_id = ool.header_id
and ooh.org_id = ool.org_id
and ool.line_id = wdd.source_line_id (+)
and nvl(released_status,'X') <> 'C'
and nvl(released_status,'X') <> 'D'
and ool.shippable_flag = 'Y'
group by ooh.order_number
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.03 0.91 1 6 0 0
Fetch 362 0.36 2570.08 1 3 0 36019
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 364 0.40 2571.00 2 9 0 36019
select oh.order_number,count(ol.line_id)
from ont.oe_order_headers_all oh,
ont.oe_order_lines_all ol,
wsh.wsh_delivery_Details wdd,
ont.OE_SYSTEM_PARAMETERS_ALL osp
where oh.header_id=ol.header_id and
ol.line_id=wdd.source_line_id (+) and
oh.org_id = ol.org_id and
oh.ORDER_TYPE_ID = osp.ATTRIBUTE1 and
oh.org_id=osp.org_id and
(oh.order_source_id = 1203 ) and
nvl(wdd.released_status,'x') <> 'C' and
nvl(wdd.released_status,'x') <> 'D' and
ol.shippable_flag='Y'
group by oh.order_number
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 362 698.53 6250.86 2015571 15852070 0 36019
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 364 698.54 6250.87 2015571 15852070 0 36019
-- another one
select /*+ parallel(ooh,4) push_subq */ ooh.order_number,count(ool.line_id)
from ont.oe_order_headers_all ooh,
ont.oe_order_lines_all ool,
wsh.wsh_delivery_Details wwdd
where ooh.header_id=ool.header_id and
ool.line_id=wwdd.source_line_id (+) and
exists
( select /*+ NO_UNNEST ordered */ 'X'
from ont.oe_order_headers_all oh,
ont.OE_SYSTEM_PARAMETERS_ALL osp,
ont.oe_order_lines_all ol,
wsh.wsh_delivery_Details wdd
where oh.header_id=ol.header_id and
ol.line_id=wdd.source_line_id (+) and
oh.ORDER_TYPE_ID = osp.ATTRIBUTE1 and
oh.org_id=osp.org_id and
oh.order_source_id=1203 and
nvl(wdd.released_status,'x') <> 'C' and
nvl(wdd.released_status,'x') <> 'D' and
ol.shippable_flag='Y' and
ooh.order_number = oh.order_number
) and
ooh.order_source_id=1203 and
nvl(wwdd.released_status,'x') <> 'D' and
ool.shippable_flag='Y'
group by ooh.order_number
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.02 2.32 0 3 0 0
Fetch 362 219.42 2056.17 205496 18468316 0 36019
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 364 219.44 2058.51 205496 18468319 0 36019
select ooh.order_number,count(ool.line_id)
from ont.oe_order_headers_all ooh,
ont.oe_order_lines_all ool,
wsh.wsh_delivery_Details wwdd
where ooh.header_id=ool.header_id and
ool.line_id=wwdd.source_line_id (+) and
exists
( select 'X'
from ont.oe_order_headers_all oh,
ont.OE_SYSTEM_PARAMETERS_ALL osp,
ont.oe_order_lines_all ol,
wsh.wsh_delivery_Details wdd
where oh.header_id=ol.header_id and
ol.line_id=wdd.source_line_id (+) and
oh.ORDER_TYPE_ID = osp.ATTRIBUTE1 and
oh.org_id=osp.org_id and
oh.order_source_id=1203 and
nvl(wdd.released_status,'x') <> 'C' and
nvl(wdd.released_status,'x') <> 'D' and
ol.shippable_flag='Y' and
ooh.order_number = oh.order_number
) and
ooh.order_source_id=1203 and
nvl(wwdd.released_status,'x') <> 'D' and
ool.shippable_flag='Y'
group by ooh.order_number
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 362 856.28 6515.79 2117825 35153157 0 36019
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 364 856.29 6515.80 2117825 35153157 0 36019
i agree - inapproriate use of hints would impact the peformance negatively,but if we use them after analysis then they help also.
what is your thought on this.
April 07, 2008 - 10:35 am UTC
there are two types of hints:
good ones - parallel, cardinality, first_rows, all_rows, dynamic sampling, cursor sharing exact...
there are bad ones - ordered, index, use_nl
use the good ones whenever you want. avoid the bad ones like the plague - use them as a temporary "bug fix", knowing that with every release you need to re-evaluate their use. also, try to understand why you felt compelled to use a hint - it is always for one reason: the optimizer got the estimated cardinality wrong - now, ask "why" and ask "how can I fix that" (eg: dynamically sample the data and fix the problem, tell the optimizer to optimize for initial response time, *give the optimizer information* - do not tell it how to do it's job)
no one likes to use it...
Reene, April 08, 2008 - 1:58 am UTC
Hi Tom
agree.no one i guess likes to go through the cumbersome process of using the good or bad hint but what to do - it is to save the job,if i as a consultant hired to tune a query then i would have to do it - if i go around i tell that i need 7 days to fix the cardinality issue..no one in general likes to hear it,it is another thing that if this thing come from a person of your repute or stature..then probably they will listen and they will never say that this guy has told me this thing as well.rather they will say -he knows nothing.
the query above where i used ordered hint - i could tune in 4 very long days as customer wanted it.that was not a pleasing thing to do..but part of job.part of life.
the point here is - no matter how much we explain or read or understand the truth in last 10 yrs i have found that hint are pretty much part of oracle code. In many oracle own standard programs - i have seen extensive use of hints.
i am sure . someone can put together - the cases where hints can be used and also where hint should not be used.
optimizer dynamic sampling or other good hints work well if query is simpler , having 2-3-4 tables and may be couple of conflicting where clauses..but when it comes to those junk code..which we suppose to tune..i just could not find a way to use them and get the work done.
may be its me..not oracle.
April 09, 2008 - 2:02 pm UTC
if you need 7 days to solve a cardinality issue, we might have a problem identified already.
and if it took 4 long days to tune a single query....
maybe you've never actually tried the other hints :(
when will CBO use semi join
Reene, April 08, 2008 - 7:16 am UTC
Hi Tom
in your respons above you said
"no, it will not - the CBO knows how to rewrite both constructs as semi-joins."
when CBO chooses a semi join and when it does not chooses a semi join ? is there a specific case where it will use semi join .
April 09, 2008 - 2:08 pm UTC
it will use a semi-join
a) when it can
b) and when the statistics drive it that way
it is all about relative costs - when a semi-join is deemed better than another approach - it'll use it.
here is a simple function
sd, May 22, 2008 - 12:44 pm UTC
CREATE OR REPLACE Function Is_Exists
RETURN char
IS
flgExists Char(1);
begin
select 'T' into flgExists from dual where exists (select empno from emp where deptno=20);
return flgExists;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return 'F';
end;
Selecting into where exists
Allan Peda, October 28, 2011 - 11:57 am UTC
I came upon this thread when googling select into where exists. I wanted to avoid the two step checking whether a row is found when doing a select into, and I don't like the idea of using an exception. I elected to try an outer join on a placeholder select of a constant from dual to force the return of at least one row. In this way if there is no result, the returned value is null. The minor caveat is that this method is limited to cases where a null result is handled in the same fashion as no result at all (selecting columns guaranteed to be "not null").
Naturally I would welcome comments if this is simply too convoluted to be considered a good idea.
declare
v_view_name user_objects.object_name%type := '';
begin
-- outer join trick to return null on no match
-- we get at least one row, simply check for null
select uo.object_name into v_view_name
from (
select o.object_name,
1 lnk
from user_objects o
where o.status = 'INVALID'
and o.object_type = 'VIEW'
and o.object_id = :object_id
) uo
right outer join (
select 1 as lnk from dual
) d
on d.lnk = uo.lnk;
if( v_view_name is not null ) then
execute immediate 'alter view '
||v_view_name||' compile';
end if;
end;
/
October 28, 2011 - 12:14 pm UTC
well, I disagree with your coding style. This is what I would code:
begin
select .. into ...
execute immediate 'alter view ' || ....;
exception
when no_data_found then null;
end;
more performant than a weird query to always get a row.
easier to read and understand.
another approach is:
begin
for x in (select ... )
loop
execute immediate 'alter ....;
end loop;
end;
It avoids the exception - but at the cost of clarity - it is no longer clear that you are getting one row at most....
a trick you can use to get at least one row and at most one row is to use an aggregate without a group by
begin
select max(object_name) into v_view_name
from user_objects
where status = 'INVALID'
and object_type = 'VIEW'
and object_id = :object_id;
if v_view_name is not null
then
execute immediate ...
end if;
end;
But - I'm a fan of the first one with the exception block - it says what it does, it does what it says, it is concise, does the least amount of work, and it easy to understand. The others are all trickery.
Checking based on primary key
Nathan, October 04, 2013 - 1:30 pm UTC
Hi Tom,
I am learning a lots of stuff from the great guru like you.What would be your approach to the following query.
Because from the very begining It was known that the query is based on primary key and must return single row.
So for this, your first apporach is good enough or can carry with the same.
declare
l_exists integer :=0;
p_empno integer :=7839;
begin
begin
select 1 into l_exists from emp where empno=p_empno;
exception
when no_data_found then
l_exists:=0;
end;
if l_exists=1 then
dbms_output.put_line('exists');
else
dbms_output.put_line('not exists');
end if;
end;
Regards,
Nathan
Jess, December 03, 2019 - 8:53 pm UTC
Is there a simple way to do a conditional assignment based on existence, but in a sql statement? Say we're hardcoding a select value based on existence like so (don't ask why, legacy code...)
select sd.id, sd.<other columns>,'classroom X'
from student_details sd
where exists (select from students s where s.id = sd.id and (<a bunch of conditionals against S>)
-- above can be rewritten to exists, of course
UNION
select sd.id, sd.<same other columns>, 'classroom Z'
from student_details sd, honor_students h
where sd.id = h.id
and NOT exists (select from students where s.id = sd.id and <the same exact conditionals against S as above>)
so essentially students are split based on whether they have/don't have certain values in student, but the driving superset is a little different...
Is there a way to do it, so that we'd only hit "student_details" and "students" once?
Thank you.
December 05, 2019 - 9:13 am UTC
You mean something like this?
create table t1 (
c1 int
);
create table t2 (
c1 int
);
insert into t1 values ( 1 );
insert into t1 values ( 2 );
insert into t2 values ( 1 );
select t1.*,
case
when exists (
select null from t2
where t1.c1 = t2.c1
)
then 'YES'
else 'NOPE!'
end match
from t1;
C1 MATCH
1 YES
2 NOPE!