CREATE TABLE TEST_TABLE ( 
	ID NUMBER(5,0), 
	SOME_GROUP NUMBER(5,0), 
	SOME_DATE DATE 
) PARTITION BY RANGE (SOME_DATE) INTERVAL (NUMTODSINTERVAL(1, 'day')) 
(PARTITION P_BEFORE_2000 VALUES LESS THAN (TO_DATE('01-01-2000', 'DD-MM-YYYY')))

CREATE INDEX I_TEST_TABLE_DATE ON TEST_TABLE (SOME_GROUP, SOME_DATE DESC) LOCAL

INSERT INTO TEST_TABLE (ID, SOME_GROUP, SOME_DATE) VALUES (1, 1, TO_DATE('2023-11-06 01:00:00', 'YYYY-MM-DD HH24:MI:SS'))

INSERT INTO TEST_TABLE (ID, SOME_GROUP, SOME_DATE) VALUES (2, 1, TO_DATE('2023-11-06 02:00:00', 'YYYY-MM-DD HH24:MI:SS'))

INSERT INTO TEST_TABLE (ID, SOME_GROUP, SOME_DATE) VALUES (3, 1, TO_DATE('2023-11-06 03:00:00', 'YYYY-MM-DD HH24:MI:SS'))

INSERT INTO TEST_TABLE (ID, SOME_GROUP, SOME_DATE) VALUES (4, 1, TO_DATE('2023-11-07 01:00:00', 'YYYY-MM-DD HH24:MI:SS'))

INSERT INTO TEST_TABLE (ID, SOME_GROUP, SOME_DATE) VALUES (5, 1, TO_DATE('2023-11-07 02:00:00', 'YYYY-MM-DD HH24:MI:SS'))

INSERT INTO TEST_TABLE (ID, SOME_GROUP, SOME_DATE) VALUES (6, 1, TO_DATE('2023-11-07 03:00:00', 'YYYY-MM-DD HH24:MI:SS'))

INSERT INTO TEST_TABLE (ID, SOME_GROUP, SOME_DATE) VALUES (7, 1, TO_DATE('2023-11-08 01:00:00', 'YYYY-MM-DD HH24:MI:SS'))

INSERT INTO TEST_TABLE (ID, SOME_GROUP, SOME_DATE) VALUES (8, 1, TO_DATE('2023-11-08 02:00:00', 'YYYY-MM-DD HH24:MI:SS'))

INSERT INTO TEST_TABLE (ID, SOME_GROUP, SOME_DATE) VALUES (9, 1, TO_DATE('2023-11-08 03:00:00', 'YYYY-MM-DD HH24:MI:SS'))

INSERT INTO TEST_TABLE (ID, SOME_GROUP, SOME_DATE) VALUES (10, 1, TO_DATE('2023-11-08 04:00:00', 'YYYY-MM-DD HH24:MI:SS'))

SELECT  
    ID, 
    TO_CHAR(SOME_DATE, 'YYYY-MM-DD HH24:MI:SS'), 
    ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC) AS row_num 
FROM 
    TEST_TABLE 
WHERE 
    SOME_GROUP = 1 
    AND SOME_DATE < to_date('2023-11-08 15:00:00', 'YYYY-MM-DD HH24:MI:SS')

SELECT /*+ FULL(TEST_TABLE) */ 
    ID, 
    TO_CHAR(SOME_DATE, 'YYYY-MM-DD HH24:MI:SS'), 
    ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC) AS row_num 
FROM 
    TEST_TABLE 
WHERE 
    SOME_GROUP = 1 
    AND SOME_DATE < to_date('2023-11-08 15:00:00', 'YYYY-MM-DD HH24:MI:SS')