It's wonderful, but will I survive?

It's Open World! Sunday a full day of IOUG lectures. Today I heard Jonathan Lewis on "Performance Tuning - being an expert"; Greg Rahm on Data Warehousing and Exedata; Cary Millsap on Performance and Chen Shapira on the uses of charts. I had an introduction to desktop widgets from two experts, I was the only attendee. And I had a nice long introduction to Apex at the Demo Grounds.

It is all Wonderful. Just one little question. How am I going to survive 3 more days? I'm going to bed!

A trivial index demo

Today I was sitting in a public library, minding my own business when a man who I had never seen before, leaned over to me and asked me, "Is it true that you can drop and index created explicitly, but not one created implicitly as part of a constraint? Initially the reaction was implicit/explicit? Please speak English. When I sorted that out there were 2 further reactions, also unspoken, "Duh, that's pretty obvious", and "Why me?"

"It is an Ancient DBA and he stoppeth one of three."

I demonstrated the matter a bit to him.

Logged on as scott, I create a play table:
SQL> create table tmp(f1 number(4), f2 number(4), f3 number(4));

Table created.

SQL> insert into tmp values (1, 2, 3);

1 row created.

SQL> create index tmp_f2 on tmp(f2);

Index created.

SQL> drop index tmp_f2;

Index dropped.


All straight forward. The index was created 'explicitly' and there is no constraint that it affects if it is dropped, so I can drop it without problem.


Now to add a constraint, and an 'implicit' index.

SQL> alter table tmp modify f2 unique;

Table altered.

SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TMP';

CONSTRAINT_NAME
------------------------------
SYS_C0011482

SQL> select index_name from user_indexes where table_name = 'TMP';

INDEX_NAME
------------------------------
SYS_C0011482

The constraint has been created as has an index


SQL> drop index SYS_C0011482;
drop index SYS_C0011482
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

and as expected, the index cannot be dropped since it is used by the constraint.


It is all very trivial.

What did get a little interesting was that I was able to enter multiple rows of nulls in a field with a 'unique' constraint. I need to review the properties of null.

And the real question that was never answered

"Now wherefore stopp'st thou me?"

OOW any free Discover tickets around?

A quick question.

I see that Oracle Open World Discover ticket costs $50. I remember that it cost something in prior years, but that there was some way to get it for free if you signed up by the early bird date. Is there such a thing again this year?

I have seen the 'chance for a full registration', and have entered, but I haven't seen the free 'discover' pass. Is it around?

TYIA

Making problems for myself

Playing around with my toy database I asked myself, "What happens if DUAL has more than 1 row?" I found out.

SQL> insert into dual values ('Y');

1 row created.

SQL> select * from dual;

D
-
X

SQL> select count(*) from dual;

COUNT(*) ---------- 1

I tried it again. Same result. "Oh, I guess I can't insert into DUAL", says I, and I went about my business.

Later I logged on as SCOTT and tried to drop a table. Playing I have more EMP tables than employees.

SQL> DROP TABLE EMP4;

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01422: exact fetch returns more than requested number of rows

WHAT??!!?

Yes it is there and there is only 1 table called EMP4.

SELECT OWNER, OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_NAME = 'EMP4'

OWNER OBJECT_NAME

------------------------------ ------------------------------

SCOTT EMP4

I looked the matter up at orafaq. and followed the instructions.

SQL> select * from dual;

D

-

X

SQL> create table temp_dual as select * from dual;

Table created.

SQL> select * from temp_dual;

D

-

X

Y

Y

Yes, I plead guilty. I DID succeed in inserting those rows into DUAL.

SQL> delete from dual where dummy = 'Y';

1 row deleted.

Strange. It deleted 1 row even though I had put 2 in.

SQL> drop table temp_dual;

drop table temp_dual

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01422: exact fetch returns more than requested number of rows

I deleted the second excess row:

SQL> delete from dual where dummy = 'Y';

1 row deleted.

and I had a functioning database back.

SQL> DROP TABLE TMP_DUAL;

Table dropped.

and then as SCOTT

SQL> DROP TABLE EMP4;

Table dropped.

OK, I get it, Oracle consults DUAL in the drop process. And don't go messing up a database of any importance. But it is odd how the fact that I was succeeding to mess things up was hidden from me. Yes it told me that I had inserted the row, but then it didn't display it with a select. It was an interesting bit of play.