The Data Warrior

Changing the world, one data model at a time. How can I help you?

Archive for the tag “oracle sql”

#OOW13 – Oracle OpenWorld 2013: Pictures, Tweets and more

#OOW13 is well underway now with lots of tweets and blog posts already out on the web. Check the stream on twitter under #oow13 for all the news.

I tried to see some of the Americas Cup race on Saturday but unfortunately the weather and wind did¬† not cooperate so the race was called for the day. ūüė¶

But got a few pictures to share:

Americas Cup Racing: Team USA

Americas Cup Racing: Team USA

And a  nice view:

Golden Gate from the East Bleachers at the Americas Cup

Golden Gate from the East Bleachers at the Americas Cup

The New Oracle Plaza

Imagine our shock when we learned that there would be no Howard Street Tent this year! The nerve.

But in its place is the open-air Oracle Plaza, full of comfortable seats for hanging out, networking, eating lunch, and watching the keynotes on a big screen. Oracle is really counting on the weather to be typical dry fall weather. Hopefully that holds up.

OOW13: The New Oracle Plaza

OOW13: The New Oracle Plaza

User Group Sunday

I did attend a few sessions on Sunday.

The only session this year (from a user) on my favorite tool, Oracle SQL Developer Data Modeler was given by Heli Helskyaho, the CEO of Miracle Finland (@HeliFromFinland).

Heli Helskyaho: Why do I need #SQLDevModler?

Heli Helskyaho: Why do I need #SQLDevModler?

She did an excellent job of introducing folks to SDDM and laid out 9 use cases for using the tool.

9 use cases for #SQLDevModeler

9 use cases for #SQLDevModeler

(Hard to read I know, but zoom in…)

I also attended a session by my buddy Stewart Bryson, who is now the new Chief Innovation Officer for Rittman Mead globaly, Congrats Stewart!

He did an amazing (IMO) talk about how to go beyond agile and achieve Extreme BI. He gave us the beginnings of a complete Oracle-based framework which looks very promising.

He said some heretical things like skip the staging area for your data warehouse! I do think he is on to something and can make it work so we have a real chance at real time BI and still have a solid architectural foundation.

Stewart Bryson's talk about Extreme BI

Stewart Bryson’s talk about Extreme BI

Opening Keynote

Of course the was the opening Keynote by Oracle CEO Larry Ellison. Key words: #bigdata #inmemory and #AmericasCup (of course). Lots of big news.

Check out this info graphic about the event.

Mark Rittman already did a nice review of the talk (among other things). So rather than repeat check out his post.

And for those interested, right now the Americas Cup standings are USA 5 to New Zealand 8. But that will change later today for sure when they race again.

More to come about #oow13 and this years Oaktable World (#OTW13).



The Oracle Data Warrior

P.S. Had a great evening last night at the annual Oracle #ACE dinner which was hosted at the Walt Disney Museum in The Presidio.

Another Free Tip for SQL Developer Data Modeler (and a Challenge)

So here is another tip on using Oracle SQL Developer Data Modeler – but this time it is one I will NOT be showing during my session at #KScope13.

In SDDM there is a really nice wizard for building views. Once your view is built, you should probably test it right?

You could generate the DDL, or use DDL Preview, then open up SQL Developer, run the script to build the view, then select from the view to see the results.

Lotta steps, eh?

Or you could simply use the Test Query button on the View Properties dialog.

Press the button, then select a database connection from the drop down list (or create a new one if you need to). This will create a connection to the database and cause the SQL in the view to be executed. If everything works you will see “Query executed successfully”.

The Test Query button will first check that the SQL will execute

The Test Query button will first check that the SQL will execute

If you want to see the output of the query, just click the Query Results tab.

The Query Results tab lets you examine the output without opening another tool

The Query Results tab lets you examine the output without opening another tool

Pretty cool. And must faster than the old way.

Now for the Challenge:

  1. You must be attending KScope13.
  2. You must be the first person to find me onsite in New Orleans and tell me you read this post.
  3. You then have to show up at my Top Ten Tips session at the conference, join me at the podium and tell the attendees this tip!

Your reward:

  1. It might be your first chance to present at an international conference.
  2. I will buy you the adult beverage of your choice that evening and chat about SDDM (or another topic of your choosing).

Not a million dollars, but who knows where it might lead or who else you might meet along the way.

So, who’s up to the challenge?

See you in New Orleans.

Oh – if oyu missed the first tip, it is here.


P.S. Jeff (@thatJeffSmith) and Chet (@OracleNerd) – sorry guys but you are not eligible! (I may buy you beer anyway)

RMOUG Training Days 2013 – Day 2

So on this 2nd and final day of the annual RMOUG Training Days event, I started out by attending an excellent session on Exadata for Oracle DBAs.

Even though I am not a DBA these days I thought it would be good for me to get a better understanding of Oracle’s engineered Exadata machine.

I feel very luck to have attended this session given by Oracle Technologist of the Year, and ACE Director, Arup Nanda. He had some of the best graphics and clearest explanations of the basic anatomy of an Oracle database I have ever seen or heard.

Technologist of the Year, Arup Nanda, Database Machine Administrator

Technologist of the Year, Arup Nanda, Database Machine Administrator

He gave some pretty detailed explanations of what he called the “magic” of Exadata and why it works so well. Arup even coined a new job title, which he claims for himself, DMA – Database Machine Administrator. Because Exadata is an engineered system, it contains database, storage, and networking all in one rack. This requires some skills beyond what most dbas have or are expected to have.

He gave us a nice break down based on his experience using Exadata.

Break down of skills needed to be a successful Exadata DMA

Break down of skills needed to be a successful Exadata DMA

After this talk I can see why he was give the awards. He really knows his stuff and how to communicate it. You can follow him on Twitter @ArupNanda and see for yourself.

Next I went to see my friend,and ACE Director, Galo Balda from Austin, Texas. He gave a very informative talk about Regular Expressions.

ACE Director, Galo Balda, doing his very first presentation

ACE Director, Galo Balda, doing his very first presentation

His presentation was very informative with easy to understand examples of how to write and use regular expression and associated metacharacters to do some pretty neat things with SQL. If you attended the conference be sure to download his slides. They will make a great cheat sheet.

You can follow him on Twitter @GaloBalda or go to his blog.

After a nice vegetarian lunch, I went to see Maria Coglan talk about using (or not using) hints in SQL and how it affects the optimizer. Last year at Kscope12, I attended one of her optimizer sessions and felt like my head would explode becuase of all the information she gave. She assured me this talk would not be as bad.

She was right. It was a very informative talk.

A full house to see Maria Coglan discuss Hints and the Optimizer

A full house to see Maria Coglan discuss Hints and the Optimizer

Her main message was to always use caution when using hints. You really need to understand what you are or doing or you could make your application or reports run worse rather than better.

Maria even explained how to work with applications that already have hints embedded in them.

Approach to ignoring hints in an existing application

Approach to ignoring hints in an existing application

Get her slides and follow her on Twitter @SQLMaria

After Maria’s session I did my final session for the event. I talked about my Top 10 favorite cool tools in SQL Developer Data Modeler. There were 30 or so people in attendance. Most of them even stayed through the whole talk!

Which is pretty good since I ran over my time. There was just so many tips and tricks to show.  I will put it up on SlideShare in the next few days.

The final session for the event that I attended was done by RMOUG President, my long time friend, Tim Gorman.

Tim talked about the various options for data compression in the Oracle stack.

Tim Gorman (in the shadows) giving the last talk

Tim Gorman (in the shadows) giving the last talk

Tim gave some pretty detailed explanations and tried to depict how compression works with some nice graphics. He also told us which ones cost additional license fees.

Data Lifecycle when using Compress for OLTP

Data Lifecycle when using Compress for OLTPut how have

For me, the most useful part was his explanation about how having columns at the end of the table allows a default sort of compression to take place. I had heard this a long time ago. It was the reason so many of us were taught to put all mandatory columns at the beginning of the table – it saves space. In recent years I have been told by various DBAs that the rule no longer applies or made sense.

They were wrong! Tim gave us a real world example of how putting populated columns at the end of a table cost a lot of extra space to be used.

I will be taking that tidbit of information back to the office for sure.

You can follow Tim on Twitter @timothyjgorman.

A side note about RMOUG: At lunch, Tim shared with the attendees an agenda from 1991 for the 2nd RMOUG Training Days. We now realize that we started this event in 1990 and next year will be the 25th anniversary! (I say “we” because I was part of the planning committee back then and one of the early speakers too).

Another interesting notes was that 2/3 of the speakers came form out of town. Many, including me, paid there own way. Several speakers and attendees I know even had to take vacation time from their jobs to attend.

It is that important and that good an event!

So put it on your calendar to attend what is probably the most successful and longest running regional Oracle user conferences in the country. It will be in early February 2014. Watch for details.

And of ocurse count on me to post it here too.

Ciao for now! I am off to ski with some RMOUGers tomorrow.


ODTUG KScope12: Day 1 Symposium Sunday

Wow. What a day!

Started off with leading a Chi Gung class at 7 AM to about 18 attendees. Great start to the day.

Then it was off to the races with the kick off of the BI Symposium, chaired by Kevin McGinley. I got to be “interviewed” about my ¬†Data Vault Modeling¬†session on Monday ( I will report on that tomorrow) , along with several other presenters. That was¬†followed¬†by a lively talk show-style discussion led by Kevin and Stewart
Bryson. Below see the room and audience in attendance at 9:00 AM on a Sunday. (pretty good turn out – way better than last year!)


The panel discussion was followed by a series of talks from Oracle BI product management. There was lots of talk about mobile BI, Oracle’s¬†acquisition¬†of Endeca and of¬†course¬†BI in the Cloud.

(At this point I switched tracks to the Db development symposium chaired by Chet Justice aka @Oraclenerd)

The next talk I attended was by Kris Rice (@krisrice) who gave an intro to Oracle SQL Developer Data Modeler. (Nicely he plugged my Data Modeler talk on Thursday)

Some review (for me) and some new stuff too. I learned his trick for showing the joins between views – use the view to table utility to convert the views to tables, add PKs, then use the Discover Foreign Keys feature. This creates FKs based on column names and know PKs.

Cool trick. Just gotta remember to set “generate DDL” to “No”.

Quick switch back to the BI Symposium to see some screen shots of a new look and feel for OBIEE with modern mobile themes.

More coolness…especially if you are an iPad sort of geek.

Back to DB dev land (is it lunch yet?) to hear Oracle product manager Jeff Smith (@thatjeffsmith) take about full lifecycle development using SQL Developer.

Lots of great tips from Jeff about generating table api’s, using version control, doing schema diffs, and unit testing.

SQL Developer definitely has lots of features I did not know about. Being able to define unit tests inside the tool seems like a valuable option. I will be getting folks at my client site to try it out next week!

Oh yeah – he also mentioned DB Doc for creating HTML documentation ¬†on your code because code is never really self-documenting. Gotta check into that more too…

<Lunch break Рyummy Italian selection of salads and food>

Post-lunch back to BI and Mike Donohue from Oracle talking about reporting on data from “beyond the data warehouse.”

Heaven forbid! (well I guess we gotta deal with it now)

So, Mike talked a bit about how Endeca Information Discovery can be used to gain¬†understanding¬†and build¬†analytics¬†on big and unstructured data. Mentioned “faceted data model” and generating a key value store. Sounds cool. Have to look into that too.

Mike also discussed using BI Publisher to allow users access to local data (in Excel, XML, OLAP, etc)  so they can build their own reports. Scary thought but, in some businesses it will make sense because in reality not all data is in an ERP system or a well built RDBMS.

Whata gonna do?

<Back to DB Dev>

No to hear the world-famous Tom Kyte (of Ask Tom fame) talk about his approach to tuning. It was, as expected, a full house.

Tom’s main point was not to necessarily tune the specific problem query but more holistically to look at the overall algorithm (or approach) that was taken to solve the problem in the first place.

In his experience many¬†queries¬†can’t be tuned all that much when¬†what¬†was written was not even the best way to solve the problem. He gave quite a few eye-opening examples where there was simply a much better way to accomplish a¬†task¬†than the SQL that was¬†originally¬†written. Seems many¬†situations¬†really require re-engineering the solution.

A nice take away (that makes you go “duh”):

More code = More bugs

Less code = Less bugs

Moral of the story Рfind the simplest solution. If the code is really complex, you are probably wrong (or at least over complicating it). Try again.

Last symposium session for the day (for me) was Maria Colgan (Oracle) talking about tips to get the most out of the Oracle Cost Based Optimizer.

Maria is the queen of the optimizer. She explained what the optimizer will do in several situations and why and if it is wrong, what you need to change to get it right.

Okay – already on brain overload (and it is just day 1!).

Need sleep.

Have my own presentation tomorrow.

And Chi Gung at 7AM.

C ‘ya


P.S. There were lots of tweets all day with more pictures of the event. To see them look for #kscope and @ODTUG on Twitter (or follow me @kentgraziano).

Post Navigation

%d bloggers like this: