#thanksODC and other news

What’s black, red, black, red, black, red?

A zebra with sunburn

Some great blogs lately and also ODC Appreciation Day on twitter sparked off some good blogs and posts on social media.

Have a look at just a selection of them below, and check out my twitter feed @Addidici and Weekly News for others.

Blogs of the week

  1. Oracle Offline Persistence Toolkit – Submitting Client Changes

Andrejus Baranovskis says, “One of the key topics related to Oracle Offline Persistence toolkit – submitting client changes to backend when data conflict exists. If data was updated on the backend, while client was offline and client wants to submit his changes – we inform about the conflict and ask what client really wants to do. If client choose to submit changes, this means we should push client changes to the backend with the latest change indicator.”

He refers us back to a previous blog.

2. Oracle Data Visualization and the Attribute function

Boris Dahav starts his blog by writing, “The Attribute function is an important function of Oracle Data Visualization, useful when we attempt to create something interesting based on measure values. It was covered by Philippe Lions in the post “Leverage the ‘Attribute’ Function to Boost Data Insights“, but I want to dig a bit deeper and show a simple use case.”

3. One row to rule them all in the cloud or not

Ric Van Dyke begins by saying, “Looking for a single row out of a table it’s always better to use an index and a unique is best.  Yes even for a small table.  As a test of this I have a table that has 10 rows in it and just two columns, a number and a sting of 20 random characters.   I’ll run a select in a loop that gets each row one at a time using the number column which will be unique, 1 to 10.  I’ll do this 100,000 times and capture both elapsed time and CPU time.  Also I’ll capture the number for consistent gets (LIOs) done for each.  The first go around it will be without an index, with an index, with a unique index and last with a unique covering index.  Here is how it stacked up in my test (I ran the test multiple times and the results were consistent).”

4. Connecting Data Visualization Desktop Version 5 to the Autonomous Data Warehouse

Dayne Carley says, “This post details the steps required in Data Visualization Desktop (DVD) version 5 (12.2.5+) to upload Autonomous Data Warehouse (ADW) wallets and to create ADW data connections.”

Start here.

5. Data Enrichment and Obfuscation in OAC 18.3.3

Mark Daynes says, “The new (September 2018) version of Oracle Analytics Cloud (OAC) now includes data enrichment functionality.  To show a snippet of this in action, we have a small excel file with made up credit card details, a name and an address.  We can use both obfuscation and enrichment to enhance and secure this data and then perform some visualisation on the enhanced data.”

He concludes by saying: “This capability is a really exciting addition to the already powerful capabilities of the OAC suite by adding additional value to the data.  It’ll be interesting to see how this capability develops in future releases.”

6. Partial Indexes–Take Care With Truncate

Connor McDonald says, “Partial indexes are a very cool feature that came along with Oracle 12c. The capability at partition level to control index existence allows for a couple of obvious use cases:

1) You index the data in recent partitions only, because small amounts of data are aggressively searched by applications and/or users, but not the older data because the queries for older data are either less frequent or are more analytical in nature.

2) You index the data in older partitions only, because you are putting the recent data into the in-memory store so almost any kind of access is very fast, but you do not want to swamp the entire in-memory store with older data that people rarely query.  But you still want reasonable access performance on that old data.

Whatever your use-case, there is currently an outstanding issue with partial indexes that you need to be aware of. (Note: This is not correct functionality and will be fixed in a future release). If you truncate a table, then the partial index dictionary definition may not be correctly observed for partitions.”

He gives an example and then the workaround.

7. Transporting SQL Patches Between Databases

Marco Mischke says, “SQL patches are not part of the application schema, they are stored in the data dictionary of the database. That is the cause why they are not transported when using Data Pump. But there is a way to transport them. ”

He tells us the procedure then gives an example. He concludes by saying, “That’s it. Nothing more to do. Keep that in mind in case your applications are fine tuned using SQL patches and you need to move them to different databases.”

8. Hybrid Fake

Jonathan Lewis says, “Oracle 12c introduced the “Hybrid” histogram – a nice addition to the available options and one that (ignoring the bug for which a patch has been created) supplies the optimizer with better information about the data than the equivalent height-balanced histogram. There is still a problem, though, in the trade-off between accuracy and speed: just as it does with height-balanced histograms when using auto_sample_size Oracle samples (typically) about 5,500 rows to create a hybrid histogram, and the SQL it uses to generate the necessary summary is essentially an aggregation of the sample, so either you have a small sample with the risk of lower accuracy or a large sample with an increase in workload. This being the case it’s worth knowing how to create a hybrid histogram using the dbms_stats.set_column_stats() API.”


Sarah Craynon Zumbrum posts her fist blog in a while, so it’s important!! She says, “there’s something we need to discuss that I don’t feel Product Management has done a great job of doing – letting you know that the newest release of OAC (namely OAAC) requires some new downloads.”

She also gives us the links to do so.

10. “Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part II (Aladdin Sane)

Richard Foote says, “In Part I of this series, I highlighted how a Non-Partitioned Global Index on a Partitioned Table is able to effectively perform “Partition Pruning” by reading only the associated index entries to access just the table blocks of interest from relevant table partitions when the table partitioned keys are specified in an SQL Predicate.”

He concludes this post by saying, “However, Global Indexes have a key performance advantage over Local Indexes which I’ll discussed in Part III.”

This week on Twitter

Last week we had ODC Appreciation Day on Twitter with the hashtag #thanksODC

Denis Savenko tweeted ODC Appreciation Day: Significance of Sharing and Feedback

Piet De Visser shared ODC Appreciation Day

Have a look at #thanksODC to see them all!


Stories from www.us-analytics.comwww.clearpeaks.com and www.thatjeffsmith.com

Videos such as:

Keep Your SQL Statements Simple But Fast!

The recipe for a great conference