Please suppose you have a multi-column index in an Oracle table:
CREATE INDEX MY_USER.MY_INDEX ON MY_USER.MY_TABLE (YEAR, SEMESTER);
The column YEAR is NUMBER(4). The column SEMESTER is NUMBER(1).
What is the most efficient way to determine the most recent couple (YEAR, SEMESTER)?
I think something like this:
select max(YEAR) into MY_YEAR from MY_TABLE; select max(SEMESTER) from MY_TABLE where YEAR = MY_YEAR;
Any other clues?
Thank you very much for considering my request.
EDIT: The table has over 50 million records.
SECOND EDIT: I am using this Oracle version:
BANNER Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio NLSRTL Version 10.2.0.5.0 - Production
semester can be only
2, this could be efficient:
with t as (select max(year), 1 as semester from MY_TABLE where semester = 1 union all select max(year), 2 as semester from MY_TABLE where semester = 2) select MAX(TO_CHAR(YEAR) || TO_CHAR(SEMESTER)) from t;
Of course, this is not a generic solution but it should work in your case.
Another idea is this one:
SELECT year, semester FROM MY_TABLE ORDER BY year, semester FETCH FIRST 1 ROWS ONLY;
The row_limiting_clause was introduced in Oracle 12.1