Category: Blogroll

Speaking in Stockholm

It’s a great honor for me to be invited to speak in Sweden in December 10-11 ! I am optimistic in getting a few copies of my book to give away 🙂

Flying toasters and dense_rank

Have fun with this caps-lock user question : asktom:Logic behind the DENSE_RANK This is one more statement on how to not use order by like in select ename, deptno, row_number() over (order by 1) from emp order by 2; ENAME DEPTNO ROW_NUMBER()OVER(ORDERBY1) ———- ———- ————————– CLARK 10 1 KING 10 2 MILLER 10 3 JONES… Continue reading Flying toasters and dense_rank

Published
Categorized as Blogroll, sql

Stored outlines

Note: Performance Tuning Guide Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines,… Continue reading Stored outlines

Cycling

How to detect cycling records in 9i, remember CONNECT BY NOCYCLE does not exist in 9i SQL> create table lsc_t as 2 select 1 parent, 2 child from dual 3 union all select 2,3 from dual 4 union all select 4,5 from dual 5 union all select 5,6 from dual 6 union all select 6,4… Continue reading Cycling

Published
Categorized as Blogroll, sql

Oracle Streams

If you have a datawarehouse and the data are getting to big for a full duplicate or tablespace transport, if you want to experience more about Streams or simply if you are in San Francisco and wants some distraction on Thursday after at 1pm, do not miss Chen session Oracle Streams – Live Demo Oracle… Continue reading Oracle Streams

About case sensitivity

SQL is a case insensitive language. That means, you can write any SQL statement in uppercase or lowercase. SQL> SELECT DUMMY FROM DUAL; D – X SQL> select dummy from dual; D – X Some formatters like Toad and some courseware like Skillsoft E-Learning advise to use UPPERCASE for commands and lowercase for tables and… Continue reading About case sensitivity

Hey, we have a journeyman !

Congrats to Denes Kubicek for being the first journeyman on OTN. OTN stars Justin and Nicolas (18954 and 15321 posts) do not get this title, as we Oracle Ace are sticked to our Ace logos. OTN upgraded the forums recently, many posts about this : OTN Forums – Change the look and feel yourself OTN… Continue reading Hey, we have a journeyman !

puzzled by Toad

one colleague just showed me how to insert duplicate rows in a table that has a primary key. create table t(x number primary key);[F5] Table created. insert into t values (1);[F5] 1 row created. insert into t values (1);[F5] 1 row created. Hey, what happened? It took me quite a while to figure out which… Continue reading puzzled by Toad

what is faster, select count(*) or select count(pk) ?

Oh no, not another post about COUNT(*) and COUNT(1) Well, it is not exactly the case. I just had the issue this morning that count(*) was too slow. SQL> create table t as select rownum id, 2 lpad(‘a’,4000,’a’) a, 3 lpad(‘b’,4000,’b’) b, 4 lpad(‘c’,4000,’c’) c, 5 lpad(‘d’,4000,’d’) d 6 from dual connect by level create… Continue reading what is faster, select count(*) or select count(pk) ?

Published
Categorized as Blogroll, sql

read uncommitted

the default isolation level is READ COMMITTED. It means, a session read the committed data. Session 1: SQL> set transaction isolation level read committed; Transaction set. Session 2: SQL> update emp set sal=4000 where ename=’SCOTT’; 1 row updated. Session 1: SQL> select sal from emp where ename=’SCOTT’; SAL ———- 3000 Session 2: SQL> commit; Commit… Continue reading read uncommitted

return size of to_char

The width of a columns is known before execution. Well, in most cases… SELECT TO_CHAR(SYSDATE,’DAY’,’NLS_DATE_LANGUAGE=FRENCH’) FROM DUAL; TO_CHAR( ——– SAMEDI The length is as most 8 characters (VENDREDI). Therefore the width of the column is 8. SELECT TO_CHAR(SYSDATE,’YEAR’) FROM DUAL; TO_CHAR(SYSDATE,’YEAR’) —————————————— TWO THOUSAND EIGHT Oracle thinks the length is at most 42 characters. This… Continue reading return size of to_char

SET LONGCHUNKSIZE

I noticed a side effect of SET LONGC today… The default setting for LONG and LONGC is 80. This is quite annoying when you SELECT TEXT FROM ALL_VIEWS as it truncates the text to 80. So why not setting it to the maximum? Let’s first demo the usage of LINESIZE, LONG and LONGCHUNKSIZE SQL> create… Continue reading SET LONGCHUNKSIZE

certification blog

I just notice a new blog on blogs.oracle.com : Oracle Certification Blog This is probably the right place to ask questions or get news about certification. Out of the many questions I may ask here are just a few I am preparing. – When will the OCM upgrade for 9i OCM be available ? –… Continue reading certification blog

secondmax

How do I retrieve the second highest salary of emp? There is no right or wrong treatment of duplicates, there are only buggy specifications… There are plenty of ways to do this. An Oracle 7 approach would be SELECT MAX(SAL) FROM EMP, ( SELECT MAX(SAL) MAXSAL FROM EMP) WHERE SAL>MAXSAL; Using TOP-n queries I could… Continue reading secondmax

Epoch

How do I get the current number of milliseconds since 1970-01-01. I still have no access to oracle.com so I created a new account to answer this question. Either you use the difference between timestamp ‘1970-01-01 00:00:00 +00:00’ and current_timestamp, or you can use java, which is more portable. YMMV create function epoch return number… Continue reading Epoch

My book is available on amazon.com

One year ago I started writing a book on SQL. Writing a book is an amazing amount of work and I am glad I got helped from my five reviewers Chen, Andrew, Tom, Marco and Lutz. It will ship in December 2008. http://amazon.com/Advanced-Oracle-SQL-Programming-Focus/dp/0977671585

Hey Scott, where have you been ?

Today I missed Scott in my emp table. When selecting from EMP, Scott is not there. Gone… Ok, let’s recreate the scott schema. C:> sqlplus / as sysdba SQL*Plus: Release 10.2.0.3.0 – Production Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 SYS@lsc02> spool scott.txt SYS@lsc02> drop… Continue reading Hey Scott, where have you been ?

DITO

I never used Oracle Web Conference OWC, for security reason. Today I created one SR and read first time about Demo It To Oracle (DITO). This is basically a link CamStudio.org, which is a tool that records AVI file and converts AVI to flash SWF file. This is nice to have because you do need… Continue reading DITO

drop all objects

warning: the script below is destructive and not 100% safe update: it is unsafe to drop SYS_ objects, check for instance note 579399.1 A question was posted on the french forums of developez.net about how to drop all objects of an user. The drop user toto cascade; followed by create user toto identified by tott;… Continue reading drop all objects

select distinct collect

I answered a question on otn today about distinct. Reprased, how to select distinct collection? select job, collect(distinct deptno) deptnos from emp group by job; JOB DEPTNOS ——— ——————————————- ANALYST SYSTPTJCzBffh0AjgQ59n0o3QCA==(20) CLERK SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30) MANAGER SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30) PRESIDENT SYSTPTJCzBffh0AjgQ59n0o3QCA==(10) SALESMAN SYSTPTJCzBffh0AjgQ59n0o3QCA==(30) 5 rows selected. select distinct collect(distinct deptno) deptnos from emp group by… Continue reading select distinct collect

10.2.0.4/Unix

Unix users will enjoy this : 10.2.0.4 is available on Solaris and HPUX patchset 6810189 Still waiting for AIX…

Select bottom rows

Today I answered a question on otn regarding order by. Let’s build a different test case. You have a view that contain an order by. You want to retrieve the last 5 records. However you do not have access to the sort column. SYS@LSC01> GRANT CREATE VIEW TO USER_A IDENTIFIED BY A; Grant succeeded. SYS@LSC01>… Continue reading Select bottom rows

alert log in xml format

The alert log is in xml format in Oracle 11g. If you want to parse the content of the file and use the XML functions to retrieve the data, you can use this way : SQL> create or replace directory alert as 2 ‘/app/oracle/diag/rdbms/lsc01/lsc01/alert’; Directory created. SQL> var c clob SQL> declare 2 b bfile… Continue reading alert log in xml format

oradebug tracefile_name

I have enabled tracing in a session and now I want to retrieve the name of the tracefile. Ex: my session has sid 335. How do I retrieve trace file name from sqlplus ? select pid from v$process where addr in (select paddr from v$session where sid=335); PID ———- 47 Now I can use oradebug… Continue reading oradebug tracefile_name

To RR or to YY ?

What is worst? To use DD-MON-RR or to use DD-MON-YY? When entering the birthday of my grandfather, I will write it as 31-JUL-1912, so both formats will be fine. If I enter it 31-JUL-12, both formats will be wrong. Ok, which date will I enter now and in the future? For short-time contracts I will… Continue reading To RR or to YY ?