If you're using dynamic SQL to source your menus and you're utilising the apex_application_pages dictionary view to build you list of menu options - I suggest you consider using Materialized Views
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
I demonstrated a number of SQL approaches to reading object types in Appendix B of the Oracle Database 12c PL/SQL Programming book. For example, the easiest one to construct and return the results from a
TO_STRING member function uses the
SELECT TREAT(base_t() AS base_t).to_string() AS "Text"
However, it seems that I could have provided one more. Here’s an example of how you can (more...)
If you’re not using Toad DBA Suite, it’s sometimes hard to find solutions. Somebody wanted to know how to find indexes that aren’t that aren’t indirect. Indirect indexes are those created for a primary key because a primary key column or set of columns are both not null and uniquely constrained. You can’t drop a unique index for a primary key without dropping the primary key constraint that indirectly created it.
The following query (more...)
When you load data with an “effective date” into a Data Warehouse, what happens when the new data is the same as yesterday’s? Do you add a new record with the new “effective date” or do you update a date range in the existing record? At Open World last month, I got both answers: Tim Gorman […]
With the release of the Oracle Database 220.127.116.11 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...)
I suppose it had to happen eventually but one of my presentations has ended up on YouTube. It’s a recent presentation I did for the Oracle Midlands user group in September.
The topic is (as the title of this blog post hints at!)Boosting select performance by clustering data. The video consists of the slides I presented, changing as the presentation progresses, with my audio over the top. It goes on for a bit, (more...)
What is the date if you only specify the time when you populate an Oracle date column (or variable)?
That was the question that came up a few days ago in the little DBA team I am currently working in. Of course, the question was posed by the “junior” (who is damned smart at this stuff as he keeps asking questions like that) and the answer from us two old hands was… “Ohh!… hang (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...)
The answers to the third problem in Part 22 of this series “We Don’t Use Databases; We Don’t Use Indexes.”(read more
As published in the November 2014 issue of the NoCOUG Journal The inventor of the relational model, Dr. Edgar Codd, was of the opinion that “[r]equesting data by its properties is far more natural than devising a particular algorithm or sequence of operations for its retrieval. Thus, a calculus-oriented language provides a good target language […]
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 answers to the third problem in Part 21 of this series.(read more
The answers to the second problem in Part 21 of this series.(read more
The answers to the first problem in Part 21 of this series.(read more
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:
Oracle Database 12c introduces a
SEARCH_CONDITION_VC column to the
USER_CONSTRAINTS views. The
SEARCH_CONDITION_VC column is a
VARCHAR2 data type equivalent to the search condition in the
LONG data type
SEARCH_CONDITION column. Unfortunately, Oracle Database 11g and earlier versions requires you to convert the
LONG data type to a
VARCHAR2 for the equivalent behavior. This post provides you with a function to help you do that in (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...)
I frequently hear of, and see, developers and DBA’s using the NOLOCK hint within SQL Server to bypass the locking mechanism and return their data sets as soon as possible. There are times when this is OK, such as when you are running an ad hoc query and are only interested in approximate results. It is somewhat less OK to write this hint into application code and reports, unless you don’t actually care whether the (more...)