For this test we'll use the Google maps geocoding rather than routing directions and try to geocode the address of Oracle headquarters. If we call the Google api with instructions (more...)
by Kim Berg Hansen
Okay, okay, Iggy, you want one buffer get? I’ll do it ;-)
Actually two different versions that might be useful in different circumstances. First one goes like this:
create materialized view log on employees with rowid, commit scn (employee_id, job_id, first_name, last_name, department_id) including new values; create materialized view log on departments with rowid, commit scn (department_id, location_id) including new values; create materialized view log on locations with rowid, commit scn (more...)
Iggy Fernandez has been asking why not combine all my three materialized views into one to get a single buffer get.
Sure it is possible to create a single one that is fast refreshable on commit that can be used for query rewrite, as demonstrated by Vlado Barun. The trouble is we have predicates both on name and job title, so we need full index scan on the single mview (which can be done with (more...)
by Kim Berg Hansen
Anything goes that is not explicitly disallowed? Okay, then let’s get down to three buffer gets ;-)
As a DBA I grant rights to HR:
grant on commit refresh to hr
And then in HR I create some materialized views and logs:
create materialized view log on employees with rowid, commit scn (employee_id, job_id, first_name, last_name, department_id) including new values; create materialized view log on departments with rowid, commit scn (department_id, (more...)
by Kim Berg Hansen
I just keep playing with this inspired by the other great entries :-)
Emmanuel Humblot posted an entry that used as few as 12 buffer gets – very good. His entry also reminded me to remember to look at the constraints of the tables in order to see if some assumptions can safely be made that enables more efficient code – like for example that it can safely be assumed that (more...)
This was my fifth consecutive KScope and I plan to go every year. That is one awesome konference for developers and it just gets better every year. I'll be at KScope15 in Florida for sure! I had a great (more...)
I'm creating a nested table type and a table with a column of that type (more...)
After a train to Copenhagen and a plane to Amsterdam, I checked in at the CitizenM hotel at Shiphol airport. A bit different hotel - toilet and shower in big glass tubes in the room - but actually fairly nice for a single traveller.
Patrick Barel was kind enough to pick me up, so getting to the Amis (more...)
Can't wait until KScope14 in Seattle?
Or can't make it to Seattle?
Then you can see some of the presentations at a preview at the AMIS offices in Nieuwegein, where some of the KScope14 presenters will try out their presentations in Database development, APEX and ADF tracks. I've been invited to do my presentation on analytic functions, so I'll be there ;-)
It'll be Thursday June 12th 2014. See the agenda here and more details (more...)
When we got Active Data Guard in the spring, (more...)
That means there is now accumulated quite a bit of my work as quizzes - each quiz demonstrating some knowledge of SQL. I could replicate this work as blog posts as well, but instead it is now possible for you to search all my quizzes on PL/SQL Challenge.
I have added that link to the right-hand menu of the (more...)
From my KScope presentation examples here is a simple case of a rolling sum of salaries:
, sum(sal) over (
partition by deptno
order by sal
order by deptno
DEPTNO ENAME SAL (more...)
If yes, then read on :-) ...
In January ODTUG started a little competition where you could win a dinner for two at KScope13 in New Orleans by telling about your favorite experience from KScope. I entered a little story from KScope12 where I presented on analytic functions.
Surprise, surprise - I won \o/ ... But as I haven't spotted any other entries in the competition, I (more...)