Doubt with range_join hints optimization, using INSERT INTO REPLACE WHERE

[Gustavo_Az]

Hello

Im optmizing a big notebook and have encountered many times the tip from databricks that says "Unused range join hints". Reading the documentation for reference, I have been able to supress that warning in almost all cells, but some of then remains that I dont understand where to place the hint.

This problematic cells are of the same type (first cell of the image attached).

In the second cell there is no warning (i am using the same temp table in 1º and 2º cell) and that would mean that there is no problem building that dataset. That being said, I think the problem is in the INSERT INTO ..... REPLACE WHERE.

Where can I find documentation about the range_joins with the INSERT INTO REPLACE WHERE operation? The one I referenced talks only about joins, and here (insert into replace where doc) there is no clue either.

Any idea to optimize this operation also?

 Thank you.

[Prajapathy_NKR]

Hi @Gustavo_Az

Try to use explain to understand what's happening. 

https://spark.apache.org/docs/latest/sql-ref-syntax-qry-explain.html

[mark_ott · Accepted solution]

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.​