Monthly Archives: October 2008

echo does not accept end of arguments operator

Leave a reply

Let’s start with an example :


$ cat AI
#!/usr/bin/bash
while :
do
  echo "What's your name ?"
  read a
  if [ ! $a ]
  then
    break
  fi
  echo "Your name is :"
  echo $a
  echo
done
echo "Bye"

$ ./AI
What's your name ?
Jo
Your name is :
Jo

What's your name ?
Jack
Your name is :
Jack

What's your name ?
-e
Your name is :

What's your name ?

Bye

This artificial intelligence is not very intelligent, it cannot recognize me if I am called “-e” (it is Friday, have a look at Little Bobby Tables ).

Most unix tools consider
-- signals the end of options and disables further option processing

But not echo :(

$ touch -e
touch: invalid option -- e
Try `touch --help' for more information.
$ touch -- -e
$ ls -l -e
ls: invalid option -- e
Try `ls --help' for more information.
$ ls -l -- -e
-rw-r--r-- 1 lsc dba 0 Oct 31 15:44 -e
$ rm -e
rm: invalid option -- e
Try `rm ./-e' to remove the file `-e'.
Try `rm --help' for more information.
$ rm -- -e
$ echo -e

$ echo -- -e
-- -e

So, what’s the solution? well, probably not using “echo”, for example printf

$ (echo “What’s your name ?”
read a
echo “Your name is :”
printf “%s\n” “$a”)
What’s your name ?
-e
Your name is :
-e

Flying toasters and dense_rank

2 Replies

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              20                          4
FORD               20                          5
ADAMS              20                          6
SMITH              20                          7
SCOTT              20                          8
WARD               30                          9
TURNER             30                         10
ALLEN              30                         11
JAMES              30                         12
BLAKE              30                         13
MARTIN             30                         14

According to the doc, order by position is invalid. Actually, order by 1 is treated as order by ‘bananas’.
When used in an analytic function, the order_by_clause must take an expression (expr). The SIBLINGS keyword is not valid (it is relevant only in hierarchical queries). Position (position) and column aliases (c_alias) are also invalid. Otherwise this order_by_clause is the same as that used to order the overall query or subquery.

Thanks Tom for being so funny :)

Stored outlines

2 Replies

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, which offer superior SQL performance and stability compared with stored outlines.

This said, let’s take a small example. If you have a query which is running fast most of the time and sometimes is running very slow due an unexpected plan change, you may want to considering enforcing plan stability with a Stored Outline.

To fake this example, let’s try to enforce a full table scan for select empno from emp where ename=’SCOTT’. 

SQL> set autot on exp
SQL> select empno from emp where ename='SCOTT';
     EMPNO
----------
      7788

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 By

For the purpose of this exercice, I consider this to be the right plan and I want to enforce Oracle to use this plan for this query.


SQL> create or replace outline o for category emp_scott on 
  select empno from emp where ename='SCOTT';

Outline created.

SQL> create unique index i on emp(ename) 
  tablespace my_slow_tape_drive;

Index created.

SQL> set timi on
SQL> set autot on exp
SQL> select empno from emp where ename='SCOTT';
     EMPNO
----------
      7788

Elapsed: 01:45:59.95

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2
   2    1     INDEX (UNIQUE SCAN) OF 'I' (UNIQUE) (Cost=1

Oracle uses an index scan, but the index is located on a tape (which is not possible on 11gR1 and older ;) ) and it takes ages to complete the query. Let’s try to use the good plan that was used at the time we created the stored outline

 
SQL> alter session set use_stored_outlines=emp_scott;

Session altered.

Elapsed: 00:00:00.00
SQL> select empno from emp where ename='SCOTT';
     EMPNO
----------
      7788

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 By

SQL> SELECT LAST_LOAD_TIME,OUTLINE_CATEGORY,sql_text 
  from v$sql 
  where sql_text like 'select%SCOTT%';
LAST_LOAD_TIME      OUTLINE_C SQL_TEXT
------------------- --------- ----------------------------
2008-10-16/09:42:30           select empno from emp where 
2008-10-16/09:46:50 EMP_SCOTT select empno from emp where

The plan using the outline is now used

where is my database link listed?

3 Replies 


$ sqlplus scott/tiger@DEVL
SQL> select * from all_db_links;
no rows selected
SQL> select * from dual@PROD;
D
-
X

Hey, why does this work???

Ok, after some research I found out that this seems to be an implicit loopback database link. The fact that the DEVL database has the global name set to PROD is just to annoy more ;)


SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
PROD.LAURENTSCHNEIDER.COM