Oracle Magazine, July/August 2017
Database Developer and DBA ETL If I defined the external table as nonpartitioned with two files defined in a single location definition all queries would need to scan both files But with a partitioned external table definition partition elimination enables access to each file in isolation if the predicates allow it For example a query to just the employees in region AU yields the following execution plan showing that only the first partition P_ AU was required to satisfy the query ORACLE MAGAZINE JULY AUGUST 2017 94 SQL set autotrace traceonly explain SQL select from ext_ emp where region AU Execution Plan Plan hash value 2018056040 Id Operation Name Rows Bytes Cost CPU Time Pstart Pstop 0 SELECT STATEMENT 82 7380 29 0 00 00 01 1 PARTITION LIST SINGLE 82 7380 29 0 00 00 01 1 1 2 EXTERNAL TABLE ACCESS FULL EXT_ EMP 82 7380 29 0 00 00 01 1 1 Note that the database does not validate the external data to ensure that it aligns with the partition definition For example I added an invalid row REGION XX into the emp_ au dat file Then when I query for REGION XX I get no rows returned because the partition definition and hence subsequent optimizer
You must have JavaScript enabled to view digital editions.
