hiRecently one of our client wants to make a selection between teradata and oracle 10g. I our evaluation tells me that oracle 10g is far better than Teradata. But it may be because there are so many persons working on oracle so will anybody here guide me
Oracle would survive only if your client was looking for purely OLTP based systems. If it's for a DW platform, the obvious choice is an MPP based RDBMS system, of which Teradata is THE leader.I am not sure what kind of "Evaluation" was performed... load times ? complex queries ? User concurrence ? ... was scalability accounted for ?Was there a bench marking done on both the systems ? (This is something normally the sales teams could be asked to do)
For more info:Software: http://mediaproducts.gartner.com/reprints/ncr/vol3/article2/article2.htmlHardware: http://mediaproducts.gartner.com/reprints/ncr/vol3/article1/article1.html
My client just moved from Oracle to Teradata. The performance is just amazing for Huge data. Teradata is excellent to handle HUGE data. You will defenitely find it difficult to get used to Teradata if you are used to Oracle.Well, as someone has already updated, it depends on the requirements.
Both the database has there advantages & disadvantages.There are a lot of factors to be taken into consideration before deciding which database is better.If you are talking about OLTP systems then Oracle is far better than Teradata.Oracle is more flexible in terms of programming like u can write Packages,procedures,functions .Teradata is useful if you want to generate reports on a very huge database.But the recent versions of Oracle like 10g is quite good & contains a lot of features to support DataWareHouse.
Teradata is a MPP System which really can process the complex queries very fastly..Another advantage is the uniform distribution of data through the Unique primary indexes with out any overhead. Recently we had an evaluation with experts from both Oracle and Teradata for OLAP system,and they were really impressed with the performance of Teradata over Oracle.
hiOracle support MPP in form of grid computing. uniform distribution of data based on primary key will not be much useful when accessing huge amount of data a full scan is required. so far we found teradata almost equal in performance with oracle 10g. Based on bench mark and after consulting from different people we find following problems in Teradata.its too expensive. you need long pockets to work with teradata.it has only one type of index while oracle has many types of indexes especially there bitmap index.teradata does not have materialize view. oracle has materialize view which decrease the IO band width and makes system more scalable.Oracle has very wide variety of analytic functions for Sql.3 types of partitioning and in oracle 11g there are some new addition in partitioningthe ability to use clusters without having to statically partition dataFurther..... these are the remarks i found on some of oracle discussion formsthe largest databases in the world run on Oraclehttp://biz.yahoo.com/prnews/031114/sff029_1.htmlthey counta) all disk on the computer, not just database diskb) the sum of all databases a customer is using -- not individual databasesBut still we saw that best database is the one which you have technical resource to work and especially tune.
nicky,Your assertions are not quite right :its too expensive. you need long pockets to work with teradata.==> this is not a technical issue so I won't answer (though it's wrth considering this point)it has only one type of index while oracle has many types of indexes especially there bitmap index.==> TD has several types of indexes (primary index, unique secondary index, non unique secondary index, value ordered index, join index, hash index...)teradata does not have materialize view. oracle has materialize view which decrease the IO band width and makes system more scalable.==> TD does have materialized views (view + join index on the view)Oracle has very wide variety of analytic functions for Sql.==> TD too and they're all ANSI compliant3 types of partitioning and in oracle 11g there are some new addition in partitioning==> horizontal partitionning is possible in TD (since V2R5) and vertical partitionning is part of the design of TD.the largest databases in the world run on Oracle==> fair enough but in this case size does not matter... it's the ability to manipulate a large amount of data that maters.
I wouldn't concede the size argument. Walmart claims to have the largest data warehouse in the world and they run on Teradata.http://www.eweek.com/article2/0,1895,1675960,00.aspI bet there are also big data warehouses at Teradata customers AT&T, Vodaphone, T-Mobile and USPS.In the WinterCorp Top 10 survey of the largest DSS databases of 2003 Teradata had 4 in the top 10, in the same survey in 2005 they had none and Oracle had 4. It seems like Teradata opted out of the survey as they had several customers who could easily have made the list. Most of these database size lists are unreliable. Not many of them mention the MySQL databases powering YouTube, Flickr and Facebook.
hinice and really productive discussion.Teradata has only one type of index i.e hash index. join index and its types are just different form of materialize view in oracle and even these indexes are based on hashing here is an extract from teradata documentationsvirtually all Teradata indexes are based on (or partially based on) row hash values rather than table column values. I think hash index can be quiet quick under MPP a plus point of teradata.teradata join index and its types are like oracle materialized view but in a much much restrict form. I have failed to find any documentation which says JI support rollup as oracle materialized view does so technically they are not equal by anyway.The partitioning faculity Teradata offer is by no mean equal to oracle. Teradata is based on providing specialized hardware to counter the huge IO's while oracle is based on how to remove these IO's. which makes oracle hard to configure and tuneAnother point which one of my colleague (teradata DBA) told me is that teradata is not very good from administration point of viewthanks
Hi Nicky,Not sure what you actually mean by "rollup" but there are Aggregate Join Indexes which can be used and it is completely Ansi SQL compliant.About "The partitioning faculity Teradata offer is by no mean equal to oracle" - Well, you will never have to and will never need to think about partitioning in Teradata because everything is parallel by default (data distribution, retrival, etc..).Please note that, Optimization criterion for Oracle and Teradata are different. Teradata is optimzed for DWHing. There is nothing you cannot do with Teradata which you can do with Oracle in terms of DWH. Besides, you can do all that much much faster. From a DBA standpoint, there are many things if a DBA wants can do. But usually they are not required to do it as per their job role. I would agree if with the fact that some aspects of DBA are not as easy as in Oracle. But life of DBA is much easier with Teradata.As stated before also by me, it might be a rough road to crossover to Teradata from Oracle background but the effort will be worth it. Try this and you will see the difference :Try to load 150 Million rows into an Oracle table - use any optimization strategy you want - just do a traight dump to make it simpler) Try doing the same in Teradata. ORTry doing a query with couple joins etc.. on a HUGE table (200 millions) selecting all rows in both environments. The difference will be obvious.
I once had a training session for folks who were porting an OLTP JDBC Application( with tons of RIs, triggers etc in cascaded fashion) from Oracle to TD, so there was the obvious complaints going on, as to how difficult TD was to use and it didn't give the performance.After executing a simple JDBC program which used the "right features of Tera JDBC", my average office latpop (which is infamous for the tones of open windows and other apps running ;-) ) configured with Demo DB, could load a million records in a minute. (I survived the rest of the days without hearing much complaints :-) )TD and Oracle are like Apples and Oranges, it's better not to be compared one to one...I would also vouch that for a Teradata DBA (who know's his stuff) life is heaven compared to an Oracle DBA (who know's his stuff). I can't name organisations due to confidentiality reasons, but some of the major telecos whom I have interacted with in the past didn't had more that 2 DBAs for their TD systems (and there was one teleco who just had 1 who couldn't recollect missing a weekend off due to a production issue call). And they all have tons of data.Most of the new TD guys come from Oracle background and has to do a lot of unlearning to do which puts them at cross with TD (They start by applying oracle logic), and similar in the experiences in the past tell me that, most of the "Oracle to TD porting" is sort of .... copy the DDLs, copy the Data, do changes for syntaxes and data types that does not work etc ...... very bad idea !! but whom do you blame ? the client doesn't want to spend more money on what looks unnecessary, the IT guys don't want to lose the contract by irking the client !. I have seen SQLs running for days in TD due to this (!)... Then you go in, do abracadabra on the physical design of some tables... and the queries are over in a few minutes. but by then, it's too late...last, but not the least, I wouldn't take those surveys without a grain of salt ;-)
thanks to all of you guys again.most of guys claims that teradata is much quicker than oracle in parallel processing. why? any technical reason.
Hi Amir,"most of guys claims that teradata is much quicker than oracle in parallel processing.why? any technical reason."Because Teradata is a parallel database system using a shared nothing approach from the very beginning, whereas Oracle just added some parallel options and even RAC is still using shared storage :-)Dieter
On top of all the issues mentioned here is the cost of resources (people). I can hire an Oracle developer with no problem. Teradata developers are few and far between, and the interviews are discouraging at best. Just trying to find someone who understands that a TD PI is for distribution can be frustrating.Teradata is "different" and the differences are annoying.1. date/time/timestamp handling is moronic at best. After 2 minutes with oracles to_date/to_char you can't imagine why teradata doesn't get it.2. skew (when the data is not properly balanced across the nodes)3. spool (when the optimizer decides it needs to copy loads of data from node to node and rehash it all) which is a big performance hit. Then there are the opportunities to exceed spool. No matter how much spool is assigned, you can always have sets that are just too big for spool.I'm not saying that all databases and all DW solutions don't have challenges, but we spend an enormous amount of time on issues other than just getting the data loaded. A lot more than I ever spent on an Oracle database. We're not building an autonomous robot to send to mars, just trying to load some data and build some reports.The join indexes, and covering JI's, are a great help, but not enough of a selling point.Where I am, a fortune 50 company, we have thousands of small to medium sized Oracle servers and a couple very expensive teradata servers. I have yet to see a comparison between comparatively priced (aka equivalently built out) servers. I would love to know what a 15 million dollar Oracle server would perform like. It would also be fun to look at a grid and see if they are introducing the same issues we suffer from on Teradata.
Hello,I don’t intent to say anything bad about any individual or any RDBMS .... no offense at all! :)I genuinely believe it all depends on the requirements & exactly how you plan to use the RDBMS. The parameters include (not limited to) data volume, data types, DBMS engine performance, SQL engine caliber, hardware requirements, software/hardware limitations, data crunching abilities .... etc. After defining all those parameters, you need to assign weights for all of them, and then it will all be clear .... or in some cases you may also have to do some benchmarking.As far as comparison between Teradata, Oracle and other RDBMSs is concerned one must first define the requirements very clearly (cannot emphasize enough on it! :)) to decide what to put on. And as far as migration of current implementation is concerned you surely cannot blame one RDBMS for storing DATE as DATE and TIMESTAMP as TIMESTAMP, because RDBMS X stores TIMESTAMP as DATE and RDBMS Y stores some creepy binary value in TIMESTAMP.Now lets talk about some facts .... Teradata's SQL engine is fair enough, why? because it is not as fancy as Oracle's SQL engine but at the same time it does what is required .... it works. That helps keep the SQL simple & straight-forward .... the decision of which one is better, again goes to your requirements & usage and your previous experience as well .... if you have been working with Oracle for long, you may say that "CREATE TABLE Table1 (Col1 INTEGER, Col2 INTEGER)" is much complex, tough and rigid than "CREATE TABLE Table1( Col1 INTEGER, Col2 INTEGER)TABLESPACE Table_Space_1PCTUSED 40PCTFREE 10INITRANS 1MAXTRANS 255STORAGE ( INITIAL 128M NEXT 128M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT )LOGGING NOCOMPRESS NOCACHENOPARALLELMONITORING;" but again, it depends! Do you really need that level of 'control' for couple trillion rows, or will you like to leave it to the RDBMS engine you have invested in?Next comes the fact, Teradata is built for DWH i.e. for HUGE DWH! Oracle is the best OLTP DBMS one can have. No DBMS can stand for long if you have an increase of couple TBs of data coming in daily and yet no increase in batch window, no matter how much money you throw in it .... because there is some point at which software’s/hardware’s limit will rise above and hinder your comfort.Next comes the parallelism, WOW! It always fascinates me! From storing a single bit, to retrieving couple GBs of data after joining many tables, its all parallel, can you just imagine that for once? And parallelism is not just a marketing hoax .... Teradata's architecture is completely based on it from start till very end .... including all of its utilities as well.Next comes the scalability, if you started with a 100 GB DWH volume and now you need more or you need your reports to be more faster regardless of the data volume increase, okay fine no problem it is linearly-scalable, you may add more nodes 'whenever' you desire .... and that also without the need to manual-rework (re-defining table-spaces, partitions, dropping & recreating of DB objects etc)! Now I believe you can't say this for every DBMS out there, can you? So, it again goes in the bag of .... what exactly do you desire? What exactly are your requirements?Lastly, every RDBMS has its own specific purpose, the questions arise when there is some area where two or more over-laps each other. The decision can be made out of what you are good at with current resources or what you can have in given resources! :)HTH!Regards,Adeel
-- If you are stuck at something .... consider it an opportunity to think anew.
Oracle and Teradata have their own unique distinct features. I have worked on both and certified in both.Being in datawarehousing,I feel the partition part Oracle is far ahead. Materialzed view oracle is far ahead. Functions, packages,procedures,triggers oracle has many options and they rule in OLTP. Now come to teradata utilities, mload,tpump,fastload,bteq,fastexport. Oracle has to come up a lot.I work on all these features. Oracle bulk load has to improve in many ways. I have many points to tell but time is a constraint.. :)
Heh... It seems there are not so many arguments against Oracle, does it? :)
Fully agree with joedsilva. Teradata is leader for parallel processing.Teradata is still the only database that loads data in parallel, backs-up data in parallel and processes data in parallel. The idea ofparallel processing gives Teradata the ability to have unlimited users, unlimited power, and unlimited scalability.Teradata allows for maximum flexibility in selecting and using data and it therefore canbe designed to represent a business and its practices.The Teradata founders settwo primary goals when they designed Teradata which were:• Perform parallel processing• Accommodate Terabytes of data
Each product has their strengths. OLTP and smaller data warehouses are places I would consider Oracle. Large data warehouses are definitely the focus for Teradata. User behavior also needs to be considered. If you will have a large community of end-users generating adhoc queries, you will need to take some precautions in a Teradata environment since they are able to impact overall system performance rather dramatically. These precautions could be as simple as educating them on query development or as drastic as isolating them in some fashion (environment or time of access). Since Oracle doesn't use MPP, it seems to hold up better against users experimenting with their queries.Hope that helps!john
I don't think share-everthing architecture is good for data warehosing, For EDW,may IBM DB2 or Teradata would better.
Hi,In simple one word I can say teradata better no need to compare with oracle I agree with you all the tools and databases have there own advantages and disadvantages,but comparatively teradata mechanism is faster then the oracle process,practically I feeled the difference we can execute massive queries and get the output and reduces the DBA work.coming to the oracle if the massive query is not fine tuned outmatically the system will get hang..on way restart of system.
BYNET is one of the reason differentiate teradata from other datawarehouses.
Its a simple issue here. Teradata is a hardware and software solution. It is also not ACID compliant based on the share nothing architecture. Oracle is ACID compliant and has a share everything architecture which restricts its I/O channels.
Now add Oracles new hardware solution (Exadata) and still remains ACID compliant and now make your argument. Its also still loads cheaper than Teradata. By the way it supports OLTP and DSS call it the database machine.
Let me clarify before someone jumps all over me....Teradata is ACID compliant, but has issues with Read Consistency on changes to data. The issue of dirty reads.
Not exactly. By default, transactions in Teradata are fully serializable with all the ACID properties. A read-uncommitted mode is available as an option for a query, but any transaction that updates a table is always prevented from seeing uncommitted changes to that table made by other transactions.
I have a SQL in Oracle ........
I need to change it into TD Specific query ;
SQL in Oracle :
when 'VALUEOF(NQ_SESSION.TENANT_ID)' is null
then 'None Assigned'
(select max(global1_curr_code) from w_global_curr_g
What would be the Respective TD sql for this query ..........???
I have worked on oracle and teradata and indeed the loading and squery response times are very quite diferent. this leaves me wandering how does it perform with these solutions as they are all met for the dwh: SQL Server 2008 R2 (datallegro), Netezza, greenPlum, vertica, paraccel which are all MPP. I belive DB2 is also MPP?
my understanding is that DB2 too comes as MPP but is meant for OLTPS. does this mean it can not fare well on DWH arena and why would it be the case. incendently SQL Server 2008 is very seriou about MPP . has any one perform some comparison on the two?
have this SQL query and I should convert it in a TERADATA query, can anybody help me?
SELECT Parent, Member_Name, Alias
WHERE ((SELECT COUNT(*) AS NRecord
WHERE (Member_Name = '71300000201')) = 0)
SELECT Parent, Member_Name, Alias
WHERE ((SELECT COUNT(*) AS NRecord
WHERE (Member_Name = '71300000201')) > 0) AND (Member_Name = '71300000201')
thanks in advance
there's no conversion neccessary, it will run as is, only "alias" is a reserved name in TD, so it must be put in double quotes.
Of course it might be better to split it into 2 separate queries and the client submit the second only if the first returned no rows.
And you might also simplify it to:
SELECT Parent, Member_Name, Alias
WHERE (Member_Name = '71300000201')
OR NOT EXISTS
WHERE (Member_Name = '71300000201')
I am translating an oracle query to Teradata:
It basically returns the value of ID and corresponding previous max value.
SELECT P.ID , (SELECT Max(Q.ID)
FROM TEST1 as Q
WHERE Q.ID < P.ID) as Prev_Max_ID
FROM TEST1 as P
IF TEST1 table contains the following data
Query Output in Oracle returns
ID1 - Prev_Max_ID
10 - null
20 - 10
30 - 20
In Teradata - It is throwing the following error message
3706: Syntax error: expected something between '(' and the 'SELECT' keyword.
Please can anyone help in correctly translating the query.
....I was wondering if Teradata has a response to the middle market offerings that Oracle (and others) have been driving. I did not see any mention of it on the website.
This is Suresh.How to copy the oracle table with data into Teradata database.It is possible please help for this one.
I am new to this forum but am really stumped on a Teradata vs. Oracle question. I hope this is the correct place to post. I have been running various versions of this query in Teradata SQL Assistant - reaching to a Teradata server. But now I need to use the same tool and reach to an Oracle server and I keep getting the error "SQL command not properly ended". Does anyone see what I am doing wrong in the code below? Please help if you can.
Select ((count (distinct MRN)*1.00)/denom.denomvar) as count_MRNs , ENC_TYP_CD, denom.denomvar
(Select count (distinct MRN) as denomvar
) as denom
Group by ENC_TYP_CD
A very good discussion and just want to add my little knowledge into it.
Teradata is different from other DMBS (like Oracle) because it uses logical addresses rather than physical addresses to store data. This allows teradata to perform (automatic) defragmentation/re-organiazation of data anytime without effecting the addresses.
It stores data on blocks, unlike other PAGE-BASED databases. These blocks are variable length that can grow and shrink based on data.
Moreover teradata is DBA-FRIENDLY because:
- It does automatic disk management
- It automaticly manage indexes (which are store in internal tables rather than B-tress or other structures)
- It does automatic defragmentation, garbage collection etc
- It uses automatic partitioning/distribution of data using hashing algos
I love reading this thread. It was really informative and productive discussion. I would really agree that it DBA-friendly.
My <a href="http://www.dream-trip-club.com/">Dream Trip</a> Planner
Hi, I have just signed up for a Free Trial version of www.smarter-crm.com – Does anyone have experience with it? It seems very easy to use and has a very nice look, and is less than half the price of all other hosted CRM systems. Please let me know if anyone has experience with Smarter CRM.
I'm new to Data Warehousing and a fresher. I've a plan to take an Oracle certification followed by a Teradata professional certification.
So, looking forward, whether I should take 'Oracle certified Associate' or 'Teradata certified Professional' certification first... and the value of Oracle Certifications in near future..
As a fresher you can go for either one of the certifications you have flashed above which eventually helps to boost your resume. But onething you should remember that all the SQL/PL SQL/T SQL concepts are same across for all the databases with few changes in their syntax. From my perspective view what I would suggest is you can pursue any certifications related to DB and make sure that you have captured the all the "basics" strongly, then going forward by gaining experiences it will be a cinch for you to deal/handle any databases.
Hi to all,
I am new to teradata, recently shfted to teradata.
can any one give the differnces between TERADATA and OTHER RELATIONAL DBMS. i am in bit confusion.
When comparing between Oracle and Teradata make sure you are comparing apple with another apple.
Teradata is an appliance database. Therefore, you need to compare it with Oracle’s Exadata which is another appliance and it is fairly new one. The main difference in my view is Oracle’s Exadata is hybrid architecture that combines OLTP as well as DS type applications and able to inserts, updates as well as reads same time. In addition Oracle architecture based on MVCC which is readers does not block writers. So, you can keep loading the data and reading it concurrently.
Teradata another hand is highly parallel system and scalable. But, a lot of customization required specially for primary indexes and it is hard to satisfy all types of loads and queries. You need to know what kind of load and query you are targeting.
First of all I’m not a regular forum poster, more or less a newbie so; sorry, for any mistakes. Second my native language is Dutch so English writing is bit “ruff” :-)
I’m look for a translation or a comparison list of teradata sql (UDF) versus Oracle preferable also versus SQL server. Within my company I have been working for many years on Oracle and SQL server system, the teradata box recently joint the family.
The list would make my transition a lot easier, now I’m surging the internet or help function, that takes a lot of time.
So is there anyone who can help me on this point? If there any tips,.. also very welcome.
( and yes I know there is training available on this subject, but a fortunately, in the current economic climate I do not have to ask )
I think following link should help you:
Considering Oracle & TD, I've noticed one significant difference in terms of PL/SQL coding point of view.
When in Oracle, Programmers are encourage to write PL/SQL such as procedure, functions, packages whereas in TD they prefer more macro.
I personally feel macro is a weaker version of Stored Proc. And, surprisingly I didn't find adequate reference for TD PL/SQL helpbook or may be I'm not aware of considering Oracle has plenty of PL/SQL materials easily available.
I would appreciate if anyone share that link.
Also, another feature of Oracle Package is Overloading methods. How that can be achieved here?
Finally, from Oracle 10g, there is a function called model clause - which can be used for forecasting your business based on the existing data as per the business logic. Is there any such functions available here?