Picking up Oracle development skills

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Working with Oracle » Picking up Oracle development skills

23 posts, Page 2 of 3

««1 2 3 »»

Picking up Oracle development skills

Rate Topic

Display Mode

Topic Options

Author

Message

Sam Peascod

Posted Monday, February 09, 2009 4:09 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 14, 2010 3:02 AM
Points: 163, Visits: 171

Thanks for all the help people, keep it coming!

:)

Post #653304

PaulB-TheOneAndOnly

Posted Tuesday, February 10, 2009 4:07 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:22 PM
Points: 3,017, Visits: 4,471

Bruce W Cassidy (2/9/2009)Try creating an index on a bit field in SQL Server 2005. Yes, it works.

Unfortunatelly that's not the concept of an Oracle bitmap index :D

Oracle bitmap indexes are built to cooperate with each other allowing you to identify the desired rows using several criterias -bitmap indexes - at once. Each index is represented as a bitmap and boolean math allows to cross reference several of them to identify what you are looking for. ;)

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #653551

Bruce W Cassidy

Posted Tuesday, February 10, 2009 12:16 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, March 22, 2013 11:49 AM
Points: 945, Visits: 998

PaulB (2/10/2009)

Bruce W Cassidy (2/9/2009)Try creating an index on a bit field in SQL Server 2005. Yes, it works.

Unfortunatelly that's not the concept of an Oracle bitmap index :D

Oh, this I know. An index of a bit (or even multiple bits) does not a bitmap index make.

Actually, the form of index I miss most from Oracle is being able to index expressions. You can sort of do this in SQL Server by creating a derived column, and then indexing the column. In both cases there are limitations to what can be indexed and what can't. But the Oracle form is very handy at times.

Post #654097

gints.plivna

Posted Wednesday, February 11, 2009 12:42 PM

Valued Member

Group: General Forum Members
Last Login: Monday, October 08, 2012 2:57 AM
Points: 66, Visits: 433

I'd like to add my favourite SQL Server vs Oracle link.

Gints Plivna
http://www.gplivna.eu

Post #655170

Sam Peascod

Posted Wednesday, February 11, 2009 8:49 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 14, 2010 3:02 AM
Points: 163, Visits: 171

Brilliant, thanks Gints! That's exactly what I'm after!

Sam

Post #655371

jgrubb

Posted Thursday, February 12, 2009 7:42 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:39 AM
Points: 277, Visits: 502

Sam Peascod (2/11/2009)

Brilliant, thanks Gints! That's exactly what I'm after!

Sam

Wow. That page is wildly wrong in many cases. It's written by an Oracle User group, and really doesn't know all SQL Servers capabilities, and is wrong about some.

Take with a grain of salt. It's a good list of oracle capabilities, but it whiffs on SQL equivalents.

Post #655707

gints.plivna

Posted Thursday, February 12, 2009 8:01 AM

Valued Member

Group: General Forum Members
Last Login: Monday, October 08, 2012 2:57 AM
Points: 66, Visits: 433

jgrubb (2/12/2009)
Wow. That page is wildly wrong in many cases. It's written by an Oracle User group, and really doesn't know all SQL Servers capabilities, and is wrong about some.

Take with a grain of salt. It's a good list of oracle capabilities, but it whiffs on SQL equivalents.

It would be nice if you could list at least some of these "many wrong cases". I suspect this might be possible, yes, because it really was written by more Oracle oiented person. But in fact I've never seen any comparison completely fair for both involved sides

Gints Plivna
http://www.gplivna.eu

Post #655732

jgrubb

Posted Thursday, February 12, 2009 9:38 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:39 AM
Points: 277, Visits: 502

gints.plivna (2/12/2009)

How about SQL 2k8 doesn't support table compression? Nvarchar limited to 8K? 0 XML handling functions? Too many to go on about.;

Post #655867

Bruce W Cassidy

Posted Thursday, February 12, 2009 1:04 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, March 22, 2013 11:49 AM
Points: 945, Visits: 998

Okay, I could spend more time on this and reference Books Online, but I'm just going to give it a quick once over and list anything I have queries about.

Are extents always 64k? I thought this could be changed (not that I have ever tried to do so.)
Tempfile in Oracle is kinda equivalent to tempdb. Kinda.
Default tablespace in Oracle is kinda equivalent to [PRIMARY].
RAC: you can do similar sorts of things with federated servers.
SQL*Loader: bcp/SSIS. bcp is less capable than SQL*Loader, SSIS is far more capable.
Oracle Clusters: this is some stuff that's been hanging around in Oracle since at least version 6. I don't know of anyone who uses them. They just make life very complicated. It's a way of merging the storage of overlapping tables. When disc space was a premium, it made sense. These days, use data compression.
Validate/NoValidate: I think there's a SQL Server equivalent to this when you create a constraint to get it to check the data?
REF: why do we need to create hierarchical data stores in a relational database? Grrr. Anyway, you could possibly do some of this with a SQLVariant.
Dimension: Oracle has a chunk of data warehousing stuff built into the RDBMS. For SQL Server, you need to step outside into Analysis Services to get the equivalent.
Parameters: OUTPUT parameters in SQL Server are the equvalent to IN OUT in Oracle.
By default, indexes in both Oracle and SQL Server are B*Tree. You don't actually specify "B*Tree" in Oracle.
Compressed Indexes: is this available in SQL 2008? I know data compression is.
They haven't shown anything for SQL Server's INCLUDE() as part of an index.
Nothing about XML indexes (which I believe you can do on both platforms.)
For Reverse, they are referring to Oracle's reverse key indexes, where it reverses the order of the bytes in the key before storing them. This reduces index hot-spots and contention in high TP scenarios. Mostly useful for non-clustered indexes. Oracle and SQL Server have very different space allocation mechanisms, so this really isn't as much of an issue with SQL Server.
Libraries: there's still (I believe) the concept of a library (DLL) within SQL Server, as distinct from an assembly. Not that I would recommend using them!
AUTHID: I believe this has some similarity to execute as.
Check constraints are deprecated in SQL Server? Who knew!
Identity vs Sequence: oh yes, we could have some arguments over this one! Having used both, I prefer identity. It's easy enough to build a table to duplicate Sequences if you really need them.
Public Synonyms: this just creates a synonym in the "public" schema. In SQL Server, just create your synonym then grant rights to public.
Compressed Table: some new stuff with SQL 2008 here (the article IS comparing to SQL 2005).
Function Based Index: it says the equivalent in SQL Server is Computed Column, but that's not quite right. You need to create an index over the computed column to get the same effect.
External Table: I'd say OPENROWSET() is an equivalent.
Global Temporary Table (on commit delete rows): #table is mostly equivalent.
Global Temporary Table (on commit preserve rows): ##table is mostly equivalent.
XML Table: in SQL Server, equivalent would be a table with an XML column.
PCTUSED, PCTFREE, INITRANS, MAXTRANS: heh. Hangovers from an older Oracle. Not even Oracle uses these (much) any more.
Types: Oracle types are far more capable than SQL Server types, unless you are willing to step outside and into .net assembly-based types. In which case, you can have all of the equivalents to Oracle's types.
Varray, a table variable is similar.
SQL Server has ! ? Who knew...
Date operators: er, dateadd(), datediff()? They're not operators, they are functions. You can use + and - with dates too (if you specifically want operators).

Should I keep going?

Post #656041

jgrubb

Posted Thursday, February 12, 2009 3:10 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:39 AM
Points: 277, Visits: 502

Please stop. The differences are legion. The page in question was written from an Oracle perception of SQL 2k8, and not an informed one. Usually, comparisons of DB platforms are done by people with a bias, intentional or not. This is not a surprise. DB's are now such beasts very few would ever get past the surface of more than one. I've been one of those, and bear the scars.

As I said earlier, the details of the differences are too much for this forum. The biggest thing to remember is that for capability, there isn't a huge difference, and that it is more perception than reality.

When it comes to migrating between the platforms, tables can be translated, and the CRUD statements are fairly similar. Just need to map various functions for aggregates and such. Complex SQL is a different story

When you get into code (PL/SQL vs Transact SQL), there's little that's portable. Completely different approach.

Post #656153

« Prev Topic | Next Topic »

23 posts, Page 2 of 3

««1 2 3 »»

Permissions