Tuesday, January 31, 2006

A rant for today...

A rant for today. How sad. This just makes me want scream. Here is the question (so similar to so many that I receive but don’t print):

I am a new in the DBA field. Please let me know how to take dbms stats and how to analyze the same.

Here was my response:

Way too broad of a question to be able to answer in a paragraph. I suggest you get this book: Cost Based Oracle Fundamentals to understand how the CBO works, how statistics fit into it, and start from there.

So, what we have is someone just starting out – new to the field, doesn’t know what to do or why they might be doing it.

Here was their response:

Didn’t help me: I want faster replies rather than scanning through books

I don’t get it. I really don’t get it. The only reply I could come up with was this:

You have a lot to learn my friend, a lot to learn.

If you don't want to spend the time to get the knowledge, your employer doesn't
really need you - they can get a piece of automated software to do it.

For example, in 10g – We don't need someone like you. The "rule of thumb" for
gathering statistics is a job burned into the database. You are not needed. The gathering of stats with the level of knowledge you apparently want to achieve (eg: no real knowledge, just to be told what to do). Everything works with AUTO.

However, if you actually know how it works, understand it, take the time to
learn it, you might actually be able to add some value.

Seriously disappointed - that describes me right now.

I really don’t get it. When I started out – I remember spending much time in the bookstore, tons of time reading what I bought there. Many hours exploring and testing the information I was getting. I would buy technical manuals I couldn’t get at work. I spent so much money here over time.

I really don’t get it. “Make me smart right now”. We don’t need that, if we could distill the knowledge down to a paragraph, we would encode it in software and let it do it for us.

Oh wait, we do that…

Just a thought for the people that want “instant knowledge”, that provides no value add over what the software already automates – therefore, it won’t do you very well. A little knowledge is a dangerous thing.

My day is off to a bad start…

Added a couple of hours later....

I liked what Lisa had to say. And the day is much better now - 2nd day of a three day seminar. A room full of people that actually want to increase their understanding, makes me feel much better.

Monday, January 30, 2006

Stuck in a rut...

Stuck in a rut. I’m on constraints this week. It all started with “something about nothing” – complete misunderstandings about NULL values, especially in indexes. Now I’m stuck on constraints. Just today I was giving a seminar session – and one of the topics was “Effective Schema” – all about constraints, using the right types, telling the CBO more information. This is an example I’ve used in that section.

I’ve been asked in the past “Why should I use a primary key? Why not just use a unique index?” Well, the answer is that you could, but doesn’t the fact you used a primary key say something over and above just using a unique index? In fact it does – it can say a lot. The same goes for the use of foreign keys, NOT NULL constraints and others – they not only protect the data, they add information about the data into the data dictionary. Using this additional information Oracle is able to perform a query rewrite more often, in many complex cases.

Consider the following small example. We will copy the EMP and DEPT tables from the SCOTT schema and create a materialized view that pre-joins the tables together for us.

ops$tkyte@ORA10GR2> create table emp
2 as
3 select * from scott.emp;
Table created.

ops$tkyte@ORA10GR2> create table dept
2 as
3 select * from scott.dept;
Table created.

ops$tkyte@ORA10GR2> create materialized view emp_dept
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select dept.deptno, dept.dname, count (*)
7 from emp, dept
8 where emp.deptno = dept.deptno
9 group by dept.deptno, dept.dname
10 /
Materialized view created.

Now, we have withheld a lot of information from Oracle here. It does not understand the relationship between EMP and DEPT, does not know what columns are primary keys, and so on. Now, let’s run a query and see what happens:

ops$tkyte@ORA10GR2> set autotrace on
ops$tkyte@ORA10GR2> select count(*) from emp;

COUNT(*)
----------
14

Execution Plan
-------------------------------------------...
Plan hash value: 2083865914

---------------------------------------------...
| Id | Operation | Name | Rows | C...
---------------------------------------------...
| 0 | SELECT STATEMENT | | 1 | ...
| 1 | SORT AGGREGATE | | 1 | ...
| 2 | TABLE ACCESS FULL| EMP | 14 | ...
---------------------------------------------...

Now, you and I know that the count(*) could easily, and more efficiently (especially if the number of employees in each department was large and there were lots of departments), have been answered from the materialized view. There we have all of the information we need to get the count of employees. We know that because we know things about the data that we kept from Oracle:

  • DEPTNO is the primary key of DEPT. That means that each EMP record will join to at most one DEPT record.

  • DEPTNO in EMP is a foreign key to DEPTNO in DEPT. If the DEPTNO in EMP is not a null value, then it will be joined to a row in DEPT (we won’t lose any non-null EMP records during a join)

  • DEPTNO in EMP is NOT NULL – this coupled with the foreign key constraint tells us we won’t lose any EMP records.

These three facts imply that if we join EMP to DEPT – each EMP row will be observed in the result set AT LEAST once and AT MOST once. Since we never told Oracle these facts, it was not able to make use of the materialized view. So, let’s make Oracle aware of them:

ops$tkyte@ORA10GR2> alter table dept
2 add constraint
3 dept_pk
4 primary key(deptno);
Table altered.

ops$tkyte@ORA10GR2> alter table emp
2 add constraint
3 emp_fk_dept
4 foreign key(deptno)
5 references dept(deptno);
Table altered.

ops$tkyte@ORA10GR2> alter table emp
2 modify deptno not null;
Table altered.

ops$tkyte@ORA10GR2> set autotrace on
ops$tkyte@ORA10GR2> select count(*) from emp;

COUNT(*)
----------
14


Execution Plan
---------------------------------------------------...
Plan hash value: 155013515

---------------------------------------------------...
| Id | Operation | Name | ...
---------------------------------------------------...
| 0 | SELECT STATEMENT | | ...
| 1 | SORT AGGREGATE | | ...
| 2 | MAT_VIEW REWRITE ACCESS FULL| EMP_DEPT | ...
---------------------------------------------------...

And now Oracle is able to rewrite the query using the materialized view. Anytime that you know Oracle could use a materialized view, but it is not doing so (and you have verified you can use materialized views in general) – take a closer look at the data and ask yourself “what piece of information have I withheld from Oracle?” Nine times out of ten, you'll find a missing piece of metadata that, when included, allows Oracle to rewrite the query.

So, what happens if this is a true data warehouse and there are tens of millions of records in the above tables? You don’t really want the additional effort of verifying a foreign key relationship – you already did that in your data scrubbing routine didn’t you? In which case, you can create a non-validated constraint – one that is used to inform the database about a relationship but that has not been validated by the database itself. Let’s look at the above example again but this time we’ll simulate a load of data into an existing data warehouse (our example above is our data warehouse). We’ll drop our constraints, load the data, refresh the materialized views and add our constraints back. We’ll start with dropping the constraints:

ops$tkyte@ORA10GR2> alter table emp drop constraint emp_fk_dept;
Table altered.

ops$tkyte@ORA10GR2> alter table dept drop constraint dept_pk;
Table altered.

ops$tkyte@ORA10GR2> alter table emp modify deptno null;
Table altered.

Now, in order to simulate our load, I will insert a single new row into EMP (not much of a load but enough to demonstrate with). Then, we will refresh our materialized view:

ops$tkyte@ORA10GR2> insert into emp (empno,deptno) values ( 1, 1 );
1 row created.

ops$tkyte@ORA10GR2> exec dbms_mview.refresh( 'EMP_DEPT' );
PL/SQL procedure successfully completed.


I purposely inserted a row into EMP that will violate the constraint we are about to put back on the EMP. This is to show you what can happen if you tell Oracle to trust your data and the data is invalid. It shows that Oracle will trust you but - Oracle will give you the wrong answer – though through no fault of its own. When you use these constraints on a large warehouse, you had better be very sure of the data. Now we tell Oracle about the relationships between EMP and DEPT:

ops$tkyte@ORA10GR2> alter table dept
2 add constraint dept_pk primary key(deptno)
3 RELY enable NOVALIDATE
4 /
Table altered.

ops$tkyte@ORA10GR2> alter table emp
2 add constraint emp_fk_dept
3 foreign key(deptno) references dept(deptno)
4 RELY enable NOVALIDATE
5 /
Table altered.

ops$tkyte@ORA10GR2> alter table emp modify deptno not null NOVALIDATE;
Table altered.

So here we have told Oracle that there is a foreign key from EMP to DEPT as before, but this time we have told Oracle to trust that if it joins EMP to DEPT by DEPTNO – every row in EMP will be retrieved at least once and at most once. Oracle will perform no validating checks. That is the purpose of the NOVALIDATE and RELY options. The NOVALIDATE bypasses the checking of existing data we loaded, RELY tells Oracle to “rely” on the integrity of the data. We are now ready to query:

ops$tkyte@ORA10GR2> alter session set query_rewrite_integrity=enforced;
Session altered.

ops$tkyte@ORA10GR2> select count(*) from emp;

COUNT(*)
----------
15

Execution Plan
----------------------------------------------...
Plan hash value: 2083865914

----------------------------------------------...
| Id | Operation | Name | Rows | Co...
----------------------------------------------...
| 0 | SELECT STATEMENT | | 1 | ...
| 1 | SORT AGGREGATE | | 1 | ...
| 2 | TABLE ACCESS FULL| EMP | 15 | ...
----------------------------------------------...


This time QUERY_REWRITE_INTEGRITY=ENFORCED is not good enough to cause a rewrite of our query as you can see. Oracle did not rewrite the query to use the materialized view because of this. We must go down a level in query integrity. We need Oracle to “trust” us:

ops$tkyte@ORA10GR2> alter session set query_rewrite_integrity=trusted;
Session altered.

ops$tkyte@ORA10GR2> select count(*) from emp;

COUNT(*)
----------
14


Execution Plan
--------------------------------------------------...
Plan hash value: 155013515

------------------------------------------------------...
| Id | Operation | Name | Row...
------------------------------------------------------...
| 0 | SELECT STATEMENT | | ...
| 1 | SORT AGGREGATE | | ...
| 2 | MAT_VIEW REWRITE ACCESS FULL| EMP_DEPT | ...
------------------------------------------------------...

So, here Oracle did in fact rewrite the query but as you can see – the side effect of that in this case is the wrong answer is returned. The reason we got the wrong answer is because the “fact” that each row in EMP would be preserved in a join to DEPT is not a fact given the data we loaded. When the materialized view refreshed, it did not get the newly added EMP row. The data we told Oracle to rely on was not reliable. This demonstration points out two things:

  • You can use materialized views in a large data warehouse very efficiently, without having to perform lots of extra, typically redundant, verifications of the data.

  • BUT, you had better be 100% sure that your data is scrubbed if you ask Oracle to rely on it.

Sunday, January 29, 2006

Constraints have a bad name...

Constraints have a bad name. The thoughts on constraints might be (as applied to a person):

  • No one likes to be constrained. Just ask them, they’ll tell you so.

  • Constraints are impediments, they get in the way.

  • They are rules – things we have to “respect and obey”. No one likes that.

  • They get in the way – they are an annoyance, they slow things down.

This week, I’ll just put up some snippets showing that constraints in the database are “awesome”, things to be respected, something you’ll want to add to your schema as often as possible. Besides the obvious “we get better data”, you get things like “better plans, better performance”.

We’ve already seen one example above – if you specify NOT NULL when the column is in fact NOT NULL (as most columns are), then perhaps we can use certain indexes more often. Oh, how I wish that NOT NULL was the default state for all columns.

Well, how about a simple check constraint? The CBO (cost based optimizer) is pretty good at using check constraints to figure out when it might not need to do something and getting better all of the time. Funny thing is, this fact comes into play most frequently in a large data warehouse, the place where constraints are seemingly most often considered “evil” (things that slow us down). In fact, it is exactly the large data warehouse is where constraints are most vital! Consider:

create table t1
( num number check (num>0),
data char(20)
);
insert into t1
select rownum, rownum
from all_objects;
exec dbms_stats.gather_table_stats( user, 'T1' );

create table t2
( num number check (num<0),
data char(20)
);
insert into t2
select -num, data
from t1;
exec dbms_stats.gather_table_stats( user, 'T2' );

create or replace view vw
as
select * from t1
union all
select * from t2;

alter session set sql_trace=true;
set termout off
select * from vw where num > 0;
select * from vw where num < 0;
set termout on

tkprof will show something like this:

select * from vw where num > 0

Rows Row Source Operation
------- ---------------------------------------------------
49958 VIEW VW (cr=3577 pr=0 pw=0 time=2348976 us)
49958 UNION-ALL PARTITION (cr=3577 pr=0 pw=0 time=1799199 us)
49958 TABLE ACCESS FULL T1 (cr=3577 pr=0 pw=0 time=699638 us)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T2 (cr=0 pr=0 pw=0 time=0 us)

select * from vw where num < 0

Rows Row Source Operation
------- ---------------------------------------------------
49958 VIEW VW (cr=3569 pr=0 pw=0 time=1349734 us)
49958 UNION-ALL PARTITION (cr=3569 pr=0 pw=0 time=1099623 us)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=0 us)
49958 TABLE ACCESS FULL T2 (cr=3569 pr=0 pw=0 time=249879 us)

Note how the CBO was able to effectively just “skip” one of the tables in each case? This sort of “elimination” has been around since version 7.3 (was called partitioned views way back when) – assuming you have constraints in place. If you use autotrace in 10gr2 (or dbms_xplan before that) you can see what is happening here:

ops$tkyte@ORA10GR2> set autotrace traceonly explain
ops$tkyte@ORA10GR2> select * from vw where num > 0;

Execution Plan
----------------------------------------------------------
Plan hash value: 2831881304

------------------------------------------------------------...
| Id | Operation | Name | Rows | Bytes | Cost (...
------------------------------------------------------------...
| 0 | SELECT STATEMENT | | 49961 | 1317K| 111 ...
| 1 | VIEW | VW | 49961 | 1317K| 111 ...
| 2 | UNION-ALL PARTITION| | | | ...
|* 3 | TABLE ACCESS FULL | T1 | 49962 | 1219K| 57 ...
|* 4 | FILTER | | | | ...
|* 5 | TABLE ACCESS FULL| T2 | 1 | 26 | 57 ...
------------------------------------------------------------...

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("NUM">0)
4 - filter(NULL IS NOT NULL)
5 - filter("NUM">0)

See the addition of a new filter? NULL IS NOT NULL at step 4. The CBO basically removed T2 from consideration!

Friday, January 27, 2006

Mull about Null...

YAMAN. Yet Another Mull About NULL. This current title was suggested based on a discussion about what exactly “NULL” in the database means. Null is the absence of a value. In a Boolean expression, NULL is “unknown” (it is neither true, nor false – leaving “I don’t know” as the only possible other answer). When persistently stored as a value in a database column – I would say NULL is “the absence of a value”, or more succinctly “nothing”. So, the last title “Something about Nothing” makes good sense to me.

In the last article we explored one of the big myths associated with NULL values and indexes in Oracle and saw that predicates of the form “WHERE COLUMN IS NULL” can easily and naturally use indexes (and have been using indexes in fact for a long time) in Oracle – contrary to popular thought. NULLs are in fact indexed in Oracle in many cases. Whenever you have an index that includes a NOT NULL attribute – all rows in the base table will in fact appear in the index. The result is that that index can be used to search for “NULLS” in any of the other attributes quite naturally without resorting to an “evil” full scan.

I will say that I seriously wish the default state of a database column was NOT NULL – that is, instead of columns by default being NULLABLE, and therefore we would have to go out of our way to make them NULLABLE. My experience is that most columns are in fact NOT NULL – and we just “forget” to state the obvious. This leads to indexes that sometimes cannot be used for a particular class of questions. For example:

ops$tkyte@ORA9IR2> create table we_dont_use_pks
2 as
3 select *
4 from dba_objects
5 where object_id is not null
6 /
Table created.

ops$tkyte@ORA9IR2> create unique index
2 we_dont_use_pks on
3 we_dont_use_pks(object_id)
4 /
Index created.

ops$tkyte@ORA9IR2> begin
2 dbms_stats.gather_table_stats
3 ( user,
4 'we_dont_use_pks',
5 cascade => TRUE );
6 end;
7 /
PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select count(*)
2 from we_dont_use_pks;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=67 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF
'WE_DONT_USE_PKS' (Cost=67 Card=31285)

ops$tkyte@ORA9IR2> set autotrace off

Many people will say “but we could just use the index to count these rows, and the index is small – why isn’t the database doing that?”. In fact, they’ll try hints – everything to no avail. The database will steadfastly refuse to use the index to count the rows in this case. The reason – it is entirely possible that an index key entry in the index we created could have an entirely NULL entry – and hence not every row in the table is represented in the index!

Now, we could “fix” that by using the function based index trick in the prior article – but the reality of the situation is that OBJECT_ID is in fact “NOT NULL” and all we need to do is tell the database this fact. And when we do – good things happen

ops$tkyte@ORA9IR2> alter table we_dont_use_pks
2 modify object_id NOT NULL
3 /
Table altered.

ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select count(*)
2 from we_dont_use_pks;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'WE_DONT_USE_PKS'
(UNIQUE) (Cost=12 Card=31285)

ops$tkyte@ORA9IR2> set autotrace off

In fact, the optimizer makes use of constraints all of the time (the cost base optimizer mostly, the rule based optimizer is pretty much brain dead in this respect:

ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select /*+ RULE */ count(*)
2 from we_dont_use_pks;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'WE_DONT_USE_PKS'

ops$tkyte@ORA9IR2> set autotrace off

In subsequent articles I will be exploring in more depth two related topics:

  • How constraints can really impact us (rather how the appalling lack of constraints can adversely affect us). Especially in a data warehouse where they are most important (and people seem most hesitant to use them – too “slow” they say – when in fact the opposite is quite the truth)

  • How NULL is not evil as a foreign key value – how outer joins and the need for them are not bad. Why you don’t want to make up some dummy default value for a NULLABLE field just to avoid an outer join. (full scans are not evil, outer joins are not evil and ‘fixing’ the perceived outer join problem with a default value is really not a good idea)

On an unrelated note, it seems the Wiki that got started is going rather well. After seeing the Wiki next to the Q&A site – I will concur that that Wiki seems to be a much better format for this sort of information. Having the comments on a secondary page as the Q&A site does is an impediment and I like having the ability to edit the Wiki pages. The organization of the Wiki with a table of contents and some structure is nicer as well. I still think it was useful to have both be tried out – for sometimes only time will tell what really works.

If someone had asked me years ago my opinion of a web site that allows people to sell junk from their garage to each other (eBay) I would have laughed at them. I don’t think any of us knows exactly what will work until we see it.

Tuesday, January 24, 2006

Something about nothing...

Something about nothing. A frequently misunderstood concept with NULLS and indexes is the myth that NULL values are not tracked in “normal” indexes such as a conventional B*Tree index. Therefore, many people conclude erroneously that “WHERE COLUMN IS NULL” will not use, in fact cannot, use an index.

The facts are:

  • Entirely NULL keys are not entered into a ‘normal’ B*Tree in Oracle

  • Therefore, if you have a concatenated index on say C1 and C2, then you will likely find NULL values in it – since you could have a row where C1 is NULL but C2 is NOT NULL – that key value will be in the index.

As long as you have some not null column in your set of indexed columns, you will find NULLs in the index. Additionally – you always have a NOT NULL (virtual) column to use no matter what!

First, consider this example, supposed we want to leave OBJECT_ID as NULLABLE, but did have a column OWNER that was NOT NULL:

ops$tkyte@ORA9IR2> create table t
2 as
3 select object_id, owner, object_name
4 from dba_objects;
Table created.

ops$tkyte@ORA9IR2> alter table t modify (owner NOT NULL);
Table altered.

ops$tkyte@ORA9IR2> create index t_idx on t(object_id,owner);
Index created.

ops$tkyte@ORA9IR2> desc t
Name Null? Type
----------------------- -------- ----------------
OBJECT_ID NUMBER
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)

ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.


Well, that index can certainly be used to satisfy “IS NOT NULL” when applied to OBJECT_ID:


ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where object_id is null;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=34)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=1)


In fact – even if the table did not have any NOT NULL columns, or we didn’t want/need to have a concatenated index involving OWNER – there is a transparent way to find the NULL OBJECT_ID values rather easily:


ops$tkyte@ORA9IR2> drop index t_idx;
Index dropped.

ops$tkyte@ORA9IR2> create index t_idx_new on t(object_id,0);
Index created.

ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where object_id is null;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=34)
2 1 INDEX (RANGE SCAN) OF 'T_IDX_NEW' (NON-UNIQUE) (Cost=2 Card=1)


The “secret sauce” here is to create what is known as a function based index (although some would argue there is no such thing! I detect a Jonathan Lewis write up there…). We simply added “0” to the index – we can search on OBJECT_ID as before, plus find all records where “OBJECT_ID IS NULL” without changing a thing.

I have used (and will continue to use!) the fact that entirely null entries are not made in B*Tree indexes as a positive thing (selective uniqueness for example – ensure that “project name is unique for all active projects” can be enforced via: create unique index proj_name_unique on t(case when status = ‘ACTIVE’ then project_name end ); - that uniquely indexes only the STATUS=’ACTIVE’ records..). But here, we can use the “reverse” of that trick to ensure we never index an entirely NULL key!

Monday, January 23, 2006

What a bother...

On blogger, it seems if I

  1. use Word to create the post
  2. post as a draft (which I do when I want to put an image or some <pre> tags in
  3. then publish
The comments and individual page do not get generated. I always have to come in an hit the "publish" button a second time to get them to generate.

And I keep forgetting to do that. Oh well, they are there now (Thanks to Doug Burns for pointing out that they were not there...)

Yet another new...

YANS. Yet another new site. Howard Rogers set up an Oracle Wiki. Looks like an interesting idea (yet something else to germinate and see if it grows and becomes something, or simply whithers on the vine and dies).

You can see my first contribution to this here. This is such a frequently asked question, I’ll just publish it anywhere I can…

I learned something new at the Wiki today. I’ll have to update my SQL Techniques seminar with this new information. In 10gR2 – we have more support for deterministic functions (thanks to Jonathan Lewis for noting that). Now in SQL – if you call a deterministic function, Oracle will cache the results. This could be dramatic depending on how long your PLSQL function takes.

Here is what we could expect in Oracle 10gR1 and before:

ops$tkyte@ORA10GR1> create or replace function f( p_deptno in number )
2 return number
3 DETERMINISTIC
4 as
5 begin
6 dbms_application_info.set_client_info(
7 userenv('client_info')||','||p_deptno );
8 return p_deptno;
9 end;
10 /
Function created.

ops$tkyte@ORA10GR1> exec dbms_application_info.set_client_info('');
PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> select ename, f(deptno)
2 from scott.emp
3 /

ENAME F(DEPTNO)
---------- ----------
SMITH 20
...
MILLER 10
14 rows selected.

ops$tkyte@ORA10GR1> select ltrim(userenv('client_info'),',')
2 from dual
3 /

LTRIM(USERENV('CLIENT_INFO'),',')
--------------------------------------------------
20,30,30,20,30,30,10,20,10,30,20,30,20,10

Notice that the function F has been called 14 times (you can count them) – once for each of the DEPTNO values in the EMP table. Even though we promised to return the same answer for each one (the deterministic clause). Even if we do things “in order”:

ops$tkyte@ORA10GR1> exec dbms_application_info.set_client_info('');
PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> select ename, f(deptno)
2 from (select * from scott.emp order by deptno)
3 /

ENAME F(DEPTNO)
---------- ----------
CLARK 10
...
WARD 30
14 rows selected.

ops$tkyte@ORA10GR1> select ltrim(userenv('client_info'),',')
2 from dual
3 /

LTRIM(USERENV('CLIENT_INFO'),',')
--------------------------------------------------
10,10,10,20,20,20,20,20,30,30,30,30,30,30

It called our function over and over and over again. So, in 10gR1 and before I looked at using scalar subqueries and the fact that Oracle will cache them (see this book for details – excellent book all together). So, running the original example with a scalar subquery, we see:

ops$tkyte@ORA10GR1> exec dbms_application_info.set_client_info('');
PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> select ename, (select f(deptno) from dual) f_deptno
2 from scott.emp
3 /

ENAME F_DEPTNO
---------- ----------
SMITH 20
...
MILLER 10

14 rows selected.

ops$tkyte@ORA10GR1> select ltrim(userenv('client_info'),',')
2 from dual
3 /

LTRIM(USERENV('CLIENT_INFO'),',')
--------------------------------------------------
20,30,10

Now, this was “the best case”, the function was called but three times – it could be called more than that even with the scalar subquery due to collisions in the subquery cache and such.

In 10gR2 however, running just this:

ops$tkyte@ORA10GR2> exec dbms_application_info.set_client_info('');
PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select ename, f(deptno)
2 from scott.emp
3 /

ENAME F(DEPTNO)
---------- ----------
SMITH 20
...
MILLER 10

14 rows selected.

ops$tkyte@ORA10GR2> select ltrim(userenv('client_info'),',')
2 from dual
3 /

LTRIM(USERENV('CLIENT_INFO'),',')
--------------------------------------------------
20,30,20,10

Demonstrated that Oracle is caching the results – not “perfectly” – it called the function twice for DEPTNO 20, but 4 calls is a lot better than 14.

Friday, January 20, 2006

How can I...

How can I bind an in-list. That is, I want the user to pick items on screen from a list box (unknown number) and then bind their values in.

My current quick answer to that:

ops$tkyte@ORA10GR2> variable txt varchar2(25)
ops$tkyte@ORA10GR2> exec :txt := '1,2,3,5,42,104'
PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> with bound_inlist
2 as
3 (
4 select
5 substr(txt,
6 instr (txt, ',', 1, level ) + 1,
7 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
8 as token
9 from (select ','||:txt||',' txt from dual)
10 connect by level <= length(:txt)-length(replace(:txt,',',''))+1
11 )
12 select *
13 from all_users
14 where user_id in (select * from bound_inlist);

USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM 5 30-JUN-05
OPS$TKYTE 104 20-JAN-06

The only truly accurate answer however? That would be “You cannot”. However, this for me falls into the category of “a trick”. I have other ways to answer this same question – but if someone walked up to me and say “Hey, how do you bind an in-list”, this is what I would draw on the whiteboard (today).

It is a neat “trick” that works in 9iR2 and above. It might need a cardinality hint. It might need some explanation for some people (to help them understand how it does what it does).

There are lots of little “hey, look – isn’t this cool” sort of things in the database. Little teasers. Quick and dirty examples. I like them, they get me thinking. They remind me of something else. They make me see something from a different point of view. They show me a trick that I might be able to apply somewhere else. They are not comprehensive. They are not 100% complete. They might not apply to me in my circumstances.

But that doesn’t make them inherently evil.

I learn something new everyday. Here is my new thing for today… How to create a text index and schedule the synchronization in one step. I did not know we could do that (new 10g feature I missed, read over in the guides). Glad I caught it (it’ll make me more accurate myself in the future). I don’t need it right now, but I’ll file it away and next time it comes up – hopefully I’ll remember it.

If I need to research it, or really dig into it – I’ll do that – but for now, someone just pointing out “hey, look what I just stumbled on” is pretty nice.

What am I trying to say? There are lots of ways to disseminate information. I personally found the idea of little nuggets – tricks and tips – intriguing. What are some of the most attended sessions at conferences? (tips and tricks). What are some of my most popular sessions (tips and tricks).

Time will tell whether a certain resource should stay or go. There is room for ideas of all sizes – small, medium and large. Comprehensive is good – but snippets are not necessarily evil.

Thursday, January 19, 2006

I have a couple of questions for you....

I have a couple of questions (three of them at least, make sure to page down!) for you – about Oracle XE. As you may or may not know, Oracle XE is the “free” Oracle. It is close to the end of the beta process for this and we (we being the people monitoring the forum, which is the only venue of support) were wondering about a couple of things. If you have time, I’m hoping you can answer a few questions for me…






Have you downloaded and installed Oracle Database XE?
Yes
No
Plan to in the near future




Free polls from Pollhost.com









Have you signed up for the Online Forum for XE?
Yes
No
Plan to in the near future
Did not know about the forum




Free polls from Pollhost.com









If you haven't signed up for the forum after downloading XE, why not?
Did not know about the forum
Planned to do it later, and did not get to it!
Did not need to sign up for the forum
Did not want to sign up for the forum
I had problems signing up




Free polls from Pollhost.com

If you did not want to sign up for the forum - or had problems signing up, please leave a comment so we know what the issues you had were.

And in closing – if you can suggest any ways we can improve the XE/forum experience – please let me know. Just add a comment to the blog.

Thanks!

Monday, January 16, 2006

A new question/answer site...

A new Q&A site just came online. Eddie Awad asked me for feedback on this new site of his last week. Looks like an interesting idea – immediately I saw a question/answer that taught me something new. A method to put a table into “read only” mode in a way. Neat idea there using the check constraint.

The Q&A site could be interesting in that people can post not only the question but the answer. Sort of a way to say “hey, I had a neat idea and would like to share it”. Sort of like this recent blog entry of mine was about.

Sunday, January 15, 2006

IM Speak....

Anyone who knows me, knows that one pet peeve of mine is what I call “IM speak”. When someone uses the abbreviations you might use on a text message on your phone to convey information in a forum, email, or any professional communication.

ANYON3 TAHT KNOWS ME KNOWS TAHT ONE PAT PEVE OF MIEN IS WUT I CAL “IM SP3AK”!!11111! LOL WHAN SOMEONE US3S TEH ABR3VIATIONS U MIGHT USE ON A T3XT MESAEG ON UR PHONE 2 CONV3Y INFORMATION IN A FORUM EMALE OR ANY PROFESIONAL COMUNICATION!111!11! OMG WTF LOL

I found a site that will take normal text and turn it into this “IM speak”. Now I’m looking for the site that does the opposite. I have had to go to online lookups at least twice in the last week just to turn some “IM speak” into text so I could understand the context (no kidding – I had no idea what they meant).

The funniest – I got a resume with a cover letter not long ago. It was unsolicited, but a formal resume none the less (they were serious). The resume and cover letter used “IM speak”! I just sat there staring at it for a while, totally incredulous. Hopefully the person that received my response to their inquiry took that response in the manner it was intended. To teach them that when not using a phone – the only way to write text is – well, to write text.

I am curious about one thing though. Is “IM speak” limited to ‘English’ – or is it a universal thing? In Chinese, or Farsi, or whatever you speak natively – do you come up with “IM speak” and do you use it in your day to day communication (not on the phone – in written correspondence)…

I found this disclaimer on that page to be really amusing:

I have no idea how it happened, but this site appears to have become somewhat of a phenomenon on the internet. As such, I've added a copyright notice at the bottom that includes contact information. Thanks to everyone who has enjoyed my creation!

I’ve a feeling I am not alone in my pet peeve and others have pointed to this site before. In fact the google plugin I use pretty much proves that out.

Friday, January 13, 2006

Got this comment...

Got this comment the other day

How come there aren't new posts as frequently as before? Just like the free things you used, can you also tell us the Top 5 websites that you browse on a daily basis so all of us can share.

Well, the posting here comes and goes – When I have something of interest to say, I post. When I have time to say something – I post. I was curious though to see if I was posting much less then before – so of course, it is time to use the database as a big calculator.

I have been posting for about 271 days:

SQL> select sysdate-to_date('17-apr-2005','dd-mon-yyyy') dys
2 from dual;

DYS
----------
271.546481

In that time, I’ve made 195 posts, so there is about 1.4 days per posting on average:

SQL> select 271/195 from dual;

271/195
----------
1.38974359

Analytics are called for now to see how this average looks over time. So I loaded up a table T with the date of each posting. Now I can compute the average days between by month, as well as the count of postings per month and even use a bit of ASCII art to visualize it all:

SQL> select m,
2 to_char(avg(days_btwn),'99.99') days_btwn,
3 count(*) cnt,
4 round(100*ratio_to_report(count(*)) over (), 2) pct,
5 lpad('*',100*ratio_to_report(count(*)) over ()/2, '*') histg
6 from (
7 select trunc(x,'mm') m,
8 x-lag(x) over
9 (partition by trunc(x,'mm') order by x) days_btwn
10 from t
11 )
12 group by m
13 order by m
14 /

M DAYS_B CNT PCT HISTG
--------- ------ ---------- ---------- --------------
01-APR-05 1.00 14 7.18 ***
01-MAY-05 1.20 26 13.33 ******
01-JUN-05 1.22 24 12.31 ******
01-JUL-05 1.73 16 8.21 ****
01-AUG-05 1.36 23 11.79 *****
01-SEP-05 1.07 28 14.36 *******
01-OCT-05 1.61 19 9.74 ****
01-NOV-05 1.75 17 8.72 ****
01-DEC-05 1.33 22 11.28 *****
01-JAN-06 1.60 6 3.08 *

10 rows selected.

So, it looks like I’ve been anywhere from 1 to 1.75 days between postings – so so far for the month of January, I’m well within the “norm”. I wanted to see what this looked like without the “outliers” – April 2005 and Jan 2006 are partial months:

SQL> select m,
2 to_char(avg(days_btwn),'99.99') days_btwn,
3 count(*) cnt,
4 round(100*ratio_to_report(count(*)) over (), 2) pct,
5 lpad('*',100*ratio_to_report(count(*)) over ()/2, '*') histg
6 from (
7 select trunc(x,'mm') m,
8 x-lag(x) over
9 (partition by trunc(x,'mm') order by x) days_btwn
10 from t
11 )
12 where m not in ( to_date('01-apr-2005','dd-mon-yyyy'),
13 to_date('01-jan-2006','dd-mon-yyyy') )
14 group by m
15 order by m
16 /

M DAYS_B CNT PCT HISTG
--------- ------ ---------- ---------- --------------
01-MAY-05 1.20 26 14.86 *******
01-JUN-05 1.22 24 13.71 ******
01-JUL-05 1.73 16 9.14 ****
01-AUG-05 1.36 23 13.14 ******
01-SEP-05 1.07 28 16 ********
01-OCT-05 1.61 19 10.86 *****
01-NOV-05 1.75 17 9.71 ****
01-DEC-05 1.33 22 12.57 ******

8 rows selected.

Just goes to show that Analytics are obviously a consume oriented feature of the database – everyone could use these things! (Analytics Rock, They Roll – if you aren’t using them, you aren’t using Oracle).

So, the number of postings really hasn’t changed significantly over time – but this week was a busy week for me. I’ve been traveling around (can you guess where I am:
Photo_011306_001
should be an easy one), doing on site visits with customers. Plan on heading home tomorrow morning.

As for the top five websites, well, that changes – but some that I am on every day:

Monday, January 09, 2006

Flashing Back...

I saw these links on boingboing last week.  It is a slightly different model from what I had when I first started working – but is almost identical.  24 lines of text, 80 columns wide.  Green text on black.  I fondly remember the keyboard in particular.  I have never had a keyboard with the same sort of play.  They might have weighted 100 lbs, not something you would want to put on your lap perhaps – but the keyboard (which was cast iron) was phenomenal.  I learned to type very fast on that one.  Every keyboard since has been somewhat of a letdown.

It is funny how IBM appears to have invented the wireless keyboard though.  As I recall – they had some monster sized cables tethering them to the monitor.  Guess the cables didn’t look good in the picture.  In fact, IBM seems to have invented the wireless network and wireless electricity as well!

Consider typing in your code on such a device.  All Text – All the time.  No mouse.  You wanted to actually “see” your code more than 24 lines at a time – print it (this is where I started my “a subroutine must fit on a screen” mantra – the line printer was down the hall – up the stairs – down another hall).  Debuggers?  Hah – the code I wrote didn’t even run on the machine I wrote the code on.  We had to wrap the code in JCL – punch it to the MVS machine where it could be compiled (any errors, came back to your RDRLIST – like email).  Then, after it was compiled and linked down there, we could submit another JCL stream to actually run it.  It was all about instrumentation.

Thursday, January 05, 2006

A 9i-10g version....

A 9i/10g version of fncRefCursor2HTML. It was asked for, so here it is. Really the only big change is the removal of the 10g “quoting” feature on the style sheet.



CREATE OR REPLACE
FUNCTION fncRefCursor2HTML(rf SYS_REFCURSOR)
RETURN CLOB
as
lHTMLOutput XMLType;
lXSL long;
lXMLData XMLType;
lContext DBMS_XMLGEN.CTXHANDLE;
BEGIN
-- get a handle on the ref cursor --
lContext := DBMS_XMLGEN.NEWCONTEXT(rf);

-- setNullHandling to 1 (or 2) to allow null columns
-- to be displayed
DBMS_XMLGEN.setNullHandling(lContext,1);

-- create XML from ref cursor --
lXMLData := DBMS_XMLGEN.GETXMLTYPE
(lContext,DBMS_XMLGEN.NONE);

-- this is a generic XSL for Oracle's default
-- XML row and rowset tags --
-- " " is a non-breaking space --
lXSL :=
'<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>
<xsl:template match="/">
<html>
<body>
<table border="1">
<tr bgcolor="cyan">
<xsl:for-each select="/ROWSET/ROW[1]/*">
<th><xsl:value-of select="name()"/></th>
</xsl:for-each>
</tr>
<xsl:for-each select="/ROWSET/*">
<tr>
<xsl:for-each select="./*">
<td><xsl:value-of select="text()"/> </td>
</xsl:for-each>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>';

-- XSL transformation to convert XML to HTML --
lHTMLOutput := lXMLData.transform(XMLType(lXSL));

-- convert XMLType to Clob --
return lHTMLOutput.getClobVal();
END fncRefCursor2HTML;
/

IETab and Bookmarklets...

IETab – now that is cool. This morning, Uwe Küchler commented on this Firefox plugin. I got it – and at first thought “it doesn’t work”. But then it proved its worth right then and there.

What I did – I installed IETab. This lets me click on a button in the status bar and have that tab use the IE engine to render the page. No more firing up IE as an external program, just flip flop back and forth between the Gecko rendering engine and IE.

After installing, the first two sites I tried where of course http://asktom.oracle.com/ - no problem, and then http://tkyte.blogspot.com/. Uh-oh I thought, this thing doesn’t work. The page looked really botched. The right hand side bar was not where it was supposed to be! So, I fired up the real IE and noticed “hey, same problem”.

Turns out the code I had embedded in the I like online communities posting was causing IE to push things around. I don’t fire up IE that often so never noticed it. This option – just press a button and see it – will let me verify that the two browsers most people use here (IE and Firefox) both render the page OK.

I’m going to try it out on other “you must use IE for this site” sites and see how it goes. Looks promising so for.

Another goodie I use all of the time is a bookmarklet. These are little javascript scripts that reside on your machine and re-do pages for you. The bookmarklets I use constantly are:
  • Zoom Images (makes graphics larger)

  • Zap Images (makes graphics go away)

  • Anchors – shows all of the hidden anchors on a page. If you have ever said “how does Tom manage to point to individual paragraphs or un-linked sections in the documentation” – this answers that question (there are thousands of hidden links in the documentation). It is also how I linked directly to Uwe’s comment above – there are hidden anchors on lots of pages (including asktom).

Tuesday, January 03, 2006

Nirvana is...

Nirvana is…

inbox

An almost empty inbox. Took forever, but there it is. One outstanding customer issue and then it is “empty”.

I like online communities...

I like online communities – I like the entire concept of “community based support”. So, it makes me really happy to have someone send me an email like this:

I'm an Oracle developer, and I thought I'd share this with you. Using 10gR2, I figured out a way to convert any ref cursor to HTML output without explicitly diving into the data dictionary. Back in 2002, I wrote something that was like this with 500 lines of code to do this same thing. Now, it's about a tenth of that and faster.

Anyway, I want to share it with everybody but don't know where to go. So, please post it/use it/share it however you like (or not).

Thanks for all the advice. Happy Holidays to you and yours.

Bill Myers

It is all about giving back sometimes. I liked his idea for two reasons. First and foremost – he is giving back. That is the best part.

Second, this has some interesting implications for HTML DB and giving it the ability to process ref cursors. Basically, he has a routine to take in any ref cursor (any cursor really, I’ll call it from SQL directly) and convert it into an html table – of course, using the same concept, you can format it as any html you want at runtime:

CREATE OR REPLACE FUNCTION fncRefCursor2HTML(rf SYS_REFCURSOR)
RETURN CLOB
IS
lRetVal CLOB;
lHTMLOutput XMLType;

lXSL CLOB;
lXMLData XMLType;

lContext DBMS_XMLGEN.CTXHANDLE;
BEGIN
-- get a handle on the ref cursor --
lContext := DBMS_XMLGEN.NEWCONTEXT(rf);
-- setNullHandling to 1 (or 2) to allow null columns to be displayed --
DBMS_XMLGEN.setNullHandling(lContext,1);
-- create XML from ref cursor --
lXMLData := DBMS_XMLGEN.GETXMLTYPE(lContext,DBMS_XMLGEN.NONE);

-- this is a generic XSL for Oracle's default XML row and rowset tags --
-- " " is a non-breaking space --
lXSL := lXSL || q'[<?xml version="1.0" encoding="ISO-8859-1"?>]';
lXSL := lXSL || q'[<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">]';
lXSL := lXSL || q'[ <xsl:output method="html"/>]';
lXSL := lXSL || q'[ <xsl:template match="/">]';
lXSL := lXSL || q'[ <html>]';
lXSL := lXSL || q'[ <body>]';
lXSL := lXSL || q'[ <table border="1">]';
lXSL := lXSL || q'[ <tr bgcolor="cyan">]';
lXSL := lXSL || q'[ <xsl:for-each select="/ROWSET/ROW[1]/*">]';
lXSL := lXSL || q'[ <th><xsl:value-of select="name()"/></th>]';
lXSL := lXSL || q'[ </xsl:for-each>]';
lXSL := lXSL || q'[ </tr>]';
lXSL := lXSL || q'[ <xsl:for-each select="/ROWSET/*">]';
lXSL := lXSL || q'[ <tr>]';
lXSL := lXSL || q'[ <xsl:for-each select="./*">]';
lXSL := lXSL || q'[ <td><xsl:value-of select="text()"/> </td>]';
lXSL := lXSL || q'[ </xsl:for-each>]';
lXSL := lXSL || q'[ </tr>]';
lXSL := lXSL || q'[ </xsl:for-each>]';
lXSL := lXSL || q'[ </table>]';
lXSL := lXSL || q'[ </body>]';
lXSL := lXSL || q'[ </html>]';
lXSL := lXSL || q'[ </xsl:template>]';
lXSL := lXSL || q'[</xsl:stylesheet>]';

-- XSL transformation to convert XML to HTML --
lHTMLOutput := lXMLData.transform(XMLType(lXSL));
-- convert XMLType to Clob --
lRetVal := lHTMLOutput.getClobVal();

RETURN lRetVal;
END fncRefCursor2HTML;
/


And then we can see the “magic” by simply running:

ops$tkyte@ORA10GR2> variable x clob
ops$tkyte@ORA10GR2> declare
2 l_cursor sys_refcursor;
3 begin
4 open l_cursor for select *
from scott.dept where rownum = 1;
5 :x := fncRefCursor2HTML( l_cursor );
6 close l_cursor;
7 end;
8 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> print x

X
---------------------------------------------
<html>
<body>
<table border="1">
<tr bgcolor="cyan">
<th>DEPTNO</th>
<th>DNAME</th>
<th>LOC</th>
</tr>
<tr>
<td>10</td>
<td>ACCOUNTING</td>
<td>NEW YORK</td>

</table>
</body>
</html>


Anyway, I’d like to thank Bill for sharing this – hope he keeps doing that (in forums, in discussions, at user group meetings, where ever). I learn something new everyday – and only because of exchanges of ideas like this…

Sunday, January 01, 2006

I was stumbling upon...

I was “stumbling upon” and hit this site. It reminded me of my all time favorite “oh, I wish it didn’t work that like” things in PLSQL.


SQL> create table t ( x int );
Table created.

SQL> begin
2 for i in 1 .. 2
3 loop
4 insert into t values ( i );
5 end loop
6 rollback;
7 end;
8 /
PL/SQL procedure successfully completed.

SQL> select * from t;

X
----------
1
2


Neat, eh? The first C thing written on that page with the comment reminded me of it, a similar issue.

PLSQL has the same issue as C with the comment though (as would many/most languages I think)

SQL> declare
2 a number := 1;
3 b number := 2;
4 c number := 3;
5 d number := 4;
6 begin
7 a := b; /* this is a bug
8 c := d; /* c=d will never happen */
9 dbms_output.put_line( 'D = ' || d );
10 dbms_output.put_line( 'C = ' || c );
11 end;
12 /
D = 4
C = 3
PL/SQL procedure successfully completed.

#7 on that referenced page on C reminds me lots of zero divide issues people have:

SQL> create table t ( x int, y int );
Table created.

SQL> insert into t values ( 1, 0 );
1 row created.

SQL> select /*+ RULE */ * from t where y > 0 and x/y > 0;
select /*+ RULE */ * from t where y > 0 and x/y > 0
*
ERROR at line 1:
ORA-01476: divisor is equal to zero


SQL> select /*+ RULE */ * from t where x/y > 0 and y > 0;
no rows selected

But best of all about that page? It is a top 10 with 17 things… I liked that as my top 5 things done wrong presentation now has 7 things in it (and I never have changed the title…)