Oracle Multi-Column Index: Determine the most recent pair (year, semester)

advertisements

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


In case semester can be only 1 or 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.

Update:

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