Home » SQL & PL/SQL » SQL & PL/SQL » Which query would execute quickest
Which query would execute quickest [message #446633] Tue, 09 March 2010 13:53 Go to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Im trying to find out the most efficient way to update a table. I have a table that has an 5 columns (say colA,colB,colC,colD,colE);

There is an index on colA and colB. Which of the following queries would you say will run quicker if run on a table with 20 million rows.

1.
update tableA
set colE='Y'
where colA='Test'
and colB='Test'


update tableA
set colE='Y'
where colA='Test'
and colB='Test'
and rowid=12jk23j232kj3l12kj3


update tableA
set colE='Y'
where rowid=12jk23j232kj3l12kj3


And also, if i know the rowid, will it help if i include the indexed columns in teh "where" clause?

Thanks
Re: Which query would execute quickest [message #446636 is a reply to message #446633] Tue, 09 March 2010 14:03 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
ALTER SESSION SET SQL_TRACE=TRUE;
-- run all SQL code here
ALTER SESSION SET SQL_TRACE=FALSE;

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>

post the contents of trace_results.txt back here

Please be aware that data caching will impact results
Re: Which query would execute quickest [message #446637 is a reply to message #446633] Tue, 09 March 2010 14:33 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Here are the results of the trace file. (I hope its correct)
Would love to know what all this means. And also, the query that i run is only shown once. i set autotrace to true then i run all 3 queries then i set it to false.

Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
  NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
  NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0)
FROM
 (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("TABLEA") FULL("TABLEA")
  NO_PARALLEL_INDEX("TABLEA") */ 1 AS C1, CASE WHEN "TABLEA"."COLA"=
  TO_NUMBER('A') AND "TABLEA"."COLB"='B' THEN 1 ELSE 0 END AS C2 FROM
  "CCS21DO"."TABLEA" SAMPLE BLOCK (12.701613 , 1) SEED (1) "TABLEA")
  SAMPLESUB


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          4          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0          4          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT AGGREGATE (cr=0 pr=0 pw=0 time=32 us)
      1   TABLE ACCESS SAMPLE TABLEA (cr=4 pr=0 pw=0 time=283 us)

********************************************************************************

update tableA
set colE='Test'
where colA='A'
and colB='B'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          5          0           0
Execute      1      0.03       0.02          0        500          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.05       0.05          0        505          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  TABLEA (cr=500 pr=0 pw=0 time=23725 us)
      0   TABLE ACCESS FULL TABLEA (cr=500 pr=0 pw=0 time=23710 us)

********************************************************************************

ALTER SESSION SET SQL_TRACE=FALSE


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Parsing user id: SYS



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.02       0.02          0          5          0           0
Execute      2      0.03       0.02          0        500          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.05       0.05          0        505          0           0

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          4          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0          4          0           0

Misses in library cache during parse: 1

    2  user  SQL statements in session.
    1  internal SQL statements in session.
    3  SQL statements in session.
    0  statements EXPLAINed in this session.
********************************************************************************
Trace file: revie_ora_28424.trc
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
       2  user  SQL statements in trace file.
       1  internal SQL statements in trace file.
       3  SQL statements in trace file.
       3  unique SQL statements in trace file.
      43  lines in trace file.
      13  elapsed seconds in trace file.

Re: Which query would execute quickest [message #446641 is a reply to message #446637] Tue, 09 March 2010 15:10 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I only see see one query in your trace file.

It's probably because the ones with the rowid are invalid.
Re: Which query would execute quickest [message #446642 is a reply to message #446633] Tue, 09 March 2010 15:10 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
I think i made a mistake. Here is what i did

ALTER SESSION SET SQL_TRACE=TRUE;

Run query1
Run query2
Run query3

ALTER SESSION SET SQL_TRACE=FASLE;

I think he meant i should run each query then set sql trace to false then do the same for the next query. Im doing that now and will post it soon.

Thanks
Re: Which query would execute quickest [message #446645 is a reply to message #446642] Tue, 09 March 2010 15:52 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
How do you account and report the time & resources it takes to obtain the ROWIDs in the first place?
Re: Which query would execute quickest [message #446647 is a reply to message #446633] Tue, 09 March 2010 15:57 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Realistically 2 and 3 should perform the same and they should both be faster than 1 (though if you have a unique index on cola and colb you'll struggle to spot the difference).

However to do 2 and 3 you need to do a select to get the rowid in the first place. So you can only really compare them to 1 when you factor that extra step in. Once you've done that I would expect 1 to be faster.
Re: Which query would execute quickest [message #446649 is a reply to message #446633] Tue, 09 March 2010 16:13 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Ok Here is the output for all 3 queries. The table has about 100,000 rows. Maybe i should test it on a bigger row?

Im trying to understand what all this means as ive never used tkprof before.

TKPROF: Release 10.2.0.3.0 - Production on Tue Mar 9 22:09:31 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Trace file: reut_ora_26950.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

update reut.tableA
set colE='Test'
where colB='B25'
and colC='B25'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.02       0.02          0        500          2           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.03       0.02          0        500          2           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  TABLEA (cr=500 pr=0 pw=0 time=23343 us)
      1   TABLE ACCESS FULL TABLEA (cr=500 pr=0 pw=0 time=22847 us)

********************************************************************************

commit


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          1           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          1           0

Misses in library cache during parse: 0
Parsing user id: SYS
********************************************************************************

ALTER SESSION SET SQL_TRACE=FALSE


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Parsing user id: SYS
********************************************************************************

update reut.tableA
set colE='Test'
where colB='B25'
and colC='B25'
and rowid='AAAGolAAEAAAUN9ACo'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          1          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          1          3           1

Misses in library cache during parse: 1
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  UPDATE  TABLEA (cr=1 pr=0 pw=0 time=280 us)
      1   TABLE ACCESS BY USER ROWID TABLEA (cr=1 pr=0 pw=0 time=61 us)

********************************************************************************

update reut.tableA
set colE='Test'
where rowid='AAAGolAAEAAAUN9ACo'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.00          0          0          0           0
Execute      2      0.00       0.00          0          1          2           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.00          0          1          2           1

Misses in library cache during parse: 1
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  UPDATE  TABLEA (cr=1 pr=0 pw=0 time=247 us)
      1   TABLE ACCESS BY USER ROWID TABLEA (cr=1 pr=0 pw=0 time=53 us)




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        8      0.02       0.01          0          0          0           0
Execute      8      0.02       0.02          0        502          8           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.04       0.03          0        502          8           3

Misses in library cache during parse: 3


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    5  user  SQL statements in session.
    0  internal SQL statements in session.
    5  SQL statements in session.
    0  statements EXPLAINed in this session.
********************************************************************************
Trace file: ccs21dev_ora_26950.trc
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
       5  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       5  SQL statements in trace file.
       5  unique SQL statements in trace file.
      70  lines in trace file.
       0  elapsed seconds in trace file.
Re: Which query would execute quickest [message #446651 is a reply to message #446647] Tue, 09 March 2010 16:14 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
cookiemonster wrote on Tue, 09 March 2010 15:57

However to do 2 and 3 you need to do a select to get the rowid in the first place. So you can only really compare them to 1 when you factor that extra step in. Once you've done that I would expect 1 to be faster.


The query itself is in a cursor so i will just be updating the cursor for when it does the select.

Thanks
Re: Which query would execute quickest [message #446652 is a reply to message #446651] Tue, 09 March 2010 16:20 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>The query itself is in a cursor so i will just be updating the cursor for when it does the select.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/sqlplsql.htm#sthref3439

"A cursor is a handle or name for a private SQL area--an area in memory in which a parsed statement and other information for processing the statement are kept."
Re: Which query would execute quickest [message #446654 is a reply to message #446633] Tue, 09 March 2010 16:28 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
What i meant was the above are just example for what i want to do. The query will be in a cursor which is then used in a loop. I want to update the query in the cursor shown below to include the rowid in teh select statement, then use that in the update statement shown towards the end of the loop

cursor table_data
select colA, colB, colC
from tableA
 
Fetch table_data_cur into rec_table_data;
 
for rec_table_Data in table_data_cur
loop
 -- A lot of processing takes place here
 -- to validate each row
 --
 -- The following statement is called if the row is not valid
 update tableA
 set invalid='Y'
 where colA=rec_table_data.colA
 and colB=rec_Table_data.colB
end loop;

[Updated on: Tue, 09 March 2010 16:29]

Report message to a moderator

Re: Which query would execute quickest [message #446655 is a reply to message #446654] Tue, 09 March 2010 16:36 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
row by row is slow by slow.
You would have to work hard to produce a slower solution.
PL/SQL runs inside "PL/SQL engine"
SQL runs inside "SQL engine"
A CONTEXT SWITCH is required to go from 1 engine to the other engine.
CONTEXT SWITCH is a resource intensive (a.k.a. slow) operation.

The proposed solution will succeed if you have the time & resources to allow it to run to completion.

[Updated on: Tue, 09 March 2010 17:21]

Report message to a moderator

Re: Which query would execute quickest [message #446656 is a reply to message #446654] Tue, 09 March 2010 17:24 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
ziggy25 wrote on Tue, 09 March 2010 22:28
What i meant was the above are just example for what i want to do. The query will be in a cursor which is then used in a loop. I want to update the query in the cursor shown below to include the rowid in teh select statement, then use that in the update statement shown towards the end of the loop

cursor table_data
select colA, colB, colC
from tableA
 
Fetch table_data_cur into rec_table_data;
 
for rec_table_Data in table_data_cur
loop
 -- A lot of processing takes place here
 -- to validate each row
 --
 -- The following statement is called if the row is not valid
 update tableA
 set invalid='Y'
 where colA=rec_table_data.colA
 and colB=rec_Table_data.colB
end loop;




If you really positively have to do it as a cursor for loop, and I still think you're going to have to bit the bullet and use bulk collect, then you'd do it this way:


cursor table_data_cur
select colA, colB, colC
from tableA
FOR UPDATE;
 
BEGIN
 
for rec_table_Data in table_data_cur
loop
 -- A lot of processing takes place here
 -- to validate each row
 --
 -- The following statement is called if the row is not valid
 update tableA
 set invalid='Y'
 where CURRENT OF table_date_cur;

end loop;



That way uses rowid automatically behind the scenes.
Re: Which query would execute quickest [message #446759 is a reply to message #446633] Wed, 10 March 2010 05:10 Go to previous message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Another thing to bear in mind.
I assume the index on cola and colb is unique (and if it isn't your rowid approach isn't going to work without major re-coding).
In that case the rowid approach is going to make very little difference to the speed.
A unique index lookup is lightning fast. Table access by rowid is only fractionally faster.

If you want to increase the speed of your procedure by a noticeable amount then you need to look for a different approach.
Previous Topic: LEFT JOIN _last_ of matchin entries
Next Topic: Calling a function in a package (merged by CM)
Goto Forum:
  


Current Time: Mon Dec 05 08:46:10 CST 2016

Total time taken to generate the page: 0.09275 seconds