{"id":1605,"date":"2020-11-21T19:43:13","date_gmt":"2020-11-21T17:43:13","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?page_id=1605"},"modified":"2020-11-21T19:54:55","modified_gmt":"2020-11-21T17:54:55","slug":"downloads","status":"publish","type":"page","link":"https:\/\/blog.sqlora.com\/en\/downloads\/","title":{"rendered":"Presentations"},"content":{"rendered":"\n
\"\"<\/a><\/figure><\/div>\n\n\n\n

SQL Macros – Game Changing Feature for SQL Developers?<\/strong><\/a><\/p>\n\n\n\n

#DOAG2020<\/a><\/p>\n\n\n\n

The fear of complex, hard-to-read SQL queries often leads developers to break them down using countless PL\/SQL functions. Sounds familiar and like best practice for you? However, you often have significant performance drops caused by well-known context switches between SQL and PL\/SQL. Even worse: the business logic hidden in PL\/SQL functions remains invisible to the Query Optimizer!
With SQL Macros – a new feature in Oracle 20c – the developer no longer has to make the difficult choice between modular, clear code and good performance. The idea behind the new PL\/SQL functions is that they are not executed at runtime (no context switch!), but return SQL snippets that are incorporated into the main query just before parsing. This allows to encapsulate the logic while remaining fully transparent to the optimizer. <\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

Polymorphic Table Functions in 18c<\/strong><\/a><\/p>\n\n\n\n

#DOAG2018<\/a><\/p>\n\n\n\n

SQL has many powerful techniques to analyze your data right out of the box, but remains extensible even if you’re missing something. In Oracle 18c, you can do it much easier with polymorphic table functions (PTF). As an evolution of table functions, PTF is called in the FROM clause and is capable to encapsulate the custom processing of the input data, whereas the type of the input row need not be known at design time and the type of the output row may be determined at runtime based on the actual PTF invocation parameters.
Based on simple examples, this session will give you an introduction to the main concepts and the infrastructure behind the PTF. Discover how you can develop your own flexible and self-describing extensions while focusing on business logic and leaving complex things like parallel execution to the database.<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

Online Statistics Gathering for ETL<\/strong><\/a><\/p>\n\n\n\n

#DOAG2018<\/a><\/p>\n\n\n\n

Online Statistics Gathering for Bulk Loads – the official name of the feature – was introduced in Oracle 12.1. The idea is to gather optimizer statistics “on the fly” for direct path loads. Sounds good for ETL? In certain scenarios it makes sense but even then there are many points to consider so that it becomes a reliable part of your ETL processes. When exactly will it be working and when not? Do you prevent it yourself? Documented, undocumented cases, known bugs. Which statistics are gathered and which are not? What has to be considered with partitioned tables? Interval partitioning – special case? <\/p>\n\n\n