The Result Cache in More Detail

With the Function Result Cache, which was added to Oracle Database 11g, developers can drastically cut the amount of time it takes to get data that database sessions ask for over and over again. This cache is kept in the SGA, is shared by all sessions in the instance, and the Oracle Database automatically gets rid of "dirty data." This feature should be used by all applications that run on 11.1 or higher. Find out all you can!

Oracle SQL Developer Data Modeler has five cool features.

Oracle SQL Developer Data Modeler (SDDM) has been around for a few years and is now on version 4. x. It is an industrial-strength tool for data modeling that can be used for any data modeling job. Over the years, Kent has found a lot of useful features and functions in the tool he uses to help him make my models more quickly and efficiently. At least five of these tools, tips, and tricks will be shown in this presentation. He will show you how to do things like changing the reporting templates that come with the software, making and using object-naming templates, using a table template and transformation script to add audit columns to every table, using the new metadata-export tool, and doing a few other cool things you might not have known about.

Since there will probably be patches and new releases before the conference, it is likely that there will also be new things to show. This demo might move quickly, so install SDDM on your device and bring it to the session so you can keep up.

"Pagination" Pattern in SQL: Making Top-N and Pagination Queries Work Well

The SQL query technique "pagination," of which "Top-N" is a special case, is used a lot. It is about getting a small number of the "most interesting" records from a large qualifying result set. Even though pagination requests seem easy (and Oracle 12c made them even easier), they need some preparation in both schema design and query coding to work well. In this presentation, you'll look at some real-world examples of pagination and learn how to make them run quickly by using things like "perfect indexes" and "restart tokens."

A few pitfalls will also be talked about, as well as how to avoid them.

Making SQL work well

When the requirements get complicated, there is a simple way to write efficient SQL that will cut down on the time you spend trying to get the database to do what you want it to do. However, you need to know the data, the metadata, and the purpose of the query to do this. In this presentation, I start with the basic idea of efficient data access, then look at ways to analyze the data (in case you don't know how to do that already), and then go back to the basic idea of efficient data access by modeling a query graphically.

Finish with a small example from a production system I was asked to look at some time ago.