SQL & PL/SQL

In remembrance of last year let me share my collected (funny) quotes from this forum:

----------
| 2010 PL/SQL Forum |
----------
Aketi> This thread is to discussion what is difference between wmsys.wm_concat and ListAgg.
michaels2> You might then also introduce a comparison with 11g's similar (undocumented) stragg function:
BluShadow>Have they been letting Tom at the source code again!
----------
Slow_Moe>There is most certainly a difference here. I use Toad 10.5.1.3 in both cases, just pressing the button.

We do have some issues with the database, performance wise, though. I'm starting to think it is
not completely sane (upgraded from 9i, not installed from scratch).

BluShadow>That could be an issue in Toad.
We've just installed Toad 10.5.1.3 and they certainly have seemed to manage to introduce a multitude of bugs in it.
----------
Op presenting some large select> This was actually a query that someone else put together and asked me to fix - so I'm not actually sure what their requirements are.

Tubby> I think you should stop immediately what you are doing, i mean this seriously.
If you don't understand the requirement there's very little chance you'll have any measurable success tuning this. The bulk of tuning (in my experience anyways) comes from understanding the data, their distributions and the exact requirement needed by the query in question. Sure you can throw in the odd bit of technical magic to save the day, but i would say this is the exception and not the norm.
It's entirely possible you could transform this query into an elegant 20 line query that executes in a fraction of the time, however, that's never going to happen without understanding the question you are trying to answer.

----------
oP> Thanks for Information.I will do in the below method.Confirm me it is OK or not
amarkiewicz >I'm not exactly a machine and can't parse it as well as the db can. It looks like a good start. Try it out and see what happens.
----------
sybrand_b>BTW will anything bad happen if you post a question consisting of more than 1 line. Salary substracted? Fingers chopped off by your boss?
----------
John Spencer (about naming conventions in Sql Server Db)>
Given that an unqualified name is usually resolved (only?) in the current database, and doing master.dbo.sp_help table_name gets tedious after a while, they chose to use the sp_ convention to "force" initial resolution to the master database.
Billy> So it was introduced just because they were lazy to type a fully qualified name and rather hacked the object resolution scope and introduce exceptions to what should be sacred grounds - consistent behaviour in scope resolution....
Makes sense... if you happen to be a Ferengi and dislike everything Vulcan...
----------
OP>I am finding out that explain plan and running the optimzer tools in SQL Navigator is much to read than in sql plus.
SomeoneElse>I shall alert the media.
----------
OP>My database session has been locked. Please suggest something.
Karthick_Arp> Kill the session.
Saubhik>Find the lock and kill....
sree>pls try alter system kill session 'SID,SERIAL#
Ulfet>Firstly investigate why session was locked. If necessary kill session.
Sven>All of you are session terrorists! All you want to do is kill...kill...kill! ;)
William>The killing is regrettable but necessary to liberate the session ;)
Billy>With most the advice offered here, you just as well can get some PE4/C4 (plastic explosives) and blow up the server. That will not only kill the "locked" session, but also kill any other potential and imagined problem arising from that server.
As William said - a session is never locked. There is no such concept at session level in Oracle. A session can be blocked - something very different from being locked.
A session can also become unresponsive for a number of other reasons. Stuck in an infinite loop courtesy of buggy code. Doing tons of I/O courtesy of bad SQL design that results in a cartesian join. Or slow because of just plain shoddy PL/SQL code. Etc.
Killing that session will not tell you anything about what happened, what went wrong, and how to prevent that from happening again.
The very first step when dealing with any IT problem is to identify the problem. Once you know WHAT the problem is, you will know HOW to address it. Killing the problem.. that only works as a solution in the military. Not in software engineering.
----------
Toon Koppelaars>Don't believe everything you read on the internet (even not this...)
----------
OP> what is the view?
JS1>It's what you see when you look out your window.
Someoneelse>http://theview.abc.go.com/
----------
OP> the application generates dynamically the condition in the form :
where <condition_string> in ( 'red','blue')
Billy> You need to consider what that means in actual terms.. in terms of the data model, in terms of dealing with end user requirements, and it terms of database performance.
In most cases. this approach is fatally flawed. Is due to a poor data model and not correctly dealing with end user requirements in a performant and scalable fashion. Never mind trashing fundamental database concepts dealing with performance and sharable SQL.
OP>of course, thats what I know. I looked for a quick workaround.
Billy> So instead of running into a brick wall at a 90 degree angle, you want to see if it hurts less using a 60 degree angle?
I would have thought that not running into the brick wall at all was the aim...
----------
BluShadow(after some heated discussion about pros and cons of SQL language syntax elements)>

SQL> select * from dual;

SQL> please process your datafiles and return the data from all of the columns from the table that is referenced by the name of dual;

I know which I'd prefer....
Munky>I like the way you said 'please'. Maybe, rather than all this 'tuning' nonense - response time could be directly correlated to how polite you are!
Boneist>That's certainly true of the forums, if not the database! *{;-)
Karthick_Arp>I would wish for MAKE_ME_RUN_FAST hint
Munky>But surely this would be better?

SQL> set timing on
SQL> Hi ther Mr. DB (I do hope you don''t mind me calling you that?).
     I''ve got some super stuff for you to do and I would be just
     thrilled if you could do it expediently.
     However  - no pressure - you just do it at your earliest convenience.
     What I''m look for is all of the data from all of the columns
     that you've got in that dual fellow.
     Would that be okay?
     Jolly good, and thanks again old boy! xxx;
 
D
-
X
 
Elapsed: 00:00:00.01
SQL>

----------
Hari>Sorry Blushadow if my information went wrong, I copied it from a OTN Forum
Blueshadow>Don't believe everything you read on the OTN forums, it's full of people posting inaccurate information. ;)
----------
William (in the "Wishlist" thread">I'd like BETWEEN to have an optional EXCLUSIVE clause for each end of the range so we didn't have to keep rewriting it using AND x < y etc. ----------
OP>If I need to remove some sensitive data from single field of a row in database (10g and 11g), is there a function provided by Oracle Db to do that? Just simplely set the value to null is not enough. I heard that 3 passes or more with certain set of values can do the trick. But I am not quite sure about it. Any suggestions? Thanks.
hoek>Tricks don't exist in Oracle database-world. Please turn you mumbo-jumbo into somethng humans and databases can read and understand.
Munky>You could get Prime's armed DBA to hack away at it? When NULL is not enough, use a chainsaw (sure I heard that somewhere?).
John>Wouldn't it be a bit insensitive to shoot sensitive data ? (armed DBA ... could be a new role ;) )
Munky>{code}SQL> CONNECT SYS as ARMED_DBA{code}
----------
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1156151916789#25335122556076

user11268895>..but i was wondering why:
user11268895> select count(distinct rowid) from mytable
user11268895> select count(rowid) from mytable
user11268895>were not rewirtten to the same thing (syntaxically/logically they are not the same, but an optimization can be made there (removal of the aggregate))...
BluShadow>It's probably not automatically rewritten to teach you a lesson for not understanding what rowid is and not knowing that rowid's are already distinct. :P
3360>Also it is a game of catch up. When Oracle protects developers from doing one stupid thing they seem to be infinitely resourceful in coming up with new ways to shoot themselves in the foot.
http://www.quotedb.com/quotes/2701
--------------
op> what is scalar subfactory .what is the purpose of it.
MichaelS>Do you - by any chance - actually mean Scalar Subquery?
op> scalar subfactory usage in performance tuning
WHiteHat>do you mean a sub-contracted industrial plant specialising in extreme musical instrument manipulation?

--------------
OP>I am getting an ORA-03113: error when executing the below trigger
CharlesRoos>This happens when you come to work in the morning run first query without reconnecting to database firstly.

--------------
OP: find first three highest salary from emp for each dept
Sven: John, Paul and George.
Munky: Got em! They were hiding under my desk! Do I get a reward for returning them safely?
OP: query to find first three highest salary from employee table for each department
Maxim: But really interesting is , what their salaries are... And whether their company still hire...
Munky: "query to find first three highest salary from employee table for each department " => I've have made the appropriate enquiry. Hope this helps!
Dbb: I suspect it is for fire them...
Riedelme: "Sven wrote: John, Paul and George." What do you have against Ringo?????
Munky: The OP only wants to fire the three that earned the most (obviously forgetting any additional income made from doing voice overs for children's programs)
Sven: Afaik Ringo doesn't earn as much as the other three.

--------------
OP: asking some question about dependencies
OP (30 minutes later): Is any one there????
Someoneelse: "Just nod if you can hear me"
OP: I'm noding can u c me?
--------------
Jay> First of all, whenever i try and put my attempted code for this in a thread it says 'sorry content not allowed'.
BluShadow> It could be that your code contains some words that aren't permitted on the forums. If it contains certain words that spammers use a lot e.g. "S h o e s" then it may be rejected by the forum on the belief that you're trying to post spam.
Billy> Or "d i s c o u n t" and similar words..
Munky> But not 'bollocks'! <cringes - sorry couldn't resist>
BluShadow>Of course not, we're allowed to talk bollocks. We do most of the time anyway. ;)
Mosaq> and some of it is necessory sometimes ;-)

---------------------
| APEX forum 2010 |
---------------------
Peter to Carl>but if you say it's possible, it should work!
carl>I say all sorts of things, that doesn't mean that it "should" work just that I think it "should" work . ;)
------------------------
j4john>Now that Craig Venter is creating synthetic life and the Higgs boson is wearing a false nose to evade detection, it's probably time to up the game in Apex file uploads.