Hi

I have two tables and indexes described as below in Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production. The index on snapshot_id is not unique because I thought it was causing a problem

create table TABLE1 (
    SNAPSHOT_ID varchar2(50),
    FLEET_BUCKET_ID number(3,0)
);

CREATE INDEX TABLE1_IDX1 ON TABLE1 ("SNAPSHOT_ID");

CREATE TABLE TABLE2
( "SNAPSHOT_ID" VARCHAR2(50 BYTE), 
 "VRM_ID" VARCHAR2(50 BYTE), 
 "SNAP_TIME_ID" NUMBER(4,0), 
 "SNAP_DATETIME" TIMESTAMP (6), 
 "ETL_LOG_ID" VARCHAR2(12 BYTE)
);
/

CREATE INDEX TABLE2_IDX2 ON TABLE2("SNAP_DATETIME");
/

CREATE INDEX TABLE2_IDX1 ON TABLE2("SNAPSHOT_ID");
/

In my actual environment the table create settings are as follows

table options
SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "XXXXXXX"

index options
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "XXXXXX" ;


I run the following sql

SELECT 
    s1.SNAPSHOT_ID,
    s1.FLEET_BUCKET_ID
FROM TABLE1 s1,
     TABLE2 s2
WHERE 1 = 1
  AND s2.SNAP_DATETIME  >= TO_DATE('2020-08-16 02:20:00','YYYY-MM-DD HH24:MI:SS') 
  AND s2.SNAP_DATETIME  <= TO_DATE('2020-08-16 02:20:01','YYYY-MM-DD HH24:MI:SS')
  AND s2.snapshot_id = s1.snapshot_id

As soon as stats are run on TABLE1 the join between TABLE1 AND TABLE2 is hash. Any reason why the optimizer is not picking up the nested loop? Seems like it should, especially when my actual tables and not the test one is 18,000,000 rows. It happens even when the table which I moved to test tables has under 100,000 rows. SNAPSHOT_ID as it turns out is distinct values in both tables.

now the use_n1(s1 s2) does pick it up in sqldeveloper, but for whatever reason where I am actually developing the time is slower and I and appears to be the hash join time.

set serveroutput off
alter session set statistics_level = all;

select * from <your query>;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +ADAPTIVE'));