1 2 Previous Next 28 Replies Latest reply on Mar 30, 2009 4:08 AM by Yasu

    Using /*+DRIVING_SITE(XYZ)*/  Hint efficiently.

    Yasu
      Hi All,

      I need to debug the script giving error ORA-01652: unable to extend temp segment.

      Script runs in Oracle 8i database(optimizer mode as FIRST_ROWS) and script uses dblink to get the result from remote oracle 9i database (optimizer mode as CHOOSE), as shown below.

      select report_num, page_num, ascii_page_num, billing_status from doc_page@tre ---tre=93910724 rows
      minus
      select report_num, page_num, ascii_page_num, billing_status from doc_page; ---tre2=81576482 rows

      Execution plan is:
      Execution Plan
      ----------------------------------------------------------
      0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3538847 Card=170
      190840 Bytes=2771076960)

      1 0 MINUS
      2 1 SORT (UNIQUE) (Cost=2307176 Card=91098360 Bytes=18219672
      00)

      3 2 REMOTE* (Cost=1058996 Card=91098360 Bytes=1821967200) TRE
      4 1 SORT (UNIQUE) (Cost=1231671 Card=79092480 Bytes=94910976
      0)

      5 4 TABLE ACCESS (FULL) OF 'DOC_PAGE' (Cost=418913 Card=79
      092480 Bytes=949109760)
      3 SERIAL_FROM_REMOTE SELECT "REPORT_NUM","PAGE_NUM","BILLING_STAT
      US","ASCII_PAGE_NUM" FROM "DOC_PAGE"


      Now if i use /*+DRIVING_SITE(XYZ)*/   hint below is the xecution plan:

      Execution Plan
      ----------------------------------------------------------
      0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE (Cost=3304797 Car
      d=91098360 Bytes=2224486800)

      1 0 MINUS
      2 1 SORT (UNIQUE) (Cost=2073126 Card=91098360 Bytes=12753770
      40)

      3 2 PARTITION RANGE (ALL)
      4 3 TABLE ACCESS (FULL) OF 'DOC_PAGE' (Cost=1058997 Card TRE
      =91098360 Bytes=1275377040)

      5 1 SORT (UNIQUE) (Cost=1231671 Card=79092480 Bytes=94910976
      0)

      6 5 REMOTE* (Cost=418913 Card=79092480 Bytes=949109760) !


      6 SERIAL_FROM_REMOTE SELECT "REPORT_NUM","PAGE_NUM","BILLING_STAT
      US","ASCII_PAGE_NUM" FROM "DOC_PAGE"



      So now as i cannot execute and test to see whether using hint will be faster and will not give ORA-01652 error.

      Can anyone please suggest by using execution plan above and suggest whether it will be fast or not please....?????

      -Yasser
        • 1. Re: Using /*+DRIVING_SITE(XYZ)*/  Hint efficiently.
          Randolf Geist
          YasserRACDBA wrote:
          I need to debug the script giving error ORA-01652: unable to extend temp segment.

          Script runs in Oracle 8i database(optimizer mode as FIRST_ROWS) and script uses dblink to get the result from remote oracle 9i database (optimizer mode as CHOOSE), as shown below.

          select report_num, page_num, ascii_page_num, billing_status from doc_page@tre ---tre=93910724 rows
          minus
          select report_num, page_num, ascii_page_num, billing_status from doc_page; ---tre2=81576482 rows

          So now as i cannot execute and test to see whether using hint will be faster and will not give ORA-01652 error.

          Can anyone please suggest by using execution plan above and suggest whether it will be fast or not please....?????
          Yasser,

          using the driving_site hint you've effectively moved the execution of the statement to the remote 9i system. It has to perform the same amount of work however over there, so if the TEMP tablespace is not large enough you'll very likely end up with the same error message thrown on the remote site.

          You'll very likely need multiple GB of TEMP space to successfully sort this quite large set to get the unique set of non-existing rows in your local system.

          What business question do you try to answer with this query? Is there a reason why you can't use a NOT EXISTS-clause e.g. instead? It could use different access patterns that don't require such a large sort space, but I think any approach that is supposed to complete within a timely manner (e.g. HASH JOIN ANTI) will require a substantial amount of TEMP space.

          You could go for a FILTER operation that checks the condition for each row by firing a recursive SQL to the remote site; this won't require any TEMP space but probably will run forever, even if it was supported by a suitable index on the remote site.

          Regards,
          Randolf

          Oracle related stuff blog:
          http://oracle-randolf.blogspot.com/

          SQLTools++ for Oracle (Open source Oracle GUI for Windows):
          http://www.sqltools-plusplus.org:7676/
          http://sourceforge.net/projects/sqlt-pp/
          • 2. Re: Using /*+DRIVING_SITE(XYZ)*/  Hint efficiently.
            Yasu
            Thanks for replying.

            I was just trying to eliminate some how this Temp space error.

            Please suggest some alternate sql auery as you suggest using not exists or filter option to reduce the temp space usage and also execution time.

            Note: It should not run for ever as you said by using filter option....as this script is already taking hume time to execute.

            -Yasser.
            • 3. Re: Using /*+DRIVING_SITE(XYZ)*/  Hint efficiently.
              Randolf Geist
              Yasser,
              YasserRACDBA wrote:
              I was just trying to eliminate some how this Temp space error.
              If your remote system has a sufficiently large TEMP space you might already be done.
              Please suggest some alternate sql auery as you suggest using not exists or filter option to reduce the temp space usage and also execution time.

              Note: It should not run for ever as you said by using filter option....as this script is already taking hume time to execute.
              You first need to answer the question what the present statement is supposed to accomplish before you can get alternative SQL suggestions.

              E.g. if you want to use the NOT EXISTS operator to find the rows that exist at the remote site but not locally, you need to know what uniquely identifies your row, i.e. the PK information.

              As I've already said, using different approaches will very likely still require a large amount of TEMP space, so you need to make sure that you have sufficient space available in one of your databases (local/remote).

              If the information required to check for the condition is significantly smaller than the entire table, you could consider to create a temporary copy of that "skinny" information in one of the databases and then run a purely local operation, which should require less TEMP space and even might allow you to use the FILTER approach more efficiently, although it will be slower than a simple join based operation, but it will require less TEMP space.

              Regards,
              Randolf

              Oracle related stuff blog:
              http://oracle-randolf.blogspot.com/

              SQLTools++ for Oracle (Open source Oracle GUI for Windows):
              http://www.sqltools-plusplus.org:7676/
              http://sourceforge.net/projects/sqlt-pp/
              • 4. Re: Using /*+DRIVING_SITE(XYZ)*/  Hint efficiently.
                Yasu
                Both local and remote sites are having 5 GB of temp.which i think is more than enough. Hence trying to tune this query.

                I just want to accomplish rows that exist at the remote site but not locally.

                My whole script is shown below.

                drop table dp_tmp;
                create table dp_tmp as
                select report_num, page_num, ascii_page_num, billing_status from doc_page@tre
                minus
                select report_num, page_num, ascii_page_num, billing_status from doc_page;


                BEGIN
                for rec in (select report_num, page_num, ascii_page_num,
                billing_status from dp_tmp)
                LOOP
                update doc_page set
                ascii_page_num=rec.ascii_page_num,
                billing_status=rec.billing_status
                where report_num=rec.report_num
                and page_num=rec.page_num;
                commit;
                END LOOP;
                END;
                /


                Please help in tuning this query.

                -Yasser
                • 5. Re: Using /*+DRIVING_SITE(XYZ)*/  Hint efficiently.
                  Randolf Geist
                  Yasser,
                  YasserRACDBA wrote:
                  Both local and remote sites are having 5 GB of temp.which i think is more than enough. Hence trying to tune this query.
                  And why do you think it is more than enough if your statement errors out with insufficient TEMP space?

                  So is this TEMP space actually available to your query or is it already in use by other sessions? 5GB doesn't sound too much given the table sizes given above. Consider multiple sessions running queries on tables of similar size, 5GB won't be sufficient I guess.

                  You can monitor your WORKAREA requirements using V$SQL_WORKAREA, V$SQL_WORKAREA_ACTIVE and V$SQL_WORKAREA_HISTOGRAM, at least from 9i on.
                  I just want to accomplish rows that exist at the remote site but not locally.

                  My whole script is shown below.

                  drop table dp_tmp;
                  create table dp_tmp as
                  select report_num, page_num, ascii_page_num, billing_status from doc_page@tre
                  minus
                  select report_num, page_num, ascii_page_num, billing_status from doc_page;


                  BEGIN
                  for rec in (select report_num, page_num, ascii_page_num,
                  billing_status from dp_tmp)
                  LOOP
                  update doc_page set
                  ascii_page_num=rec.ascii_page_num,
                  billing_status=rec.billing_status
                  where report_num=rec.report_num
                  and page_num=rec.page_num;
                  commit;
                  END LOOP;
                  END;
                  /
                  I'm not sure if I understand the logic of your script:

                  1. The query populates a table that contains the rows that can't be found in the local database but exist in the remote database. Note that by using all these columns you can end up with rows that have the same primary key but different attributes in "ascii_page_num" and "billing_status" returned by the MINUS operation.

                  2. Your (very slow row-by-row processing) loop then updates existing records with the same primary key as found in the table. You don't insert records that do not exist in the local database, but merely update existing records that obviously have different "ascii_page_num" or "billing_status" attributes. Is this what you intend to do?

                  3. You do a COMMIT inside the loop, which is generally speaking a bad idea. Why don't you commit after the loop completes, which will be much faster? If you want to have intermediate COMMITs there are other possibilities. At least you should use PL/SQL bulk processing or SQL bulk execution.

                  How many rows do you expect in the DP_TMP table?

                  Suggestions:

                  1. Check the error message you got (ORA-01652: unable to extend temp segment) thoroughly. Using the CREATE TABLE AS SELECT (CTAS) statement you could actually have an issue in the tablespace where you attempt to create the DP_TMP table if there is insufficient space, since CTAS creates a temporary segment in the target tablespace first which is at completion of the operation turned into the actual table segment.

                  2. Why don't you use an bulk UPDATE operation to update the records found?

                  An (untested) code sample using an updatable join view:
                  create unique index dp_tmp_idx on dp_dmp (report_num, page_num) compute statistics;
                  
                  exec dbms_stats.gather_table_stats(null, 'DP_TMP', estimate_percent=>10)
                  
                  update  (
                          select 
                                   a.ascii_page_num
                                 , a.billing_status
                                 , b.ascii_page_num as ascii_page_num_new
                                 , b.billing_status as billing_status_new
                          from 
                                   doc_page a
                                 , dp_tmp b
                          where
                                   a.report_num = b.report_num
                          and      a.page_num   = b.page_num
                          )
                  set
                          ascii_page_num = ascii_page_num_new
                        , billing_status = billing_status_new
                  ;
                  3. You could even think about using a single UPDATE statement to perform both, identify the records and update in one shot, although I assume the performance won't be really that good given the remote operation.

                  Regards,
                  Randolf

                  Oracle related stuff blog:
                  http://oracle-randolf.blogspot.com/

                  SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                  http://www.sqltools-plusplus.org:7676/
                  http://sourceforge.net/projects/sqlt-pp/
                  • 6. Re: Using /*+DRIVING_SITE(XYZ)*/  Hint efficiently.
                    Yasu
                    Thanks once again.

                    I think you were confused with dp_tmp table and doc_page table.

                    Could you please rewrite the update query......its difficult for me (Weak in SQL)

                    It like updating local doc_page by comparing remote doc_page table.

                    Your (very slow row-by-row processing) loop then updates existing records with the same primary key as found in the table. You don't insert records that do not exist in the local database, but merely update existing records that obviously have different "ascii_page_num" or "billing_status" attributes. Is this what you intend to do?-- YES THIS WHAT I WANT(But updating in local doc_page table)

                    Please can you rewrite the whole script as i am totally confused with your script and my script.

                    -Yasser.

                    Edited by: YasserRACDBA on Mar 23, 2009 6:07 PM

                    Edited by: YasserRACDBA on Mar 23, 2009 6:09 PM
                    • 7. Re: Using /*+DRIVING_SITE(XYZ)*/  Hint efficiently.
                      Randolf Geist
                      Yasser,
                      YasserRACDBA wrote:
                      I think you were confused with dp_tmp table and doc_page table.
                      Why?
                      Could you please rewrite the update query......its difficult for me (Weak in SQL)

                      It like updating local doc_page by comparing remote doc_page table.

                      Please can you rewrite the whole script as i am totally confused with your script and my script.
                      May be there is a misunderstanding, so you might want to clarify again what the purpose of your exercise is:

                      1. I understand that for rows that exist in both the remote and local table according to the primary key (report_num, page_num) but have different contents in "ascii_page_num" and/or "billing_status" you want to update the local table to have the same content as the remote table.

                      2. You don't care for records that don't exist locally but exist in the remote table according to the primary key. The DP_TMP table will potentially contain records that do not exist in the local table according to the primary key, in this case nothing will happen to the local table, i.e. the non-existent records won't be added to the local table.

                      If above understanding is correct, my SQL bulk update is going to do exactly that: Update the local DOC_PAGE table according to the contents of the DP_TMP table which is supposed to contain the records from the remote table that are different according to the attributes report_num, page_num, ascii_page_num, billing_status.

                      The prerequisites for the join view to be updatable is that there is a unique index available that ensures that the join is preserving the keys, i.e. doesn't duplicate any rows from the table DOC_PAGE.

                      Furthermore I gather statistics so that the optimizer knows a bit about the data volume in the DP_TMP table. I assume that there are reasonable statistics available for the DOC_PAGE table.

                      If you have trouble understanding the SQL you might want to read up information about "updatable join views".

                      If you don't understand my proposal, simply stick to your current approach which should do the same, but probably much slower. That shouldn't matter however, if the expected number of rows in the DP_TMP table is small.

                      Regards,
                      Randolf

                      Oracle related stuff blog:
                      http://oracle-randolf.blogspot.com/

                      SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                      http://www.sqltools-plusplus.org:7676/
                      http://sourceforge.net/projects/sqlt-pp/
                      • 8. Re: Using /*+DRIVING_SITE(XYZ)*/  Hint efficiently.
                        Yasu
                        Seems like Your assumption of logic in my script is perfect. Sorry for asuming you wrong.

                        But i do have some doubts:
                        1. Appreciate for implementing "Updatable Join Views" in this script, but i did not find any performance difference in my script and yours as in both case we need
                        a. Interim table dp_tmp which is built by using Minus operator between local and remote doc_page tables.
                        b. Unique index has to be created for using "Updatable Join View" method.
                        c. Updatable Join View method may consume temp space more and may rise ora-1555 error.

                        2. Updating local doc_page table using my method (normal update and for loop) and yours "Updatble Join View" seems no difference.Could you please eloborate performance gains in "Updatable Join View" comparing to normal Update ??

                        3. There will be around 0.5 to 1 Million records in dp_tmp, hence creating unique index repeatedly on interim table dp_tmp looks script performance killer?

                        4. Can you please demonstrate on using "UPDATE statement to perform both, identify the records and update in one shot"

                        5. When to use commit in your bulk update statement when using "Updatable Join View" as you have demonstrated ?.

                        6. Can you please suggest on using parallel update in "Updatable Join View"?

                        6. Is there any alternate method to overcome MINUS operator for creating dp_tmp table which will use less sort space(Temp space) as none of the other process are consuming Temp space in the database. Might be by using NOT EXISTS or NOT IN operators?

                        So basically we have two parts in this script: 1.Building interim table dp_tmp 2.Updating Local table.
                        I think part 2 can be easily tuned using "Updatable Join View" according to you. But i think i have to research on part 1

                        Please correct me if i am wrong anywhere.

                        I am really pleased to thanks to you!!!!! You have really helped me a lot.....Once again thanks a lot for answering my questions with such a wonderful explanation.

                        -Yasser
                        • 9. Re: Using /*+DRIVING_SITE(XYZ)*/  Hint efficiently.
                          Randolf Geist
                          YasserRACDBA wrote:
                          Seems like Your assumption of logic in my script is perfect. Sorry for asuming you wrong.

                          But i do have some doubts:
                          1. Appreciate for implementing "Updatable Join Views" in this script, but i did not find any performance difference in my script and yours as in both case we need
                          a. Interim table dp_tmp which is built by using Minus operator between local and remote doc_page tables.
                          b. Unique index has to be created for using "Updatable Join View" method.
                          c. Updatable Join View method may consume temp space more and may rise ora-1555 error.

                          2. Updating local doc_page table using my method (normal update and for loop) and yours "Updatble Join View" seems no difference.Could you please eloborate performance gains in "Updatable Join View" comparing to normal Update ??

                          3. There will be around 0.5 to 1 Million records in dp_tmp, hence creating unique index repeatedly on interim table dp_tmp looks script performance killer?
                          Yasser,

                          it looks like you still haven't understood the difference between your approach and my proposal:

                          You're using a PL/SQL loop to fetch the rows from your DP_TMP table and for each row you're going to execute an UPDATE command, so for 1 million rows you're going to execute 1 million UPDATE commands.

                          My proposal is using a single UPDATE command that updates all 1 million rows with one single UPDATE command, and there is no PL/SQL loop involved. Neither the index is going to be created multiple times, it's a one-time operation.

                          You're right that the bulk UPDATE might require more TEMP space since it needs to join the two tables. It won't suffer from ORA-01555 as along as the data joined is not modified by other sessions simultaneously. Actually your solution is more likely to fail with an ORA-01555 (due to the intermediate COMMITs), but that shouldn't be the point here.
                          4. Can you please demonstrate on using "UPDATE statement to perform both, identify the records and update in one shot"
                          Just an untested code sample how this could look like:
                          update doc_page t
                          set (
                                   ascii_page_num
                                 , billing_status
                              ) = 
                              (
                          select
                                   ascii_page_num
                                 , billing_status
                          from
                                   doc_page@tre s
                          where
                                   s.report_num = t.report_num 
                          and      s.page_num = t.page_num
                              )
                          where (
                                    report_num
                                  , page_num
                                ) in 
                                (
                          select 
                                    l.report_num
                                  , l.page_num 
                          from 
                                    doc_page@tre r
                                  , doc_page l
                          where
                                   l.report_num = r.report_num 
                          and      l.page_num = r.page_num
                          and      (
                                       (l.ascii_page_num != r.ascii_page_num) 
                                    or (l.billing_status != r.billing_status)
                                    or (l.ascii_page_num is not null and r.ascii_page_num is null)
                                    or (l.ascii_page_num is null and r.ascii_page_num is not null)
                                   )
                                )
                          ;
                          In 10g you could use a MERGE statement instead using only the WHEN MATCHED branch, probably this would perform better.
                          5. When to use commit in your bulk update statement when using "Updatable Join View" as you have demonstrated ?
                          The bulk statement would process all rows, afterwards you could commit all changes or rollback.
                          6. Can you please suggest on using parallel update in "Updatable Join View"?
                          This would perform parallel query and parallel DML if supported by your release and edition. I'm not sure how 8i this would handle, in 9i it should work fine.

                          Note that parallel DML attempts to lock the whole table exclusively so if you have ongoing transactions this is not going to work, and if it works it will block all other sessions while running the update.
                          ALTER SESSION ENABLE PARALLEL DML;
                          
                          update  (
                                  select /*+ parallel(a, 2) parallel(b, 2) */ 
                                           a.ascii_page_num
                                         , a.billing_status
                                         , b.ascii_page_num as ascii_page_num_new
                                         , b.billing_status as billing_status_new
                                  from 
                                           doc_page a
                                         , dp_tmp b
                                  where
                                           a.report_num = b.report_num
                                  and      a.page_num   = b.page_num
                                  )
                          set
                                  ascii_page_num = ascii_page_num_new
                                , billing_status = billing_status_new
                          ;
                          6. Is there any alternate method to overcome MINUS operator for creating dp_tmp table which will use less sort space(Temp space) as none of the other process are consuming Temp space in the database. Might be by using NOT EXISTS or NOT IN operators?
                          See my UPDATE statement sample above how you could join the two tables instead of using MINUS.

                          Regards,
                          Randolf

                          Oracle related stuff blog:
                          http://oracle-randolf.blogspot.com/

                          SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                          http://www.sqltools-plusplus.org:7676/
                          http://sourceforge.net/projects/sqlt-pp/
                          • 10. Re: Using /*+DRIVING_SITE(XYZ)*/  Hint efficiently.
                            Yasu
                            Thanks.

                            So does sql statement provided by you without using MINUS operator will perform better in terms of sort usage and performance?

                            Or shall i seek for any other sql statement using NOT EXISTS or NOT IN or alternate sql??

                            By the way what is difference in NOT EXISTS AND NOT IN oeprator in terms of performance?

                            Also if you dont mind will you please explain me Nested Loop, Merge Join, Hash Join in execution plan and also when optimizer decides to use these mechanics?

                            -Yasser
                            • 11. Re: Using /*+DRIVING_SITE(XYZ)*/  Hint efficiently.
                              Randolf Geist
                              Yasser,
                              YasserRACDBA wrote:
                              So does sql statement provided by you without using MINUS operator will perform better in terms of sort usage and performance?
                              It's quite likely so, but ultimately it depends on the execution plan generated by Oracle.
                              Or shall i seek for any other sql statement using NOT EXISTS or NOT IN or alternate sql??
                              You can definitely give the different alternatives a try, why not.
                              By the way what is difference in NOT EXISTS AND NOT IN oeprator in terms of performance?
                              In recent releases of Oracle there is no difference in terms of performance if certain conditions are met. Oracle always transforms the NOT IN into a NOT EXISTS internally, but further transformations (NOT EXISTS into ANTI JOIN operation) are not always possible/performed.

                              The most important point to consider is that there are functional differences between the two operators:

                              1. NOT IN (subquery) is uncorrelated whereas NOT EXISTS is correlated, although you can always transform the uncorrelated into the correlated form

                              2. NOT IN handles NULL values differently. The equivalent != ALL(subquery) operator makes this obvious: If any of the values of the subquery or the list of values is NULL then the result is always FALSE, because NULL can't be compared to any other value. So the following will always return 0 rows: select ... from tab where col != ALL('A', 'B', NULL) (this is equivalent to ...col NOT IN ('A', 'B', NULL)) whereas this will return rows that satisfy the condition: select ... from tab where col != ALL('A', 'B')
                              Also if you dont mind will you please explain me Nested Loop, Merge Join, Hash Join in execution plan and also when optimizer decides to use these mechanics?
                              Please refer to the documentation: http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/optimops.htm#i51523

                              It's a good starting point for further reading.

                              Regards,
                              Randolf

                              Oracle related stuff blog:
                              http://oracle-randolf.blogspot.com/

                              SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                              http://www.sqltools-plusplus.org:7676/
                              http://sourceforge.net/projects/sqlt-pp/
                              • 12. Re: Using /*+DRIVING_SITE(XYZ)*/  Hint efficiently.
                                Yasu
                                My last request ..

                                Could you please reconstruct alternatives by using NOT IN or NOT EXISTS or any other.

                                Thanks

                                -Yasser
                                • 13. Re: Using /*+DRIVING_SITE(XYZ)*/  Hint efficiently.
                                  Randolf Geist
                                  YasserRACDBA wrote:
                                  My last request ..

                                  Could you please reconstruct alternatives by using NOT IN or NOT EXISTS or any other.
                                  Yasser,

                                  since you're the one that is supposed to do the job, what have you tried so far? It might be a good opportunity to improve your SQL skills.

                                  You've seen my above proposal using the join. Since you're actually looking for rows that exist in both remote and local copies of the table but that have different attributes I would use the join approach, or EXISTS.

                                  Note that the IS NULL / IS NOT NULL predicates are only necessary if these attributes can be NULL. If they are mandatory the unequal comparison is sufficient.
                                  -- Join
                                  select 
                                            r.report_num
                                          , r.page_num
                                          , r.ascii_page_num
                                          , r.billing_status
                                  from 
                                            doc_page@tre r
                                          , doc_page l
                                  where
                                           l.report_num = r.report_num 
                                  and      l.page_num   = r.page_num
                                  and      (
                                               (l.ascii_page_num != r.ascii_page_num) 
                                            or (l.billing_status != r.billing_status)
                                            or (l.ascii_page_num is not null and r.ascii_page_num is null)
                                            or (l.ascii_page_num is null and r.ascii_page_num is not null)
                                           )
                                  ;
                                  
                                  -- EXISTS
                                  select 
                                            r.report_num
                                          , r.page_num
                                          , r.ascii_page_num
                                          , r.billing_status
                                  from 
                                            doc_page@tre r
                                  where
                                           exists 
                                           (
                                           select
                                                    null
                                           from 
                                                    doc_page l
                                           where
                                                    l.report_num = r.report_num 
                                           and      l.page_num   = r.page_num
                                           and      (
                                                        (l.ascii_page_num != r.ascii_page_num) 
                                                     or (l.billing_status != r.billing_status)
                                                     or (l.ascii_page_num is not null and r.ascii_page_num is null)
                                                     or (l.ascii_page_num is null and r.ascii_page_num is not null)
                                                    )
                                           )
                                  ;
                                  Regards,
                                  Randolf

                                  Oracle related stuff blog:
                                  http://oracle-randolf.blogspot.com/

                                  SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                                  http://www.sqltools-plusplus.org:7676/
                                  http://sourceforge.net/projects/sqlt-pp/
                                  • 14. Re: Using /*+DRIVING_SITE(XYZ)*/  Hint efficiently.
                                    Yasu
                                    Thanks a lot Randolf Sir !!!!!

                                    Would dream about having technical skills like you in future....also you have motivated me towards hardwork as you have been replying my quries from past two days.

                                    Might be thats why Expert DBA's are really having genious technical knowledge becuase of there dedication,hardwork,interest.....so on


                                    -Yasser
                                    1 2 Previous Next