Cyber Drop

Found this via one of my interesting mails & felt it's worth sharing.

“For those who are not aware of Shishu pala, He was killed by Lord Krishna for opprobrious insult that too after 100 times. A poem was written about Shishupala’s death and it is called perfect palindrome.

The poem is noted for its intricate wordplay, and textual complexity. The 19th canto contains the following stanza which is an example of what has been called “the most complex and exquisite type of palindrome ever invented”. It was devised by the Sanskrit Aesthetics, who termed it Sarvatobhadra, that is, “perfect in every direction” - it yields the same text if read forwards, backwards, down, or up:

sa-kA-ra-nA-nA-ra-kA-sa-
kA-ya-sA-da-da-sA-ya-kA
ra-sA-ha-vA vA-ha-sA-ra-
nA-da-vA-da-da-vA-da-nA.
(nA da vA da da vA da nA
ra sA ha vA vA ha sA ra
kA ya sA da da sA ya kA
sa kA ra nA nA ra kA sa)

Reference:
http://en.wikipedia.org/wiki/Shishupala_Vadha

Hide text is an easy way to avoid SPAM. It allows you to convert any text into GIF format which can be used in your Websites or blogs.

LOL :)

Anyone with proper understanding on ORDER BY clause will not be surprised by this post :) Still, I feel it’s worth sharing.

Query 1

SELECT employee_id , last_name,salary,department_id FROM employees ORDER BY 1;

This is positional sorting. So, the result set will be ordered by employee_id column in ascending order.

Query 2

SELECT employee_id , last_name,salary,department_id FROM employees ORDER BY 5;

It will throw an error “ORA-01785: ORDER BY item must be the number of a SELECT-list expression” as there is NO 5^th column listed in the SELECT list. (employee_id is the 1^st column listed in the SELECT statement, last_name is the second. salary is the third and department_id is the fourth. So, there is no 5^th column in SELECT list)

Query 3

SELECT employee_id , last_name,salary,department_id FROM employees ORDER BY 4+2-1;

4+2-1 is 5 so, should have the same behavior as Query 2? If you said, ‘yes’ then you need to rework on your basics J

This query 3 will run fine with out any error. As you can see here in the documentation Oracle Database SQL Language Reference, you can specify an expression or a position. The position is an integer. As in Query 1, if in case we have given "order by 1" then the 1 is treated as a position of the column in SELECT list whereas "4+2-1" is treated as an expression, which yields 5. And this becomes an ordering by a constant, in other words a non operation.

This post is the continuation of the difference between COUNT(*) and COUNT(1)

You can NOT ask for anything better than this… The whole credit goes to the user Padder from oracle OTN Forums. Read the whole thread at OTN Forums. A two year old thread, definitely worth revisiting! To be honest, I am still trying to understand some parts of this procedure J I always used the Execution Plan in Oracle SQL Developer and never bothered or tried to write one. This one is ace! I mean… not that it can not be done by anyone else but the idea…

The below test generates explain plans for well-known variants of COUNT (*) and queries the plan for the projection column (new in 10g). Assuming we agree on what the projection column represents this appears to concur with the generally held view that Oracle internally rewrites COUNT(1) (and other simple COUNT (literal)) to COUNT(*).

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> SET SERVEROUTPUT ON SIZE UNLIMITED;

SQL> <>
2 DECLARE
3 v_val vc2s
4 := vc2s ('*', '0', '1', '9', '+1', '-1', '1 + 1', '1 - 1', '''A''', '''A'' || ''A''',
5 '37 * 45 + 12', 'ROWNUM', 'TO_CHAR(1)', 'SYSDATE', 'ROWID');
6 v_cnt VARCHAR2 (100);
7 BEGIN
8 EXECUTE IMMEDIATE 'TRUNCATE TABLE toad_plan_table';
9
10 FOR l_val IN 1 .. v_val.COUNT LOOP
11 v_cnt := 'COUNT(' || v_val (l_val) || ')';
12 SAVEPOINT sv_count_projection;
13
14 EXECUTE IMMEDIATE 'EXPLAIN PLAN '
15 || 'INTO '
16 || 'toad_plan_table '
17 || 'FOR '
18 || 'SELECT '
19 || v_cnt
20 || ' FROM dual';
21
22 FOR r_row IN (SELECT SUBSTR (projection, 1, INSTR (projection, ')', -1)) projection
23 FROM (SELECT SUBSTR (tpt.projection,
24 INSTR (tpt.projection, ' ') + 1) projection
25 FROM toad_plan_table tpt
26 WHERE tpt.operation = 'SORT'
27 AND tpt.options = 'AGGREGATE')) LOOP
28 dbms_output.put_line (v_cnt || ' is rewritten to ' || r_row.projection);
29 END LOOP ;
30
31 ROLLBACK TO sv_count_projection;
32 END LOOP ;
33 EXCEPTION
34 WHEN OTHERS THEN
35 ROLLBACK;
36 raise_application_error (-20000,
37 'failed to derive count projection'
38 || '['
39 || 'v_cnt => '
40 || v_cnt
41 || ']',
42 TRUE);
43 END count_projection;
44 /

COUNT(*) is rewritten to COUNT(*)
COUNT(0) is rewritten to COUNT(*)
COUNT(1) is rewritten to COUNT(*)
COUNT(9) is rewritten to COUNT(*)
COUNT(+1) is rewritten to COUNT(*)
COUNT(-1) is rewritten to COUNT((-1))
COUNT(1 + 1) is rewritten to COUNT(2)
COUNT(1 - 1) is rewritten to COUNT(0)
COUNT('A') is rewritten to COUNT(*)
COUNT('A' || 'A') is rewritten to COUNT('AA')
COUNT(37 * 45 + 12) is rewritten to COUNT(1677)
COUNT(ROWNUM) is rewritten to COUNT(ROWNUM)
COUNT(TO_CHAR(1)) is rewritten to COUNT('1')
COUNT(SYSDATE) is rewritten to COUNT(SYSDATE@!)
COUNT(ROWID) is rewritten to COUNT(ROWID)

PL/SQL procedure successfully completed.

SQL>