{"id":787,"date":"2012-01-12T04:39:51","date_gmt":"2012-01-12T09:39:51","guid":{"rendered":"http:\/\/www.iri.com\/blog\/?p=787"},"modified":"2018-04-30T14:33:37","modified_gmt":"2018-04-30T18:33:37","slug":"unload-big-data-from-oracle-et-al","status":"publish","type":"post","link":"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/","title":{"rendered":"Unload Very Large Databases"},"content":{"rendered":"

One of the main concerns DBAs who need to unload big data from very large database (VLDB) tables have is speed.\u00a0Faster unloading makes\u00a0data accessible in different forms for different purposes and platforms. The faster that data is acquired, the faster it can be processed and delivered. The ultimate result\u00a0is faster\u00a0time-to-solution, and thus the productivity and competitiveness of companies that make money from information.<\/p>\n

Most of the available tools and methods currently used for extracting data from large fact tables are simply too slow. Those that purport to be faster are complicated, proprietary, or require the purchase of a costly ETL package. A shift to an ELT or Hadoop paradigm\u00a0entails\u00a0major hardware (or DB appliance) expenses, taxes the underlying DB now transforming, and\u00a0imposes steep learning curves and maintenance burdens.<\/p>\n

Are there not other options for the unload, and downstream processes, that are faster, simpler, and more affordable?<\/p>\n

\"\"<\/p>\n

Why Unload VLDB Tables?<\/h2>\n

The are many\u00a0reasons why you would bulk-unload data from these tables in the first place:<\/p>\n

Database Migration<\/a><\/strong>: The faster you can get\u00a0data out of the legacy database(s), the faster you can map and move the data into the new database(s). Bulk data movement would be beneficial\u00a0if data volumes are massive, there is a benefit to offline transformation, protection, and\/or analysis (which is possible at once in IRI CoSort<\/a>), and when pre-sorted bulk loads<\/a> are the only way to meet SLA deadlines.<\/p>\n

Database Reorg<\/strong><\/a>: Unload, sort, re-load to keep the database operating efficiently and optimize common queries by keeping the tables in join order. Unloading is performed in off-line, or external reorganizations. See this comparison on off-line and on-line reorg<\/a> methods.<\/p>\n

Data Integration<\/strong><\/a>: Large scale DW extract-transform-load (ETL) operations start with extraction of tables into the data staging area. Data dumped from tables and co-mingled with mainframe data sets, web logs, and other flat files can most efficiently be integrated and processed in the file system. External transformations\u00a0via\u00a0\u00a0are not only more efficient because multiple actions can be staged in a single I\/O pass, but because the computational overhead of all this work is removed from the database (see ETL vs.\u00a0ELT<\/a>).<\/p>\n

Data Replication<\/a> \/ Archive<\/strong>: By offloading fact tables, golden source operational data can be duplicated and stored in a portable format. Data in flat files can be queried, manipulated and re-formatted with tools like IRI NextForm<\/a> or CoSort, and used to populate other databases and applications. Similarly, an accessible repository of this data can also be stored off-line for backup restore and retrieval, or distribution to parties who require access to the data in a different environment.<\/p>\n

Business Intelligence<\/strong><\/a>: It can be faster and easier to import operational data into Excel and other BI tools in flat-file formats like CSV and XML rather than trying to bridge between the table and a spreadsheet or BI cube. Offloading tables into flat files therefore, is an initial and efficient step in data franchising<\/a> – the preparation of data for BI operations.<\/p>\n

A Look at Unload Methods<\/h2>\n

VLDB extraction methods vary in performance and functionality. A good utility for unloading big data from Oracle and other major DBs needs to be efficient, easy-to-use, and support certain reformatting functions that would not encumber the process.<\/p>\n

The SQL SPOOL command can dump data to a flat file, but it is typically slow in volume. Native utilities like Oracle’s export or data pump are faster, but produce proprietary extracts that can only be re-imported into the same database, and cannot be analyzed as a flat file would.<\/p>\n

If you need to rapidly unload large tables to portable flat files, consider\u00a0IRI FACT<\/a><\/span>\u00a0(Fast Extract) for Oracle, DB2, Sybase, MySQL, SQL Server, Altibase, Tibero, etc. FACT uses parallelism and native database connection protocols (like Oracle OCI) to optimize extraction performance.\u00a0The free FACT GUI<\/a> (wizard) that’s part of the IRI Workbench<\/a>, built on Eclipse\u2122,\u00a0presents available\u00a0tables and columns to extract and uses\u00a0SQL SELECT syntax.<\/p>\n

Another benefit to using FACT is its metadata integration with the SortCL<\/a> program in IRI Voracity<\/a> for transforming, protecting, targeting, and even reporting on, the flat-file extracts. FACT also creates the database bulk load utility control file in anticipation of same-table re-populations using pre-CoSorted flat files<\/a> in large scale ETL<\/a> or reorg<\/a> operations.<\/p>\n","protected":false},"excerpt":{"rendered":"

One of the main concerns DBAs who need to unload big data from very large database (VLDB) tables have is speed.\u00a0Faster unloading makes\u00a0data accessible in different forms for different purposes and platforms. The faster that data is acquired, the faster it can be processed and delivered. The ultimate result\u00a0is faster\u00a0time-to-solution, and thus the productivity and<\/p>\n

Read More<\/a><\/div>\n","protected":false},"author":3,"featured_media":11661,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[108,31,776,3],"tags":[25,52,44,51,28,46,53,50],"class_list":["post-787","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-big-data-2","category-data-migration","category-etl","category-vldb-operations","tag-big-data","tag-business-intelligence-2","tag-cosort","tag-data-replication","tag-data-warehousing","tag-etl-tools","tag-fact","tag-oracle"],"acf":[],"yoast_head":"\nUnload Very Large Databases - IRI<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Unload Very Large Databases\" \/>\n<meta property=\"og:description\" content=\"One of the main concerns DBAs who need to unload big data from very large database (VLDB) tables have is speed.\u00a0Faster unloading makes\u00a0data accessible in different forms for different purposes and platforms. The faster that data is acquired, the faster it can be processed and delivered. The ultimate result\u00a0is faster\u00a0time-to-solution, and thus the productivity andRead More\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/\" \/>\n<meta property=\"og:site_name\" content=\"IRI\" \/>\n<meta property=\"article:published_time\" content=\"2012-01-12T09:39:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-04-30T18:33:37+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2012\/01\/shipyard.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"602\" \/>\n\t<meta property=\"og:image:height\" content=\"402\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"David Friedland\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"David Friedland\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/\"},\"author\":{\"name\":\"David Friedland\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/person\/cdb89f0c0a9c88810b8516d4b140734a\"},\"headline\":\"Unload Very Large Databases\",\"datePublished\":\"2012-01-12T09:39:51+00:00\",\"dateModified\":\"2018-04-30T18:33:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/\"},\"wordCount\":722,\"commentCount\":7,\"publisher\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2012\/01\/shipyard.jpg\",\"keywords\":[\"big data\",\"business intelligence\",\"CoSort\",\"data replication\",\"Data Warehousing\",\"ETL tools\",\"FACT\",\"Oracle\"],\"articleSection\":[\"Big Data\",\"Data Migration\",\"ETL\",\"VLDB\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/\",\"url\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/\",\"name\":\"Unload Very Large Databases - IRI\",\"isPartOf\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2012\/01\/shipyard.jpg\",\"datePublished\":\"2012-01-12T09:39:51+00:00\",\"dateModified\":\"2018-04-30T18:33:37+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#primaryimage\",\"url\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2012\/01\/shipyard.jpg\",\"contentUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2012\/01\/shipyard.jpg\",\"width\":602,\"height\":402},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.iri.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Unload Very Large Databases\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.iri.com\/blog\/#website\",\"url\":\"https:\/\/www.iri.com\/blog\/\",\"name\":\"IRI\",\"description\":\"Total Data Management Blog\",\"publisher\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.iri.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.iri.com\/blog\/#organization\",\"name\":\"IRI\",\"url\":\"https:\/\/www.iri.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png\",\"contentUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png\",\"width\":750,\"height\":206,\"caption\":\"IRI\"},\"image\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/person\/cdb89f0c0a9c88810b8516d4b140734a\",\"name\":\"David Friedland\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/995ea08bc7d036da625671cb48a636eb?s=96&d=blank&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/995ea08bc7d036da625671cb48a636eb?s=96&d=blank&r=g\",\"caption\":\"David Friedland\"},\"url\":\"https:\/\/www.iri.com\/blog\/author\/davidf\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Unload Very Large Databases - IRI","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/","og_locale":"en_US","og_type":"article","og_title":"Unload Very Large Databases","og_description":"One of the main concerns DBAs who need to unload big data from very large database (VLDB) tables have is speed.\u00a0Faster unloading makes\u00a0data accessible in different forms for different purposes and platforms. The faster that data is acquired, the faster it can be processed and delivered. The ultimate result\u00a0is faster\u00a0time-to-solution, and thus the productivity andRead More","og_url":"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/","og_site_name":"IRI","article_published_time":"2012-01-12T09:39:51+00:00","article_modified_time":"2018-04-30T18:33:37+00:00","og_image":[{"width":602,"height":402,"url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2012\/01\/shipyard.jpg","type":"image\/jpeg"}],"author":"David Friedland","twitter_card":"summary_large_image","twitter_misc":{"Written by":"David Friedland","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#article","isPartOf":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/"},"author":{"name":"David Friedland","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/person\/cdb89f0c0a9c88810b8516d4b140734a"},"headline":"Unload Very Large Databases","datePublished":"2012-01-12T09:39:51+00:00","dateModified":"2018-04-30T18:33:37+00:00","mainEntityOfPage":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/"},"wordCount":722,"commentCount":7,"publisher":{"@id":"https:\/\/www.iri.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#primaryimage"},"thumbnailUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2012\/01\/shipyard.jpg","keywords":["big data","business intelligence","CoSort","data replication","Data Warehousing","ETL tools","FACT","Oracle"],"articleSection":["Big Data","Data Migration","ETL","VLDB"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/","url":"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/","name":"Unload Very Large Databases - IRI","isPartOf":{"@id":"https:\/\/www.iri.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#primaryimage"},"image":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#primaryimage"},"thumbnailUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2012\/01\/shipyard.jpg","datePublished":"2012-01-12T09:39:51+00:00","dateModified":"2018-04-30T18:33:37+00:00","breadcrumb":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#primaryimage","url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2012\/01\/shipyard.jpg","contentUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2012\/01\/shipyard.jpg","width":602,"height":402},{"@type":"BreadcrumbList","@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/unload-big-data-from-oracle-et-al\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.iri.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Unload Very Large Databases"}]},{"@type":"WebSite","@id":"https:\/\/www.iri.com\/blog\/#website","url":"https:\/\/www.iri.com\/blog\/","name":"IRI","description":"Total Data Management Blog","publisher":{"@id":"https:\/\/www.iri.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.iri.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.iri.com\/blog\/#organization","name":"IRI","url":"https:\/\/www.iri.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png","contentUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png","width":750,"height":206,"caption":"IRI"},"image":{"@id":"https:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/person\/cdb89f0c0a9c88810b8516d4b140734a","name":"David Friedland","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/995ea08bc7d036da625671cb48a636eb?s=96&d=blank&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/995ea08bc7d036da625671cb48a636eb?s=96&d=blank&r=g","caption":"David Friedland"},"url":"https:\/\/www.iri.com\/blog\/author\/davidf\/"}]}},"jetpack_featured_media_url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2012\/01\/shipyard.jpg","_links":{"self":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts\/787"}],"collection":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/comments?post=787"}],"version-history":[{"count":124,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts\/787\/revisions"}],"predecessor-version":[{"id":12112,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts\/787\/revisions\/12112"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/media\/11661"}],"wp:attachment":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/media?parent=787"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/categories?post=787"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/tags?post=787"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}