I know I haven’t been very good about posting on the blog or replying to questions lately (and a big thank you to anyone who has answered some of the recent questions correctly), but tonight is a Friday night, and I have a few moments to spare, so here’s a question prompted by a recent comment on OTN.
I have a table declared as follows (and the ellipsis means repeat the column definitions according to the simplest and most obvious pattern to give you 1,000 columns all of type number(1)):
create table t1 ( col000 number(1), col001 number(1), col002 number(1), ... col997 number(1), col998 number(1), col999 number(1), constraint t1_pk primary key (col000) ) ;
I have one row in the table.
How many row pieces might that row consist of ?
You’ve probably heard about adaptive cursor sharing, and possibly you’ve wondered why you haven’t seen it happening very often on your production systems (assuming you’re running a modern version of Oracle). Here’s a little bug/fix that may explain the non-appearance.
MOS Doc ID 9532657.8 Adaptive cursor sharing ignores SELECTs which are not fully fetched.
This bug is confirmed in 126.96.36.199, and fixed in 188.8.131.52. The problem is that the ACS code doesn’t process the statistical information from the cursor unless the cursor reaches “end of fetch” – i.e. if you don’t select all the data in your query, Oracle doesn’t consider the statistics of that execution when deciding whether or not to re-optimise a statement.
A recent post on the OTN database forum raises a problem with v$sql_shared_memory:
query to V$SQL_SHARED_MEMORY don’t return rows
please explain why ?
A follow-up posting then describes how the OP picked the view definition from v$fixed_view_definitions and use the text of that query instead of the view itself – and still got no rows returned:
When I wrote a note last week about the fixes to the subquery factoring optimizer code in 184.108.40.206, I finished with a comment about having more to say on the test case if I materialized the subquery. Today’s the day to talk about it. As a reminder, here’s the query, but with the /*+ materialize */ hint in place:
with subq as ( select /*+ materialize */ outer.* from emp outer where sal > 1000000 and outer.sal > ( select avg(inner.sal) from emp inner where inner.dept_no = outer.dept_no ) ) select * from subq ;
Here are the three plans – from 10.2.0.5, 220.127.116.11, and 18.104.22.168 respectively:
In my previous post, I made the comment:
In general, if you have a three-column index that starts with the same columns in the same order as the two-column index then the three-column index will be bigger and have a higher clustering_factor.
So what scenarios can you come up with that fall outside the general case ?
Alternatively, what argument could you put forward that justifies the general claim ?
I’ll try to respond to comments on this post a little more quickly than the last one, but I still have quite a lot of other comments to catch up on.
In a recent question on OTN someone asked why Oracle had put some columns into the overflow segment of an IOT when they had specified that they should be in the main index section (the “IOT_TOP”) by using the including clause.
The answer is simple and devious; there’s a little trap hidden in the including clause. It tells Oracle which columns to include, but it gets applied only after Oracle has re-arranged the column ordering (internally) to put the primary key columns first. The OP had put the last column of the primary key AFTER the dozen columns in the table that he wanted in the index section, but Oracle moved that column to the fifth position in the internal table definition, so didn’t include the desired 10 extra columns.
Here’s an odd little detail about the to_char() function that happened to show up in a little demonstration code I used to create some data for last Friday’s quiz night.
When generating test data I often rely on doing things with rownum, and one of the thngs I do quite frequently is turn it into a character string. Nowadays I usually use the lpad() function to do this conversion because that lets me specify the defined length of the resulting column. But last Friday, rather than starting from scratch with my standard template script, I created my test case by cloning a script that I’d written in 2003 and the script had used the to_char() function.
So here’s a simple script to create a little table of character data, creating each column in a different way:
It was good to see the answers to the last Quiz Night accumulating. The problem posed was simply this: I have two IOTs and I’ve inserted the same data into them with the same “insert as select” statement. Can you explain the cost of a particular query (and it’s the same for both tables) and extreme differences in work actually done. Here’s the query, the critical stats on the primary key indexes, the shared plan, and the critical execution statistic for running the plan.
Inspired by Martin Widlake’s series on IOTs, I thought I’d throw out this little item. In the following, run against 10.2.0.3, tables t3 and t4 are index organized tables, in the same tablespace, with a primary key of (id1, id2) in that order.
That’s index organized tables, of course. Searching back through my blog I find that I’ve only written one article about IOTs- although I’m very keen on taking advantage of them, and have made a few references to them in other articles. Rather than addressing this oversight myself, I thought I’d direct you to a series on IOTs by Martin Widlake.