There is no official documentation covering the use of range_join hints directly with the INSERT INTO ... REPLACE WHERE operation in Databricks—existing documentation around range joins focuses only on explicit joining operations, not on conditional insert statements like REPLACE WHERE. The warning about "Unused range join hints" typically appears when hints are present in queries where Databricks does not use them, especially outside SELECT JOIN clauses.​

Range Join Hint Functionality

• The range_join hint is meant for optimizing joins that include range predicates (e.g., where one table’s values must fall within a range of another’s).​

• Hints must go on a SELECT ... JOIN ... statement or on Spark DataFrame .join() with the .hint() method, not on INSERT statements or their predicates.​

• When used within non-join statements (like INSERT INTO with REPLACE WHERE), Databricks will ignore such hints and may issue the unused hint warning.​

REPLACE WHERE Limitations

• The REPLACE WHERE clause for INSERT INTO applies only to Delta tables (Databricks Runtime 12.2 LTS+).

• It atomically deletes and then inserts rows matching your predicate, lending precision and atomicity to data replacement.​

• It’s unrelated to join internals, so join hints like range_join do not modify or optimize the REPLACE WHERE operation.​

• To optimize this operation, focus on indexing, partitioning, and file compaction (using OPTIMIZE), rather than join hints.​

• For batch efficiency, ensure your source query is well-partitioned and consider using MERGE INTO if doing upserts.​

Optimization Tips for INSERT INTO REPLACE WHERE

• Index columns used in the REPLACE WHERE predicate for faster lookup and atomic replacement.​

• Use partitioning wisely—if you’re replacing a subset, partition by the predicate column(s) to avoid expensive full-table scans.​

• Regularly use the OPTIMIZE command on your Delta tables to compact small files.​

• Consider MERGE INTO for complex upserts instead of multiple REPLACE WHERE statements.​

In summary, range join hints are not used or valid with INSERT INTO ... REPLACE WHERE in Databricks, and any optimization must focus on Delta table management, indexing, and source query structure, not join hints.​