Weekend in Antalya

We had been to Antalya, for 2 nights, annual meeting of my company, based on invention and simplicity was held.

“Steal” Jobs video shown by our CEO is impressive to me at first…
Steve Jobs interviewed just before returning to Apple

But after a day passed, only impressive thing was “simply” :

Beautiful sand, shore, and sea….
waves, sand and sea shells…

Check out stellarium, star map application, I had chance to try in Antalya night sky, in which you could easily sea stars… A snap from application:

A photo from Garipce, Istanbul

When did Optimizer use index FFS in queries having Max(datetime)?

Assume a Table MYTABLEA1, having date type column for example datetime:

select max(datetime) from MYCHEMA.MYTABLEA1;

Row count: 1384

 

Plan
SELECT STATEMENT CHOOSE
	2 SORT AGGREGATE
		1 TABLE ACCESS FULL MYCHEMA.MYTABLEA1

 

select max(datetime) from MYCHEMA.MYTABLEA2;

Row count: 8139221

Plan
SELECT STATEMENT CHOOSE Cost: 442,815 Bytes: 8 Cardinality: 1
	3 SORT AGGREGATE Bytes: 8 Cardinality: 1
		2 PARTITION RANGE ALL Partition #: 2 Partitions accessed #1 – #26
			1 INDEX FULL SCAN (MIN/MAX) NON-UNIQUE MYCHEMA.MYTABLEA2 Cost: 442,815 Bytes: 2.491.053.600 Cardinality: 311.381.700 Partition #: 2 Partitions accessed #1 – #26

 

I quoted folowing from Julian.

The indexed columns must have a NOT NULL constraint

The table does not need to be analysed

http://www.juliandyke.com/Optimisation/Operations/IndexFullScanMinMax.html

 

Strange procedure executions, and dbms_lock.sleep(7)

I had executed a number of procedures (approx. 80) from TOAD as follows:

exec <schema_name>.<procedure name>;

…

exec <schema_name>.<procedure name>;

exec <schema_name>.<procedure name>;

Although it says as:

PL/SQL procedure successfully completed.

It does not. Because the tables that must be accumulated by those procedures are empty. For this reason I tried it in SQL*Plus, but it does not work either. After then, I put the executions of the procedures in a PL/SQL block with dbms_lock.sleep(3) as follows:

begin
<schema_name>.<procedure name>;

dbms_lock.sleep(3);

<schema_name>.<procedure name>;

dbms_lock.sleep(3);

…

<schema_name>.<procedure name>;

 Then it worked!

I amcurrently using, Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit on a ia64 hp server rx4640 box.

rename table

Renaming table can be surprising, if you tried both rename and alter table for renaming.

If you issue below statement, you will get following error:

alter table schema1.T1 rename to schema1.T2;

ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations

and if you issue below statement, you will get another surprising error:

rename schema1.T1 to schema1.T2;

ORA-01765: specifying table’s owner name is not allowed

The last resort and true one is issueing:

alter table schema1.T1 rename to T2;

Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!