April 30, 2009 at 2:32 pm
I could create what's called a nested table (a one-dimensional collection) and use that in an inner-join where the inventory Coil Id equals the nested table's coil id, but Oracle won't allow me to use an inner join on an Update statement. I haven't completely given up on this method, though, since I might be able to trick Oracle into doing the same inside of a where clause.
Oracle can perform an UPDATE using a join condition. True, the syntax is a bit different than SQL Server, but it does work. It has been around since Oracle5 (~1985).
Sample code snipit:
UPDATE osolUsers
SET (firstName,lastName,username,Title,siteid,activeTil) =
(SELECT firstName,lastName,username,Title,siteID,activeTil
FROM osolTempUsers
WHERE osolUsers.userID = osoltempusers.userID )
WHERE EXISTS
(SELECT NULL FROM osoltempusers
WHERE osolusers.userid = osoltempusers.userid)
;
But it seems like I should be able to include the results of the parsing directly within an update statement. Of course, my head keeps telling me that to make that work, the parsing routine (which contains a select statement), would have to be the object of the "IN" predicate, and I keep thinking that an IN predicate is rather a lot like RBAR, not so?
Actually not. Don't worry about large IN lists in Oracle. I've had subqueries returning thousands - 10s of thousands of rows fed into an IN list. The Oracle optimizer is smart enough to construct an underlying intermediate table and perform the "appropriate" join logic. I.e., it will only process dups in the IN list once. I performance benchmarked this against join logic under Oracle 8i (8.1) over a decade ago. Virtually no difference in performance.
April 30, 2009 at 2:59 pm
That would be the ANSI standard for UPDATE... subqueries for each field(s) being updated. If you do such a thing in SQL Server, the duration of the update increases by 100% for each correlated subquery you add and, unlike Oracle, you can't update multiple columns with the same subquery. I was very happy when Oracle came out with MERGE because the UPDATE portion of MERGE can use a FROM clause... like SQL Server.
Heh... sure makes life interesting if you have to work with both.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2009 at 3:25 pm
JohnG (4/30/2009)
...To return a record set from Oracle to ADO.Net you use "REF" cursors. The method that SQL Server uses (firehose output) is not ANSI compliant. ...
Could you elaborate on this John? I ask because it does not seem to jibe with several things that I thought that I knew.
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2009 at 3:37 pm
Yea. "Interesting" like in the chinese curse "May you live in interesting times." :w00t:
April 30, 2009 at 3:49 pm
I did figure out how to do the Update with the join in the where clause. But the nested table isn't a true table, it's more like an unbound array. I couldn't join with it. It was looking bad when I finally ran some tests on the IN clause, which, as you say, Oracle's optimizer rocked right through it.
I've also taken a few minutes to see what the differences are in my vb code, too, between setting up the Oracle versus SQL Server DataGridView for displaying the data, and they may not be as great as I first thought. The SYS_REFCURSOR appears to behave differently, but if I were to manually create a strongly-typed DataSet, I think I could mitigate those differences. Of course, I'd loose the wizard in Visual Studio. Alternatively, I haven't played at all with the OTN, and it likely has a wizard of its own.
April 30, 2009 at 5:05 pm
JohnG (4/30/2009)
Actually not. Don't worry about large IN lists in Oracle. I've had subqueries returning thousands - 10s of thousands of rows fed into an IN list. The Oracle optimizer is smart enough to construct an underlying intermediate table and perform the "appropriate" join logic. I.e., it will only process dups in the IN list once. I performance benchmarked this against join logic under Oracle 8i (8.1) over a decade ago. Virtually no difference in performance.
That also holds true with current versions (dunno about the old ones anymore) of SQL Server. SQL Server treats them as a normal inner join unless something strange has been done.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 1, 2009 at 7:48 am
RBarryYoung (4/30/2009)
JohnG (4/30/2009)
...To return a record set from Oracle to ADO.Net you use "REF" cursors. The method that SQL Server uses (firehose output) is not ANSI compliant. ...Could you elaborate on this John? I ask because it does not seem to jibe with several things that I thought that I knew.
Thanks,
When SQL Server introduced server-side (i.e., firehose) result sets from stored procedures back in the mid-late 90s (6.0?, 6.5?) Oracle was requested to develop the same -- i.e., return result sets from stored procedures.
The statement made by Oracle at the time was that the server-side firehose cursors were not ANSI compliant as the consumer of the cursor (i.e., the client) did not have control over the cursor. I.e., it did not open it nor close it as well as the client was required to consume the entire result set. I.e., it could not close the cursor (result set) after consuming "n" rows.
So REF (meaning "reference") cursors, or cursor output variables as they are generically called, came to be. The REF cursor, as an output variable (parameter), is passed back to the consumer. The consumer then performs the open, fetch, and close of the cursor. These REF cursors can be referenced by stored procedures, functions, etc. within the database engine as well as client applications (ADO, .Net, etc.). I've successfully called Oracle stored procedures returning a REF cursor (server-side result set) from VBScript using the Oracle OLE-DB provider. It was an ASP page web application. This help prevent SQL injection and provided security.
In SQL Server have you ever wanted to conditionally consume (e.g., loop through) the result set returned by a stored procedure? For example, the output returned by the numerous system stored procedures? You are forced to create a temporary table and fill it with the entire result set first, then work with the contents of the table.
SQL Server has started down this path, albeit with major limitations. See Using the CURSOR Data Type in the BOL http://technet.microsoft.com/en-us/library/ms175498(SQL.90).aspx A few system stored procedures provide this. See sp_help_fulltext_tables and sp_help_fulltext_tables_cursor.
http://technet.microsoft.com/en-us/library/ms186929(SQL.90).aspx and http://technet.microsoft.com/en-us/library/ms174432(SQL.90).aspx
Unfortunately, unlike Oracle, the cursor data type is not supported by any client consumer (OLE-DB, .Net, etc.). It can only be used by T-SQL (i.e., within stored procedures, etc.)
May 1, 2009 at 10:31 am
If you do create a TALLY table, for whatever purpose, in Oracle my recommendations are:
1. Create it as an Indexed Organized Table (IOT) using the ORGANIZATION INDEX clause. This is equivalent to a CLUSTERED INDEX in SQL Server.
2. It should have zero free space so set PCTFREE to 0.
3. I would create it under a single "common" user (schema owner) with a PUBLIC synonym on it. That way, you don't have to create multiple copies of it for each application (schema owner).
May 1, 2009 at 10:53 am
JohnG (5/1/2009)
If you do create a TALLY table, for whatever purpose, in Oracle my recommendations are:1. Create it as an Indexed Organized Table (IOT) using the ORGANIZATION INDEX clause. This is equivalent to a CLUSTERED INDEX in SQL Server.
2. It should have zero free space so set PCTFREE to 0.
3. I would create it under a single "common" user (schema owner) with a PUBLIC synonym on it. That way, you don't have to create multiple copies of it for each application (schema owner).
Perfect... those are the same basic recommendations I make for SQL Server, as well.
I'd still really like to see how folks to splits in Oracle. I know that it really shouldn't be done in the DB but, still, do to bad design or whatever, people end up having to do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2009 at 1:39 pm
Thanks for the advice. I did the first two, but the last item isn't up to me. I can create it however I want on my local (development) system, but when it goes into production at the customer's site, it's up to them. (Of course, I'll recommend that they move the table.) For that matter, if they decide they'd rather rewrite the stored procedure, they're welcome to do exactly that, so long as they don't ask us to support it (or try to sue our company because it caused something to break as a result of their change).
Designing the stored procedure that returns a SYSTEM_REF cursor isn't the real problem, as much as the fact that I have two procedures, one in PL/SQL and the other in T-SQL with different numbers of parameters (which must be declared in my executable code). As I said before, I had hoped to isolate differences within the stored procedures, but that was a naive hope of which I have become quickly disabused. If I can make use of the Oracle Developer Tools to write automated chunks of software, this will become less of an issue, but I won't have time to play with that on this project. Perhaps the next one will afford a little more time to try things out -- it's Oracle also.
May 4, 2009 at 2:33 am
JohnG (5/1/2009)In SQL Server have you ever wanted to conditionally consume (e.g., loop through) the result set returned by a stored procedure? For example, the output returned by the numerous system stored procedures? You are forced to create a temporary table and fill it with the entire result set first, then work with the contents of the table.
I am probably missing your crucial point, but I have used stored procedures in the past that returned multiple row sets. I could perfectly consume those using plain old ADO doing a simple NextRecordSet() or GetNextRecordSet() call on the active record set. It returns a reference to the next record set obviously and is painless in its use. No deliberate cursors needed, just fetch fetch fetch to go over all the results, be it multiple result sets or just one.
May 4, 2009 at 5:34 am
I missed that, Peter. Thanks for pointing it out.
I agree... there is no need to fill a temp table in SQL Server to read a result set. Of course, it does help if you avoid cursors because they return more than one result set but, as Peter pointed out, even that can be done quite easily.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2009 at 6:47 am
peter (5/4/2009)
JohnG (5/1/2009)In SQL Server have you ever wanted to conditionally consume (e.g., loop through) the result set returned by a stored procedure? For example, the output returned by the numerous system stored procedures? You are forced to create a temporary table and fill it with the entire result set first, then work with the contents of the table.
I am probably missing your crucial point, but I have used stored procedures in the past that returned multiple row sets. I could perfectly consume those using plain old ADO doing a simple NextRecordSet() or GetNextRecordSet() call on the active record set. It returns a reference to the next record set obviously and is painless in its use. No deliberate cursors needed, just fetch fetch fetch to go over all the results, be it multiple result sets or just one.
True, from an ADO client application. However, It doesn't work from T-SQL. I.e., You cannot consume a result set returned by a stored procedure within T-SQL except through a CURSOR output parameter. That is why Microsoft has been providing two different flavors of some of the system stored procedures.
May 4, 2009 at 8:03 am
Jeff Moden (4/29/2009)
JohnG (4/29/2009)
Should you wish to parse a string into an Oracle array, there is no real need for the tally table. It will work quite well with a PL/SQL function using the array datatype and loop logic.I'd love to see that.
Why not post it here? I'm sure folks would appreciate it. Something that would parse a "standard" 8k string would be really cool.
To satisfy Jeff's curiosity I have attached a ZIP package containing the components of a delimited string to table parser for Oracle. The package contains a number of files that can be run through SQL*Plus at a command prompt. The reader is strongly encouraged to refer to the copious Oracle documentation to understand the commands and/or techniques that I've used.
Package contents:
1. A script to create the types that will be used later.
Note on the type: I could just return a scalar array (table) of integers. However, I expanded it to a two-dimensional array containing both the value and a "row number" whcih contains the ordinal position of the entry in the list. It was sometimes used by our application. Since we're working with an array and have an index position as part of the parsing logic, it is a cheap, but very useful item to have.
2. A script to create the "utility" package and package body.
This contains the parsing logic.
Note the use of the CLOB datatype which allows for longer strings. CLOB is used as numbers contain only ASCII digits. If this was parsing and returning strings, NCLOB would be used.
3. A test script that exercises the functionality.
For the number values in the delimited string I used 1,000 randomly generated numbers and not the typical (incorrect) test data of 1,2,3,... The resulting string is over 10,000 characters.
The script also shows use of the "row number" column by listing the values in descending position order. It also shows use of a REF cursor with SQL*Plus.
Final note: Oracle also provides the capability to compile PL/SQL into run-time object code to further improve performance.
May 4, 2009 at 8:22 am
Hearing the differences between Oracle and SQL Server, although interesting, is getting a bit old.
Let's remember one crucial thing SQL Server Oracle.
Let's stop complaining about the differences and just learn to use the tools we have available. They are both different products, and there really is no reason to try and make them the same.
Viewing 15 posts - 226 through 240 (of 511 total)
You must be logged in to reply to this topic. Login to reply