In day to day practice, as you may very well know, lots of different situations show up that require you to sort things, create filters, find out “the what”, “the who”, “the where”, “the which”. Knowing how to exactly search or find the criteria you need, can save you from quite a waste of time and a headache.
That’s why today we are writing about how to easily retrieve a record from a table, the record being the maximum following certain criteria (date, ID etc). The first reaction is to write a query making a JOIN with the table itself. In this case, the function MAX will use a lot of time.
SELECT a MAX
This rule regards a sentence like this:
SELECT * FROM TABLE T WHERE EFFDT = (SELECT MAX(EFFDT) FROM TABLE T1 WHERE T.ID = T1.ID)
Sometimes, it could be more efficient for ORACLE to modify the query as below:
SELECT * FROM (SELECT T.* , MAX(EFFDT) OVER (PARTITION BY ID) MAXDT FROM TABLE) WHERE EFFDT = MAXDT
This final form avoids a DOUBLE SCAN made by ORACLE on the table TABLE. Be careful, this query must be tested to find if it improves the performances. Furthermore, this sentence could be used into a sub-query that depends on the result of the first query.
Nested Select
Another case that could exist in practice is the nested SELECT. Although sometimes it’s easier to write a query using SELECT in SELECT, in many cases such structure is a great time consumer. SELECT in SELECT( Nested SELECT )
SELECT …, NVL(SELECT …) CASE … SELECT … FROM … SELECT …, CASE SELECT … FROM …
This type of query could have a strong impact on the performances. As an example, if the main query returns 50,000 records, the second SELECT will be executed 50,000 times (once for each record), with a great time consumption. This type of sentence should be used only if we have no choice. As a general rule, to transform this query into a query with external links should solve this kind of issue.
Hope you find these little tricks useful and time saving! Keep in touch!
No Comments