Non-Iceberg External Data

For CSV, JSON, Avro, Parquet, ORC, and similar file-based datasets, Oracle queries files through an external table. Optimize the physical layout and external table definition together.

File Format, Columns, and Predicates

Technique Guidance
Use columnar formats Prefer Parquet or ORC for analytic scans when the producing system supports them. Their column and row-group or stripe metadata can reduce I/O.
Select required columns Avoid production SELECT *. Column projection is most valuable for wide columnar datasets.
Use filterable predicates Filter on columns with compatible Oracle and file types. Keep predicates simple when possible; functions, implicit conversions, and unsupported operators can become residual filters.
Avoid expensive text parsing CSV and JSON can require reading and parsing more bytes. Use them when interoperability matters; materialize or convert hot analytic datasets when latency matters more.

Oracle’s ORACLE_BIGDATA driver supports Parquet, ORC, Avro, and text file formats. Predicate and column elimination depend on the format, data types, driver, and release. For more information, see External Tables Concepts.

Folder Layout and Partition Pruning

Organize paths by stable, commonly filtered dimensions. Put the most useful high-level filters early in the path hierarchy.

/orders/region=US/year=2026/month=07/day=01/
/orders/region=DE/year=2026/month=07/day=01/
Model Use when Performance and maintenance behavior
Unpartitioned external table Small datasets or sources without a stable folder convention. Simple, but broad locations can require listing and scanning many irrelevant objects.
Implicit partitioning Database datasets already use consistent Hive-style or path-tail folders. Partition columns are derived from paths at query time. New objects are discovered without an explicit partition synchronization step.
Explicit partitioned external table You need explicit Oracle partition metadata and controlled partition lifecycle. Provides normal partition-pruning benefits, but partition metadata must be synchronized by running a separate routine DBMS_CLOUD.SYNC_EXTERNAL_PART_TABLE.

Example of optimized implicit partitioning for a fixed Hive-style hierarchy:

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name => 'ORDERS_XT',
    credential_name => 'LH_DATA_RO',
    file_uri_list => 'https://object-store.example/orders/*',
    column_list => 'order_id NUMBER, amount NUMBER,
                        region VARCHAR2(10), year NUMBER,
                        month NUMBER, day NUMBER',
    format => '{
      "type":"parquet",
      "implicit_partition_config":{
        "partition_type":"hive",
        "partition_columns":["region","year","month","day"],
        "strict_column_order":true
      }}');
END;
/

Query the path-derived partition columns directly:

SELECT order_id, amount
FROM orders_xt
WHERE region = 'US'
  AND year = 2026
  AND month = 7
  AND day = 1;

Include the leading path keys where practical and confirm partition elimination in the executed plan.

strict_column_order is a contract. Enable it only when every path uses the same partition columns in the same order with no missing prefixes. If folder conventions change, update the table definition or disable the optimization. See Query External Tables with Implicit Partitioning

Partition Design Rules

File Size and File Count

There is no universal target file size. Balance object-listing and open costs against parallelism, row-group pruning, write behavior, and recovery requirements.

Layout Typical effect Action
Very many tiny files High listing, open, authentication, scheduling, and metadata overhead. Compact files in the producing system and preserve useful partition boundaries.
A few very large files Can limit parallel reads. Poor row-group or stripe layout can also make selective queries read more data. Use multiple columnar files and size them for representative Oracle queries.
Files cross partition boundaries Filters cannot eliminate a whole file cleanly. Keep each file within one logical partition value set.

Statistics and Cardinality

Gather optimizer statistics with DBMS_STATS. Because external files are not changed through Oracle DML, Oracle does not mark external-table statistics stale when the source changes. Regather them after material changes to row counts, distributions, partitions, or file layout.

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'LAKEHOUSE_APP',
    tabname => 'ORDERS_XT',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    degree => DBMS_STATS.AUTO_DEGREE);
END;
/

Compare estimated and actual rows at joins and aggregations. Bad estimates can cause the optimizer to choose an expensive join order or distribution strategy even when remote pruning is working. See Gathering Optimizer Statistics.

Parallel Query

External tables can be queried in parallel. Apply parallelism after pruning and file layout are sound.

ALTER TABLE orders_xt PARALLEL 8;

-- Restore the deployment default when the test is complete.
ALTER TABLE orders_xt NOPARALLEL;
Parallel query helps when It can hurt when
The scan is large, there are enough independent files or file splits, and object-store throughput is available. The query should have pruned most files, file count is too low, the system is highly concurrent, or the remote service is throttling requests.
Database CPU and memory can support parallel decompression, filtering, joins, and aggregation. More workers only increase contention, open requests, egress, or spill without reducing elapsed time.

Review parallel decisions in the executed plan. Deployment resource management can override or limit the requested degree. See Parallel_Clause.

Select the Execution Strategy

Choice Use it for Do not use it as a substitute for
Query in place Fresh, infrequent, exploratory, or highly selective reads where copying data is unnecessary. Partitioning, statistics, or an SLA for repeatedly scanning the same remote bytes.
Lake Cache Autonomous AI Database + 26ai Repeated dashboards, joins, notebooks, and hot partitions or columns from eligible external or mounted catalog tables. Maintaining source tables, refreshing catalog metadata, or caching the entire data lake.
Data Lake Accelerator AI Lakehouse Only Large, complex, adhoc scans where scale-out external processing is more useful than retaining a local copy. Pruning or repeated-read caching. It accelerates work that remains after elimination.
Load or materialize High-concurrency, predictable-latency workloads; repeated complex joins; indexes, materialized views, enforced constraints, or transactional integration. A lightweight path to the freshest external snapshot when replication delay is unacceptable.
Hybrid Local hot set or cache plus query-in-place or acceleration for the full history. Clear refresh, consistency, and ownership rules.

Lake Cache has its own prerequisites, sizing rules, lifecycle, and diagnostics. Use the complete procedure in Section Lake Cache.