Tuesday, June 10, 2008

I don't know...

I don't know if

a) I'm getting grumpier as I get older

b) people are losing the ability to phrase a question. 

It just seems to me that day by day, the quality of the questions goes down.  I don't remember people asking questions like this just 8 years ago.  I've been participating in forums since 1994.  "Back in the day", the questions were mostly thought out, with examples - with some background, with some thought.  In the last couple of years - this seems to be changing - universally.

Oh well, it is probably related to both bullet points... Anyway here is the QOD - question of the day.

Subject: query is not using particular partition and index despite use full table scan

Entire question:

SELECT count(*)
FROM
DIM_BANK RIGHT OUTER JOIN FACT_JRNL_ACTG ON (DIM_BANK.BANK_ID=FACT_JRNL_ACTG.BANK_ID)
LEFT OUTER JOIN DIM_BSA ON (FACT_JRNL_ACTG.BSA_ID=DIM_BSA.BSA_ID)
LEFT OUTER JOIN DIM_FY ON (FACT_JRNL_ACTG.FY_ID=DIM_FY.FY_ID)
LEFT OUTER JOIN DIM_APD ON (FACT_JRNL_ACTG.APD_ID=DIM_APD.APD_ID)
LEFT OUTER JOIN DIM_PSCD ON (FACT_JRNL_ACTG.PSCD_ID = DIM_PSCD.PSCD_ID)
WHERE
(
(DIM_BANK.BANK_ACCT_CD IN ('33')
OR '*' IN ('33'))
AND DIM_PSCD.PSCD_CLOS_CL_CD IN ('1', '4', '5')
AND CASE DIM_BSA.CASH_ACCT_FL WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END = 'Yes'
AND DIM_APD.PER != 0
AND DIM_APD.PER != 99
AND DIM_APD.FY < 2008
OR DIM_FY.FY = 2008
AND DIM_APD.PER <= 6
AND DIM_APD.PER != 0
AND CASE DIM_BSA.CASH_ACCT_FL WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END = 'Yes'
AND DIM_PSCD.PSCD_CLOS_CL_CD IN ('1', '4', '5')
AND (DIM_BANK.BANK_ACCT_CD IN ('33')
OR '*' IN ('33'))
);

 

That's it folks.  "My query isn't doing partition elimination (probably, I'm sort of GUESSING) and isn't using some index (on some column of some table)"

Before anyone says anything - this comes from the US.

Some wows from reading the query:

  • "(DIM_BANK.BANK_ACCT_CD IN ('33') OR '*' IN ('33')) - why would you do that?
  • "CASE DIM_BSA.CASH_ACCT_FL WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END = 'Yes'" - why would you do THAT?
  • "CASE DIM_BSA.CASH_ACCT_FL WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END = 'Yes'" - just in case you didn't believe us the first time
  • wow, more duplicated predicates...
  • outer joins to DIM_BSA, but we have that neat predicate using CASE - if we actually needed to outer join to DIM_BSA then all of the attributes would be NULL - the predicate would never be true - hence, we do not need or want to outer join to DIM_BSA
  • Same comment about DIM_FY, we outer join to DIM_FY, but if we make up a row - then DIM_FY's attributes will all be null and DIM_FY.FY = 2008 cannot be true (or false, it is unknown)
  • Ditto for DIM_APD
  • and of course DIM_PSCD
  • and just to make it 100% complete, the outer join to DIM_BANK - ditto. every single outer join in this query, should not be there.

I've responded with:

My car won't start.  Now we are even, we have shared the same level of detail regarding our respective problems.

POST A COMMENT

105 Comments:

Blogger Chris said....

I am often stunned by the lack of grammar, details, and basic politeness in questions to your forum. Your response was perfect, and I do not think you are getting grumpier. My patience would not have lasted nearly as long.

--Chris
Exception
When others then
null;
End;

Tue Jun 10, 04:29:00 PM EDT  

OpenID sterolandro said....

I feel much the same as you do.
No, you are not becoming grumpier.
In my opinion, 10~12 years ago there were a lot less people involved in IT in general, and in Oracle in particular.
These days it seems that everyone is urged to write queries, and there are literally hundreds of freshers (at least, in my experience) that want to start directly from the end (instead the other way round). They do not have any pride in what they do. They just want someone else do their job. An you (me... anyone else) is just that someone.
We call them "code typewriters".

@chris: please, forgive at least the grammar. Many of us (I count for one) are not English native speakers... :-P

Have a nice day

Tue Jun 10, 05:44:00 PM EDT  

Blogger Graham Day said....

I think you're seeing the result of de-skilling in the IT sector. Ten, fifteen years ago, your questioner may have had someone "local" to ask... or someone who could have at least framed the question better.

Tue Jun 10, 06:17:00 PM EDT  

Blogger Michael O'Neill said....

Mainstream sites such as AskTom are too easily found by the neophytes and their unprofessional nature and English illiteracy drive away most of the people of quality you used to find in the forums.

Once I got into a stupid fight with you on AskTom - and I regretted it but never once did I think you were grumpy then, nor do I think you are grumpy now.

Tue Jun 10, 06:22:00 PM EDT  

Blogger Gary Myers said....

Maybe it is that a lot of the people who previously would have asked the well-thought out questions have got the answers from either previous AskTom queries, or your books or the documentation.
I know that I am much less on the hunt for the rare AskTom 'Ask a question' prompt than I was a few years ago.

Tue Jun 10, 07:04:00 PM EDT  

Blogger Graham Day said....

But Gary, they could also have got it from a swift query on AskTom...And yet, they don't do it, they don't even look at previous queries as a clue towards presenting their own question (My own experience is that Tom Kyte's approach to Oracle Database problems has helped be solve a lot of similar issues). And my thoughts on that are that 2008 model IT professionals don't have the time and space that the 1998 model (i.e. me) had. Tom's talking about someone in the US, the trend is clear in the UK too (it's often said that what the US gets will arrive in the UK - in this case, it's deskilling).

Tue Jun 10, 07:24:00 PM EDT  

Blogger Gary Myers said....

Graham,
I wasn't quite clear. I'm suggesting that rather than an increase in 'lazy' questions, we may just be seeing a decrease in the 'well-thought out' questions. So its not that there are fewer 'thinkers' or more 'non-thinkers', just that the 'thinkers' don't need to AskTom as much so he is seeing proportionately more non-thinkers.
Of course, I could be wrong and, as the saying goes, while the population of the planet is increasing, the total IQ is a constant.

Tue Jun 10, 07:42:00 PM EDT  

Anonymous David Aldridge said....

Add to your list a lack of appropriate parentheses -- lord knows what result that query would bring back.

Rule 1: when tuning a query, first make sure it returns the correct result.

Tue Jun 10, 07:47:00 PM EDT  

Blogger Jonah H. Harris said....

In regard to your reply, a more illustrative response could not be asked for.

Tue Jun 10, 11:40:00 PM EDT  

Blogger Noons said....

Hmmm, more "grumpy" material!
LOL!

Incredible how you go through this for years and years!

Still, nowadays I'm finding myself just grinning and ignoring this sort of thing.
Why, you'd need a DISTINCT somewhere in there (to "avoid more joins", of course!) to really get me bouncing off the walls!

Then again, I'm quite sure now that I mentioned it, someone will come up with a way to fit it in...

Wed Jun 11, 02:33:00 AM EDT  

Blogger robert said....

Tom, I am feeling the same about another forum. There I explained it myself with increased popularity of Ruby and so a larger number of people joining.

Also, we might be part of the problem: by giving away information for free we nurse expectations that everything is for free and thus people can simply throw their problem at forums and get back complete solutions immediately.

Another reason might be that more and more people are forced to do things for which they are not properly trained - and they don't get the time to learn / do their job properly. My impression is that resources (time, money) are cut more and more but the job assignments stay the same (or go up).

Wed Jun 11, 03:10:00 AM EDT  

Blogger Narendra said....

I think it is the general attitude of new generation (I know I am speaking like a grandpa but)...people want quick answers, sometimes no matter even if they are not correct
Look at one such posting
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:995030557145

How, on earth, can a question like this be tolerated ?

And then it has been proved time and again...people, unfortunately, do not value something if it is available free...

Wed Jun 11, 05:42:00 AM EDT  

Blogger Pete_S said....

I have seen (and blogged) about those CASE ... = 'YES' type constructs before - looks like they captured a query from a BI tool such as Business Objects.

The day after I did my blog-rant I found one example that ran quickly - the not matching variant actually used a bitmap index to identify non-matching rows and was oddly efficient... unlike the rest of those queries!

Wed Jun 11, 07:58:00 AM EDT  

Anonymous Josh said....

Cheers on a clever answer Tom. I too feel this way much of the time. It seems it is evident even in the workplace each day. I think in general people are just becoming lazy. They find that by googling a problem they don't actually have to understand or troubleshoot it properly.

P.S. I think your car is out of gas.. :-)

Wed Jun 11, 10:07:00 AM EDT  

Blogger Thomas Kyte said....

@Josh

Oh, didn't I mention, it runs on electrics too - it can start without gas.

But it still won't start, please help me.

Wed Jun 11, 10:25:00 AM EDT  

Anonymous Anonymous said....

Well, I will have to disagree with everyone. My sense is that AskTom is getting stale for you Tom. When I read many of your responses it seems like there is a sharpness and edge to your answers. I don't think this is all due to poor posts as your responses frequently seem sharp to me.

Now I can't blame you if this has become somewhat stale for you as you have been doing it for a long time.

And I do agree there are more poorly formed questions, probably due to the quantity of work being outsourced, and hence, the poor grammar/English.

My sense is you need a break, a long one, to regain your patience and perspective.

Wed Jun 11, 10:29:00 AM EDT  

Blogger Thomas Kyte said....

@last anonymous...

how about the question framed above - does it seem

a) like something that needs patience and perspective

b) like something totally showing lack of thought and effort on their part


How about this one I just got - just now:

SUBJECT: How to Use Alternate/Unique Keys

QUESTION: Customer is Migrating from Oracle to Netezza. Was wondering how to enforce alternate/unique keys in Netezza database

Now again - this question comes from the US (San Jose, CA).

The current Oracle database is 8.1.7 - probably the customer said "hey, this is slow"... someone said "hey, netezza is fast, I heard someone say that" - so they buy brand new hardware to replace the 10 year old machine 8.1.7 is on and buy software compiled in the year 2008 to replace the software compiled in the year 1998



Time for a bike ride I guess........

Wed Jun 11, 12:24:00 PM EDT  

Anonymous Anonymous said....

To Tom:

Sometimes we don't recognize when we have changed. Sometimes change occurs so slowly that we do not recognize it in ourselves. I have been reading your posts for many years and I was just pointing out something I had been noticing recently. It is up to you Tom to decide if something in it resonates at all with you. We all only have one life. It should be lived sweetly.

Wed Jun 11, 12:38:00 PM EDT  

Blogger Thomas Kyte said....

@anonymous again

but, you didn't answer the question...

I know in 2008 I'm very much different from 2000 - and if you want to see a real difference, go read some of my stuff from 1995 in the usenet newsgroups (especially when I posted to comp.databases.sybase or informix... I was a little aggressive sometimes in those groups :) )

Wed Jun 11, 12:43:00 PM EDT  

Anonymous Mark Brady said....

I think you might be downplaying the ingenuity.

Ever play poker on a free internet site. Some of us play there to have fun, treating the chips as if they have value helps. But that's just one strategy. Others go to win and the best way to do that is increase your odds by only playing games with a large advantage. They go "all in" on the first hand if they win it, they have a huge chip lead on everyone else. If they lose they move on to the next game and try again. Only playing games they are likely to win. This starts to make it no fun for the rest of us.

This poster may be doing something like that... posting the same question to dozens of help-sites and looking for the one which will pity the fool and help.

Well, ok, probably not, but why assume incompetence when a conspiricy is much more fun to think about.

Wed Jun 11, 12:46:00 PM EDT  

Blogger Craig Martin said....

To be a fair comparison, you should have included a picture of your car.

Wed Jun 11, 01:45:00 PM EDT  

Anonymous danp said....

IMHO, In today's world of "drag and drop" IDE's, I think (newer) developers "expect" instant answers without understanding the processing. So they don't really know how to ask a question because they don't fully understand what they need. Many times, I get "you can do that easier in TOAD", when I am using my tool of choice - the tried-and-true SQL*Plus. I feel that SQL*Plus makes me look into my problem, so I can solve my probem. I don't think it is time that makes us grumpier. It is the same "problem with no explanation of what they want" that gets asked over and over that makes ME grumpy.

Wed Jun 11, 01:59:00 PM EDT  

Anonymous Brian Tkatch said....

Tom, I think you are definitely getting grumpier, though at your age it is expected. :P

One thing is certain, AskTom is very popular. Blame yourself for great answers. Blame SQL Developer for including it as the default search. Blame Google for having it turn up so often. Blame Microsoft because everyone already blames them for everything else.

Even if the ratio of good and bad questions stays the same, an increase in volume makes it stand out more. Perhaps the volume is just getting on your nerves.

I disagree with an earlier poster that there are more people now then ten years ago. The amount of people in IT 10-years ago was staggering, with rampant ignorance. Perhaps what the comment meant, was that more people ask and expect everyone else to do their job for them.

Personally, i'd love a section where we could see pending questions, somewhat similar to slashdot's firehose. Besides being amusing, we might be able to take some of the load off your shoulders.

Either way, i love AskTom. Please don't ever get too angry and make it go away.

Wed Jun 11, 01:59:00 PM EDT  

Anonymous Michael Olin said....

@Craig

The picture of the car would have to only show the driver's side front door, from an appropriate distance such that the actual make and model of the care could not be determined (although revealing the color would probably be OK). That would provide a roughly equivalent amount of useful information for diagnosing the problem.

@Tom

In the event that your car will still not start come September, we will happily send someone to pick you up and drive you to our NYOUG meeting. We're looking forward to your keynote.

Wed Jun 11, 02:06:00 PM EDT  

Anonymous GaryS said....

IT is percieved as a high-pay field (DBA work is near the top of this field). As word, "gets out," more people try to enter the field who are only motivated by the pay.

IT workers need a professional organization with licensure, with an end to certification by vendors (who face an obvious conflict of interest in this area).

It's not likely to happen, since the corporations that use most of our services are not likely to go along with it (unless they can be convinced of the benefits for them).

My wife's hairdresser has a license issued by the state of Missouri. She has to display it where she works.

Wed Jun 11, 03:51:00 PM EDT  

Anonymous Stew Ashton said....

Funny how perceptions are different. I was very disappointed during the last week of May, when every "reviewer" either didn't know how to cut and paste or didn't know how to do anything else, and you had few openings to provide interesting followups. Then in early June I was learning stuff so fast I could hardly keep up!

I guess not seeing most of the "questions" is a real blessing.

My take on "grumpiness" : frustration is when you care; if you don't care, you're cool because who cares?

Wed Jun 11, 04:58:00 PM EDT  

Anonymous Jonathan Adams said....

I'm afraid I must concur with "anonymous", Tom; your posts are definitely acquiring more of an edge in recent months, and your patience is diminished. Even the fact that here on your blog, you have several times dedicated entire posts to picking out questions from AskTom to make fun of, speaks volumes.

It's an understandable difference in perspective; after years of answering the same sorts of questions over and over (and over), you wonder why everyone hasn't "gotten it" yet. But remember -- every day, someone brand new to Oracle comes to AskTom for the very first time. Sometimes they post in haste, not knowing the ground rules of what you expect, such as your abhorrence of IM-speak, and your desire for a stripped-down, fully-reproducible test case. Often, they get sarcasm instead of correction.

Consider way back when you were beginning your Oracle career, if you had inquired of a respected mentor regarding a problem, and had been told, "My car won't start. There, we're even." The contempt fairly drips from such a reply. It gets a laugh from the posse here, but what does it do to the newbie?

If you only want to cater to experienced Oracle veterans who have already exhausted all other avenues, perhaps a disclaimer to that effect on the main page of AskTom would be appropriate. If not, then remember the neophyte who knows no better, and for his/her sake, restrain the caustic comebacks.

This opinion only offered because you asked the question.

Wed Jun 11, 05:00:00 PM EDT  

Anonymous Anonymous said....

Tom,
exception
when others then
null;
end;

So, sit back, relax and enjoy :)

Compared to all you Gurus out there, I'm simply a "Toddler" Oracle Developer! But even I feel like saying "RTFM" to some of the questions asked in Asktom.
And wonder in awe how you decipher the no-head-or-tail questions!
So, if anything, you have way too much patience!

Your comment - "just in case you didn't believe us the first time" is Priceless!!
-GND

Wed Jun 11, 05:06:00 PM EDT  

Anonymous Anonymous said....

The Exception part was for your car trouble :)
-GND

Wed Jun 11, 05:07:00 PM EDT  

Blogger Joel Garry said....

Maybe 8 years ago you just ignored bad questions.

As far as high pay, I hear plumbers do quite well...

word: jfrcixr

Wed Jun 11, 07:58:00 PM EDT  

Blogger Serge said....

Well, I believe that the level of technical questions reflects a cost of entry into the cyberspace. If it doesn't take much (money or effort) to ask a stupid question, why not?

Wed Jun 11, 10:29:00 PM EDT  

Anonymous Anonymous said....

to everybody,,,

I wonder what this fuss is all about...

As long as asking questions on asktom.oracle.com is free, there will be questions that evoke stupidity. I am not sure why you single them out,blame the grammar
and make a blog post out of it ..

Tom, if only you need sensible questions with Queen's grammar in it, you should think of charging every question by making a e-payment to your checking account through Mastercard or Visa. I'm quite sure you not only be asked questions of your lifetime but you will also be bowled over with prudence.

Thu Jun 12, 01:34:00 AM EDT  

Anonymous Anonymous said....

@Jonathan Adams:

But what I don't get is:
Why can't we expect (even of an oracle novice) to RTFM, documentation, to give all the information needed (You have just to read the guidelines to know you should give the version etc.)

Thu Jun 12, 02:31:00 AM EDT  

Blogger Stew Ashton said....

@various:
I searched AskTom on "my car won't start" and found followups from 2003 ! Hard to prove a crescendo of grumpiness from that.
On the other hand, I feel that comments on this blog have gotten much edgier recently. Is it just me, or are we all getting grumpier ;)
Now if you are worried about Tom's psychological state, I think the solution is obvious: buy the guy a new car! He's been complaining about it long enough. I am starting a fund to this effect; please send checks made out to me at the following address............

Thu Jun 12, 02:38:00 AM EDT  

Blogger Igor said....

>(DIM_BANK.BANK_ACCT_CD IN ('33') OR '*' IN ('33')) - why would you do that?

Well, looks like another 'direct from the form' (TM) SQL. If the user would put * in the form field query would return results for all account codes.

As for your main question here are my two cents.

First: people believe in your ability to handle incomplete questions because you've handled questions in the past that where as complete as this one (at least in their eyes)

Second: For increasing barrier of entry you can create a captcha with Oracle questions (from exams, etc.) ;-) instead of charging people

PS. When you say 'this comes from the US' you don't assume 'this comes from the English native speaker', right?

Thu Jun 12, 03:07:00 AM EDT  

Anonymous ApexBine said....

To be honest, I think it's a general Usenet/Web problem. In 1992 when I started using the Usenet, people wrote whole sentences, were able to quote properly, etc. There where some acronyms, of course.
Newbies were told pretty fast when they did not abode "the rules". Nowadays there are just too many of them at a time, so the 'old' users just can't teach everyone how to behave, and there are many different groups with their own abbreviations and acronyms. (Unability to quote, however, is mainly caused by MS Outlook's standard behaviour.)

This may sound horrible, but that's what I have observed during the years. No idea if there's a way back.

But to make things easier for you:
What if you had a kind of junkyard where you could put unanswerable questions, so that the original questioners could rework their questions?

Thu Jun 12, 04:17:00 AM EDT  

Anonymous AJA said....

Consider this...

For those of us with fewer days before us than behind; Our time is more valuable (at least to us) today than it was yesterday. And it will be more valuable tomorrow than it is today. So STOP WASTING IT with your laziness, stupidity, and...

For those of us with more days before us than behind; We have no concept of the value of time. We have plenty to waste, so you must have plenty to waste too.

Or something like that..

Thu Jun 12, 08:52:00 AM EDT  

Blogger rhalabicki said....

Tom, maybe you don't see the impact you've really had on the industry. I personally used to post (some pretty stupid) questions on asktom. I tried to always give enough detail, properly explain the question as best I could. After posting a few I began to notice my skills at asking and problem solving increase along with my knowledge of the database.

It wasn't long after that when I would go to post, I would often answer my own question while creating the post, and I'd cancel it out entirely. YOU taught me the skills. I've since stopped posting more because I don't need to as much. I'm working it out on my own.

I *believe* I can't be the only one. With as many people who use your website as there are, I think gary myres said it best with "So its not that there are fewer 'thinkers' or more 'non-thinkers', just that the 'thinkers' don't need to AskTom as much so he is seeing proportionately more non-thinkers."

In the end, I have to agree, I think you should stop for a while. Get a break, make those non-thinkers think on their own for a while

Thu Jun 12, 09:44:00 AM EDT  

Anonymous ApexBine said....

rhalabicki, I agree with you.
Putting together a proper test case very often solved my problems :-)

Still I think it would help if Tom just rejected incomplete questions instead of wasting time on them.
But maybe he likes the idea of taking a break... (which I don't!)

Best regards,
Sabine

Thu Jun 12, 10:50:00 AM EDT  

Blogger Bill S. said....

I've read through many of the comments here, and I have to say I don't think the problem is that you're getting grumpier, Tom. Indeed, I very much disagree with "Anonymous" when s/he states you need a "long break". If you feel like a vacation is in order by all means, take one. But I don't honestly think an extended absence from AskTom is going to make the lazy posters go away. Or the ones that are incapable of taking what you say and trying it out themselves if they "have a doubt".
I think that you still get some really good "yeah-but"s and I'd be willing to lay odds that you are still learning new things.
Maybe folks like Anonymous need an extended vacation?

Bill S.

Thu Jun 12, 03:15:00 PM EDT  

Anonymous Nerf Herder said....

You are a grumpy dude Tom, but its who you are. Some let it roll off their back and just ignore it, others blog about it. Neither approach is best - its whatever works for the person.

That being said, its not that today's IT professionals are dumber, lazier or less trained than those of yesteryear. You are just seeubg a proliferation of the Internet all over the world and access to an unbelievable amount of resources. People 10 years ago didnt ask stupid questions because they probably werent trolling on the internet for answers and there werent as many forums available.

Fast forward ten years and it only takes a few mouse clicks to get someone to help. Google
Oracle Forum and there are 778,000 hits. So people show up, maybe dont speak the language natively and werent cutting edge forum people back in the 1990's.

Whats my point? Had the Internet been as popular 10 years ago as it was today, you would have seen just as many stupid questions. You were just seeing a small subset of the data, who happened to properly phrase questions.

Every generation thinks the ones under them are spoiled, lazy and stupid. For those of you in your 40's if your parents are still around ask them what they think of your generation. Likely they will say they dont know how to save money, make stupid decisions...blah blah. You know you're truly old when you utter the phrase "Kids today..." Followed swiftly by "Get off my lawn!"

-Fin

Thu Jun 12, 05:25:00 PM EDT  

Anonymous Anonymous said....

Tom,
I have been visiting your forum for almost 5 years now. I have admired your patience every time I get a chance. Please share the secret of your patience with us. Still don't know how you do it.

These kind of questions generally come from newbies jumping into the Oracle arena without a firm grasp (or even a slipping grasp) of Oracle concepts. Mostly under time pressure such questions are asked.

Another major contribution to the class of dumb questions comes from the top notch consulting companies where I guess managers get paid solely to ask such questions. What are the ten things to check in a database, how do I secure data in a database, can a tool automatically tune my queries, what are top ten SQL commands that should not be used in a query...

It is not you. Every seasoned Oracle person will beat his head on the wall when faced with such questions. I again salute your patience.

Thu Jun 12, 07:27:00 PM EDT  

Blogger Bill said....

Tom,
Ever since the dotcom bomb dropped in early 2000 and IT cuts sent salaries south and scattering good talent to the four corners of the planet, anybody who can spell oracle or database (or IT for that matter) expects to be an expert on day one without doing the research necessary to keep up with the rest of us. I hate being bothered with questions that just take a little extra time to think through, or even worse, the same person asking the same questions twice not getting it through their thick skulls or making notes the first go-round. I mean, for the love of larry, I'm a busy dataman!

Sorry for the run-on sentences above but it was necessary to express the amount of ranting I do on this subject regularly.

By the way, maybe it's the beard that's making you feel more grizzled than usual these days. :)

Fri Jun 13, 11:38:00 AM EDT  

OpenID stewstryker said....

I think Graham Day was very close.

To me, these "hey do my work for me" questions are just a form of spam, and exist for the same reason as spam - little to no cost to produce, large field to send to, gives some hope of getting help.

I think the "cost of entry" to access an Oracle expert such as yourself has gotten so low that people will toss out these "hey do my work for me" questions on the off-chance that someone will respond. There are so many "Oracle expert" sites that their chances of getting an answer is worth their minimal effort.

Of course, yours is the best site for Oracle answers! :-)

Fri Jun 13, 04:12:00 PM EDT  

Anonymous Tubby said....

Do A and B have to be mutually exclusive?

Fri Jun 13, 11:35:00 PM EDT  

Blogger I said....

It doesn't matter where the question comes from. A recent import from India whose best English is shaking his head in agreement can be working in San Jose, so geographics are meaningless. It's proper communications that count.

I've been in IT before the termed was coined, at a time when FORTRAN was the first intro to programming. What I find is that the current generation doesn't care about communicating properly and most don't care to think before they ask. The norm today seems to be to implement steps 1-n to get something done, and any deviation leads to doing a Google search. What happened to thinking things through?

Shoot -- _I_ am getting old and grumpy!

Sat Jun 14, 02:40:00 PM EDT  

Anonymous Anonymous said....

>> whose best English is shaking his head in agreement can be working in San Jose

and these are the same group of people who left their families and friends back in their home country, to work for you day in and day out and sometimes forcing themselves to do a 2-man job while you are out playing golf.

Spare their best English idiosyncracy from San Jose..

I would like to see you ask for a cup of coffee in your non-native country !!!!

Sat Jun 14, 09:32:00 PM EDT  

Blogger Total Bloody Legend said....

I think it is a natural result of the off-shoring of database support. I know from discussions with Indian staff at work, that in India, many Universities hand-feed their students everything, so they are not trained to think or work things out for themselves. It is such a problem locally here that I would say about 50% of IT departments in Melbourne purposefully avoid employing Indian DBAs and other tech staff. Which is a shame, because I also know some great Indian technical folks.

Sun Jun 15, 07:30:00 AM EDT  

Anonymous Anonymous said....

Off topic. Is something wrong with Asktom.oracle.com website? I cannot access it since yesterday. It keeps showing:

The server is temporarily unable to service your request due to maintenance downtime or capacity problems. Please try again later.

Sun Jun 15, 11:26:00 AM EDT  

Anonymous Anonymous said....

Total Bloody Legend
>> 50% of IT departments in Melbourne purposefully avoid employing Indian DBAs

Honestly, That's for Melbourne to worry about..I don't want to comment more about that

>>>>
know from discussions with Indian staff at work, that in India, many Universities hand-feed their students everything, so they are not trained to think or work things out for themselves.

That's a gross misconception. Education out there in India is one of the gruelling challenges that brings forth Darwinism to its best. And I recall Bill Gates who once said after his visit to India. "If atleast half the world imbibes a quarter of Indian education system, the world would have doubled up in technology years

Sun Jun 15, 12:45:00 PM EDT  

Blogger Thomas Kyte said....

I'd appreciate it if we could keep the "this that or the other country", "offshoring", "they all" (whomever they is) off this

I tried to head that off from the get go with "from the US"

It falls into none of the categories, stereotypes from above.

I find people are not able to phrase a question. I find this to be universally true - from every country. It does seem to have more to do with experience than anything else.

Sun Jun 15, 12:51:00 PM EDT  

Blogger Joel Garry said....

How about if we allow the "they all" to be vendor training or vocational schools?

I think they all generally envision their point to be to spoon-feed their students. That's what the students expect and demand - a business sending their workers to vendor training expects the workers to be shown how to use the tools to do the work they need, whether it is generating pretty reports or making sure the CRM system works. Does figuring out using a particular index fit into that sort of spoon feeding? I think some would argue "yes," at least that appears to be the marketing of some performance tuning.

Does this mean vendor or vocational training should include training on how to ask and answer questions, how to solve problems? I'd say yes, that is a major failing, and has been as long as I've seen people coming out of such training (18 years and counting, and I'd say PLATO was a step in the wrong direction, being a gee-whiz and cool thing that did not address this question, letting the problem propagate through all such online communities and systems to the present).

Then there's the question of whether (all) university systems should deal with this. Besides the obvious "yes, all of their students are online and they should be taught to think," the question is begged as to whether they need to be relevant to the larger society and specifically teach things like Oracle and how to ask questions on asktom.

So given that technology has become ubiquitous, should there be an expectation that newbies should know how to ask questions?

Or maybe there should be a saying like "summer is the time when grumpy old men turn to thoughts of string bikinis." I can only speak for myself. :-)

word: iacurg
word: ezsmns
word: fxnwebwv

Sun Jun 15, 01:55:00 PM EDT  

Blogger Total Bloody Legend said....

Thanks Anonymous and Tom for your comments. I still standby my observations, comments from my Indian co-workers, and the general concensus amongst colleagues that overseas staff bombard you with questions without trying to work out things for themselves. I have spoken with a number of recruiters who have explicitly said that companies they represent are looking for non-Indian DBAs.

But yes, framing a question is a skill that everyone has to learn.

My point is that the escalation in questions may be related to offshoring, whereas in the past, folks would have done a little research and just worked it out for themselves.

Sun Jun 15, 04:52:00 PM EDT  

Anonymous Anonymous said....

How about popping up a "guidelines to follow" document, during the course of posting a question?
I think it will certainly help
AskTom newbies, and especially you!

May be you could host it in http://asktom.oracle.com/mycarwontstart.html ;-)

Mon Jun 16, 06:07:00 AM EDT  

Blogger Thomas Kyte said....

@last Anonymous

anyone, everyone that clicks on "ask a question" is presented with

this page

It starts with READ THIS, REALLY - take your time.

and goes on to say what is necessary.

It has been there for a long time.

It has no effect :(

Mon Jun 16, 10:19:00 AM EDT  

Blogger rhalabicki said....

I thought about this last night because I had to ask myself "why didn't I know this, its so simple"

But, the point I think we have to remember, is that most education systems teach kids how to answer, or how to regurgitate some specific reply back. We're never taught (or really even exposed) how to ask a question. Heck, we're encouraged NOT to.

Its always, "this is the right way to do 'x', or thats not how to do it, this is how" We become robotic, knowing the specific answer to a specific question. Thus when we're put in a new situation, our ability to apply the knowledge or logic is gone because "its different".

So I think there are 2 issues (one relating to why the question is being asked, and the other is why it can't be asked properly)
#1 - Not being taught how to ask a question, right from the get go.
#2 - "Memorizing the answer", just being able to replicate something back, versus an understanding of why something is happening.

Mon Jun 16, 11:26:00 AM EDT  

Anonymous Anonymous said....

Tom,

I was thinking you should ask the askTom developers to see if they can provide you with a small menu of default responses, already typed out based on what you want from your follow-up screen.

Maybe something like the format bar we get, it could be "responses" and in there you could have

"My car won't start..."
"No create, no insert no look...."
A link to the documentation or "RTFM"

Stuff like that. That way you can save a lot of time, and also get to those ones you normally ignore, but invariably have to go back to because you get "please respond to above..." and get 'em in the first pass.

-Alexander

Mon Jun 16, 04:50:00 PM EDT  

Blogger dave_katz said....

One person answering the questions of the entire Oracle community does not scale.

Tue Jun 17, 04:58:00 PM EDT  

Anonymous Anonymous said....

You're obviously getting grumpier.

I suspect that you could even prove it with a whizzy text-based query against your asktom responses over time by looking for "grumpy" keywords/phrases. I expect to see an increase in grumpy words over time - and maybe even an increase in the density of grumpy words per post.

query left as an exercise for the developer

Tue Jun 17, 05:41:00 PM EDT  

Anonymous Anonymous said....

I have to disagree that you can keep this conversation away from off-shoring by stating "from the US". A large part of the off-shoring business centers around rotating temporary workers through offices in the source country.

I think you are seeing the result of this. I think it would make an interesting study, if there were some reliable way of scientifically studying this.

Tue Jun 17, 09:33:00 PM EDT  

Blogger Luis Santos said....

Easy to anwser: In 1994 both Internet and Oracle weren´t popular as now, and were used just by technically advanced professionals.

This is the price for popularity. This happens in any field, in any technology, anytime.

Bu we´ll survive!

Best regards
Luis Santos

Thu Jun 19, 02:44:00 PM EDT  

Blogger Sunil Shah said....

I have a problem while running the query
Which uses 3 table wh5110 wh1500 and gn1000
I have indexes on them
But the index is used and goes skip scan of any other index
So I would like to have a better solution than thes
I have almost 35,00,000 records in wh1500 and 2000000 in wh5110
Please convert my explain plan index scan to unique scan or range scan with low cardinality
And cost


SELECT 'WH',3,SYSDATE,A.NO_CMP,a.no_loc,a.vc_grinspno,A.NO_LN,'WH5110','WH1500','Quantity Mismatch BETWEEN WH5110 AND WH1500'
FROM nealv.WH5110 a ,nealv.WH1500 b ,nealv.GN1000 c
WHERE a.vc_grinspno = b.vc_objno
AND a.no_wh = b.no_wh
AND a.vc_partno = b.vc_partno
AND a.no_ln = b.no_ln
AND a.no_accqty != b.no_qty
AND a.vc_uom = c.vc_uom
AND a.no_loc = b.no_loc
AND a.no_cmp = b.no_cmp
AND a.vc_partno = c.vc_partno
AND b.vc_partno = c.vc_partno
AND b.dt_bk >= '01-apr-2007'
AND b.ch_canccd = 'N'
AND NOT EXISTS (SELECT /*+ ORDERED INDEX (wh1500 pk_wh1500) */ e.no_refbk
FROM nealv.WH1500 e
WHERE e.no_refbk = b.no_bk
AND e.no_loc = b.no_loc
AND e.no_cmp = b.no_cmp
AND e.vc_objno = b.vc_objno
AND e.no_ln = b.no_ln
AND e.no_wh = b.no_wh
AND e.vc_partno = b.vc_partno
and e.dt_bk = b.dt_bk )
ORDER BY a.no_cmp,a.no_loc,a.vc_grinspno;



CREATE TABLE WH5110
(
NO_CMP NUMBER(4),
NO_LOC NUMBER(4),
VC_GRNNO VARCHAR2(25 BYTE),
NO_LN NUMBER(4),
NO_WH NUMBER(4),
VC_PARTNO VARCHAR2(22 BYTE) NOT NULL,
VC_UOM VARCHAR2(5 BYTE) NOT NULL,
NO_QTY NUMBER(14,3) NOT NULL,
NO_MODRCT NUMBER(3) NOT NULL,
VC_USRCRT VARCHAR2(12 BYTE) NOT NULL,
DT_CRT DATE NOT NULL,
NO_CHANGE NUMBER(4) NOT NULL,
VC_GTENTNO VARCHAR2(25 BYTE),
NO_LN1 NUMBER(4),
NO_OBJTYP NUMBER(4),
VC_OBJNO VARCHAR2(25 BYTE),
NO_LN2 NUMBER(4),
VC_GONNO VARCHAR2(25 BYTE),
NO_LN3 NUMBER(4),
VC_REM VARCHAR2(200 BYTE),
NO_BK NUMBER(20),
VC_QCTSTNO VARCHAR2(25 BYTE),
NO_LN4 NUMBER(4),
VC_GRINSPNO VARCHAR2(25 BYTE),
DT_GRINSP DATE,
NO_BKKEY NUMBER(4),
CH_CANCCD CHAR(1 BYTE),
NO_ACCQTY NUMBER(14,3),
NO_UOMCONVCD NUMBER(17,9),
VC_APPRID VARCHAR2(25 BYTE),
NO_APPRSTS NUMBER(2),
VC_USRMOD VARCHAR2(12 BYTE),
DT_MOD DATE,
NO_BALQTY NUMBER(14,3),
NO_CAPREV NUMBER(1) NOT NULL,
NO_CURCD NUMBER(4),
NO_EXCRT NUMBER(18,6),
NO_UNTRT NUMBER(14,3),
VC_ASTCD VARCHAR2(12 BYTE),
VC_TAG VARCHAR2(10 BYTE),
VC_OBJNO1 VARCHAR2(25 BYTE),
DT_EXP DATE,
DT_IMP DATE,
VC_INDREF VARCHAR2(1000 BYTE),
VC_DPTREF VARCHAR2(100 BYTE),
NO_CLNQTY NUMBER(14,3),
NO_WBQTY NUMBER(14,3),
NO_QCQTY NUMBER(14,3),
NO_REJREM NUMBER(4),
VC_CCCDREF VARCHAR2(100 BYTE),
VC_REM1 VARCHAR2(500 BYTE),
DT_APPR DATE,
NO_TOTFNVLBC NUMBER(18,6),
NO_EXCVALBC NUMBER(18,6),
NO_CESSVALBC NUMBER(18,6),
NO_BNFVALBC NUMBER(18,6),
CH_EXCFLG CHAR(1 BYTE),
NO_RVEXVLBC NUMBER(18,6),
NO_RVCSVLBC NUMBER(18,6),
NO_REFPTY NUMBER(8),
VC_REJREM VARCHAR2(200 BYTE),
CH_PODBREQ CHAR(1 BYTE),
NO_DELPTY NUMBER(8),
VC_DONO VARCHAR2(25 BYTE),
DT_DO DATE,
NO_CVD NUMBER(18,6),
NO_SCCSVALBC NUMBER(18,6),
NO_RSCCSVLBC NUMBER(18,6),
NO_PURPER NUMBER(5,2),
NO_ACTQTY NUMBER(14,3)
)
TABLESPACE PURCHASE
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE INDEX IN_WH5110_6 ON WH5110
(CH_CANCCD, VC_OBJNO, NO_LN2, NO_LOC, NO_CMP)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX IN_WH5110_7 ON WH5110
(VC_PARTNO, NO_WH, CH_CANCCD, NO_LOC, NO_CMP)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE UNIQUE INDEX PK_WH5110 ON WH5110
(VC_GRNNO, NO_LN, NO_WH, NO_LOC, NO_CMP)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX IN_WH5110_1 ON WH5110
(VC_GRINSPNO, NO_LN, NO_LOC, NO_CMP)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX IN_WH5110_2 ON WH5110
(VC_GTENTNO, NO_LOC, NO_CMP)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX IN_WH5110_3 ON WH5110
(NO_BK, NO_LOC, NO_CMP)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX IN_WH5110_4 ON WH5110
(DT_DO, VC_DONO)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX IN_WH5110_5 ON WH5110
(VC_OBJNO, NO_LN2)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


ALTER TABLE WH5110 ADD (
CONSTRAINT PK_WH5110
PRIMARY KEY
(VC_GRNNO, NO_LN, NO_WH, NO_LOC, NO_CMP)
USING INDEX
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));


ALTER TABLE WH5110 ADD (
CONSTRAINT FK_WH5110_1
FOREIGN KEY (VC_GRNNO, NO_LOC, NO_CMP)
REFERENCES WH5100 (VC_GRNNO,NO_LOC,NO_CMP));

ALTER TABLE WH5110 ADD (
CONSTRAINT FK_WH5110_2
FOREIGN KEY (NO_WH, NO_LOC, NO_CMP)
REFERENCES WH1010 (NO_WH,NO_LOC,NO_CMP));

ALTER TABLE WH5110 ADD (
CONSTRAINT FK_WH5110_3
FOREIGN KEY (VC_UOM)
REFERENCES GN1410 (VC_UOM));

ALTER TABLE WH5110 ADD (
CONSTRAINT FK_WH5110_4
FOREIGN KEY (VC_GTENTNO, NO_LN1, NO_LOC, NO_CMP)
REFERENCES GN8110 (VC_GTENTNO,NO_LN,NO_LOC,NO_CMP));

ALTER TABLE WH5110 ADD (
CONSTRAINT FK_WH5110_5
FOREIGN KEY (VC_PARTNO, NO_WH, NO_LOC, NO_CMP)
REFERENCES GN1020 (VC_PARTNO,NO_WH,NO_LOC,NO_CMP));

ALTER TABLE WH5110 ADD (
CONSTRAINT FK_WH5110_6
FOREIGN KEY (NO_BK, NO_LOC, NO_CMP)
REFERENCES WH1500 (NO_BK,NO_LOC,NO_CMP));

ALTER TABLE WH5110 ADD (
CONSTRAINT FK_WH5110_8
FOREIGN KEY (VC_QCTSTNO, NO_LN4, NO_LOC, NO_CMP)
REFERENCES PR5910 (VC_QCTSTNO,NO_LN,NO_LOC,NO_CMP));

ALTER TABLE WH5110 ADD (
CONSTRAINT FK_WH5610_7
FOREIGN KEY (VC_GONNO, NO_LOC, NO_CMP)
REFERENCES WH5400 (VC_GONNO,NO_LOC,NO_CMP));


CREATE TABLE WH1500
(
NO_CMP NUMBER(4),
NO_LOC NUMBER(4),
NO_BK NUMBER(20),
VC_PARTNO VARCHAR2(22 BYTE) NOT NULL,
NO_BKMNT NUMBER(2) NOT NULL,
DT_BK DATE NOT NULL,
NO_WH NUMBER(4) NOT NULL,
CH_CANCCD CHAR(1 BYTE) NOT NULL,
NO_BKKEY NUMBER(4) NOT NULL,
VC_OBJNO VARCHAR2(25 BYTE) NOT NULL,
NO_LN NUMBER(4) NOT NULL,
NO_QTY NUMBER(14,3) NOT NULL,
NO_OBJTYP NUMBER(4) NOT NULL,
VC_USRCRT VARCHAR2(12 BYTE) NOT NULL,
DT_CRT DATE NOT NULL,
NO_CHANGE NUMBER(4) NOT NULL,
VC_UOM VARCHAR2(5 BYTE),
NO_REFBK NUMBER(20),
NO_RETTYP NUMBER(2),
NO_CAPSTK NUMBER(14,3),
NO_DRCR NUMBER(1),
VC_ASTCD VARCHAR2(12 BYTE),
NO_EXCRT NUMBER(18,6),
NO_CURCD NUMBER(4),
NO_CCCD NUMBER(4),
NO_UCD NUMBER(4),
NO_PER NUMBER(5,2),
DT_OBJ DATE,
VC_REQALLNO VARCHAR2(25 BYTE),
NO_LN1 NUMBER(4),
NO_OUTTYP NUMBER(2),
NO_BALQTY NUMBER(14,3),
NO_ISSBK NUMBER(20),
NO_BALVAL NUMBER(18,6),
NO_PROJCD NUMBER(4),
CH_BKTYP CHAR(2 BYTE),
VC_TXT VARCHAR2(30 BYTE),
VC_APPRID VARCHAR2(25 BYTE),
NO_APPRSTS NUMBER(2),
VC_USRMOD VARCHAR2(12 BYTE),
DT_MOD DATE,
NO_BASVALFC NUMBER(18,6),
NO_BASVALBC NUMBER(18,6),
NO_UNTRT NUMBER(18,6),
NO_CAPREV NUMBER(1),
DT_RETDT DATE,
DT_VALDFM DATE,
DT_VALDTO DATE,
VC_REFOBJNO VARCHAR2(25 BYTE),
NO_LN2 NUMBER(4),
NO_FIFOAMT NUMBER(18,6),
NO_LIFOAMT NUMBER(18,6),
NO_SIMAAMT NUMBER(18,6),
NO_VALOID NUMBER(2),
NO_WTDAAMT NUMBER(18,6),
NO_CNT NUMBER(3),
NO_FBLQTY NUMBER(14,3),
NO_LBLQTY NUMBER(14,3),
NO_SBLQTY NUMBER(14,3),
NO_WBLQTY NUMBER(14,3),
NO_FRATE NUMBER(18,6),
NO_LRATE NUMBER(18,6),
NO_SRATE NUMBER(18,6),
NO_WRATE NUMBER(18,6),
NO_RATE NUMBER(18,6),
NO_DPT NUMBER(3),
CH_RETTYP CHAR(1 BYTE),
DT_APPR DATE,
VC_REM VARCHAR2(200 BYTE),
NO_INVAMT NUMBER(18,6),
NO_DNCNAMT NUMBER(18,6),
NO_DBAMT NUMBER(18,6),
NO_JVAMT NUMBER(18,6),
NO_POAMT NUMBER(18,6),
NO_TRNAMT NUMBER(18,6),
CH_LOCK CHAR(1 BYTE)
)
TABLESPACE PURCHASE
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE INDEX IN_WH1500_8 ON WH1500
(DT_BK)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX IN_WH1500_6 ON WH1500
(NO_LN2, VC_REFOBJNO, NO_OBJTYP, NO_WH, NO_LOC,
NO_CMP)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX IN_WH1500_7 ON WH1500
(CH_CANCCD, DT_BK, VC_PARTNO, NO_WH, NO_LOC,
NO_CMP)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE UNIQUE INDEX PK_WH1500 ON WH1500
(NO_BK, NO_LOC, NO_CMP)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX IN_WH1500_1 ON WH1500
(VC_OBJNO, NO_LN, VC_PARTNO, NO_WH, NO_BKKEY,
NO_OBJTYP, NO_LOC, NO_CMP, CH_CANCCD)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX IN_WH1500_2 ON WH1500
(CH_BKTYP, DT_BK, NO_WH, NO_LOC, CH_CANCCD,
NO_CMP)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX IN_WH1500_3 ON WH1500
(VC_REQALLNO, NO_LN1, NO_LOC, NO_CMP)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX IN_WH1500_4 ON WH1500
(VC_REFOBJNO, NO_LN2)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX IN_WH1500_5 ON WH1500
(NO_REFBK, NO_LOC, NO_CMP)
LOGGING
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


ALTER TABLE WH1500 ADD (
CONSTRAINT PK_WH1500
PRIMARY KEY
(NO_BK, NO_LOC, NO_CMP)
USING INDEX
TABLESPACE INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));


ALTER TABLE WH1500 ADD (
CONSTRAINT FK_WH1500_1
FOREIGN KEY (VC_PARTNO, NO_WH, NO_LOC, NO_CMP)
REFERENCES GN1020 (VC_PARTNO,NO_WH,NO_LOC,NO_CMP));

ALTER TABLE WH1500 ADD (
CONSTRAINT FK_WH1500_2
FOREIGN KEY (VC_ASTCD)
REFERENCES GN4200 (VC_ASTCD));

ALTER TABLE WH1500 ADD (
CONSTRAINT FK_WH1500_3
FOREIGN KEY (NO_CURCD)
REFERENCES GN4100 (NO_CURCD));

ALTER TABLE WH1500 ADD (
CONSTRAINT FK_WH1500_4
FOREIGN KEY (NO_CCCD, NO_LOC, NO_CMP)
REFERENCES GN0410 (NO_CCCD,NO_LOC,NO_CMP));

ALTER TABLE WH1500 ADD (
CONSTRAINT FK_WH1500_5
FOREIGN KEY (NO_UCD, NO_CCCD, NO_LOC, NO_CMP)
REFERENCES GN0610 (NO_UCD,NO_CCCD,NO_LOC,NO_CMP));

ALTER TABLE WH1500 ADD (
CONSTRAINT FK_WH1500_6
FOREIGN KEY (NO_PROJCD, NO_CMP, NO_LOC)
REFERENCES GN0810 (NO_PROJCD,NO_CMP,NO_LOC));

ALTER TABLE WH1500 ADD (
CONSTRAINT FK_WH1500_7
FOREIGN KEY (VC_UOM)
REFERENCES GN1410 (VC_UOM));

ALTER TABLE WH1500 ADD (
CONSTRAINT FK_WH1500_8
FOREIGN KEY (NO_BKKEY)
REFERENCES WH1200 (NO_BKKEY));



Thanks and Regards 
Sunil Shah

Fri Jun 20, 10:24:00 PM EDT  

Blogger Thomas Kyte said....

@Sunil

The sad thing here is, I'm not sure if that is a joke or not these days...

Fri Jun 20, 10:41:00 PM EDT  

Blogger Thomas Kyte said....

@myself

well, at least now I know...

it wasn't a joke

That realization is not a good thing.

Fri Jun 20, 11:30:00 PM EDT  

Anonymous Anonymous said....

Darn!! I am ashamed for Sunil Shah whoever that measle is...

Tom, Unlike you I can't hold my nerves...

Sat Jun 21, 12:33:00 AM EDT  

Blogger Sunil Shah said....

I have a problem while running the below query
Which uses 3 table wh5110 wh1500 and gn1000
I have indexex placed for them
when i forcefuly used one index then , the explain plan for sql query using the index shows me
index skip scan for
b.dt_bk >= '01-apr-2007'
AND b.ch_canccd = 'N'
having cost 232 and cardinality 1436
can i do any more then this or i there a better way to make some different index. using the columns
in where clause



SELECT 'WH',3,SYSDATE,A.NO_CMP,a.no_loc,a.vc_grinspno,A.NO_LN,'WH5110','WH1500','Quantity Mismatch BETWEEN WH5110 AND WH1500'
FROM nealv.WH5110 a ,nealv.WH1500 b ,nealv.GN1000 c
WHERE a.vc_grinspno = b.vc_objno
AND a.no_wh = b.no_wh
AND a.vc_partno = b.vc_partno
AND a.no_ln = b.no_ln
AND a.no_accqty != b.no_qty
AND a.vc_uom = c.vc_uom
AND a.no_loc = b.no_loc
AND a.no_cmp = b.no_cmp
AND a.vc_partno = c.vc_partno
AND b.vc_partno = c.vc_partno
AND b.dt_bk >= '01-apr-2007'
AND b.ch_canccd = 'N'
AND NOT EXISTS (SELECT /*+ ORDERED INDEX (wh1500 pk_wh1500) */ e.no_refbk
FROM nealv.WH1500 e
WHERE e.no_refbk = b.no_bk
AND e.no_loc = b.no_loc
AND e.no_cmp = b.no_cmp
AND e.vc_objno = b.vc_objno
AND e.no_ln = b.no_ln
AND e.no_wh = b.no_wh
AND e.vc_partno = b.vc_partno
and e.dt_bk = b.dt_bk )
ORDER BY a.no_cmp,a.no_loc,a.vc_grinspno;

Sat Jun 21, 11:27:00 PM EDT  

Blogger Thomas Kyte said....

@Sunil Shah

please...

read the original blog post.

then the comments right above.

and think about whether or not someone could actually say anything meaningful here.


I will just say this (something I've said many times before)


loop -- infinite on purpose
say indexes are not all goodness
say full scans not evil
exit when (you believe it)
end loop


give the inputs you have given (almost none, a schema, big spiel), that is about the only output you can expect.


Your query will go very fast if you impose a limit of one row per table referenced in your query. If you employ a business rule that says "only one row per table", you'll solve your performance issues for retrieving data.


Oh, and drop the hints - you are telling us what to do, maybe if you let Oracle figure it out - it'll do better.

Not that the hint:

(SELECT /*+ ORDERED INDEX (wh1500 pk_wh1500) */ e.no_refbk
FROM nealv.WH1500 e
WHERE e.no_refbk = b.no_bk

EVEN COUNTS, since hints use correlation names - and "E" is the only "TABLE" in the query - so the index hint - useless. And, well, given that there is one table "E" only - ORDERED is sort of "inferred". One wonders why one put such a hint in there in the first place (but, hints are cool.... they make us look like we did something 'good')

Sat Jun 21, 11:36:00 PM EDT  

Anonymous Anonymous said....

From Wikipedia:

In 1411, the rule of the Muzaffarid dynasty was established in Gujarat. According to tradition, Sultan Ahmed Shah, while camping on the banks of the River Sabarmati, saw a hare chasing a dog. Impressed by this act of bravery, the Sultan, who had been looking for a place to build his new capital, decided to locate the capital here and called it Ahmedabad.

In 2008, Sunil should say "Ahmedabad post."

Skip scans can be cool, too.

Sun Jun 22, 02:01:00 PM EDT  

Anonymous Anonymous said....

You made a good point, although some commentators seem to have missed the wood for the trees a bit.Your life is so wonderful,Reading your article is a kind of enjoyment.Thank you.


Tactical Flashlights
r c helicopter
video game

Mon Jun 23, 01:59:00 AM EDT  

Anonymous Anonymous said....

I think "information technology" is not attracting the brightest and the best these days. Maybe it is an ongoing result of the dot com collapse.

Mon Jun 23, 03:56:00 PM EDT  

Anonymous Robert said....

maybe it also has to do with the amount of flying you do Tom. What with the extra charges, congestion, ,bad service, bad food, crowded flights, delays, TSA, taking your shoes off, taking your belts off, taking your pants off......I shudder when I think about flying these days.

Tue Jun 24, 04:48:00 PM EDT  

Anonymous Slater said....

Hey Tom maybe this Brittish TV show is something for you :-)
Grumpy Old Men TV show

Maybe we should make a "Grumpy Old DBA's" sitcom for OOW.

Wed Jun 25, 03:02:00 AM EDT  

Blogger Byte64 said....

Tom,
my car won't start too.

How did you manage to start yours?

;-)

I think you should have been more tolerant, the guy didn't even add the magical spell "IT'S URGENT" with five exclamation points...

:-D

Flavio

Thu Jun 26, 05:32:00 AM EDT  

Blogger Jeff Hunter said....

I don't know how you don't go home and beat your dog every day. People try to take advantage of you every day because it's the easy way out and you're too nice to say no. A question gets asked on AskTom about partitions and some dude says "Oh, I understand that, but what about my RMAN problem....". I'd just have to show him the 357mag, close my eyes and walk away. You're allowed a little grumpy every now and then.

Thu Jun 26, 09:25:00 PM EDT  

Anonymous Anonymous said....

Everything goes right way. The problem gives birth for solutions and vice versa. Everything is progressing. The aim of the any progression is to make human life better and "simpler". The results are that you can't repair your car by yourself even you shouldn't :) The same things are all over!

>sterolandro said...
>please, forgive at least the >grammar. Many of us (I count for >one) are not English native >speakers... :-P

Fri Jun 27, 12:32:00 PM EDT  

Anonymous Anonymous said....

Yes! Yes!! YES!!! The whole world is now a cesspool of incompetence, illiteracy and rudery. There's no way forward and we are all doomed to living alongside the culturally bankrupt and technically shabby until we shrug off our mortal coils. Then again, maybe everything's pretty much as it ever was!

Tom, I suggest more roughage in your diet and a few prunes, too. Grumpiness in middle age is often a symptom of decreased "regularity"!

Sat Jun 28, 07:47:00 AM EDT  

Anonymous Anonymous said....

The sporting of a ginger beard is also often associated with grumpiness and adding a dash of vinegary spite to one's blog - see Jonathan Lewis' site for numerous proofs of this. So roughage, prunes and a shave may well help your plight.

Sat Jun 28, 12:46:00 PM EDT  

Anonymous aja said....

It's all going to be moot anyway. As soon as the Large Hadron Collider at CERN goes to full power, it will generate a micro black hole that will consume the entire earth in less that the blink of an eye. Then, some 300 million years in the future, some astronomer in an observatory some 300 million light years away will say something like "Gee, I wonder what happened there? I thought I saw signs of intelligent life, then all of a sudden it went away. Oh well. Too bad."

So, let's all keep things in perspective and don't sweat the small stuff. I do not get stunned by anything I see in this profession any more. I just kind of sigh and handle it as best I can. Giving the same advice over and over to people who do not seem to learn. Providing samples and solutions to the same problems over and over. Those who get it soon move on and are replace by others that do not.

So, to quote Dr. Sidney Feeman "When things get too much for you, take my advice. Pull down your pants and slide on the ice."

Mon Jun 30, 04:33:00 PM EDT  

Anonymous Stephen Windsor said....

I haven't read all 78 comments, but I can echo the same sentiment. I've been an oracle DBA for about 15 years now, and gone are the days where software versions stuck around for a few years. Oracle products are so complicated and so vast, I honestly feel that I will never understand them the same way I used to understand version 8, or even version 9. Add to that, that I simply don't have time to do what I consider casual research into the product... half my day is putting out fires (like today).

I do try to exhaust all online possibilities before asking a question... I encourage my application programmers to utilize the 'embedded query' in the from clause, to select only that which they need... it greatly reduces the need for complicated where clauses...

Tue Jul 01, 01:46:00 PM EDT  

Anonymous Jonathan Brain said....

Over the years I have been amazed at your patience and quality of answers.

However, I do feal that more recently you might be in danger of becoming as impatient, terse, rude etc as myself (a real quote about me!) - I could never have done what you have done with Ask Tom.

Ask Tom has a wealth of information and I often do a search on it - I am sure I have seen it here somewhere....

But I wonder if the current format has had its day and it needs a rethink / you need a well deserved break.

Wed Jul 02, 04:41:00 AM EDT  

Anonymous Jonathan Brain said....

Over the years I have been amazed at your patience and quality of answers.

However, I do feal that more recently you might be in danger of becoming as impatient, terse, rude etc as myself (a real quote about me!) - I could never have done what you have done with Ask Tom.

Ask Tom has a wealth of information and I often do a search on it - I am sure I have seen it here somewhere....

But I wonder if the current format has had its day and it needs a rethink / you need a well deserved break.

Wed Jul 02, 04:43:00 AM EDT  

Blogger Kevin said....

My car won't start either.

Wed Jul 02, 09:29:00 AM EDT  

Anonymous Anonymous said....

My car wouldn't start too, but I just knew it had to do with the auxiliary constructs.

Thanks Tom!

*Happy cruisin' again*

Wed Jul 02, 03:20:00 PM EDT  

Blogger rhalabicki said....

I wonder what would happen if you outsourced your asktom site.

Would the people answering the questions be the same ones asking. It would create a paradox! :D

Fri Jul 04, 10:39:00 AM EDT  

Anonymous Anonymous said....

I think there should be a layer between Tom and the community, so if someone asks a question like the one which this post was created about, the community could scold the daylights out of the person who asked. Questions that are actually worth Tom's time could be flagged for him to look at, while stupid questions are rejected & deleted. Tom should appoint some faithful followers or colleagues to be his first line of defense and screen the questions on AskTom. I'll bet there are ten thousand people who would do it for free.

Sat Jul 05, 11:50:00 AM EDT  

Anonymous nameless said....

You said on occasions that you like teaching. That’s a profession which has left many disillusioned ... the bigger the audience the more likely to happen. I’d say, take pride in knowing you have truly influenced some and helped so many ... and try ignoring the obtuse.

Whether you’re getting grumpier or not is beside the point and certainly not a vote to be counted in the community (I like that word as much as I do evangelist ... my own grumpiness, I guess).

Asktom has started as a hobby and as progressed to a professional outfit. Is there anything left from the hobby part? Do you still enjoy doing it?

Sun Jul 06, 09:03:00 PM EDT  

Anonymous Daniel Pietraru said....

The most amazing thing is the realization of the fact that most people asking questions on technical forums are people with a high level of education or getting there.
Not being able to ask intelligible questions is not only a sign of lack of respect for the potential helpers but also a sign of a bad education. And not only from the politeness point of view. Not being able to ask a question in a smart way is crippling.

Mon Jul 07, 12:41:00 PM EDT  

Anonymous Anonymous said....

I think in general it's the generation of people coming up. They tend to be impatient, feel they are entitled and somewhat lazy. They want someone to do all the work for them and thus the questions are not well thought out and show a lack of basic research into many of the issues they have raised. Did they try Google? A lot of times, no they didn't.

Mon Jul 07, 05:36:00 PM EDT  

Anonymous Anonymous said....

and by the way...
My brain won't start :-)

Mon Jul 07, 05:38:00 PM EDT  

Anonymous Leslie said....

You need several 'Toms'. There was never just one 'Dear Abby' (not that there is any comparison). You could take volunteers to weed through the junk questions, and leave you the techie puzzlers.

Thanks for all your hard work. We do appreciate you, Tom.

Tue Jul 08, 07:50:00 PM EDT  

Blogger kim said....

When reading some questions, not only here but on the Oracle forums also, I wonder if some really know what Oracle is or they just have a guide next to them and tend to "go with it" and see where they come out. Sometimes I even wonder how some of these people can work as Oracle DBAs.

So no it's not you, I think a lot of people agree with you.

Wed Jul 09, 07:33:00 AM EDT  

Anonymous Anonymous said....

You can't pick and choose what people leave on your public site. You can choose whether you respond or not.

If you continue to be grumpy about it, people will move away from Oracle because its too clubby. Same applies to forums.oracle.com.

Your role, and that of other contributors to forums.oracle.com is that of a messiah. That entails saying the same things over and over again, to new converts as you come across them. and politely.

Its frustrating, certainly if you've been doing it for ten years. If you don't like the heat in this particular kitchen, then pass the baton and let someone else pick up the work.

Sorry, but thats the way it is.

Dave Martin

Tue Jul 15, 04:29:00 AM EDT  

Blogger Thomas Kyte said....

@Dave Martin

I don't mind saying the same thing over and over again.

I do that - that is not a problem. (By the way, use bind variables...)

What I am minding is the *question* or lack thereof. In the year of the short attention span, the ability to simply phrase a question someone can actually answer seems to be disappearing.

Repetition is the key to success, Repetition is the key to success,
...


but the ability to ask a well formed question........

Tue Jul 15, 07:38:00 AM EDT  

Anonymous xplain_plz said....

Tom,

Having worked in hardware support in the past I have to ask...

Is/Was the car plugged in?

Fri Jul 18, 02:20:00 PM EDT  

Anonymous JohnA said....

I would say that you are getting grumpier - Or maybe just losing your memory. Back in 98 when I first started reading/answering mailing lists, those kinds of questions were all too common.

Thu Jul 24, 04:38:00 PM EDT  

Anonymous David Hecksel said....

> Car won't start ( electric or gas )

Put the key in?

Sun Jul 27, 12:15:00 PM EDT  

Anonymous Anonymous said....

You are definitely a real grump these days - and have become much more so over the past 2 years. Maybe it's time for a change? I have been an Oracle DBA for 6 years and want to thank you sincerely for being, in no small way, a real benefit to me, with respect to Ask Tom. But, even though I've been involved with Oracle for a lot less time than you, I am feeling the "7-year itch"; perhaps you are too?

Wed Aug 06, 03:48:00 AM EDT  

Anonymous Troy said....

Tom, I have observed this same decay over the years and I have come to blame it on all of the great work that has been done in the name of user-friendly systems. I see it as a classic case of the Law of Unintended Consequences.

Back in the day, doing ANYTHING with computers required years of study and understanding of a myriad of technologies. The folks doing computer work back then (and consequently asking the questions) tended to be logical thinkers with broad technological understanding and well developed problem solving skills. This generated much better questions.

As we've made advances in the name of ease of use and computers have become "user-friendly", we have essentially lowered the bar on what it takes to hold a job as a computer professional. This new breed of point-and-click "professionals" seems to think that as they were unshackled from the bounds of command line syntax, they also magically have no need for a solid theoretical background. The GUI IDEs with their pop-up help have made it possible to produce code without the burdensome need of knowing what one is doing. They base their problem solving skills on how well they can formulate a GOOGLE search. Question quality has suffered accordingly.

We used to see it as the difference between DataProcessing degrees and ComputerScience degrees. The DP folks tended to focus on how to push the buttons. The CS folks tended to read Knuth and Wirth and focus on why it was better to push this button here than that button over there. The sad part is that, no matter how much easier the buttons have gotten to push, it is still important to select the right button. And the group of bright folks such as yourself that have the dedication to do the legwork necessary to select the right button seems to be dwindling every year.

Thu Aug 07, 10:39:00 AM EDT  

Blogger sLars said....

My experience says that is during the process of forming the question the answer will get there as well.
You really really have to think carefully about what problems you have and boil it down to what might be the error. Most of the time the error was in your head...

But the questions sent to your forum is...well...since english is not my native tounge I have no word for it. It is lack of just anything that IT professionals stands for. Or at least stood for.

I think that the reason is that there are many many new people to Oracle software, that is, of course generally a good thing. The easiness internet provides gives the person asking the question a impression of well God knows what.

Always when I ask someone a question it is a matter of give and take. The one's asking these questions are just energy takers. And having duplicate clauses in the SQL statement shows that it is panic button. Since long ago.

Sun Aug 17, 08:08:00 PM EDT  

Anonymous Jim said....

a) I'm getting grumpier as I get older
b) people are losing the ability to phrase a question.

Why can't it be both?

Thu Sep 11, 03:57:00 PM EDT  

Anonymous Dave said....

Hi Tom. I started learning APEX on my own. There aren't a lot of books out there. I purchased one called Pro Oracle Application Express. I was a bit hesitant not recognizing the authors, but after reading the Foreword(by you), felt more comfortable. I've read the first two chapters and I really like the insight that is given that I haven't found elsewhere.

Tue Sep 23, 03:58:00 PM EDT  

Anonymous russellh said....

I think the heart of the problem is what is happening to American culture.

"It is an odd reflection on the priorities of our society," Thiel says, "that we value finance over technological innovation."

http://money.cnn.com/2008/09/29/technology/View_from_Valley_OBrien.fortune/index.htm?postversion=2008093012

"Meet Your New Recruits: They Want to Eat Your Lunch"

http://www.businessweek.com/magazine/content/08_21/b4085042677127.htm?chan=bschools_undergraduate+business+programs+--+new+design_career+placement

When I was an undergrad in the early eighties, I remember that many of the smartest, most ambitious people wanted to pursue careers in technology, science, and computers. Sure, plenty of them wanted to get rich, but they also wanted to do something useful. Maybe start a company that provided software to make businesses more efficient or to automate manufacturing.

These days, when I talk to young people, it seems that only the money part matters. Science and engineering are seen as a waste of time. Go to Wall Street and make your fortune.

Maybe that sounds cynical, but it sure seems to be what is happening.

Tue Sep 30, 03:08:00 PM EDT  

Anonymous Anonymous said....

"My car won't start"

How I laughed!

I'm still lauging.

I stumbled upon this post as I was
setting up to ask you a question.
By almighty Bob I will prepare thoroughly!

Rob

Fri Oct 10, 09:02:00 AM EDT  

Anonymous Dave said....

I'm following your asktom site for a number of years now but I can't say that you are getting grumpier.

Heck, if you still answers questions like this :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:18909623623984#1248047800346692135

then I think that that you are more than reasonable, EVEN WITH THE SHOUTING!

BTW: Get rid of the Lada, they never start properly! ;-)

Tue Oct 21, 06:14:00 AM EDT  

Anonymous Anonymous said....

so why does the new myspace suck everythings scrunched together and its just not as good as it was before and i think that you or someone else on the myspace staff should change it back thanks
teddy

Thu Nov 06, 09:22:00 AM EST  

POST A COMMENT

<< Home