Просторы интернета
You're probably aware of the “identity” construct that appeared in 12.1 and uses Oracle's sequence mechanism to model the Autonumber or Identity columns that other databases have. A posting from Clay Jackson on the Oracle-L list server suggests that something about their code path has introduced a surprising [...]
I've just been prompted to complete and publish a draft I started a few years ago. It's (ultimately) about a feature that appeared in 9i but doesn't seem to show up very often at client sites or as a common solution to performance problems on the various Oracle forums – [...]
I've just received an email asking (yet again) a question about counting the number of rows in a table.
We have a large table with a CLOB column, where the CLOB occupies 85% storage space.
when we use select count(*) from , the DBA says that you should not use count(*) as [...]
Many years ago (2011) I wrote a note describing how you could attach the Outline Information from one query to the SQL_ID of another query using the official Oracle mechanism of calling dbms_spm.load_plans_from_cursor_cache(). Shortly after publishing that note I drafted a follow-up note with an example demonstrating that even when [...]
A quirky little feature of interval partitioning showed up on Twitter today – a parallel insert that would only use a single PX slave process to do the inserting. With 1.1 billion rows and the option for running parallel 32 this this made the loading process rather slower than [...]
It is possible to take subquery factoring (common table expressions / CTEs) too far. The most important purpose of factoring is to make a complex query easier to understand – especially if you can identify a messy piece of text that is used in more than one part of the [...]
The semantics of comparing character columns of different types can lead to some confusion, so before I get into the main body of this note here's a little test based on a table with one row:
create table t1(c2 char(2), c3 char(3), vc2 varchar2(2), vc3 varchar2(3)); insert into t1 values ('XX','XX','XX','XX'); commit; select count(*) [...]The view v$undostat is a view holding summary information about undo activity that can be used by the automatic undo mechanism to deal with optimising the undo retention time (hence undo space allocation). The view holds one row for every ten minute interval in the last 4 days (96 hours) [...]
I hesitate to call something a bug simply because Oracle doesn't do what I thought it would do; but when a trace file says:
“I'm not going to do X because P is not true“
followed a little later by
“I'm going to do Y because P is true“
then I think it's [...]
I am seeing “traditional” Oracle SQL syntax being replaced by “ANSI”-style far more frequently than I used to – so I thought I'd just flag up another reminder that you shouldn't be too surprised if you see odd little glitches showing up in ANSI style that don't show up when [...]
Here's another anomaly that appears when you mix and match Oracle features. In this case it's “With” subqueries (common table expressions / CTEs) and Active Dataguard (ADG) Standby databases. The problem appeared on the Oracle-l listserver and luckily for the OP another member of the list had seen it [...]
These screenshot are from JDevelopers - 12c
1. Click on Window - Resources
2. In the Resources pallet - Click on Import to open the 'Import Catalogs and Connections' window.
3. Now browse to locate the *.rcx file containing connection details.
4. Then click on Import to import [...]
It's a long time since I wrote a note with the “philosophy” theme, but a recent complaint about Oracle prompted me to suggest the following thought:
“If you write SQL that is technically incorrect it's not Oracle's fault if sometimes the SQL completes without an error.”
Consider the following fragment of code:
drop [...]This is a case study based on a question that appeared on the Oracle Developer Community forum a few days ago.
What I'm aiming to present in this note is the pattern of thinking that you should adopt in cases like this. The final suggestion in this note isn't necessarily [...]
Here's a bug that was highlighted a couple of days ago on the Oracle Developer Community forum; it may be particularly worth thinking about if if you haven't yet got up to Oracle 12c as it appeared in an optimizer feature that appeared in 12.2 (and hasn't been completely [...]
When I was a child I could get lost for hours in an encyclopedia because I'd be looking for one topic, and something in it would make me want to read another, and another, and …
The same thing happens with MOS (My Oracle Support) – I search for something [...]
This is a note I drafted in September 2015 and only rediscovered a couple of days ago while searching for something I was sure I'd written about collections and/or table functions. The intention of collections and table functions is that they should behave like tables when you use them in [...]
Regular readers will have noticed that for the last few weeks I've been adding a footnote (now deleted) to any new blog posts asking for donations to the Nepal Youth Foundation (UK), with an offer to match (up to a limit) any donations made by my readers. The [...]
The ODC SQL and PL/SQL forum came up with an example a couple of days ago that highlighted an annoying limitation in the optimizer's handling of table functions. The requirement was for a piece of SQL that would generate “installments” information from a table of contract agreements and insert [...]
THe problem with finding something new and fiddling with it and checking to see how you can best use it to advantage is that you sometimes manage to “break” it very quickly. In yesterday's blog note I introduced the /*+ push_having_to_gby(@qbname) */ hint and explained why it was a [...]
I came across an interesting new hint recently when checking the Outline Data for an execution plan: /*+ push_having_to_gby() */ It's an example of a “small” change designed to reduce CPU usage by reducing the volume of data that passes through the layers of calls that an execution plan [...]
This is an observation that came up on the Oracle Developer Forum a couple of days ago, starting life as the fairly common problem:
I have a “select” that runs quickly but when I use in a “create as select” it runs very slowly.
In many cases this simply means [...]
You may know that whenever you call PL/SQL functions from within SQL query, each query in the function is consistent to the SCN of its start and not to the SCN of parent query.
Simple example:
create table test as select level a, level b from dual connect by [...]A classic example of Oracle's “mix and match” problem showed up on the Oracle Developer Forum a few days ago. Sometimes you see two features that are going to be really helpful in your application – and when you combine them something breaks. In this case it was the [...]
This is a note I first drafted about 5 years ago (the date stamp says March 2014) and rediscovered a few days ago when the question came up on a Twitter thread.
How do you purge a single SQL statement from the library cache without having to execute “alter system [...]
This is a note I first drafted about 5 years ago (the date stamp says March 2014) and rediscovered a few days ago when the question came up on a Twitter thread.
How do you purge a single SQL statement from the library cache without having to execute “alter system [...]
This is a note courtesy of Jack can Zanen on the Oracle-L list server who asked a question about “wait for CPU” and then produced the answer a couple of days later. It's a simple demonstration of how Java in the database can be very deceptive in terms of [...]
Here's a worrying bug that showed up a couple of days ago on the Oracle-L mailing list (I can't link directly to the exact post at the moment – the data is currently hiding). It's a problem that I've tested against 12.2.0.1 and 19.3.0.0 – it may be present [...]