1. Give More Feedback

Oracle Update Statements Version 11.1 Basic Update Statements The Oracle statement processes one or more rows in a table and sets one or more columns to the values you specify. Update all records SET = test objectname, objecttype allobjs; objectname test; test SET objectname = 'OOPS'; objectname test;; Update a specific record SET = = objectname test; test SET objectname = 'LOAD' objectname = 'DUAL';; objectname test Update based on a single queried value SET = ( ); test tablename, (' (30)) AS lowername usertables; desc test. test tablename '%A%';. test tablename '%A%'; - this is not a good thing.

To update multiple columns in a table based on values from a second table using a Select statement to. UPDATE tbl1 SET col1 = (SELECT colA. Update table with multiple columns from. Want and as many new columns as you want??? Update ( select old.a. In the merge statement?

Test t SET lowername = ( (tablename) usertables u u.tablename = t.tablename u.tablename '%A%' ); - look at the number of rows updated. test; - neither is this test t SET lowername = ( (tablename) usertables u u.tablename = t.tablename u.tablename '%A%' );. test; test t SET lowername = ( (tablename) usertables u u.tablename = t.tablename u.tablename '%A%' ) t.tablename '%A%';. test; Update based on a query returning multiple values SET (, ) = ( (, ) = ); test AS t.

Tablename, t. Tablespacename, s.extentmanagement usertables t, usertablespaces s t.tablespacename = s.

Sure, there are drawbacks. Now, just to be clear, there are two 'select' constructs you are using here a) scalar subqueries - a single row, single column query that you use in place of a 'column', it looks like a column or function. B) an inline view - the from (select. From (select. ) ) The inline views - great, fine, no problem - we can make them disappear when we optimize. The scalar subqueries - they should be the exception - not the rule.

You should be JOINING in general. Not using scalar subqueries. If you have a query select (select t2.a from t2 where t2.c = t1.c), col2, col3, (select t2.b from t2 where t2.c = t1.c), (select t3.x from t3 where t3.d = t1.d), from. You should just JOIN (outer join if need be) to T2 and T3, it would be: select t2.a, col2, col3, t2.b, t3.x from., T2, T3 where. And t2.c(+) = t1.c and t3.d(+) = t1.d - outer join IF and ONLY IF necessary Even if the query was something like: select (select count(.) from t2 where t2.c = t1.c), col2, col3, (select max(b) from t2 where t2.c = t1.c), (select sum(x) from t3 where t3.d = t1.d), from.

You would just select t2.a, col2, col3, t2.b, t3.x from., (select count(.) a, max(b) b, c from t2 group by c) T2, (select sum(x), d from t3 group by d) T3 where. And t2.c(+) = t1.c and t3.d(+) = t1.d - outer join IF and ONLY IF necessary Scalar subqueries can sometimes be useful in 'fast return' queries (optimize for initial response time).

If you have a 'large' query (it would return a TON of data if you fetched the last row) but you get the first 25, then the next 25 and so on (paging through the results for example) - you might use a scalar subquery to get the first 25 rows really fast - and then run the scalar subqueries say 25 times to fill in the blanks. If you got ALL of the rows this way, it would be very slow, but since you are interested in getting 25 and stopping, getting 25 and stopping - it might be the most efficient way of getting the first 25. Followup April 01, 2009 - 8:21 am UTC. They are slow.

No, it is better said: sometimes they are slower than a join, sometimes they are faster than a join, it depends. It depends on the context and the conditions. I gave an example above whereby a scalar subquery just might be the best thing ever (initial response time). Other times - they are not necessarily the best thing ever (they are very 'procedural' like) Here is another example - if you are going to call PLSQL from SQL, I recommend always using a scalar subquery, that is, instead of: select f(x) from t where g(y) =?; code: select (select f(x) from dual) from t where (select g(y) from dual) =?; search this site for This is true for all releases - even 11g with PLSQL FUNCTION result caching.

Yep, they're logically equivalent, but implemented differently ( at least through version X. ) and always subject to change! BTW, I concocted this example to help remember the terminology of 'subquery factoring', 'scalar subquery' and 'inline view': WITH factoredsubquery AS - ANSI SQL-99: 'Common Table Expression' ( SELECT 1 AS scalarvalue FROM DUAL ) SELECT ( SELECT 1 FROM dual WHERE ROWNUM = 1 ) as scalarsubquery, scalarvalue FROM ( SELECT. FROM factoredsubquery ) /. as./ inlineview /. Followup April 02, 2009 - 9:28 am UTC.

Would you also use joins on this type of query. If T1 has one million records and T2 has 100 records and you want the records from T1 that exist in T2. Of course not, that screams for IN.

You wrote 'that exist in', you don't want data from T2, you would not join, you would use in or exists (we don't care which, we consider them the same). Your two queries are not equivalent by the way. Not even close, they return different result sets. You never need the distinct in the subquery - using it just implies 'i don't really understand what I'm doing' But that you did use it, implies to me that orderno is NOT unique in T2 - so joining T1 to T2 would give you a completely different result than using IN. But never use distinct/unique in the IN subquery - not only is it not necessary, but it makes you look bad;) I do remember in the early 1990's - when using Sybase SQLServer, you had to do that because they unrolled an IN to a join without considering that there could be duplicates (eg: they processed the query entirely wrong) - but - you would never use distinct/unique in the subquery like that.

Followup April 14, 2009 - 11:17 am UTC It makes you look bad because you obviously thought 'I need to distinct that data in order for the result to be correct' (why else would you have put it there?). Whether the end result is the same is quite simply 'not relevant', you are performing (in your mind) an operation you apparently think you need to. But you don't.

Give More Feedback

Select. from dept where dept.deptno in (select emp.deptno from emp); emp.deptno is not unique. IN does not require, desire, need, want to have a 'distinct' there. If you use distinct in the subquery, it would appear you believe you do need it - which is absolutely incorrect. It would not change the result and the only possible thing that could happen is the optimizer does the extra distinct step when it doesn't need to.

That is - nothing good could come from it, only bad could come from it. It is sort of like coding: select. from t1, t2 where t1.key = t2.key(+) and t2.val 5; It would appear to demonstrate a lack of understanding of SQL and the set operations it performs and how it performs them.

Sort of like my pet peeve - when people code: select count(1) from. I immediately downgrade my opinion of their level of expertise. Why would you want to count non-null evaluations of the expression '1' - you apparently want to count the ROWS in the result set, so just use count(.), count(.) says what you mean - count the rows. Count(1) says 'count the number of occurrences of the number 1 that are not null'.

It is like reading 'IM speak' in an email from anyone over the age of 18 - it knocks points off of their IQ score. Hi Tom, In reponse to the difference between count(1) and count(.), you said '.count(.) says what you mean - count the rows. Count(1) says 'count the number of occurrences of the number 1 that are not null'.' I could not understand the statement regarding count(1). Here is some data which i tried to test for both count(.) and count(1):- create table testtable (a number, b number); insert into testtable values ( 1, 2); insert into testtable values (1,1); insert into testtable values (2,1); insert into testtable values (3,1); insert into testtable values (4,2); insert into testtable values (5,0); insert into testtable values (6,1); Insert into testtable values (NULL,NULL); COMMIT; SQL SELECT COUNT(1) FROM TESTTABLE; COUNT(1) - 8 SQL SELECT COUNT(.) FROM TESTTABLE; COUNT(.) - 8 Both queries are yielding the same result. I could not understand what you meant?

Can you please elaborate further? Followup April 14, 2009 - 2:11 pm UTC did you want to a) count the number of times the expression '1' evaluated to be not null in testtable OR b) count the number of rows in testtable what were you trying to do? Because a) is done by select count(1) from testtable whereas b) is accomplished by select count(.) from testtable.

Why would you want to count the number of times the expression '1' is not null? That doesn't seem to make any sense? Now, sometimes counting the number of rows in a set makes sense - and thankfully they gave us a way to do that - via select count(.) I don't care if they return the same answer - one of them says you want to do one thing, the other says you want to do another thing.

You probably always mean to do (b) (as I cannot see the business case for counting non-null evaluations of the expression '1' - can you?) But if you phrase it as (a) - with count(1), it just looks like you might not have read a SQL manual. Yes, being a bit harsh, but as I said - this count(1) is a pet peeve of mine.

Did you know we had a bug filed in older releases of Oracle whereby the kernel group had to rewrite internally 'select count(1)' as 'select count(.)' to avoid the performance penalty, ugh. Hello Tom, I am a little confused about when to use scalar sub-queries. I executed the example from your book 'Effective Oracle by Design' and this is what I get. Oracle version is Oracle Database 10g Express Edition Release 10.2.0.1.0: SQL; 1 select username, userid, created, 2 (select count(.) from allconstraints where owner = allusers.username) cons, 3 (select count(.) from alltables where owner=username) tables 4. from allusers SQL / 18 rows selected. Followup April 05, 2010 - 10:36 am UTC my examples were all about initial response time versus total query throughput.

If you want the first row as fast as possible - scalar subqueries might benefit you. If you are going for ALL ROWS, then big bulk operations are. You are getting all rows, and want to optimize for all rows, therefore, scalar subqueries probably do not make any sense for you. I have examples such as 'paging through a result set' where I get the 10 or 25 rows of interest from some base table and then apply the scalar subquery to just those 10 or 25 rows - preventing us from having to join to all of those tables while trying to FIND the 10 or 25 rows of interest.

I encourage you to re-read that section. What I tried to outline was 'how they work'. If you get that - you should be able to close your eyes, imagine the work the server would do - and have a reasonable guess beforehand as to which would make more sense. Hi Tom, When you have a query with numerous inline views, each doing 'select (.) from x where y', it is usually considered pretty bad form.

The norm (and the way I learned it) seems to be to only select the columns you actually need, so fair enough. This can help with not needing to change the code later if the table changes for a column you don't care about. But doesn't optimizer look at it and pick out only the columns that are being used anyway (as opposed to reading all the columns including those not used in the query)?

If so, there shouldn't be negative performance implications. But if so, why is it considered to be so bad across the board? Followup September 26, 2012 - 12:04 pm UTC This can help with not needing to change the code later if the table changes for a column you don't care about. That is the least of the reasons. The most important reasons are: a) documentation, you get what you need and nothing more, if you are selecting it, there must be a reason for it b) performance, if you select.

and don't need., you are making it harder for the optimizer to optimize. Select just what you want and you may well find you get a much better plan! But doesn't optimizer look at it and pick out only the columns that are being used anyway it depends - it depends on what can and cannot be merged. Usually it can - but you cannot count on it in all cases. And it depends on the final select list too.

It is best to select what you need.