Why a Full Table Scan Beats an Index in This Oracle Data‑Warehouse Query

Author background : Luo Min has over 20 years of experience in Oracle technology research, development, and services, including more than a decade at Oracle China as a senior technical consultant, and has authored several Oracle performance books.

1. A not‑too‑complex SQL statement

The following SQL originates from an industry data‑warehouse system:

SELECT /*+ PARALLEL(4) */
A.NSRZHDAH, A.SSSQQ, A.SSSQZ, A.NSRSBH
FROM J1_LDM.LDMT02_YE_SBXX A, J1_DI.DI_ZSXM B
WHERE A.SBQX >= '20160101'
  AND A.SBQX <= '20160331'
  AND A.GZLX_DM IN ('1', '2')
  AND A.ZSXM_DM = B.ZSXM_DM
  AND B.SFBZ_DM = '1'
  AND (A.SBRQ > A.SBQX OR SBRQ IS NULL));

2. Existing execution plan

The current plan shows an execution time of 5 minutes 58 seconds, with 386,750 logical reads and 130,678 physical reads. Oracle accesses LDMT02_YE_SBXX via the SBQX index and performs a full scan on the code table DI_ZSXM. The LDMT02_YE_SBXX table is partitioned by month on SBQX (global range partition) and also by SBRQ (monthly). Both tables are accessed in parallel.

The plan appears normal, and a six‑minute run for three months of transaction data seems acceptable.

3. Full‑table scan surprisingly faster

To explore further optimization, the parallel hint was changed from /*+ PARALLEL(4) */ to /*+ PARALLEL(a,4) */, explicitly tying the degree of parallelism to table alias a. The new plan (shown below) shows Oracle abandoning the SBQX index and performing a full scan on LDMT02_YE_SBXX.

Execution time drops to 2 minutes 52 seconds – a two‑fold speedup – but logical reads rise to 651,387 and physical reads to 620,747, indicating higher resource consumption. For data‑warehouse workloads, a full scan with parallelism can outperform index access.

4. Further optimization potential

Scanning tens of millions of rows is still costly. If LDMT02_YE_SBXX were partitioned by SBQX (monthly range), Oracle could prune partitions and limit the scan to the three relevant months, potentially reducing execution time to under a minute and dramatically lowering I/O.

However, the author could not test this in production due to limited on‑site time, but experienced DB designers recognize the likely benefit.

5. Partition‑index design considerations

Many developers mistakenly assume the most frequently filtered column must be the partition key. In this case, partitioning by SBQX (monthly) aligns with business logic, while SBRQ (daily) can still have a partition‑aware index (global range or local non‑prefixed). The two index types behave differently:

Global Range Partition index : accesses only one sub‑partition, offering good performance, but can become invalid after partition operations (drop, split, merge), risking business continuity.

Local non‑prefixed Partition index : remains valid after partition changes, but unless the query includes the partition column ( SBQX), the optimizer may perform a full index scan, degrading performance.

Given that SBQX stores month‑level data (coarser granularity) and SBRQ stores day‑level data (finer granularity), partitioning by SBQX better matches the data‑warehouse access pattern.

6. Insight 1 – Targeted technology for data‑warehouse workloads

OLTP systems prioritize low‑latency transaction processing and favor index‑based access, whereas OLAP systems handle large‑scale batch processing and benefit from parallelism, partition pruning, hash joins, merge statements, external tables, bitmap indexes, and materialized views. In this case, full‑table (or full‑partition) scans with parallelism are the appropriate strategy.

7. Insight 2 – Aligning business and technology

The case illustrates a common gap: application developers understand business logic but lack deep knowledge of Oracle partitioning techniques, while DB experts may overlook business requirements. This misalignment leads to sub‑optimal designs, excessive I/O, and unnecessary architectural complexity such as premature sharding.

Bridging the gap by jointly analyzing workload characteristics and leveraging Oracle’s rich partitioning features yields more efficient, maintainable solutions.