We have a reporting application that does just this; and the query also links to an authorisation table that decides what types of users see which reports.
You can create a snapshot (materialized view) using something as simple as
create materialized view apx_application_pages
With the release of the Oracle Database 18.104.22.168 there was a number of new features and options. Most of the publicity has been around the in-Memory option. But there was lots of other features for the DBA and a few for the developer.
One of the new SQL functions is the APPROX_COUNT_DISTINCT(). This function is different to the tradition count distinct, COUNT(DISTINCT expression), in that is performs an approximate count distinct. (more...)
In this post I want to show some example of using a new feature in 12c for selecting the first X number of records from the results set of a query.
See the bottom of this post for the background and some of the reasons for this post.
Before we had the 12c Database if we only wanted to see a subset or the initial set of records from the results of a query we (more...)
Kim Berg Hansen from Denmark wins the SQL mini-challenge for his UNION ALL materialized view with fast refresh on commit. An analysis of his solution has been published in the NoCOUG Journal. Judge’s statement: “Kim won on participation, efficiency, and accuracy. He kept submitting refinements, lowering the bar until he reached the theoretical lower limit of 1. His solutions survived the test cases written by the judging committee.”
The SQL Mini-Challenge is now closed. The judges will be Kyle Hailey, Tim Gorman, and Iggy Fernandez. The winner will be announced in the fall issue of the NoCOUG Journal.
Correctness is the primary screening criterion that will be used by the judges. Submissions are expected to produce the same results as the original query even if the data changes. The following script has been constructed to test submissions:
update employees set first_name='Stevie', last_name='Wonder', hire_date=sysdate, job_id='AD_VP', department_id=50 (more...)
One of the most wonderful things of Oracle Open World are the Demo-Grounds. When you want to learn more about a certain feature, this is the place to go to. The actual developers and product managers are there to answer your questions and more!
In a previous blog I had written about Temporal Validity in Oracle 12c and whether it would be possible to have multiple validity periods on one table. You can read that (more...)
The Hitchhiker’s Guide to the EXPLAIN PLAN Part 22: The Final Exam! (Part III We Don’t Use Databases; We Don’t Use Indexes)
During a trial run for my presentation at Oracle Open World "Oracle 12c for Developers", you can find the slides on slideshare. there was a question regarding "Temporal Validity".
What is Temporal Validity?
The documentation says it best:
Temporal Validity Support lets you associate one or more valid time dimensions with a table and have data be visible depending on its time-based validity, as determined by the start and end dates or time stamps of (more...)
12c gave us two new features among others: DBMS_UTILITY.EXPAND_SQL_TEXT and temporal validity. DBMS_UTILITY.EXPAND_SQL_TEXT is advertised as a means to expand SQL text that is using views, but it is also very useful to see how Oracle internally rewrites some SQL queries. Here I'm going to use it to see how temporal validity queries are actually executed. All tests are done using 22.214.171.124 Enterprise Edition.
First I'll create a table for (more...)