This article applies to Oracle versions 10.2.0.5 and higher.

A slow-performing query with the following structure was identified:

SELECT 
  a.*
FROM (
  SELECT
    station.ID,
    'small_station_info' AS table_name,
    (SELECT base.name FROM scene_base_info base WHERE base.id = station.antenna_selection) AS antenna_selection,
    station.antenna_height,
    station.down_angle,
    station.azimuth_angle,
    station.ITI_ID,
    attachment.longitude,
    attachment.latitude,
    attachment.attach_id
  FROM demand_consolidation dc
  LEFT JOIN test_demand_info tdi ON dc.id = tdi.cd_id
  LEFT JOIN plan_demand_info pdi ON tdi.id = pdi.tdl_id
  LEFT JOIN building_plan_info bpi ON pdi.id = bpi.dpi_id
  LEFT JOIN near_far_place_info nfpi ON bpi.id = nfpi.bpi_id
  LEFT JOIN small_station_info station ON nfpi.id = station.nfpi_id
  LEFT JOIN site_attachment attachment 
    ON TO_NUMBER(attachment.longitude) IS NOT NULL
    AND TO_NUMBER(attachment.latitude) > 26.074423
    AND TO_NUMBER(attachment.latitude) < 26.077573
    AND TO_NUMBER(attachment.longitude) > 119.191148
    AND TO_NUMBER(attachment.longitude) < 119.197649
    AND attachment.attach_name = SUBSTR(station.AZIMUTH_ANGLE_PHOTO,
          INSTR(station.AZIMUTH_ANGLE_PHOTO, '/', -1) + 1,
          LENGTH(station.AZIMUTH_ANGLE_PHOTO))
) a
WHERE a.longitude IS NOT NULL

The execution plan revealed a Cartesian product operation (MERGE JOIN CARTESIAN) causing significant performance degradation:

| Id  | Operation                           | Name                    |
|-----|-------------------------------------|-------------------------|
|   0 | SELECT STATEMENT                    |                         |
|   1 |  TABLE ACCESS BY INDEX ROWID        | SCENE_BASE_INFO         |
|*  2 |   INDEX UNIQUE SCAN                 | SCENE_BASE_INFO_PK      |
|   3 |  VIEW                               |                         |
|*  4 |   FILTER                            |                         |
|*  5 |    HASH JOIN OUTER                  |                         |
|*  6 |     HASH JOIN OUTER                 |                         |
|*  7 |      HASH JOIN OUTER                |                         |
|*  8 |       HASH JOIN OUTER               |                         |
|*  9 |        HASH JOIN OUTER              |                         |
|  10 |         MERGE JOIN CARTESIAN        |                         |
|* 11 |          TABLE ACCESS BY INDEX ROWID| SITE_ATTACHMENT         |
|* 12 |           INDEX RANGE SCAN          | IDX_SITE_ATTACHMENT_JWD |
|  13 |          BUFFER SORT                |                         |
|  14 |           INDEX FAST FULL SCAN      | PK_DEMAND_CONSOLIDATION |

Resolution approaches include:

Using LEADING Hint

SELECT /*+ no_merge(a) no_push_pred(a) */
  a.*
FROM (
  SELECT /*+ leading(dc tdi pdi bpi station) */
    ...
) a
WHERE a.longitude IS NOT NULL

This approach eliminated the Cartesian join and improved execution time to approximately 0.17 seconds.

Using MATERIALIZE Hint

WITH A AS (
  SELECT /*+ MATERIALIZE */
    ...
)
SELECT a.* FROM A 
WHERE a.longitude IS NOT NULL

This method created a global tempoarry table but maintained performance around 0.19-0.2 seconds.

Optimization recommendations:

1. Collect fresh table statistics before applying hints
2. Prefer LEADING hint over MATERIALIZE when possible
3. Use hints to override suboptimal optmiizer decisions