Printable View
Hi,
I am writing a query as
select * from test
where trade_date = (select max(trade_date) from test where trade_date <> trunc(sysdate));
where I want the record having the max trade_date and which is not todays date.
The data in the table looks something like this
TRADE_DAT SHARES
--------- ----------
22-SEP-02 200
21-SEP-02 300
20-SEP-02 400
24-SEP-02 100
is there a better way of writing this query.
Thanks
If there was a way that you could store the max(trade_date) before today without having to compute it.
You could cache the data in a table and write a pl/sql procedure that could run shortly after midnight every day which would update the value. Why calculate the value many times a day when you can calulate it once a day. I'm guessing that it is doing a select on a detail table that has a lot of rows. Which is adding unnecessary overhead.
You could even write a function to return the max trade date.
select * from test where trade_date = getmaxtradedate();
"<>" in the where clause will not induce index.
If trade_date is always less than Sysdate and an index is available on trade_date column, then use LESS THAN "<" and remove truncate function.
select * from test
where trade_date = (select max(trade_date)
from test
where trade_date < sysdate);
Exactly as Tamilselvan said.
Do not implement it as a function as gandolf989 suggested - functions add unnecessary overhead.
Additionally, we need to know if that column is indexed, and whether or not this simplified example is anywhere close to what your real-world SQL will be. This sql is trying to say 'get me the latest entry in the entire table.
However, unless there is a unique index on that date column, it is possible to return more than one row from this SELECT. Is that what you want?
If the column were indexed, a simple MIN/MAX lookup by the optimizer would get the date you want followed by a range scan of the index to get the actual row. This should all be very fast. However, if in the real world you are applying multiple predicates to your actual statement, you must consider more. Here's another version of the statement:
In this case, you will want to make sure there is an index on SALESPERSON_ID, TRADE_DATE.Code:SELECT
*
FROM
TEST
WHERE
SALESPERSON_ID = :SALESPERSON_ID AND
TRADE_DATE = (
SELECT
MAX(TRADE_DATE)
FROM
TEST
WHERE
SALESPERSON_ID = :SALESPERSON_ID AND
TRADE_DATE < TRUNC(SYSDATE)
)
If you do not have the appropriate index for this query (or don't want to make one), another solution is:
With this solution, the table is only accessed once instead of twice. However, only the SALESPERSON_ID index will be used, if it exists. A SALESPERSON_ID, TRADE_DATE index, if it existed, would not be used fully (meaning the second column would be irrelevant). That is why, if a SALESPERSON_ID, TRADE_DATE index exists, use the original sub-select solution. If not, this solution may prove faster.Code:SELECT
*
FROM
(
SELECT
T.* ,
ROW_NUMBER()
OVER(
ORDER BY TRADE_DATE DESC
)
AS RN
FROM
TEST T
WHERE
SALESPERSON_ID = :SALESPERSON_ID AND
TRADE_DATE < TRUNC(SYSDATE)
)
WHERE
RN = 2
HTH,
- Chris
Use the following SQL.
I changed <> to > and <.
This uses index.
select * from test
where trade_date = (select max(trade_date)
from test
where trade_date < sysdate and trade_date > sysdate);
in Chris post, if your table is big then second solution is always better, the consistent gets is much much smaller.
2 weeks ago I had a very similar query in a 5 millions rows table, with correlated query (indexed column) it took 17 minutes, with analytic functions 7 minutes with 10% consistent gets of correlated query
If this value doesn't change during the day then why keep performing queries to find a value that doesn't change. If it does change during the day the then some type of correlated subquery is needed. Which takes more effort a function that returns one value from a table with one record, or a range scan on an index for a table that could be quite large?
Never say always (and never say never:D). Sometimes it is much better, sometimes it is worse. There appear to be a lot of variables and I haven't been able to pin them all down yet. The MIN/MAX optimiser step is very fast, so if a good index exists, it is hard to beat.Quote:
Originally posted by pando
in Chris post, if your table is big then second solution is always better, the consistent gets is much much smaller.
Unfortunately, you are mixing solutions. Yes, if they keep querying the exact same value throughout the day (which rarely happens in the real world), then storing it somewhere (most likely as a de-normalized column in another table) would likely be more efficient. In either case, however, SELECTing that value from whichever table *directly in the sub-select* would be faster than having that same SELECT in a function. The function solution causes the function's statement to be parsed, etc. for every row as well as a context switch for every row. This is an awful lot of overhead. While using PL/SQL functions in SQL is necessary for certain situations, it should be avoided where possible.Quote:
Originally posted by gandolf989
If this value doesn't change during the day then why keep performing queries to find a value that doesn't change. If it does change during the day the then some type of correlated subquery is needed. Which takes more effort a function that returns one value from a table with one record, or a range scan on an index for a table that could be quite large?
- Chris
your function still has to read the table to get the maxvalue right?
nothing is saved with a function
I am trying to get the latest entry from the table which is before sysdate. it can be sysdate-1 or sysdate-100.Quote:
Originally posted by chrisrlong
Additionally, we need to know if that column is indexed, and whether or not this simplified example is anywhere close to what your real-world SQL will be. This sql is trying to say 'get me the latest entry in the entire table.
Yes, the Trade date column has a unique index on it and should return only one row.Quote:
Originally posted by chrisrlong
However, unless there is a unique index on that date column, it is possible to return more than one row from this SELECT. Is that what you want?
Using the above query returns no rows and will never return any rows :-)Quote:
Originally posted by tamilselvan
Use the following SQL.
I changed <> to > and <.
This uses index.
select * from test
where trade_date = (select max(trade_date)
from test
where trade_date < sysdate and trade_date > sysdate);
I'm curious Ronnie, does the value returned change during the day? Also, if the same function is called many times during the day does it really get parsed every time. I don't think it does. I think that as long as a function stays cached it will stay parsed. And besides by creating a function you will prevent yourself from coding this logic in multiple places. Which leads to the axiom code once use many. Why keep coding the same logic over and over again when you can leave it in one place? If it is related to other code then create a package otherwise leave it as a stand alone function. Too many times I have heard a programmer say we can't change that because it is hard coded in too many places. If business logic is encapsulated into a set of functions then you can easily change how this value is calculated without changing a lot of code. But don't listen to me. This is alos one of the things that I picked up from Steven Feuestein, you might want to listen to him.
Test it. The SQL will absolutely be parsed and executed each time (at least in 8i). Of course, only the first time (if not already cached) will be a hard parse - the rest will be soft. But regardless, it is a whole lot more overhead than the in-line sub-select. Sorry.Quote:
Originally posted by gandolf989
Also, if the same function is called many times during the day does it really get parsed every time. I don't think it does. I think that as long as a function stays cached it will stay parsed.
I'm glad you brought that up.:) The proper way to handle this issue is to indeed create a function, but the function simply adds SQL to a string.Quote:
Originally posted by gandolf989
And besides by creating a function you will prevent yourself from coding this logic in multiple places. Which leads to the axiom code once use many. Why keep coding the same logic over and over again when you can leave it in one place? If it is related to other code then create a package otherwise leave it as a stand alone function. Too many times I have heard a programmer say we can't change that because it is hard coded in too many places. If business logic is encapsulated into a set of functions then you can easily change how this value is calculated without changing a lot of code.
The process should be something like:
- Build static and unique parts of SQL string.
- Call procs to build re-usable parts of SQL
- Use code and procs to build variable parts of SQL
- Put it all together
- Execute it
Of course, I have a lot of supporting procs to make this all very easy, but this is the proper solution, IMHO.
Again, using functions *in your SQL* is innefficient, period. You don't want to do it if you don't have to. However, as you mention, you also want to consolidate code as much as possible - re-usability is critical. The solution is to code functions that generate the oft-used pieces of SQL to be added to the larger SQL statements.
In this way, you have re-usability at the same time that your final SQL statement is as efficient as possible.
- Chris
Another advantage of a function is that it will get parsed the same way every time. If a program has the same sql code in many places, along with the support problems, there might be subtle differences in the code causing it to get hard parsed more than once. The more duplicate code in an application the more likely Oracle is to hard parse. So which is faster?
You are quite persistent, aren't you? ;)Quote:
Originally posted by gandolf989
Another advantage of a function is that it will get parsed the same way every time. If a program has the same sql code in many places, along with the support problems, there might be subtle differences in the code causing it to get hard parsed more than once. The more duplicate code in an application the more likely Oracle is to hard parse. So which is faster?
Again you bring up a good concern - something that one should always consider - but one that does not apply here.
Basically, these 'subtle' differences might exist in the 'calling' SQL. Say there are 2 different statements that use this functionality. In my case, the SELECT MAX() (or whatever) is part of that statement. In yours, it is a separate function. In either case, these two 'base' SQL statements are both hard-parsed the first time they are called and soft-parsed every time after that, *regardless* of whether they contain a function call or a sub-select. However, in your case, we also have the hard parse of the statement inside the function as well as a soft parse of that same statement for every single row in the outer SQL.
The other case might be that the 'calling' SQL is the same, but the sub-SELECT is different on two calls (for whatever reason). So, if this sub-SELECT is in the actual SQL, then we end up with 2 hard-parses. If it is in the function, then we end up with 3 hard parses and one soft. The 'base' SQL is the same in both calls, so one hard and one soft.The sub-SELECT contained in the function changes, so 2 hard parses.
In reading your reply again, it occurs to me that you may be ignoring my last response and again be assuming that this 'common' sub-select is actually hard-coded each time it is used. I already addressed that, but we can follow this path anyway.
So, you appear to have the following assumptions:
- This 'exact-same functionality' is required in, say, 2 SQL statements
- However, it is implemented differently in these 2 statements.
- This is the only difference between the 2 statements, so now we have 2 'different' statements instead of the 1 we would have had had they both called your function
Seems a little extreme, don't you think? First, if the statements are exactly the same, why are there 2 of them? Shouldn't they simply be calling the same statement in both cases, thereby eliminating the problem? Further, you are assuming they used the *exact* same spacing, case, CR/LFs, etc. for the *entire* SQL statement *except* for the sub-SELECT. Again, quite unlikely.
More likely is that either they would have one single statement that was called twice, or they have the same statement twice, but it is either exactly the same in both places (if they are detailed enough to do that and still not make one statement) or they differ in more than just the sub-SELECT. Again, in either of these cases, whether they hard-code the sub-SELECT or call your function has no bearing on the number of final 'base' statements in the SGA. However, the function-based solution will *always* add more statements, parses and executions, as I've already shown.
You can keep trying, but I guarantee you that my approach is faster.
- Chris
If talking about good coding practices does not apply here, then when does it become relevent? As the cliche goes programming is a discipline. So how can you pick and choose when you should write proper code?
Okay, now I'm torn. I was hoping you hadn't completely given up, because this has been somewhat entertaining. However, I am now thoroughly disappointed in that you seem to have completely missed my argument.Quote:
Originally posted by gandolf989
If talking about good coding practices does not apply here, then when does it become relevent? As the cliche goes programming is a discipline. So how can you pick and choose when you should write proper code?
I *fully support* proper coding. I am actually incredibly anal about it. You should work with me sometime. I have yet to meet somebody more anal about coding practices than I am. :D
And, in this case, as always, I *am* fully advocating the use of proper coding practices. I am advocating the use of re-usable code. What you seem to be missing is that my re-usable code *builds the string* to perform the functionality while yours actually *performs* the functionality. Mine will be faster, but it is equally as maintainable. I have taken both considerations into account: Maintainability and efficiency. The string that my re-usable code builds is then added to the final SQL string so that an in-line sub-SELECT is the final product, which I have shown to be faster. But this re-usable string is still built in its own function which can be called by anybody who is building a string that needs that functionality.
I don't know how to make it any clearer,
- Chris
In re-reading, I see where your previous comment about coding practices 'not applying here' came from. You mis-read my reply. What I was saying was that 'yes, you should always be on the lookout for duplicate code, but *there is no duplicate code* here.' That is what I meant by your concern not applying - you were bringing up something that, rather obviously, I'm afraid, was never a problem with my solution. And I would have thought that the rest of my reply would have made that apparent.Quote:
Originally posted by chrisrlong
You are quite persistent, aren't you? ;)Quote:
Originally posted by gandolf989
Another advantage of a function is that it will get parsed the same way every time. If a program has the same sql code in many places, along with the support problems, there might be subtle differences in the code causing it to get hard parsed more than once. The more duplicate code in an application the more likely Oracle is to hard parse. So which is faster?
Again you bring up a good concern - something that one should always consider - but one that does not apply here.
- Chris
I'm sure I don't get what you are talking about like I'm sure that you don't get what I am talking about. I don't believe in hard coding sql statements, not do I beleive in creating redundant code. If this value is only needed once fine. But what are the odds that a value like this will only get used in one place? In my mind this is the kind of thing that pops up all over, hence a single function that computes the value. why not maintain business logic in one place and one place only. But since Ronnie has lost interest in the post, the whole conversation is rather meaningless. Except to say you were ready to put down my code without thinking about what I was trying to say. No big deal. I'm just expressing my opinion. Hopefully, I am being more clear. and yes your answers were amusing as well.
I give up. You are obviously not reading my answers. I AM RE-USING CODE!! I AM CONSOLIDATING THE LOGIC!!! Just like you are advocating. You are simply not listening. Read the thread over - many times. I have. I am extremely confident that I understand what you are talking about and I have directly addressed your issues in every one of my replies.
Please, please, please re-read the thread. Then at least let me know that you have a glimmer of understanding about what I am telling you. Otherwise I will simply have to assume that you are 'leading me on' and 'feigning ignorance'. And I honestly don't have the time for such things.
- Chris
I do know what you are talking about. Here is a quote from Steven Feuerstein's Oracle PL/SQL best practices, Chapter 6 Page 86.
One of the reasons developers like PL/SQL so much is that it's easy to write SQL inside a PL/SQL block of code.
One of the most dangerous aspects of PL/SQL is that it's so easy to write SQL inside a PL/SQL block of code.
If this was your system you would store the sql as text and execute it when needed, fine. If it was up to me I would write a stored procedure, I don't see the hard parse as being that big of an issue. I think that this is a philosophical difference.
And besides if this is a real time system do you think that they are entering detail records for trades with yesterday's date? I'm guessing, and I can only guess, that the query is being used to create reports of what happened yesterday. Hence a static function. My answer was based on that assumption as you pointed out already.
As it is I have already spent too much time on this issue. It was never my intention to have a go at you.