select * from (select * from bob) and other easy stuff
Serge Rielau said:
Quote:
In my experience complex queries don't live in the application.
Yeah? Well, in my experience that is true for 95% of bad SQL. The stuff
generated by report writers is 5% of the pain *I* have to deal with. And I
actually spend my life with actual customers. ;o)
Quote:
I met in Vienna with customers who run >100TB warehouses. You don't get that big with a bad design.
Oh yeah? The databases are 100TB, but they were actually only supposed to
be 100GB. )
Quote:
Just accept the fact that different usages of the DBMS require different language capabilities. Just because YOU haven't needed nested subqueries doesn't mean IDS doesn't need them for good reasons.
Bollocks. ;o)
--
Bye now,
Obnoxio
"... no bill is required as no value was provided."
-- Christine Normile
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
select * from (select * from bob) and other easy stuff
Serge Rielau wrote:
Quote:
bozon wrote:
Quote:
If you have the views to simplify the logic then the BI tools should be able to use them to simplify the user's queries.
I meant the BI tools would use the views as tables, not create views on
the fly.
Quote:
These BI tools work against multiple DBMS. It is a major endeavor to convince the vendors to customize for any given product. Every feature can get abused of course. One can argue against stacking views 10 levels deep or using SERIAL, or ....
Yes, but I think that certain things get abused more than others. And
sometimes they are substitutes for knowing what you are doing. For
example, it is perfectly acceptable to use distinct but I have seen it
abused so often I am now suspiscious. I have seen queries missing joins
but the "distinct" clause makes it look OK. The developer will then ask
why is their query running slowly when it only returns a couple of
rows. I say because you are joining a 100,000,000 row table to
1,000,000 row table without a join clause but you are throwing away
most of the rows because they are duplicates. So you really returned a
google number of rows and then filtered it with distinct.
select distinct status::boolean from big_table_a, bigger_table_b where
a.id > 100
select * from (select * from bob) and other easy stuff
Serge Rielau wrote:
Quote:
bozon wrote:
Quote:
I just noticed that Google didn't eat my earlier post denouncing inline views but posted it 4 times instead. This is clearly very different than eating it. I am so sorry, that we don't allow newsgroups (except non-technical recreational (I mean p*rn for the euphemistically challenged) but that is another diatribe) at our office and I have to post using Google's tool. bozon wrote:
Quote:
I completely agree. In fact I had posted a similar comment but google ate it somehow and I didn't care enough to post it again. Views are good things. These "temporary views" are overused. I can only think of a few times that they make sense (and only in the select clause for things that just can't be done with regular views, see posts on rotating a table.) Somehow this is Oracle's fault for overselling this feature. I had a friend who had just gone through an Oracle demo from a salesbot, excitedly tell me about this new Oracle feature where you could include select statements in the from clause. I asked him how this differed from views, he stammered that it was cool. I can't be everywhere to head off this "misuse" of technology. Somethings are better left in demo's.
To create views you need privileges the app developer typically should not have. You typically don't want to clutter you db schema with everyone's ad-hoc views. Even worse you sure don't want to create/drop these beasts ad-hoc. If one looks at data warehousing SQL can get really complex and can't be broken apart easily without loosing performance. Nested sub queries are a must in that context.
Any VIEW that's encoded into an application belongs in the DB where it can
be reviewed and shared. If it's in the code, it's not dynamic, so some
planning is not only possible but desirable.
If the app programmers don't have the permissions (except on DEV we don't
give that away here) they should be submitted to the DBAs for review,
possible optimization, correction, and generalization, and installation on
test and production. They should NEVER be buried inside an application
where noone can see them or share them.
But, as Gumby says, what do I know? I'm only doing DBA, DB Design,
Architecture, Application Design, and coding for 24+ years.
select * from (select * from bob) and other easy stuff
Art S. Kagel wrote:
Quote:
Serge Rielau wrote:
Quote:
bozon wrote:
Quote:
I just noticed that Google didn't eat my earlier post denouncing inline views but posted it 4 times instead. This is clearly very different than eating it. I am so sorry, that we don't allow newsgroups (except non-technical recreational (I mean p*rn for the euphemistically challenged) but that is another diatribe) at our office and I have to post using Google's tool. bozon wrote:> I completely agree. In fact I had posted a similar comment but google> ate it somehow and I didn't care enough to post it again.>> Views are good things. These "temporary views" are overused. I can only> think of a few times that they make sense (and only in the select> clause for things that just can't be done with regular views, see posts> on rotating a table.) Somehow this is Oracle's fault for overselling> this feature. I had a friend who had just gone through an Oracle demo> from a salesbot, excitedly tell me about this new Oracle feature where> you could include select statements in the from clause. I asked him how> this differed from views, he stammered that it was cool. I can't be> everywhere to head off this "misuse" of technology. Somethings are> better left in demo's.
To create views you need privileges the app developer typically should not have. You typically don't want to clutter you db schema with everyone's ad-hoc views. Even worse you sure don't want to create/drop these beasts ad-hoc. If one looks at data warehousing SQL can get really complex and can't be broken apart easily without loosing performance. Nested sub queries are a must in that context.
Any VIEW that's encoded into an application belongs in the DB where it can be reviewed and shared. If it's in the code, it's not dynamic, so some planning is not only possible but desirable. If the app programmers don't have the permissions (except on DEV we don't give that away here) they should be submitted to the DBAs for review, possible optimization, correction, and generalization, and installation on test and production. They should NEVER be buried inside an application where noone can see them or share them. But, as Gumby says, what do I know? I'm only doing DBA, DB Design, Architecture, Application Design, and coding for 24+ years.
I give up, please re-read my posts. I'm not talking about crummy SQL
written by app programmers at all.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
The MyLounge.com forum is intended for informational use only and should not
be relied upon and is not a substitute for any advice. The information contained
on MyLounge.com are opinions and suggestions of members and is not a representation
of the opinions of MyLounge.com. MyLounge.com does not warrant or vouch for
the accuracy, completeness or usefulness of any postings or the qualifications
of any person responding. Please consult a expert or seek the services of an
attorney in your area for more accuracy on your specific situation. Please note
that our forums also serve as mirrors to Usenet newsgroups. Many posts you see
on our forums are made by newsgroup users who may not be members of MyLounge.com
Term of Service
select * from (select * from bob) and other easy stuff
)
Linear Mode
