Query External Tables with Partitioning Specified in Source Files

If you want to query multiple data files in the Object Store as a single external table and the files can be represented as multiple logical partitions, it is highly recommended to use an external partitioned table. Using an external partitioned table preserves the logical partitioning of your data files for query access.

Using partitioned external tables has the potential to dramatically improve query performance by only accessing the data required for the query. For example, you may have two years of daily partitions stored in separate objects on Cloud Object Store. When you use partitioned external tables, a query for a single day only needs to access that day's source data. When you use partitioned external tables the database automatically partition prunes, and in this example only needs to scan a very small fraction of the data.

There are two ways to create an external partitioned table with the DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE procedure:

• Using the file_url_path value in combination with the format parameter: Autonomous Database analyzes Cloud Object Store file path information supplied with this parameter to determine the partition columns and data types (or you can manually specify the partition columns and data types).

  This type of partitioning provides a synchronization routine to handle changes when external partition files are added or removed.

• Using the partitioning_clause parameter: Autonomous Database uses the explicit partitioning clause you supply to create an external partitioned table.

  This type of partitioning does not support a synchronization routine.

  See Query External Partitioned Data (with Partitioning Clause) for a description of this type of external table.

• About External Tables with Source File Partitioning
  On Autonomous Database you can create partitioned external tables from Hive style partitioned data or from simple folder partitioned data stored on your Cloud Object Store.
• Query External Partitioned Data with Hive Format Source File Organization
  Use DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE to create an external partitioned table and generate the partitioning information from the Cloud Object Store file path.
• Query External Partitioned Data with Folder Format Source File Organization
  Use DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE to create an external partitioned table and generate the partitioning information from the Cloud Object Store file path.
• Refresh External Partitioned Tables with Updated or Deleted Source Files
  You can use DBMS_CLOUD.SYNC_EXTERNAL_PART_TABLE to refresh an external partitioned table. Use this procedure when new partitions are added or when partitions are removed from the object store source.