Data WareHouse

Question

Post reply

Data WareHouse

Farnaz Safaee Nik

SSC Journeyman

Points: 75
More actions
March 25, 2012 at 10:20 am

#391868

Hello
For The data warehouse oracle is better or sql ?
Best Regards

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

farnaz224 (3/25/2012)
Hello
For The data warehouse oracle is better or sql ?
Best Regards

"It Depends". What do you actually mean by "better"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Farnaz Safaee Nik SSC Journeyman Points: 75 More actions · Answer 2

My mean is that i want to set up data warehouse. i want to set up data ware house in sql or oracle.now,my data are store in sql but it is very big(my data is too much) for example i want to have good speed when i take report from data ware house or i want to use from data warehouse for data mining, which is the best? oracle or sql.

for example for speed, oracle is better or sql or it is depend to design of data warehouse and index and hard ware?

another example for data mining, which is the best? oracle is better or sql?

best regards

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

If you have someone really good in either, it'll be a virtual tie for performance. Both products are capable of "enterprise class" size databases and performance.

For "average" developers and having used both, I think that SQL Server is a lot easier for someone with average knowledge of the product to write some decent high performance code. For example, you don't have to know how to write a reference cursor to return PL/SQL results to a GUI (including the programming interfaces available) in SQL Server.

From both an initial cost and total cost of ownership point of view, I think that SQL Server comes with more tools out of the box than Oracle does. A lot of times, you'll need to buy "add ons" (for lack of a better term) to get Oracle to do what you want. For some reason, Oracle DBAs seem to command a better salary than do most SQL Server DBAs although that gap is quickly becoming narrower.

You will hear tales that Oracle performs better with procedural code (cursors/loops) than SQL Server does. My personal opinion is that although they've made it a bit easier to use procedural code in Oracle, it has the very same performance gap between cursors/loops and setbased code that SQL Server has. The use of "RBAR" (see my signature line below for the definition) in either is usually diametrically opposite of any hopes of real performance. I'll also say (again, a personal opinion from having to work with it) that it's actually a bit more difficult in Oracle to write decent set based code.

There's also a fairly large paradigm shift between the two products. Just because someone can write good code in one doesn't mean they can write good code in the other even though, technically speaking, they both use the "SQL" language.

Personally speaking, I'll take SQL Server over Oracle any day.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

P.S. I also think that there's a whole lot more "community" support for SQL Server than there is for Oracle. Again, it's just my opinion but I believe there are more forums for SQL Server and there's a lot more free training available in the form of SQL Saturdays and a bazillion easy Google hits on good blocks and articles for nearly any SQL Server problem you may wish to explore. I didn't find the same level of community spirit in the world of Oracle when I was working with it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Martin Schoombee SSCoach Points: 19126 More actions · Answer 5

+1 on all Jeff's points above. Most people in this forum would lean towards SQL Server, and for good and valid reasons as Jeff has mentioned.

To give another perspective, you should also (and maybe firstly) consider what you are planning to use for a front-end reporting tool(s). If you plan on using Reporting Services and/or Excel, then SQL Server would almost be a logical choice because of the fact that the different Microsoft tools integrate pretty well with each other.

In my opinion, the development cycle or amount of time it will take to get something out to your end users are also a bit shorter on the MS side (as opposed to Oracle). The reason again being better integration of tools and ease of use.

From a TCO (total cost of ownership) point of view, I believe that Oracle would be a much more expensive choice as you would have to purchase ETL and reporting tools separately.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Jeff Moden (3/25/2012)
P.S. I also think that there's a whole lot more "community" support for SQL Server than there is for Oracle. Again, it's just my opinion but I believe there are more forums for SQL Server and there's a lot more free training available in the form of SQL Saturdays and a bazillion easy Google hits on good blocks and articles for nearly any SQL Server problem you may wish to explore. I didn't find the same level of community spirit in the world of Oracle when I was working with it.

+1 On the community. Having worked with Oracle for a short time (1 year), I could not find the kind of community support in Oracle as I have found with SQL Server.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 7

Farnaz Safaee Nik (3/25/2012)
My mean is that i want to set up data warehouse. i want to set up data ware house in sql or oracle.now,my data are store in sql but it is very big(my data is too much) for example i want to have good speed when i take report from data ware house or i want to use from data warehouse for data mining, which is the best? oracle or sql.
for example for speed, oracle is better or sql or it is depend to design of data warehouse and index and hard ware?
another example for data mining, which is the best? oracle is better or sql?

Could you define what you mean with "data is too much"? do you have more than 10 Terabytes?

For a small size Data Warehouse, anything around 1 Terabyte any RDBMS will perform just fine, not just SQL Server and Oracle.

If you have large Data Warehouse e.g. 10 Terabytes you may want to take advantage of some built in functions Oracle has to offer like Bitmap Indexes, Join-Bitmat Indexes, Star Transformation and Query Rewrite but... for this to work you have to have a really Senior Oracle DBA with proven experience as a Data Warehouse Architect - otherwise you are going to find yourself in a big, big hole.

For even larger Data Warehouses you may want to consider other RDBMS providers like Teradata as well.

Ask yourself these questions...

- Do I have a small, large or very large DWH? 1TB, 10TB, 10+TB

- Do I have the budget to buy the hardware and software needed to support it?

- Do I have the expertise in house to use this or that other RDBMS?

Hope this puts things on perspective.

_____________________________________
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.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 8

I'd really love to hear someone's opinions on why the Oracle community is somewhat difficult to find. Very different from the SQL Server community which has important events like the SQL Saturday as mentioned, Simple-Talk, SQLServerCentral, etc.

I feel like there's no centralized community for Oracle articles, blogs, forums, etc.

Maybe there is a place we could all go to and I'm unaware of but it's really hard to find (at least for me). You can certainly find some information here and there but that's not really "community".

I wonder why it is so. :unsure:

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 9

codebyo (3/25/2012)
I'd really love to hear someone's opinions on why the Oracle community is somewhat difficult to find. Very different from the SQL Server community which has important events like the SQL Saturday as mentioned, Simple-Talk, SQLServerCentral, etc.
I feel like there's no centralized community for Oracle articles, blogs, forums, etc.
Maybe there is a place we could all go to and I'm unaware of but it's really hard to find (at least for me). You can certainly find some information here and there but that's not really "community".
I wonder why it is so. :unsure:

Probably you are not looking at the right places, there is a vibrant community indeed - one entry point would be http://www.oracle.com/technetwork/index.html - the famous Oracle Technology Network. 😉

_____________________________________
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.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 10

PaulB-TheOneAndOnly (3/26/2012)
Probably you are not looking at the right places, there is a vibrant community indeed - one entry point would be http://www.oracle.com/technetwork/index.html - the famous Oracle Technology Network. 😉

Thanks a lot! That will really help me.

I always found the Oracle website really hard to browse but it seems the website is looking good. Was it always like that?

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

PaulB-TheOneAndOnly (3/26/2012)
codebyo (3/25/2012)
I'd really love to hear someone's opinions on why the Oracle community is somewhat difficult to find. Very different from the SQL Server community which has important events like the SQL Saturday as mentioned, Simple-Talk, SQLServerCentral, etc.
I feel like there's no centralized community for Oracle articles, blogs, forums, etc.
Maybe there is a place we could all go to and I'm unaware of but it's really hard to find (at least for me). You can certainly find some information here and there but that's not really "community".
I wonder why it is so. :unsure:
Probably you are not looking at the right places, there is a vibrant community indeed - one entry point would be http://www.oracle.com/technetwork/index.html - the famous Oracle Technology Network. 😉

YMMV. I never seemed to get any help.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 12

codebyo (3/26/2012)
PaulB-TheOneAndOnly (3/26/2012)
Probably you are not looking at the right places, there is a vibrant community indeed - one entry point would be http://www.oracle.com/technetwork/index.html - the famous Oracle Technology Network. 😉
Thanks a lot! That will really help me.
I always found the Oracle website really hard to browse but it seems the website is looking good. Was it always like that?

it all depends on the definition of "always" 🙂 it has been pretty much the same for a number of years now.

Also, take into consideration that even when Oracle documentation is public and free; Oracle DBA's are used to check "Metalink" before going to any other resource, Metalink is Oracle's knowledge base where you have access if you have a valid product license.

_____________________________________
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.