OGB Appreciation Day: Efficiency with Partitions.

Partitioning is Very Useful - If ... you take the effort to Design for it properly.

What I like best about Partitioning is the "life cycle feature". You can remove large amounts of (old) data with a single statement in the blink of an eye, and with very little Redo incurred.

If you want, you can also Add or Move data  around in the blink of an eye as well.

Google for :
Alter table Drop Partition
Alter table Add Partition


Use Partitioning for Fast and Efficient Data Manipulation (notably Deletes)

The main "successful" use I've seen from partitioning is this: (Re)Moving large amounts of data in the blink of an eye, without incurring Undo+Redo for the total amount of data, and without locking.

Very little Redo (it is a DDL operation, not DML)
No Locking.
No Index Rebuilding (e.g. only local indexes).
Fast!
Efficient!


Your Mission, should you choose to accept it: 
Verify this for yourself!

Here is your homework.

If you are serious about trying / using this feature:
 - Create a partitioned table with at least 2 partitions.
 - Insert data into both partitions, at least 100K records (and commit)
 - generate statistics, verify the approx row-counts in each partition.
 - set Autotrace on
 - set timing on
 - Delete all the data in 1 partition with a "Delete from ... Where ..."
 - Note the time and the amount of Redo involved.
 - commit (just dont forget...)
 - Re-generate stats and verify the row-counts, your data is deleted ?

Now for the Partition-operation...
 - Re-create the same table, same partitions, with same data (commit!)
 - generate + verify stats...
 - Set autotrace + timing again
 - Drop a partition: Alter table ... drop partition ;
 - note: the implicit commit... this is a DDL operation.
 - Note the time and the amount of redo.
- Verify the data is gone.. (trust-no-1....)


QED.

Now how cool is that?
And so Simple...


Go ahead, do it, and et us know the differences in Time and Redo in the comments...

My (not quite conform) example script is here

And if You can Do this:
Congratulations! You are on your way to Master the use of Partitioning.



Now, there is a lot more to Partitioning, of course.

My opinion in Short, rather simplified statements.
 - You need to "design" partitioning from the start. A "bolt on" to an existing data-model will 99/100 fail.
 - Use (only) automatic interval partitioning, prevent yourself from having to pre-create partitions regularly (you will forget...)
 - Every SQL to Ever Access that table needs a partition-key in the where-clause.
 - Avoid global indexes if possible.

NB: I know the "maintain global indexes" was created to prevent+fix some of my pet-problems-with-partitions, but I am still skeptical about the usage in live (OLTP) systems.

NB2: Kuddos to Hermann (dot) Baer (at oracle dot com), a.ka. @sdjh2000 for constantly improving the capabilities of the partitioning option. Good Work on a Great Feature.



That's all for now folks.

With a Large Kuddos to Tim Hall (@oraclebase) for the Yearly OGB-appreciation day (link). Makes me blog at least once per year...

OGB-appreciation day was f.k.a. the OTN-Appreciation-Day,
re-branded as the ODC-Appreciation day,
re-branded as the OGB-Appreciation Day.
Who knows what next year will bring - Oracle Cloud Dev-Ops (OCD) Appreciation day?


And if you got this far, a word of warning:
Do Not Ever Make Fun of Oracle Buzzwords or Hashtags.
Humour and Oracle only work if it is Oracle-Approved humour.
(mandatory buzzword compliant content...)
Remember: We all work for Larry, Only some of us dont know it yet.

CU at some event, at some webinar or next year on this blog-event.

Hashtag : #ThanksOGB

ODC Appreciation Day..

It is that time of year: Write about your favorite feature and say "Thank You Oracle"..

This year, I'll pick Oracle Golden Gate

Short story: if you need to replicate data, GG is one of your easy choices.

Slightly Longer story:

I was lucky: customer choose GG to replicate data for one country to another in major move of IT systems and data (yes, to the cloud, to those large Remote Database Systems in AWSome land...).

GG helped us run in sync for a number of weeks, and the final cutover was a breeze.
GG also (sort of) kept open a replica back to our original datacenter, so management felt they could potentially chicken out and move back to the old provider.

The whole process was easier than I expected,
and a Large Tip of the Hat to Golden Gate is appropriate.


(the issues, the problems, the quirks.. Later)

The reason for this post Tim Hall
https://oracle-base.com/blog/2018/10/11/odc-appreciation-day-effective-evangelism-staying-positive/

The link to Golden Gate (Hi Bobby!)
https://www.oracle.com/middleware/data-integration/goldengate/

What is SmartDB

What is #SmartDB ?

This question came up in the twitter discussion about #SmartDB, and all the advantages it brings (link to twitter).

Over the last year or so (and way before that, with the Helsinki-declaration in 2008), Toon Koppelaars has given us the reasons and guidelines for #SmartDB, and it boils down to “do the work in the database” (correct?)

So the shortest description, IMHO, would be : 

SmartDB: 

Any IT system using a database should do as much as possible of its processing inside the Database, and as little as possible of its processing in other layers.

(Agreed?)

TL;DR ? Read no further.  ;-)

Background of this approach is that this would lead to least-complexity, least-components, least round-trips, least-overhead, and least-complicated troubleshooting (only one component to examine and fix… ). 

Also, my too-short definition doesn’t include (yet) the need to apply sound database-practices. Good IT systems start with good (system) design (based on requirements). It also  includes things like 3NF, ACID, notably resilience, and adequate security based on minimum-privs and minimum exposed surface. Then there are “scalability”, “upgrades” and “monitoring” to allow the system to remain in action over longer periods of time, and under various loads. Sustainability, if you like.

To me, all the above still makes sense. And I feel comfortable to design/build an IT system given those guidelines. 

Of course, some are not content with an extremely short definition, and others demand a more elaborate description or a how-to cookbook-guide. All of that, Toon and Bryn are trying to provide in various presentations, videos and white papers on the subject.

I’m going to add a a suggestion.

To better define and describe SmartDB, I suggest to follow these steps:

- Requirements, 

- Reasoning, 

- Recommendations.

Those three steps should lead us to a better description and thus to a better way to “evangelise” the SmartDB concept. 

To Elaborate each step:

The (list of) Requirements should state why SmartDB is needed, and which problems the concept tries to solve.

By following logical Reasoning, with the knowledge and technology available, we can explain how each requirement is addressed in what is the most efficient way known to us.

To finalize, we create a list of Recommendations (if not: Directives!), on how to implement SmartDB. The recommendations should, at first, not be connected to any particular database or programming language. Those details can be filled in later. Each "vendor" should do this for his own product, and hopefully stay within the concept of SmartDB.

The result of this exercise should be (yet another) white paper, 10 page max, and some presentation material that we could throw at Developers, Architects, Managers and even Dev-Ops and UX wizards to explain and convince them.

Some history: 

Long time ago, in a cubicle-space far far away, an OFA-standard was defined via a similar process in the early 90s
I am that old. I recall how this Oracle Flexible Architecture was created and "explained" (OFA was the mother of all Oracle Standards - link needed).

OFA was created roughly in those three steps: Requirements, Reasoning and Recommendations (Directives!) I’ve not checked the original paper for years but this is what I recall…

This proces of problem identification and reasoned solutions was clear and could be explained to most users (Sysadmins, DBA’s) at the time. The standard was widely adapted and cited in the Oracle-DBA world. 

So, to recapitulate, I think we should follow a similar path to define the SmartDB concept: Requirements, Reasoning, Recommendations. Those three steps should lead us to a better description and thus to a better way to explain, promote, and verify the SmartDB concept. 

(I know, I know, Bryn is going to say that all of the above is covered 11.2 years ago with EBR, and we only need 4 schemas… but still, it wont hurt to re-assert some items…)

Additional notes:

note1: I have barely mentioned Oracle (or PostgreSQL, or MySQL, sukkel-Srvr, or even sp-hna). The SmartDB concept should not be locked into a particular product.

note2: I have not mentioned any theoretical or academic knowledge, but I would assume IT ppl to be familiar with handling requirements, IS-design, ACID, 0+12,  UX, some OOP, various methods of testing, etc… 

note3: I have not mentioned any procedural language, but SmartDB does imply the use of some stored-procedure dialect

note4:… there is much more, but it needs to be discussed. 

Quick notes on SmartDB (temporary post)

Since there is a discussion going on, mostly on Twitter (link) about SmartDB, I'm going to put my own notes here. That way I have a public-place to "think out loud" about the concept of SmartDB.

Smart-DB is, in my view:
A concept where an IT system is built In the DataBase.
Constructed so that most of the work (and processing, and maintenance) 
is done IN THE DATABASE.

This makes for a single point where all (99%) of the logic can be found, and where all (99%) of the code can be found and maintained.

Maximum logic/work/processing on the DB.
Minimum processing in front-end-tier (browser?).

Eliminate other layers and components.

Key point, in my view: Eliminate layers, eliminate components, and eliminate sources of problems.

Also eliminates round-trips between components, a notable source of delay.

note: SmartDB is more then just using PL/SQL or pl/pgsql, but those "stored procedure" constructs are an essential part of the SmartDB concept. Hence the focus often shifts to these tools.

Keep in mind the overall goal: 
A Working and Sustainable IT system (to process data for some end-user-business purpose).

Data and Data-model tends to survive the tools and even the "processes" of the system.

SmartDB methodology, as i  was taught years ago, would go more or less like this....

 - Ensure you have a notion of IT technology and Design. (e.g. design of process and data)
 - define the goal of your system: why will this system exist and who are the users, “consumers” ? 

 - define datamodel. what data needs to go  in+out of the system, and what needs to be stored.

 - define processes, functionality, functions.

 - refine data-model and table design. one possible Quality checks is 3NF. 

 - define the interaction between processes and data (CRUD matrix)

 - list/define the interfaces : which external actors bring/consume data to/from the system

 - define/determine the format of the interfaces (e.g. message-definitions)

 - program the interfaces to receive and return their data and statuses (e.g. error-messages)

 - check for completeness: determine the life-cycle of all entities/records.

List of notable advantages (Toon + Bryn hava a much better list...link?):

 - logic and data in 1 place. (look no further)

 - back end can survive multiple versions of "front-end" or UX (if a front-end exists at all!)

 - troubleshooting in 1 place (look no further, no discusion, no hiding, no escape.... )

 - potential to minimize round-trips. Every "interaction" should be a message, preferably a single round trip.

 - Messages can be de-coupled (most, not all...), and queueing mechanisms can be used for resilience.

 - messages should be constructed to allow re-play (re-submit) without damage to integrity of the system  or database.

 - ACID only required in the Database. 

 - The database-replication mechanisms, and only the DB-replica!, provides DR capability.

Use/sale of Tools or Products is not a goal
Use/sale of Cloud is not a goal
Use/sale of "big data" is not a goal.
Use/sale of UX / Microservices / ClientServer / Cobol / R-sharp / Dockernetis / etc... is not a goal...

Future work:

- include links to proven methods of data-design (Heli?) . Include data-design in text.

- include links to ACID and 12+1 rules for Databases, include in text.

- include links to proven methods of Requirements and how-to Design... 

 - Do we have to start "Teaching IT from Scratch" again ??? 

So far my notes.

I also have life, you know..

ODC Appreciation Day: IOT, Index Organized Tables

The ODC appreciation Day is a yearly tradition created by Tim Hall of Oracle-Base (link) to say "Thank You" to the Oracle Developer Community. This year he designated 10-Oct-2017, hence this post.

link to Tim: https://oracle-base.com/blog/2017/09/25/odc-appreciation-day-2017-thanksodc/

To join in on the ODC chorus, I'll present my favorite, very Simple, feature. 

The INDEX ORGANIZED TABLE. 

This feature is a favorite of mine, because 1) it is simple, you can explain it to almost anyone in under five minutes 2) there are a few additional benefits that kick in with the way Oracle has implemented this feature. 

Short Description:

In an Index-Organized Table (IOT) is a table of which the of records are (physically) stored in the order of their defined primary-key index, even if they are inserted at very different points in time. In a conventional (heap) table, the dependants of 1 parent may get scattered all over the heap, depending on the time of insert, whereas in an IOT the children of a parent will always end up in the same block. (or adjacent blocks if more are needed).

Use-Cases:

In my experience, IOTs can be beneficial in 3 specific cases :

1) parent-child tables where retrieval often includes the "fetch all children" case. 

2) link-tables that connect two sets in an n:m relation.

3) lookup tables.

I will not say you need to take these cases as "must-do" or "best practices", because there are cases where IOTs will fit nicely, but there are also cases where they will backfire on you. Go Read.

History and "other databases":

IOT has been around since Oracle 8 or 8.1 (the nineties!), but somehow has only been used by nerdy-geeky DBA's in specific cases. Competing Databases have "discovered" and used this feature more than Oracle. Hence explaining this feature to converted colleagues is sometimes very easy.

Further reading:

I would encourage you to explore the Manual and other sources of information, especially if you have large "hierarchical" sets of data with parent-child relations such as history-keeping or sets of object-attribute tables.

Richard -Mr Index- Foote has discussed IOTs in 2012:

https://richardfoote.wordpress.com/2012/01/10/index-organized-tables-an-introduction-of-sorts-pyramid-song/

And Martin Widlake in 2011:

https://mwidlake.wordpress.com/2011/07/18/index-organized-tables-the-basics/

And here is the link to my original blogpost about IOTs, mainly based on my work on a few project from around that time:

http://simpleoracledba.blogspot.nl/2007/05/index-organized-tables-iots-forgotten.html

Voodoo Anecdote: 

Kamil and his team on the Voodoo product have recently found to their surprise(?), that direct-inserts do not work on IOTs. (link) Not surprising, if you realise that each record will have to be inserted in exactly the right place in the table: in between it's "siblings", to maintain the correct ordering of the records. Hence a Direct-Insert (inserting into a new block) would only be possible if the inserted data adhered to at least two conditions: 1) new records should have higher PK values than the highest existing record and 2) the newly inserted data would need to be presented in a (pre)ordered way to maintain the IOT property.

Link to Kamil: http://blog.ora-600.pl/2017/10/02/direct-path-insert-and-iots/

Careful study of the Really Terribly Fantastic Manual may reveal that a direct-insert on IOT is (was?) possible, but only when inserting pre-ordered data into an empy-table (link to docu, if I can still find it).

IoT versus IOT

Since the abbreviation IoT is now in use for Internet of Things, any mention of IOT (index organized table) seems to get lost in the noise. Hence this blog-post is also little reminder to the search engines to keep the legacy-item of IOT alive.

PG Day Russia, out of comfort zone.

In one week from now, I will be visiting the pgday.ru event in St Petersburg.

A big Thanks (Спасибо) goes to Timur Akhmadeev for bringing the event to my attention.

Curious as I am, I decided to try and send them some abstracts, and Low-and-Behold: I got selected to speak. Twice. Wow, Honored. My head was swelling with pride...

To some of my oracle Friends: I can recommend visiting non-Oracle events: You can stop bickering about the CBO or the opatch-inventory, and re-start living again. We can learn a lot, and we may even warn the PG community of some of our oracle-based mistakes.

It is Interesting and Fun just exploring the fellow-speakers and topics.
The PG community seems full of talent and experience. For example, they are also (re)discovering the benefits of "smart-DB" and "thick-DB" (link to "continuous delivery of stored-procedures").

So today I got my Passport back (visum approved), and I am all set to go. I really look forward to wandering around this event as a complete noob in a Foreign Environment.

#Curious  #любопытный

OUG Meetings Spring 2017

Planning is in the air.

This spring, if all continues to go well, I will visit six events in a nice, long spring "tour". And here is the Logo-and-Link-fest of the various events:

15-16 May, the Riga Dev Days (RDD):

24-26 May: Serbian Oracle User Group conference in Zlatibor:

30 May: meetup of RoOUG: 

02-04 June: BgOUG spring conference :

15-16 June: the OGH tech experience:

20 June: AOUG, Austrian Oracle Anwender Conferenz :

I cannot wait to get on the motorcycle again. On a map, it looks like this:

Hmm, if I stretch it, I could make an interactive Google-map with all the events + dates on it. But I'm too lazy; I'd rather go visit those places by motorcycle... 

The map link for the complete trip-plan is here, if you want to zoom in on the details: https://goo.gl/maps/1YvXPzCowMJ2 .

And if you live along that route and feel like grabbing a cup of Coffee while I check the Wifi Signal: Contact me!

Regrettably, I will have to skip two events due to time constraints: OUGF and ITOUG
I have politely cancelled going to Helsinki this year. I’m really sorry to miss the OUGF.fi Harmony event because it is always good quality content and nice social activities. I’ll try to be there next year folks. Kiitosh.

And, although I am very curious, I have to politely skip the (first?) Italian ITOUG tech day on 06 Giugno. I love to go to “new” places and start-up-usergroups, but this one would mean a race from Bulgaria to Milano. I hope to visit an Italian event soon though (it will mean going over the Alps and drinking Good Coffee, always a Pleasure!)

And by the way, I've done trips like this for 3 years in a row now. I know how to do this by now, and it is Great Fun. Check last year's tour on the interactive map-log at MyAdventure.bike...

See  you on an OUG somewhere !