Home » SQL & PL/SQL » SQL & PL/SQL » Regular expression to find commits in all Oracle source (Oracle 10g)
Regular expression to find commits in all Oracle source [message #598386] Mon, 14 October 2013 09:00 Go to next message
Buchas
Messages: 101
Registered: March 2006
Senior Member
Hello,

I would like to write a select that would return all places in DB that are commiting transaction.
E.g. package for testing:

CREATE OR REPLACE PACKAGE test.TEST_COMMIT  AS

PROCEDURE THE_ONLY_COMMIT_IN_DB ;

END TEST_COMMIT;--not a match


CREATE OR REPLACE PACKAGE BODY test.TEST_COMMIT  AS

PROCEDURE THE_ONLY_COMMIT_IN_DB IS
  BEGIN
    COMMIT; --ok
COMMIT; -- ok
commit  ; --ok spaces between t and ;
        commit


; -- ok comma on a different line
    END;

END TEST_COMMIT; --not a match


The select should return 4 rows with --ok.

My best try so far (I am new in regexp):

SELECT * 
FROM ALL_SOURCE ASO 
WHERE 
REGEXP_LIKE(ASO.TEXT,'commit(\s*);','i' )
AND name = 'TEST_COMMIT'


Help please.
Re: Regular expression to find commits in all Oracle source [message #598393 is a reply to message #598386] Mon, 14 October 2013 09:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
from ALL_SOURCE you can get 3 of them but not the one with ';' on another line as it is not on the same row:
SQL> SELECT line, text
  2  FROM ALL_SOURCE ASO
  3  WHERE REGEXP_LIKE(ASO.TEXT,'(^|\s)commit(\s*);','i')
  4  AND name = 'TEST_COMMIT';
      LINE TEXT
---------- --------------------------------------------------------------------------------
         5     COMMIT; --ok
         6 COMMIT; -- ok
         7 commit  ; --ok spaces between t and ;

[Updated on: Tue, 15 October 2013 00:08]

Report message to a moderator

Re: Regular expression to find commits in all Oracle source [message #598440 is a reply to message #598386] Mon, 14 October 2013 19:26 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Looking for word commit will not find all the places where code commits, just the explicit ones. Code can have dynamic SQL that executes DDL which will commit implicitly. Or code can be wrapped. But even looking for explicit commits in not wrapped code isn't that simple. Did you open SQL Language Reference manual to check commit syntax:

COMMIT [ WORK ]
  [ [ COMMENT string ]
    | [ WRITE [ WAIT | NOWAIT ] [ IMMEDIATE | BATCH ]
    ]
  | FORCE { string [, integer ]
          | CORRUPT_XID string
          | CORRUPT_XID_ALL
          }
  ] ;


As you can see, there can be quite a few clauses between commit and semi-colon and this means word commit and semi-colon can be on different lines. But someone might write code where commit and semi-colon are on different lines even without using the above clauses. Like this:

SQL> create or replace
  2    procedure p1
  3      is
  4      begin
  5          commit
  6                ;
  7  end;
  8  /

Procedure created.

SQL> SELECT  line,
  2          text
  3    FROM  USER_SOURCE
  4    WHERE REGEXP_LIKE(text,'(^|\s)commit(\s*);','i')
  5      AND name = 'P1'
  6  /

no rows selected

SQL>


You might want to use DBMS_METADATA to account on word commit and semi-colon being on different lines:

SQL> with t as (
  2             SELECT  object_name,
  3                     dbms_metadata.get_ddl('PROCEDURE',object_name) text
  4               FROM  USER_OBJECTS
  5               WHERE object_name = 'P1'
  6            )
  7  SELECT  object_name
  8    FROM  t
  9    WHERE REGEXP_LIKE(text,'(^|\s)commit(\s*);','i')
 10  /

OBJECT_NAME
----------------------------------------------------------------------------
P1

SQL>


You will also need to adjust REGEXP pattern to account for all additional clauses that can be used in commit.

SY.
Re: Regular expression to find commits in all Oracle source [message #598464 is a reply to message #598440] Tue, 15 October 2013 03:19 Go to previous messageGo to next message
Buchas
Messages: 101
Registered: March 2006
Senior Member
Thank you for the valuable insights. However I have run into strange problem - REGEXP_LIKE throws ora-31603 exception.
I have modified the Solomon Yakobson's suggestion into the following:

with t as (
            SELECT  OWNER,object_name,OBJECT_TYPE
               FROM  ALL_OBJECTS
               WHERE OBJECT_TYPE IN ( 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'JOB','TRIGGER','TYPE BODY')
               AND OWNER IN ('DWPIPE' )
            )
  SELECT  OWNER,object_name,OBJECT_TYPE, dbms_metadata.get_ddl(REPLACE(OBJECT_TYPE,' ','_'),object_name,OWNER) as source
    FROM  t
    WHERE REGEXP_LIKE(dbms_metadata.get_ddl(REPLACE(OBJECT_TYPE,' ','_'),object_name,OWNER),'(^|\s)commit(\s*);','i') --commenting this line gives no error


It gives an error: ora-31603: object "I_SNAP$ADJUST_FACT_MV" of type INDEX not found in schema "DWPIPE".
The strange thing is that if I comment the condition "WHERE REGEXP_LIKE(dbms_metadata.get_ddl(REPLACE(OBJECT_TYPE,' ','_'),object_name,OWNER),'(^|\s)commit(\s*);','i')", no error is given, one row is retrieved and I can see the source code of a package body in a column "source".
I tried googling and granted SELECT_CATALOG_ROLE to the user I connect with as was suggested in
http://sshailesh.wordpress.com/2010/05/08/dbms_metadata-get_ddl-avoid-ora-31603-error-by-using-select_catalog_role/
but it didn't help.

Another strange thing is that Oracle goes crazy about INDEX type, though those types are filtered out.

Any ideas?
Re: Regular expression to find commits in all Oracle source [message #598473 is a reply to message #598464] Tue, 15 October 2013 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Add the hint "/*+ materialize */" in the WITH clause and/or "/*+ NO_MERGE(t) */" in outer SELECT.

Re: Regular expression to find commits in all Oracle source [message #598479 is a reply to message #598473] Tue, 15 October 2013 04:54 Go to previous messageGo to next message
Buchas
Messages: 101
Registered: March 2006
Senior Member
Adding the hint "/*+ materialize */" was enough, it solves the problem, thanks!
But it only adds more mystery to this situation. Smile

[Updated on: Tue, 15 October 2013 04:57]

Report message to a moderator

icon3.gif  Re: Regular expression to find commits in all Oracle source [message #598487 is a reply to message #598479] Tue, 15 October 2013 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The explanation is simple: Oracle optimizer merged the subquery into your outer query and so the condition "REGEXP_LIKE..." may encounter objects of other types that those selected because the condition on the type is checked AFTER the REGEXP_LIKE condition.
With "materialize" hint you force Oracle to FIRST select the objects from the wanted types to build a memory table and after that execute the outer query with REGEXP_LIKE.

Re: Regular expression to find commits in all Oracle source [message #598488 is a reply to message #598479] Tue, 15 October 2013 05:32 Go to previous messageGo to next message
Buchas
Messages: 101
Registered: March 2006
Senior Member
Both queries
SELECT * FROM ALL_OBJECTS AO WHERE AO.OBJECT_NAME = 'I_SNAP$ADJUST_FACT_MV'
SELECT * FROM ALL_INDEXES AI WHERE AI.INDEX_NAME = 'I_SNAP$ADJUST_FACT_MV'


give 0 rows. I think it means that there is no such index, so no, ADJUST_FACT_MV is not using it.
Re: Regular expression to find commits in all Oracle source [message #598490 is a reply to message #598479] Tue, 15 October 2013 05:37 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Lalit Kumar B wrote on Tue, 15 October 2013 06:13
I am not sure how did the MATERIALIZE hint worked for you. It seems to be an error when dbms_metadata.get_ddl could not get details about object "I_SNAP$ADJUST_FACT_MV" of type INDEX in schema "DWPIPE".

Check the status of the index I_SNAP$ADJUST_FACT_MV. Is it really being used by the MV?

And how safe is it to use undocumented hint?


I agree on undocumented part, but the rest of it is quite obvious. Look at my code. You will notice DBMS_METADATA in with clause and in any case not in WHERE clause. WHERE clause is a culprit. Optimizer decides to merge with clause as a result we have:

WHERE OBJECT_TYPE IN ( 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'JOB','TRIGGER','TYPE BODY')
  AND OWNER IN ('DWPIPE' )
  AND REGEXP_LIKE(dbms_metadata.get_ddl(REPLACE(OBJECT_TYPE,' ','_'),object_name,OWNER),'(^|\s)commit(\s*);','i')


So now we are at the mercy of in what order optimizer will decide to apply predicates. If it applies DBMS_METADATA before OBJECT_TYPE IN ( 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'JOB','TRIGGER','TYPE BODY') we will end up with very bad performance at best waisting all that time or running not so fast DBMS_METADATA for objects we don't need. But more likely (and that's what OP experienced) we will end up running dbms_metadata against some dbms_metadata in some other schema like SYS and will obviously get:

SQL> select dbms_metadata.get_ddl('INDEX','I_SNAP$ADJUST_FACT_MV','SYS') from dual
  2  /
ERROR:
ORA-31603: object "I_SNAP$ADJUST_FACT_MV" of type INDEX not found in schema
"SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1



no rows selected

SQL>


SY.
Re: Regular expression to find commits in all Oracle source [message #598494 is a reply to message #598487] Tue, 15 October 2013 05:49 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Tue, 15 October 2013 15:57

With "materialize" hint you force Oracle to FIRST select the objects from the wanted types to build a memory table


Yes, I am aware of how it works. The memory table you mentioned is a GTT where the subquery result is resolved. What I am wondering is, why to hint the optimizer, it may decide to whether or not to materialize WITH tables regardless of the hint. We can overcome OP's error by handling the objects that throws the error.

I am absolutely open for correction on this.

[update : Saw SY's post above after I posted my comments]

[Updated on: Tue, 15 October 2013 05:53]

Report message to a moderator

Re: Regular expression to find commits in all Oracle source [message #598504 is a reply to message #598494] Tue, 15 October 2013 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
What I am wondering is, why to hint the optimizer, it may decide to whether or not to materialize WITH tables regardless of the hint.


The problem is that in this case it is the optimizer that creates the problem.
There is NO error in objects.
In addition, the intermediate table is not a GTT but a memory table.

See my previous post.
This is a rare case you have to hint the optimizer.
If you don't like the hint you can just add the condition "ROWNUM >= 1" which will force the optimizer to materialized the query.



Re: Regular expression to find commits in all Oracle source [message #598507 is a reply to message #598504] Tue, 15 October 2013 07:30 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Tue, 15 October 2013 17:45

In addition, the intermediate table is not a GTT but a memory table.


Since this is undocumented, I had to experiment it on my own to understand step-by-step some time ago. I followed Tim Hall's demo. In this link Tim says, If the contents of the WITH clause is sufficiently complex, Oracle may decide to resolve the result of the subquery into a global temporary table. I have followed him a lot, hope I understood his explanation properly.
Re: Regular expression to find commits in all Oracle source [message #598592 is a reply to message #598507] Tue, 15 October 2013 17:00 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Hi Michel,

Since I quoted Dr. Tim Hall's link about the materialize optimizer hint. And that we differ about the temporary table being a GTT or a memory table, I posted a question to Tim, and here is what he explained.

I had a conversation with him, and this is what he finally quoted " This is an undocumented feature. I've given you an example that uses a global temporary table, but perhaps there are other circumstances that don't."

As I stated that, "I believe that GTT are not supposed to be created and dropped on the fly, rather once created, it resides in the DB. A memory table, however, is used to hold the sub-result set while the query is under execution." based on his test case he quoted, "Yes. Like any DDL operation, you should not create and drop tables (including GTTs) on the fly, but this is an internal action of the optimizer, so however they do it, I can only assume it is efficient."

Here is the forum thread from Oracle-Base which I posted, MATERIALIZE optimizer hint in WITH Clause:Subquery Factoring

And, Dr. Hall took out from his precious time to update the same in his blog too, MATERIALIZE Hint

Since it is an undocumented feature, it's a privilege to have an explanation along with a test case about the subject. Many thanks to Dr. Tim Hall.

Regards,
Lalit

[Updated on: Tue, 15 October 2013 17:25]

Report message to a moderator

icon2.gif  Re: Regular expression to find commits in all Oracle source [message #598606 is a reply to message #598592] Wed, 16 October 2013 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I get not exactly the same thing with:
SQL> EXEC DBMS_MONITOR.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> 
SQL> WITH query1 AS (
  2    SELECT /*+ MATERIALIZE */ * FROM t
  3  )
  4  SELECT * FROM query1;
C
-
Y


2 rows selected.

SQL> EXEC DBMS_MONITOR.session_trace_disable;

PL/SQL procedure successfully completed.

Abstract of the trace file:
...
PARSING IN CURSOR #5 len=171 dep=1 uid=0 oct=1 lid=0 tim=340880475664 hv=2308051955 ad='6b50499c'
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_20844BC" ("C0" VARCHAR2(1 CHAR) ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254950912 ) NOPARALLEL
END OF STMT
PARSE #5:c=0,e=13625,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=340880475654
....

Note the "IN_MEMORY_METADATA" and storage clause.

So both are correct there is GTT but not a stored one.

icon2.gif  Re: Regular expression to find commits in all Oracle source [message #598607 is a reply to message #598606] Wed, 16 October 2013 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So maybe the good old way with ROWNUM is better, here are the explain plan I get for both:
WITH query1 AS (
  SELECT /*+ MATERIALIZE */ * FROM t
)
SELECT * FROM query1;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 853372184

---------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                            |     2 |     4 |     5   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                            |       |       |            |          |
|   2 |   LOAD AS SELECT           |                            |       |       |            |          |
|   3 |    TABLE ACCESS FULL       | T                          |     2 |     4 |     3   (0)| 00:00:01 |
|   4 |   VIEW                     |                            |     2 |     4 |     2   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6601_20844BC |     2 |     4 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

WITH query1 AS ( SELECT * FROM t where rownum >= 1 )
SELECT * FROM query1;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 518606641

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     2 |     4 |     3   (0)| 00:00:01 |
|   1 |  VIEW                |      |     2 |     4 |     3   (0)| 00:00:01 |
|   2 |   COUNT              |      |       |       |            |          |
|*  3 |    FILTER            |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T    |     2 |     4 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(ROWNUM>=1)

Note
-----
   - dynamic sampling used for this statement

Re: Regular expression to find commits in all Oracle source [message #598609 is a reply to message #598607] Wed, 16 October 2013 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With a bigger table (which may seem irrelevant to materialize but to workaround an optimizer bug) as follow:
SQL> create table t as select owner, object_name, object_type from dba_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.


SQL> EXEC DBMS_MONITOR.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly statistics
SQL> WITH query1 AS (
  2    SELECT /*+ MATERIALIZE */ * FROM t
  3  )
  4  SELECT * FROM query1;

49825 rows selected.


Statistics
----------------------------------------------------------
          4  recursive calls
        311  db block gets
       1106  consistent gets
        300  physical reads
        744  redo size
    1613589  bytes sent via SQL*Net to client
       5874  bytes received via SQL*Net from client
        500  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49825  rows processed

SQL> WITH query1 AS ( SELECT * FROM t where rownum >= 1 )
  2  SELECT * FROM query1;

49825 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        803  consistent gets
          0  physical reads
          0  redo size
    1613589  bytes sent via SQL*Net to client
       5874  bytes received via SQL*Net from client
        500  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49825  rows processed

SQL> set autotrace off
SQL> EXEC DBMS_MONITOR.session_trace_disable;

PL/SQL procedure successfully completed.

I get for the first query (no CREATE GTT in the trace file):
STAT #7 id=1 cnt=49825 pid=0 pos=1 obj=0 op='TEMP TABLE TRANSFORMATION  (cr=1106 pr=300 pw=300 time=1091948 us)'
STAT #7 id=2 cnt=1 pid=1 pos=1 obj=0 op='LOAD AS SELECT  (cr=305 pr=0 pw=300 time=242754 us)'
STAT #7 id=3 cnt=49825 pid=2 pos=1 obj=120026 op='TABLE ACCESS FULL T (cr=305 pr=0 pw=0 time=299076 us)'
STAT #7 id=4 cnt=49825 pid=1 pos=2 obj=0 op='VIEW  (cr=801 pr=300 pw=0 time=550013 us)'
STAT #7 id=5 cnt=49825 pid=4 pos=1 obj=-40016382 op='TABLE ACCESS FULL SYS_TEMP_0FD9D6602_20844BC (cr=801 pr=300 pw=0 time=201216 us)'

and for the second one:
STAT #5 id=1 cnt=49825 pid=0 pos=1 obj=0 op='VIEW  (cr=803 pr=0 pw=0 time=1146063 us)'
STAT #5 id=2 cnt=49825 pid=1 pos=1 obj=0 op='COUNT  (cr=803 pr=0 pw=0 time=797276 us)'
STAT #5 id=3 cnt=49825 pid=2 pos=1 obj=0 op='FILTER  (cr=803 pr=0 pw=0 time=498315 us)'
STAT #5 id=4 cnt=49825 pid=3 pos=1 obj=120026 op='TABLE ACCESS FULL T (cr=803 pr=0 pw=0 time=199352 us)'

or with EXPLAIN PLAN:
---------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                            | 49825 |  4573K|   130   (1)| 00:00:02 |
|   1 |  TEMP TABLE TRANSFORMATION |                            |       |       |            |          |
|   2 |   LOAD AS SELECT           |                            |       |       |            |          |
|   3 |    TABLE ACCESS FULL       | T                          | 49825 |  1848K|    72   (2)| 00:00:01 |
|   4 |   VIEW                     |                            | 49825 |  4573K|    59   (2)| 00:00:01 |
|   5 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6603_20844BC | 49825 |  1848K|    59   (2)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 49825 |  4573K|    72   (2)| 00:00:01 |
|   1 |  VIEW                |      | 49825 |  4573K|    72   (2)| 00:00:01 |
|   2 |   COUNT              |      |       |       |            |          |
|*  3 |    FILTER            |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T    | 49825 |  1848K|    72   (2)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(ROWNUM>=1)

icon2.gif  Re: Regular expression to find commits in all Oracle source [message #598611 is a reply to message #598609] Wed, 16 October 2013 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This was in 10.2.0.4, in 11.2.0.1 I get:
...
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6613_2154AA6" ("C0" VARCHAR2(30),"C1" VARCHAR2(128),"C2" VARCHAR2(19 CHAR) ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254950931 ) NOPARALLEL
...
STAT #7 id=1 cnt=72287 pid=0 pos=1 obj=0 op='TEMP TABLE TRANSFORMATION  (cr=1605 pr=441 pw=440 time=788609 us)'
STAT #7 id=2 cnt=0 pid=1 pos=1 obj=0 op='LOAD AS SELECT  (cr=444 pr=0 pw=440 time=0 us)'
STAT #7 id=3 cnt=72287 pid=2 pos=1 obj=107317 op='TABLE ACCESS FULL T (cr=444 pr=0 pw=0 time=162355 us cost=102 size=2819193 card=72287)'
STAT #7 id=4 cnt=72287 pid=1 pos=2 obj=0 op='VIEW  (cr=1161 pr=441 pw=0 time=484213 us cost=86 size=6794978 card=72287)'
STAT #7 id=5 cnt=72287 pid=4 pos=1 obj=-40016365 op='TABLE ACCESS FULL SYS_TEMP_0FD9D6613_2154AA6 (cr=1161 pr=441 pw=0 time=150595 us cost=86 size=2819193 card=72287)'
WAIT #7: nam='SQL*Net message from client' ela= 703 driver id=1111838976 #bytes=1 p3=0 obj#=-40016365 tim=343597861365

STAT #18 id=1 cnt=72287 pid=0 pos=1 obj=0 op='VIEW  (cr=1164 pr=0 pw=0 time=1068525 us cost=102 size=6794978 card=72287)'
STAT #18 id=2 cnt=72287 pid=1 pos=1 obj=0 op='COUNT  (cr=1164 pr=0 pw=0 time=770409 us)'
STAT #18 id=3 cnt=72287 pid=2 pos=1 obj=0 op='FILTER  (cr=1164 pr=0 pw=0 time=475370 us)'
STAT #18 id=4 cnt=72287 pid=3 pos=1 obj=107317 op='TABLE ACCESS FULL T (cr=1164 pr=0 pw=0 time=168624 us cost=102 size=2819193 card=72287)'
WAIT #18: nam='SQL*Net message from client' ela= 756 driver id=1111838976 #bytes=1 p3=0 obj#=-40016365 tim=343598351635

With EXPLAIN PLAN:
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
Plan hash value: 4042732524

---------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                            | 72287 |  6635K|   187   (1)| 00:00:03 |
|   1 |  TEMP TABLE TRANSFORMATION |                            |       |       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6614_2154AA6 |       |       |            |          |
|   3 |    TABLE ACCESS FULL       | T                          | 72287 |  2753K|   102   (1)| 00:00:02 |
|   4 |   VIEW                     |                            | 72287 |  6635K|    86   (2)| 00:00:02 |
|   5 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6614_2154AA6 | 72287 |  2753K|    86   (2)| 00:00:02 |
---------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 518606641

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 72287 |  6635K|   102   (1)| 00:00:02 |
|   1 |  VIEW                |      | 72287 |  6635K|   102   (1)| 00:00:02 |
|   2 |   COUNT              |      |       |       |            |          |
|*  3 |    FILTER            |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T    | 72287 |  2753K|   102   (1)| 00:00:02 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(ROWNUM>=1)


So my conclusion is to use the old "ROWNUM >= 1". Wink

icon14.gif  Re: Regular expression to find commits in all Oracle source [message #598614 is a reply to message #598611] Wed, 16 October 2013 03:14 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
That's a solid explanation with test results.

Thanks Michel Smile
Previous Topic: Nested table or Object table
Next Topic: please improve my script which sends *.csv [with SQL*Plus, SQL, KSH] to customer
Goto Forum:
  


Current Time: Thu Mar 28 09:30:53 CDT 2024