Using External Tables to Load and Unload Data

Transcript

1. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

   | Your SQL Office Hours session will begin soon… Using external tables to load & unload data with Chris Saxon, @ChrisRSaxon & @SQLDaily https://www.youtube.com/c/TheMagicofSQL https://blogs.oracle.com/sql

2. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

   | Welcome to Ask TOM Office Hours!

3. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

   | create table t_ext ( … ) organization external ( default directory tmp location ( 'sales.csv' ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

4. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

   | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales.csv select * from t_ext;

5. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

   | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales_20180101.csv sales_20180102.csv sales_20180103.csv select * from t_ext; alter table t_ext location ( '…20180101.csv' ); alter table t_ext location ( '…20180102.csv' ); alter table t_ext location ( '…20180103.csv' );

6. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

   | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales_20180101.csv sales_20180102.csv sales_20180103.csv select * from t_ext external modify ( location ( '…20180101.csv' ) );

7. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

   | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales_20180101.csv sales_20180102.csv sales_20180103.csv select * from t_ext external modify ( location ( '…20180102.csv' ) );

8. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

   | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales_20180101.csv sales_20180102.csv sales_20180103.csv select * from external ( ( <cols> ) default directory … location ('…') )

9. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

   | create table t_ext ( … ) organization external ( type oracle_datapump default directory tmp location ( 't.dmp' ) ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

10. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | create table t_ext ( … ) organization external ( type oracle_datapump default directory tmp location ( 't.dmp' ) ) as select * from … ; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Not expdp/impdp compatible!

11. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales.dmp create table ext ( … ) … ( … oracle_datapump ) as select * from sales;

12. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales.dmp select * from ext;

13. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | create table t_ext ( … ) organization external ( … ) partition by range ( … ) ( partition p0 values less than ( … ) location ( '…201901.dmp' ), partition p1 values less than ( … ) location ( '…201902.dmp' ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

14. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | create table t_ext ( … ) organization external ( … ) partition by range ( … ) ( partition p0 values less than ( … ) location ( '…201901.dmp' ), partition p1 values less than ( … ) location ( '…201902.dmp' ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

15. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales create table as select

16. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales sales_ext create table as select

17. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales sales_ext

18. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | create view sales_all as select * from sales_ext union all select * from sales blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

19. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | create table t_ext ( … ) external partition attributes ( … ) partition by range ( … ) ( partition ext values less than ( … ) external location ( '….dmp' ), partition int values less than ( … ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

20. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | create table t_ext ( … ) external partition attributes ( … ) partition by range ( … ) ( partition ext values less than ( … ) external location ( '….dmp' ), partition int values less than ( … ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

21. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales create table as select

22. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon create table as select sales

23. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales

24. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Sounds Awesome! Ryan McGuire / Gratisography blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon There are some caveats…

25. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Hybrid Partitioned Tables Restrictions •Partial indexes only (local or global) •No unique indexes! •Constraints must be RELY DISABLE •List & range partitioning only –NOT automatic or interval partitioning blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

26. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | DEMO

27. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | Now it's over to you!

28. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    | asktom.oracle.com #AskTOMOfficeHours Ryan McGuire / Gratisography