Oracle SQL Programming

This links to my blog posts and pages dealing with Oracle SQL programming. They include SQL programming solutions, concept examples, techniques, and user defined types. The blog pages show below the top-level page in the right hand column, while blog posts only show in the list below.

Creating specialized views ↓

These blog posts show you how to explore the Oracle database’s data dictionary.

Learn how you create a view of external files that are defined in an Oracle database.

Learn how you create a view of all schema tables and views that mimics a DESCRIBE for all objects.

Learn how you create a view of all user defined collection data types.

Learn how to query the data dictionary to find key and reserved words.

Hierarchical queries ↓

These blog posts show you how to work with hierarchies or trees in the Oracle database.

Learn how to use hierarchical queries in an Oracle database.

SQL data loading patterns ↓

These blog posts show you how to work SQL*Loader, Oracle Data Pump, an external tables.

Learn how you create external tables that use Oracle Data Pump.

Learn how you create external tables that use SQL*Loader.

Learn how you convert and upload XML data through external tables into Oracle.

SQL joins, subqueries, statements, and normalization ↓

These blog posts show you how to work with SQL to solve problems in a database.

Learn how you use the basic SQL joins.

Learn how a basic SQL query works.

Learn how you normalize your data model and why you do it.

SQL language functions ↓

These blog posts show you how specific functions in an Oracle database.

Learn how you use the CONCAT function, and compare it against MySQL and SQL Server.

Learn how you assign a dynamic INTERVAL YEAR TO MONTH data types.

Learn how you use the TO_YMINTERVAL function, and the leap year limits.

Learn how you use the ADD_MONTHS function with month ending dates.

Learn how you use the NUMTODSINTERVAL function.

SQL Large Objects (LOBs) ↓

These blog posts show you how to work with large objects in an Oracle database.

Learn about Oracle LOB Features.

Learn how you cleanup wasted LOB space.

Learn how you match LOBs to their respective LOB indexes.

SQL operators ↓

These blog posts show you how to work specialized SQL operators in various databases.

Learn how you use the IS OF operator for object type comparisons.

SQL techniques ↓

These blog posts show you how to work with quirks or special characteristics of databases.

Learn how you join nested tables results to other table elements.

Learn how you use a natural join, the ups, downs, and whoa that’s not what’s wanted.

Learn how you use inline views, table fabrication, and the WITH clause.

Learn how you validate foreign key column constraints.

If you have suggestions of additional topics, let me know by posting a comment.

Written by maclochlainn

November 13th, 2008 at 12:33 am

Posted in Uncategorized

6 Responses to 'Oracle SQL Programming'

Subscribe to comments with RSS or TrackBack to 'Oracle SQL Programming'.

Is there a limit on the number of rows a query may return in Oracle? is 3,000,000,000 to many?

jbain

14 May 10 at 4:37 pm

I had these errors thrown for this entry

SQL> SET sqlprompt "'SQL:'_user at _connect_identifier&gt;"
SP2-0734: unknown command beginning " SET sqlpr..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.

Brandon Hawkes

20 Sep 12 at 12:49 pm

Thanks for providing these useful topics. It makes searching topics regarding to SQL and Oracle much easier.
Thanks for that.

Oracle Datenbanken

4 Mar 13 at 4:18 am

Hi Mac,

I need suggestion for below query.
in this query the tables are very huge in production DB (Billons of records).also all columns used in where condition are indexed still its not getting tuned,i tried using using parallel hint but still cost in expaln plan was high,Need your valuable suggestion in below case.

DB VERSION :8i

SELECT  COUNT(a.due),SUM(a.due)
TO_CHAR(payment_date,'DD/MM/YYYY')
FROM tab a, tab b
WHERE  a.key_a = b.key_b
AND SYSDATE BETWEEN b.eff_sdate AND b.eff_edate
AND a.due > 0
AND  a.ind_flag IS NOT NULL
AND  b.ind_flag IS NOT NULL
AND (EXISTS (SELECT 1 FROM pay p WHERE a.acc_id = p.acc_id)
     OR 
     EXISTS (SELECT 1 FROM acc ac WHERE a.acc_id = ac.acc_id))

swapnil

15 Apr 14 at 2:21 am

Swapnil,

Let’s make two assumptions – the tables contain the quantities of rows below and the “acc_id” column values are unique and indexed in the “a” table. Then, let’s assume the join result from table “a” and “b” is 1,000,000 rows, and you have 2,000,000 rows in the “pay” table and you have 3,000,000 rows in the “acc” table. You’ll process 1 x 10⁶ rows times 2 x 10⁶ rows plus 1 x 10⁶ rows times 3 x 10⁶ rows -> or 5 x 10¹² rows. It sounds like you’re trying to do more rows than that. You should not be using correlation! You should be using something like this:
AND (a.acc_id = p.acc_id OR a.acc_id = ac.acc_id)
AND (a.acc_id = p.acc_id OR a.acc_id = ac.acc_id)
maclochlainn

15 Apr 14 at 3:26 pm
Hi Mac,

Thanks for last input ,in same context i got some weird issue regarding INDEX.

i have query say
SELECT ccol1 FROM tab_A WHERE TYPE IN ('type1','type2');
select ccol1 from tab_A where type in ('type1','type2');
i have non-unique index on type column.
so its using INDEX BY ROWID SCAN in one of the environment.

But when i moved to new environment
it had stopped using index .

then i changed query as :
SELECT ccol1 FROM tab_A WHERE TYPE IN ='type1';
select ccol1 from tab_A where type in ='type1';
its again used index by rowid scan.

so is that because of IN condition its not using any index.

I tried rebuilding the index, gathering stats but didn’t work also optimizer_mode was in CHOOSE.

DB VERSIOIN:8I

Kindle let me know your valuable comments on this.
swapnil

24 Apr 14 at 5:33 am

MacLochlainns Weblog

Oracle SQL Programming

6 Responses to 'Oracle SQL Programming'

Leave a Reply

Recent Posts

Things Written About

Pages

Blogroll

Archives