MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle SQL Programming

with 6 comments

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.

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'.

  1. 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

  2. I had these errors thrown for this entry

    SQL> SET sqlprompt "'SQL:'_user at _connect_identifier>"
    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

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

  4. 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

  5. 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 106 rows times 2 x 106 rows plus 1 x 106 rows times 3 x 106 rows -> or 5 x 1012 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)

    maclochlainn

    15 Apr 14 at 3:26 pm

  6. 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');

    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';

    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

Leave a Reply