Re: New to Oracle

damorgan <damorgan_at_exesolutions.com> wrote in message news:<3CAB3224.B907552C_at_exesolutions.com>...
> You have quite a few mistakes in what you wrote is causing you grief which leads
> me to believe that you haven't really studied Oracle much. For example ... the
> inner join syntax is different in 8i but both are supported in 9i. CASE in
> PL/SQL can be used in any manner in 9i and in 8i it is supported in PL/SQL
> through native dynamic SQL. And there many are other examples I could cite.

I'm using 8i because that is all that is available on Novell Netware. So far as I understand, the INNER JOIN syntax is part of ANSI 92 and so I was surprised to find it unsupported in 2002 by Oracle. In 8i a SQL statement with CASE in it will not compile in a stored procedure. It's good to know that I can put the statement in as dynamic SQL, but I have to say that I regard it as an undesirable glitch of Oracle that selected components of standard SQL will not compile in stored procedures. Even if I did understand Oracle better, there would still be these unforseen inconsistencies that would bite you now and again.
>
> But what really troubles me is that you don't seem to have spent any time
> learning how to do things in Oracle ... it appears that you are just trying to
> do the same thing in another product which is, if not impossible, a great way to
> make an unscalable mess lacking in data integrity. Your comment about temp
> tables, for example. While temp tables are commonly used in TSQL there is almost
> never a rational reason to use them in Oracle. I would guess that 95% of the
> time I see them it is developers from a TSQL background who don't understand
> Oracle's multiversioning, transaction model, use of resources, locking, etc.
>

You've already told me you don't like temp tables, and I guess I could look more closely at nested tables, index by tables etc. but for my purposes I don't think it makes a tap of difference which I use. In general I am trying to use set-based approaches to what I am doing rather than procedural approaches in cursor loops for neatness, portability and speed reasons.

Basically I am setting up a data warehouse from old unnormalised data where data integrity rules were not properly enforced. Once set up there will be monthly additions of data in a batch. The temporary tables are used while I clean up the data before adding it to the real files.

I'm currently the only user of the data (and one of two users of the database)so I don't have to worry about locking, concurrency issues etc. Within reason, speed of execution is not really an issue either.

I'm quite open to suggestions of ways other than temp tables to do things, but at the moment it seems like a reasonable approach to continue using.

> I strongly urge you to stop banging code and learn the underlying architecture
> before you continue down this road any further. In the end you will blame Oracle
> for what is clearly your own lack of learning the product.
>

Partly there's the whole driving a car if you're not able to fix it argument in here, and partly there is just the pragmatic need to get things done. So I will continue actually coding, and will just have to remain mystified why, for example, I can get the following to run and complete in a few minutes (there are 20 duplicates in a file of 225000):

create table delids as (select c1.rowid delid from children c1,children c2 where c2.fil = c1.fil and c2.fname = c1.fname and c2.dob=c1.dob and c2.sex = c1.sex and c2.rowid > c1.rowid); delete from children where children.rowid in (select delid from delids);
drop table delids;

and the statement

delete from children c1 where exists (select * from children c2 where c2.fil = c1.fil and c2.fname = c1.fname and c2.dob=c1.dob and c2.sex = c1.sex and c2.rowid > c1.rowid)

is expected to take 48 hours to run.

Jon Received on Thu Apr 04 2002 - 11:41:08 CST