Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL Tuning havng MAX function 1
- Thread starter siraj777
- Start date
-
1
- #2
jaggiebunnet
Programmer
Have you tried creating a function based index on MAX(rcqtr_num)? Can you post your explain plan for the query?
- Thread starter
- #3
Try running separately the subquery
and then the main query using the result of the above:
If it takes total significantly less then 30 sec, it makes sence to separate these queries using PL/SQL.
Code:
SELECT MAX(rcqtr_num) FROM rcprod11
Code:
SELECT quarter
FROM rcqtr
WHERE rcqtr_num = [b]the_result[/b]jaggiebunnet
Programmer
nagornyi that is possibly the silliest thing I have ever heard.
siraj777 are the statistics on the table up to date? I think it would be a good idea to try the function based index on rcprod11(rcqtr_num) as that will be quicker than just a normal index. also you could try:
But I recommend the FBI for this sort of thing.
siraj777 are the statistics on the table up to date? I think it would be a good idea to try the function based index on rcprod11(rcqtr_num) as that will be quicker than just a normal index. also you could try:
Code:
SELECT r.quarter
FROM rcqtr r, (SELECT MAX(rcqtr_num) mymax FROM rcprod11) a
WHERE r.rcqtr_num = a.mymax;But I recommend the FBI for this sort of thing.
SantaMufasa
Technical User
Nagornyi,
Take heart...There is a great book entitled, "They All Laughed", which chronicles some of the greatest inventions of all time, at which the general public first laughed at the notion thinking it was a "silly thing". I personally like your idea. It is beautiful for its simplicity: You suggestion first models an improved solution, then suggests a correct (non-manual) solution if the results are positive. Provided there is an index on rcqtr's rcqtr_num column, yours is an elegant solution because of its simplicity.
The best measure of "brillian silliness" is for siraj777 to "set timing on" and "set time on" (or use some other method to measure code speed, and to publish the comparative result of each suggestion, above. Then we would see how "silly" your solution is. I'd bet we'd see that your solution is among the quickest.
So, in the future, if someone suggests that your solution is "funny" or "silly", take it as a compliment...it could mean that either the critique hadn't thought of the idea before you, or it simply didn't fit the critique's paradigms.
Cheers,
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
(aka Dave of Sandy, Utah, USA @ 15:23 (31Jul04) UTC (aka "GMT" and "Zulu"), 08:23 (31Jul04) Mountain Time)
Take heart...There is a great book entitled, "They All Laughed", which chronicles some of the greatest inventions of all time, at which the general public first laughed at the notion thinking it was a "silly thing". I personally like your idea. It is beautiful for its simplicity: You suggestion first models an improved solution, then suggests a correct (non-manual) solution if the results are positive. Provided there is an index on rcqtr's rcqtr_num column, yours is an elegant solution because of its simplicity.
The best measure of "brillian silliness" is for siraj777 to "set timing on" and "set time on" (or use some other method to measure code speed, and to publish the comparative result of each suggestion, above. Then we would see how "silly" your solution is. I'd bet we'd see that your solution is among the quickest.
So, in the future, if someone suggests that your solution is "funny" or "silly", take it as a compliment...it could mean that either the critique hadn't thought of the idea before you, or it simply didn't fit the critique's paradigms.
Cheers,
Mufasa(aka Dave of Sandy, Utah, USA @ 15:23 (31Jul04) UTC (aka "GMT" and "Zulu"), 08:23 (31Jul04) Mountain Time)
jaggiebunnet
Programmer
You suggested removing one sql statement from its native environment and splitting it into two and putting it into a procedure.
A procedure that would then be compiled into pcode and have to switch back to the sql environment at run time to execute the sql.
That is very lazy and sloppy programming.
A procedure that would then be compiled into pcode and have to switch back to the sql environment at run time to execute the sql.
That is very lazy and sloppy programming.
I've just finished reengineering some report that took about 1 hour to run. The report used just straight SQL to bring the data. Now the report uses a dozen of stored procedures, half a dozen of temporary tables, but takes less then 1 min to execute. Users are extremely happy and do not care of what technical means I used to speed it up. They have very vague idea about SQL, know nothing about PL/SQL and pcode, but they need the report fast, and they are getting what they need.
SantaMufasa
Technical User
Hmmmm...Jaggie...After my having taught Advanced PL/SQL from 1990 through 1997 for Oracle Education, you are the first from whom I have ever heard that suggests "A procedure that would then be compiled into pcode and have to switch back to the sql environment at run time to execute the sql...is very lazy and sloppy programming." Do you have a "chapter and verse" reference supporting that notion from some source besides yourself? Such substantiation is pretty important. I remember a wise man once suggesting,
Standard systems design methodology suggests: 1) Identify goals and objectives, 2) Identify alternatives to achieve the goals and objectives 3) Identify tangible and intangible costs of each alternative 4) Choose and implement the least-cost alternative.
Discounting Nagornyi's alternative out-of-hand because his suggestion "...is possibly the silliest thing I have ever heard", falls outside accepted systems-design discipline...I don't see any support from your "facts and figures". I assert that whatever coding method (whether straight SQL or PL/SQL) runs the fastest and incurs the least cost to run is also "The Best" method, regardless of the supposed "lazy" or "slop" factor of the programmer.
If you don't agree, that is okay. We'll just agree to disagree.
Mufasa
(aka Dave of Sandy, Utah, USA @ 15:59 (31Jul04) UTC (aka "GMT" and "Zulu"), 08:59 (31Jul04) Mountain Time)
Standard systems design methodology suggests: 1) Identify goals and objectives, 2) Identify alternatives to achieve the goals and objectives 3) Identify tangible and intangible costs of each alternative 4) Choose and implement the least-cost alternative.
Discounting Nagornyi's alternative out-of-hand because his suggestion "...is possibly the silliest thing I have ever heard", falls outside accepted systems-design discipline...I don't see any support from your "facts and figures". I assert that whatever coding method (whether straight SQL or PL/SQL) runs the fastest and incurs the least cost to run is also "The Best" method, regardless of the supposed "lazy" or "slop" factor of the programmer.
If you don't agree, that is okay. We'll just agree to disagree.
Mufasa(aka Dave of Sandy, Utah, USA @ 15:59 (31Jul04) UTC (aka "GMT" and "Zulu"), 08:59 (31Jul04) Mountain Time)
- Thread starter
- #11
jaggiebunnet
Programmer
Santa
Far be it from me to tell such a teaching guru about pcode and context switching, but maybe if you have never heard of it you should refund some of the money back to your students.
Pcode is byte code stored in the oracle database that is interpreted at run time by the VM that plsql runs under.
For an example from the oracle docs:
Plsql runs in it's own engine, when it executes an sql statement it has to switch context to the sql engine therefore causing an added overhead to the process:
For someone to say instantly to run such a simple piece of sql inside a procedure shows no creativity but rather a lack of knowledge and laziness.
You [bold]liked[/bold] the idea for its simplicity, when in fact it would add maintenance and extra code for no reason to the system. Perhaps you should rethink your System Methodology.
But sure I agree not to agree. Even with such a tumultuous Oracle Teacher as yourself.
Far be it from me to tell such a teaching guru about pcode and context switching, but maybe if you have never heard of it you should refund some of the money back to your students.
Pcode is byte code stored in the oracle database that is interpreted at run time by the VM that plsql runs under.
For an example from the oracle docs:
Plsql runs in it's own engine, when it executes an sql statement it has to switch context to the sql engine therefore causing an added overhead to the process:
For someone to say instantly to run such a simple piece of sql inside a procedure shows no creativity but rather a lack of knowledge and laziness.
You [bold]liked[/bold] the idea for its simplicity, when in fact it would add maintenance and extra code for no reason to the system. Perhaps you should rethink your System Methodology.
But sure I agree not to agree. Even with such a tumultuous Oracle Teacher as yourself.
jaggiebunnet
Programmer
Cool, thanks for letting us know that it worked.
SantaMufasa
Technical User
Jaggie,
Whoa...I'm happy to discuss these issues on their merits, without your needing to shift gears into "insult/dripping sarcasm" mode.
Let me make sure I understand your assertions correctly: 1) We should always opt for pure SQL code over PL/SQL when results are identical, 2) Use of PL/SQL versus "equal" SQL code is a sign of "...no creativity (and) a lack of knowledge and laziness."
Is this correct?
Whoa...I'm happy to discuss these issues on their merits, without your needing to shift gears into "insult/dripping sarcasm" mode.
Let me make sure I understand your assertions correctly: 1) We should always opt for pure SQL code over PL/SQL when results are identical, 2) Use of PL/SQL versus "equal" SQL code is a sign of "...no creativity (and) a lack of knowledge and laziness."
Is this correct?
- Status