The cost displayed in query plans is a misleading and useless piece of information. It is an estimate that is more likely to be wrong than correct. By default, it is computed using particular values of bind variables and therefore does not apply to subsequent executions with different values. And, it is measured in units of SREADTIM, not clock seconds.(read more
Some time ago I had some blog posts introducing some of the basic Statistical function available in Oracle. Here are the links to these.
Oh-oh, yes I'm the great pretender
Pretending that I'm doing well
My need is such, I pretend too much
I'm lonely but no one can tell
Yes, I'm the great pretender
Just laughin' and gay like a clown
I seem to be what I'm not, you see
I'm wearing my heart like a crown
Pretending that you're still around
—The Great Pretender by The Platters
Previous installment: Throw Away that Execution Plan
Next installment: (more...)
Over the past while I've been working a lot with text strings. Some of these have been short in length like tweets from Twitter, or longer pieces of text like product reviews. Plus others of various lengths.
In all these scenarios I have to break up the data into individual works or Tokens.
The examples given below illustrate how you can take a string and break it into its individual tokens. In addition to tokenising (more...)
An enduring Oracle Database myth is that EXPLAIN PLAN and AUTOTRACE show the execution plan. This may have been true in early versions of Oracle but no longer. As Tom Kyte said: “It ain’t so much the things we don’t know that get us into trouble. It’s the things you know that just ain’t so or just ain’t so anymore or just ain’t always so.”(read more
Instead of focusing on EXPLAIN PLAN at the outset, focus on logical database and physical database design, give the optimizer the information it needs to do a good job, and write well-structured SQL statements that the optimizer can easily understand.(read more
An occasional question in the forums relates to issues ordering a particular column. It's one of those things that will probably keep coming up, so it's worth having another reference out here on the web.
The basic example stems from the need to order data that might contain characters.
with data as
(select '1' vc from dual union all
select '11' vc from dual union all
select '2' vc from dual union all
select 'a' (more...)
Occasionally I'll want some form of report noting file sizes of blobs in a database.
The solution is relatively simple, and I thought I'd write it up here for a place to copy syntax each time.
APEX users also have a handy table to verify this against (apex_application_files). Well, a synonym/view that ultimately maps to the core table wwv_flow_file_objects$.
It contains a doc_size
column, which is no doubt evaluated at some point during upload of (more...)
Before continuing with this series, please take a minute to read any installments that you may have missed.(read more
Part 1—DON’T PANIC: Even experienced application developers may not understand EXPLAIN PLAN output. As the great Renaissance artist Leonardo da Vinci said in his dicourse on painting: “Those who are in love with practice without science are like the sailor who gets into a ship without rudder or compass, who is never certain where he […]
In the Kscope14
sunday symposium today, Steven Feuerstein explained that MEMBER OF syntax was slow in SQL and fast in PL/SQL. I challenged him that perhaps it was missing indexes on the nested table? My mistake - I got the task of testing it and see if that was the case... So I tested and was surprised at the answer.
I'm creating a nested table type and a table with a column of that type (more...)
EXPLAIN PLAN output can sometimes be very confusing. In the EXPLAIN PLAN output that we obtained for the relational calculus solution of our first teaching example “employees who have worked in all accounting job classifications,” some operations seem to be located in the wrong nodes of the tree. The mystery can be solved by referring to the “predicate information” section of the EXPLAIN PLAN output and inserting additional nodes into the tree.(read more
It is popularly believed that the number of join orderings of N tables is FACTORIAL(N) = N * (N – 1) * (N – 2) * … * 3 * 2 * 1 because FACTORIAL(N) is the number of possible permutations of N objects. FACTORIAL(N) is actually the number of deep-left trees; it omits all the other possibilities. The actual number of join orderings is much larger.(read more
Over at ToadWorld … Part 5: SQL Sucks! Part 6: Trees Rule Part 7: Don’t pre-order your EXPLAIN PLAN Part 8: Tree Menagerie The story so far: A relational database is “a database in which: the data is perceived by the user as tables (and nothing but tables) and the operators available to the user for (for […]
There are four varieties of EXPLAIN PLAN trees: deep-left trees, deep-right trees, zigzag trees, and bushy trees. Deep left trees are very common because the optimizer typically picks a “driving” table and then joins tables to it one by one. Deep-right trees are useful in data warehouses for joining large fact tables to small dimension tables using hash joins. Hash tables are best constructed from the smaller of the inputs so Oracle will switch the (more...)
An EXPLAIN PLAN is a “tree” structure corresponding to a relational algebra expression. It is printed in “pre-order” sequence (visit the root of the tree, then traverse each subtree—if any—in pre-order sequence) but should be read in “post-order” sequence (first traverse each subtree—if any—in post-order sequence, then only visit the root of the tree).(read more
The DBMS converts your SQL statement into an equivalent sequence of relational algebra operations (the query execution plan). EXPLAIN PLAN output is simply a listing of that query execution plan. The Oracle documentation incorrectly states that “The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right.” In reality, the EXPLAIN PLAN has a “tree” structure.(read more
SQL Analytics provides a fairly simple mechanism for determining positional rank within a set of results.
Before I demonstrate that query - which is already found in many good libraries
- I thought I'd show how we could take it a step further and add the ordinal suffix (st, nd, rd, th) to a result.
We can do this using date format masks
with placing as (select rownum rn from dual connect by level < (more...)
In practice, we don’t use relational algebra or relational calculus but an English-like query language called SQL. As with relational calculus expressions, a SQL statement must be converted into an equivalent sequence of relational algebra operations (a query execution plan) by the DBMS. SQL is a heavily redundant language offering multiple ways of posing the same query. Unfortunately, and for no fault of the application developer, semantically equivalent but syntactically different SQL statements typically end (more...)