Why You Must Use an ORDER BY Sometimes
Suresh submitted this question on the Oracle Community forum:
in oracle 10 g , the below query results in alphabetical order , there is no order by caluse. but when i am running the same query in oracle 11g , it gives me in random order. what is the problem here?
People, it is really simple: You want an ordered result set? use ORDER BY.
Tom Kyte wrote about it in this blog post and in his books:
Do not ever count on the order of rows from a query unless you have an ORDER BY statement on your query!
The only way to retrieve data from the database in some sorted order is to include an ORDER BY on your query. There is no substitute for ORDER BY.
By the way, the query in the forum post looks suspiciously Hibernated. Don’t let Jeff know 
User Exceptions Gotcha, When an Error Appears as a Success
This is a guest post by David Clement, a veteran database engineer, a coworker and a friend of mine. You can find him online at davidclement.org.
Here is an interesting feature that’s worth knowing about because of the potential for strange bugs. When you run a SQL script in the Unix shell, if you use a user-defined exception, you have to take care to prevent failure and success from returning the same result.
The following code snippet shows a normal way to return an error code to the Unix shell from a SQL script. I saved this as return_error_code.sql:
3 Comments | Filed in Oracle, Tips | Tags: gotcha, linux, pl/sql, shell, sql, unixNeat Oracle Database 11g Release 2 Feature: Preprocessing External Tables
External tables enable users to access data in text files, immediately eliminating the need to load input text files to intermediate tables for processing—saving both time and storage space. Now, with Oracle Database 11g Release 2, intermediate processing of any kind—such as decompression of compressed input files—is eliminated, further saving time and storage, not to mention a change to the existing code.
But the power of inline preprocessing is not limited to decompression alone. It can be applied to any type of prior processing required, as long as it produces an output that can be parsed by the external table. The directory listing is just one small demonstration of this rich capability of preprocessing in external tables. You can also use it to massage datafiles to fit a specific format, append or augment data from multiple sources dynamically—without creating any intermediate storage—or even send an e-mail when a specific text file is accessed by an external table. What you can do with inline preprocessing is limited only by your imagination.
1 Comment | Filed in Links, Oracle | Tags: 11g, sqlBefore External Tables existed in the Oracle database, loading from flat files was done via SQL*Loader. One option that some used was to have a compressed text file and load it with SQL*Loader via a named pipe. This allowed one not to have to extract the file, which could be several times the size of the compressed file. As of 11.1.0.7, a similar feature is now available for External Tables (and will be in 10.2.0.5). This enhancement is a result of Bug 6522622 which is mentioned in the Bugs fixed in the 11.1.0.7 Patch Set note.
What you need to know about calling functions from SQL
Tom Kyte:
When calling functions from SQL, you had better not rely on how often the functions get called, in what order, or whatever. In short, assume nothing. And remember – SQL rewrites kick in and we rewrite your SQL all of the time. Don’t rely on side effects.Comments Off | Filed in Oracle, Tips | Tags: sql
Conventional (NOAPPEND) parallel inserts available in Oracle DB 11g
Oracle 11g obviously has added the capability to perform a conventional, non-direct-path insert in parallel.
Greg Rahn:
Parallel conventional (NOAPPEND) insert was an 11g new feature, though it seems to have escaped the new features list in the docs. It was added to support cases where parallel insert as select was desired, but the restrictions associated with direct path inserts were not desired.Comments Off | Filed in Links, Oracle | Tags: 11g, sql
Join factorization, a new Oracle DB 11gR2 feature
Hong Su from Inside the Oracle Optimizer:
The Join Factorization transformation was introduced in Oracle 11g Release 2 and applies to UNION ALL queries. Join factorization is a cost-based transformation. It can factorize common computations from branches in a UNION ALL query which can lead to huge performance improvement.
Yet another reason to upgrade to 11gR2.
Comments Off | Filed in Links, Oracle | Tags: performance, sqlSQL Hints and Table Aliases
When a hint is added to a query, the Oracle optimizer uses it to choose an execution plan, unless some condition exists that prevents the optimizer from doing so. This post draws your attention to one specific condition that will definitely prevent the optimizer from using the hint.
Note that it is recommended that hints should be used sparingly, and only after you have collected statistics on the relevant tables and evaluated the optimizer plan without hints using EXPLAIN PLAN.
If you have to use hints, however, you need to pay attention to this very basic rule if the hint requires a tablespec:
You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, then use the alias rather than the table name in the hint. However, do not include the schema name with the table name within the hint, even if the schema name appears in the statement.
The following is an example that demonstrates the above rule.
eddie@DB112> SELECT banner FROM v$version;
BANNER
---------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
eddie@DB112> CREATE TABLE my_table
2 NOPARALLEL
3 AS
4 SELECT *
5 FROM all_objects
6 WHERE ROWNUM <= 10000;
Table created.
eddie@DB112> EXECUTE dbms_stats.gather_table_stats(user,'MY_TABLE');
PL/SQL procedure successfully completed.
In the following query, the PARALLEL hint instructs the optimizer to use a parallel operation. Notice that the table has no alias and the hint uses the table name.
eddie@DB112> EXPLAIN PLAN
2 FOR
3 SELECT /*+ PARALLEL (my_table) */
4 COUNT (*) FROM my_table;
Explained.
eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 2272413588
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 10000 | 23 (0)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| MY_TABLE | 10000 | 23 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
As expected, the plan shows that a parallel operation was indeed used. Now let’s add an alias to the table and keep everything else the same:
eddie@DB112> EXPLAIN PLAN
2 FOR
3 SELECT /*+ PARALLEL (my_table) */
4 COUNT (*) FROM my_table t;
Explained.
eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Plan hash value: 3996063390
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| MY_TABLE | 10000 | 42 (0)| 00:00:01 |
-----------------------------------------------------------------------
Oops! No parallel operation was performed. The hint was totally ignored. The plan is the same as if the hint was not there:
eddie@DB112> EXPLAIN PLAN
2 FOR
3 SELECT COUNT (*) FROM my_table t;
Explained.
eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Plan hash value: 3996063390
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| MY_TABLE | 10000 | 42 (0)| 00:00:01 |
-----------------------------------------------------------------------
But, when we use the table alias instead of the table name, the optimizer obeys the hint:
eddie@DB112> EXPLAIN PLAN
2 FOR
3 SELECT /*+ PARALLEL (t) */
4 COUNT (*) FROM my_table t;
Explained.
eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 2272413588
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 10000 | 23 (0)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| MY_TABLE | 10000 | 23 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Imagine for example a data warehouse process that relies on the PARALLEL hint to finish its SQL aggregation just before the data is fed to a sales report used by management every morning. A developer changes an aggregation query adding an alias to the table but forgetting to change the hint. Oops!
Yes, this is a very basic rule but could have serious consequences if forgotten.
3 Comments | Filed in Oracle, Tips | Tags: hint, sqlPlease Request a Reasonable Conversion
Here is something you need to be aware of when you use the EXECUTE IMMEDIATE … USING and OPEN FOR … USING statements in PL/SQL.
Let’s execute this simple anonymous PL/SQL block:
eddie@db11gr2> DECLARE
2 l_string VARCHAR2 (4000);
3 l_rc sys_refcursor;
4 BEGIN
5 l_string := RPAD (' ', 4000);
6 OPEN l_rc FOR 'select :1 from dual' USING l_string;
7 EXECUTE immediate 'select :1 from user_objects where rownum = 1'
8 INTO l_string USING l_string;
9 END;
10 /
PL/SQL procedure successfully completed.
The EXECUTE IMMEDIATE and OPEN FOR statements are used with the USING clause. USING supplies a bind argument for the SQL string. In this example the value of the bind argument is a 4,000 character string.
Now, let’s pass a string greater than 4,000 characters to the OPEN FOR statement:
eddie@db11gr2> DECLARE
2 l_string VARCHAR2 (4001);
3 l_rc sys_refcursor;
4 BEGIN
5 l_string := RPAD (' ', 4001);
6 OPEN l_rc FOR 'select :1 from dual' USING l_string;
7 END;
8 /
DECLARE
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 6
Oops, got an error. Let’s also try the EXECUTE IMMEDIATE statement with a string greater than 4,000 characters:
eddie@db11gr2> DECLARE
2 l_string VARCHAR2 (4001);
3 BEGIN
4 l_string := RPAD (' ', 4001);
5 EXECUTE immediate 'select :1 from user_objects where rownum = 1'
6 INTO l_string USING l_string;
7 END;
8 /
DECLARE
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 5
Got the same error: “ORA-01460: unimplemented or unreasonable conversion requested”.
How about if we pass a NULL:
eddie@db11gr2> DECLARE
2 l_rc sys_refcursor;
3 BEGIN
4 OPEN l_rc FOR 'select :1 from dual' USING NULL;
5 END;
6 /
OPEN l_rc FOR 'select :1 from dual' USING NULL;
*
ERROR at line 4:
ORA-06550: line 4, column 45:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 4, column 3:
In this case we get “PLS-00457: expressions have to be of SQL types”.
Well, there is a restriction on what values you can bind: When binding values to dynamic SQL, only SQL datatypes are supported. You can bind strings, numbers, dates, collections, LOBs, XML documents… However, you cannot bind values having a PL/SQL specific datatype such as Booleans, associative arrays and varchar2 values greater than 4000.
Make sure you keep the above restriction in mind when you use the EXECUTE IMMEDIATE … USING and OPEN FOR … USING statements.
Source and resources:
- EXECUTE IMMEDIATE Statement
- Unsupported Data Type in Native Dynamic SQL
- OPEN FOR Statement
- Discussions about the ORA-01460 error
See How Easily You Can Post to Twitter From an Oracle DB
Posting to Twitter from inside an Oracle database is not something new (OraTweet, ORA_Tweet). However, what if you could post to Twitter by simply executing a SELECT statement without using any custom PL/SQL code?
Try the following in your SQL*Plus. It will ask you for your Twitter username and password and the status message that you want to post:
eddie@db11gr2> set define !
eddie@db11gr2> set verify off
eddie@db11gr2> set sqlterminator off
eddie@db11gr2> SELECT HTTPURITYPE(
2 UriFactory.escapeUri(
3 'http://query.yahooapis.com/v1/public/yql?q=
4 USE "http://awads.net/yql/twitter.xml" as tw_t;
5 INSERT INTO tw_t (status, username,password)
6 VALUES("!status","!!username","!!password")
7 &format=json
8 &env=store://datatables.org/alltableswithkeys&format=xml&callback=cbfunc')).getclob()
9 json_response
10 FROM dual
11 /
Enter value for status: Tweeting from SQL*Plus.
Enter value for username: sqlyql
Enter value for password: sqlyqlpw
JSON_RESPONSE
--------------------------------------------------------------------------------
cbfunc({"query":{"count":"1","created":"2010-05-02T02:02:14Z","lang":"en-US","re
sults":{"status":{"created_at":"Sun May 02 02:02:14 +0000 2010","id":"1322057475
5","text":"Tweeting from SQL*Plus.","source":"<a href=\"http://apiwiki.twitter.c
om/\" rel=\"nofollow\">API</a>","truncated":"false","in_reply_to_status_id":null
,"in_reply_to_user_id":null,"favorited":"false","in_reply_to_screen_name":null,"
user":{"id":"139255376","name":"sqlyql","screen_name":"sqlyql","location":null,"
description":null,"profile_image_url":"http://s.twimg.com/a/1272578449/images/de
fault_profile_1_normal.png","url":null,"protected":"false","followers_count":"1"
,"profile_background_color":"9ae4e8","profile_text_color":"000000","profile_link
_color":"0000ff","profile_sidebar_fill_color":"e0ff92","profile_sidebar_border_c
olor":"87bc44","friends_count":"0","created_at":"Sun May 02 01:59:28 +0000 2010"
,"favourites_count":"0","utc_offset":null,"time_zone":null,"profile_background_i
mage_url":"http://s.twimg.com/a/1272578449/images/themes/theme1/bg.png","profile
_background_tile":"false","notifications":"false","geo_enabled":"false","verifie
d":"false","following":"false","statuses_count":"1","lang":"en","contributors_en
abled":"false"},"geo":null,"coordinates":null,"place":null,"contributors":null}}
}});
Elapsed: 00:00:01.92
The above is made possible using HTTPURITYPE and YQL, the Yahoo! Query Language. Awesome combination!
Note that on June 30, 2010 Twitter will be shutting off basic authentication on the Twitter API. After that date, the above will stop working unless the YQL query is modified to use OAuth.
Are you on Twitter? Follow me at eddieawad and the rest of the Oracle Tweeple at eddieawad/oracle.
2 Comments | Filed in Oracle | Tags: sql, twitter, yqlAre You Soft on Your Deletes?
Frustrated with soft deletes in SQL, Reeditor writes:
In the past, I’ve added a deleted_at (datetime) column and appended “WHERE deleted_at IS NULL” to every query involving that table. But it’s a total pain in the ass, and it’s complicated as shit: there’s always a few queries that are missed, a few developers that forgot or didn’t know they had to check that the record wasn’t deleted, denormalized counts are hard to keep in sync, etc. etc. It always turns into an embarrassing fiasco rather than something you add once and then don’t have to worry about.
Good discussion followed which included a couple of interesting links: What is the best way to implement soft deletion? and The trouble with soft delete.
So, if there is a business need to retain deleted records, do you go soft or hard?
Comments Off | Filed in Oracle | Tags: sql
