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>

What is the difference between count (*) and count (1). One can argue all their life about this.

There are sites on the Internet saying,

Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields. For example, based on the example above, the following syntax would result in better performance:

SELECT department, COUNT (1) as "Number of employees" FROM employees WHERE salary > 25000 GROUP BY department;

Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT (*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

Do you think it’s correct? No? The result to this question may also depend on the oracle version you use.

Well, I am not trying to prove anything here. Just wanted to say that please read Ask Tom’s page on COUNT (*) vs. COUNT (1) A lot of useful information out there for you to learn and also to think about. There was a user saying,

Hi Tom,

There is another opinion in:

OCP Introduction to Oracle 9i: SQL Exam Guide, p. 124

"Do not use count (*) to determine the number of rows in a table. Use count (1) or count (ROWID) instead. These options are faster because they bypass some unnecessary operations in Oracle's SQL processing mechanism."

And the reply to it was…

OPINIONS DON'T COUNT

FACTS -- FACTS COUNT.

First, count (1) internally is rewritten as count (*). That is a FACT.

Don’t you agree with this? Well, read more on Ask Tom’s page on COUNT (*) vs. COUNT (1) when you have time. It’s a real lengthy discussion which worth spending time on.

“If both Truncate and Delete commands delete all the rows of a table, then what is the difference between DELETE and TRUNCATE command?

Any interviewer may ask this question. The most expected replies could be any of the below:

1. DELETE is a DML while TRUNCATE is a DDL statement
2. DELETE is less drastic, in that a deletion can be rolled back whereas a truncation cannot be.
3. DELETE is also more controllable, in that it is possible to choose which rows to delete, whereas a truncation always affects the whole table.
4. DELETE is, however, a lot slower and can place a lot of strain on the database. TRUNCATE is virtually instantaneous and effortless

(To know more such answers, look through Geek Interviews Page. I don’t take liability for the irrelevant answers :))

Alright! But how does one prove that TRUNCATE is virtually instantaneous, effortless and is really of better performance than DELETE? How does it work internally?

As said in the first point, TRUNCATE is a DDL command and it operates within the data dictionary and affects the structure of the table, not the contents of the table. However, the change it makes to the structure has the side effect of destroying all the rows in the table.

An insight to it…

The data dictionary will have the definition of data and also table’s physical location. When a table is created, a table is allocated a single area of space (fixed size) in the database’s data files. This is known as an extent and initially will be empty. Then, as rows are inserted into the table, the extent fills up. Once an extent is full, more extents will be allocated to the table automatically. Therefore, a table may consist of one or more extents which hold the rows. Along with tracking the extent allocation, the data dictionary also tracks how much of the space allocated to the table has been used. This is done with the high water mark. The high water mark is the last position in the last extent that has been used; all space below the high water mark has been used for rows at one time or another, and none of the space above the high water mark have been used yet.

It should be noted that it is possible for there to be plenty of space below the high water mark that is not being used at the moment; this is because of rows having been removed with a DELETE command. Inserting rows into a table pushes the high water mark up. Deleting them leaves the high water mark where it is; the space they occupied remains assigned to the table but is freed up for inserting more rows. Truncating a table resets the high water mark. That is, within the data dictionary, the recorded position of the high water mark is moved to the beginning of the table’s first extent. As Oracle assumes that there can be no rows above the high water mark, this has the effect of removing every row from the table. The table is emptied and remains empty until subsequent insertions begin to push the high water mark back up again. In this manner, one DDL command, which does little more than make an update in the data dictionary, can annihilate billions of rows in a table.