Skip to Main Content
  • Questions

  • Any ideas on how to make SELECT faster

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Vladimir.

Asked: February 07, 2003 - 4:25 pm UTC

Last updated: July 15, 2004 - 11:29 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom.
We have SELECT statement that runs too long.
There is table that is accessed very , very often.
KEY column contain first digits of value. Key_len stores the key length. The SELECT may return multiple rows. All we need is to make sure that there is key in slow_select table that maps to first digits of value. Value is longer(has more digits) than the key.

SQL> create table slow_select(key number(12,0), key_len number(2,0), indicator number(2,0), other_columns varchar2(32)) nologging;

Table created.

SQL> insert into slow_select values(1, 1,23, 'fdvewa');

1 row created.

SQL> insert into slow_select values(12, 2, 45, 'fdvewa');

1 row created.

SQL> insert into slow_select values(123, 3, 12, 'arwef');

1 row created.

SQL> insert into slow_select values(123456, 6, 12, 'arwef');

1 row created.

SQL> insert into slow_select values(10, 2, 43,'aweraw');

1 row created.

SQL> insert into slow_select values(100, 3, 43,'awer');

1 row created.

SQL> insert into slow_select values(1000, 4, 3, 'esfd');

1 row created.

SQL> insert into slow_select values(10000120,8,34,'sefg');

1 row created.

SQL> alter table slow_select add primary key(key);

Table altered.

SQL> analyze table slow_select compute statistics;

Table analyzed.

SQL> set autot trace
SQL> select 1 from slow_select where key=SUBSTR(&&value, 1, key_len) and other_columns = 'fds';
Enter value for value: 1234567890123456
old 1: select 1 from slow_select where key=SUBSTR(&&value, 1, key_len) and other_columns = 'fds'
new 1: select 1 from slow_select where key=SUBSTR(1234567890123456, 1, key_len) and other_columns = 'fds'

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=10)
1 0 TABLE ACCESS (FULL) OF 'SLOW_SELECT' (Cost=1 Card=1 Bytes=
10)





Statistics
----------------------------------------------------------
39 recursive calls
4 db block gets
8 consistent gets
0 physical reads
0 redo size
175 bytes sent via SQL*Net to client
310 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>select 1 from slow_select where key=SUBSTR(&&value, 1, key_len) and other_columns = 'arwef';
old 1: select 1 from slow_select where key=SUBSTR(&&value, 1, key_len) and other_columns = 'arwef'
new 1: select 1 from slow_select where key=SUBSTR(1234567890123456, 1, key_len) and other_columns = 'arwef'


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=10)
1 0 TABLE ACCESS (FULL) OF 'SLOW_SELECT' (Cost=1 Card=1 Bytes=
10)





Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2 consistent gets
0 physical reads
0 redo size
305 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> spool off

Sure it uses FULL table scan because I inserted just a few rows.
However in real life this table has 100000 rows. It takes 2 seconds to get the result. And this table is accessed very, very often.
I tried to build index on key and key_len. I used the index hint to make Oracle optimizer to use this index. It was even worse since it did INDEX FULL scan and then accessed table by ROWID.

I cannot come up with any good SELECT in this case. I thought of partitioning this table but data in KEY column are not distributed evenly. You may have 10 keys starting with 1, 50000 key starting with 4, and 4000 starting with 7 for example.

Thank you very much in advance.
Vladimir

and Tom said...

Oh man -- another case of

'our most popular query, the one that will make our system live or die, runs really slow. why didn't we try tons of other methods of storing the data first, before we rolled it out'....


using numbers to store strings -- or comparing strings to numbers and converting -- bummer.


select 1 from slow_select where key=SUBSTR(1234567890123456, 1,
key_len) and other_columns = 'fds'


Lets see, you could query:

select *
from your_problem_table
where other_columns = 'fds' <<<<=== this is useful maybe
and key_len <= length(:bind_variable) <<<== if key_len >, substr fails
and substr(key,1,1) = substr(:bind_variable,1,1) <<<=== that has to be true
and key = substr(:bind_variable,1,key_len)


and then a function based index on

(other_columns,key_len,substr(key,1,1))

might help you reduce the number of rows you need to scan through.
see

</code> http://asktom.oracle.com/~tkyte/article1/index.html <code>


Partitioning? how would that help -- probably make it worse. You need to find a way to phrase your query such that every row need not be inspected -- no magic here...




Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

All ingeniuos is simple

Vladimir, February 08, 2003 - 7:07 pm UTC

Tom,
thank you very much for your help and time.
Vladimir

Let me thank you again

Vladimir, February 08, 2003 - 7:09 pm UTC

Thank you very, very much.

Differebce between an Join and small Diff queries

A reader, February 09, 2003 - 1:19 am UTC

Hi Tom,
Is there a difference between using a join or using small queries.
For eg
I have join on 10 tables
Instead of joining the 10 tables i try and use the following
select id1
from table
where columnid = 10,(select id2
from table2
where column = 10),....


If there a difference in execution of queires in this manner or using joins, which method is more quicker and suggested

Thanks




Tom Kyte
February 09, 2003 - 3:17 pm UTC

syntax error -- don't get what you are saying here. the above is not valid SQL

Hopefully you are not running multiple queries -- just one query would be the right answer.

Alternative query

David, February 09, 2003 - 9:06 pm UTC

Tom,
I entirely agree that the data model is wrong.

I think this alternative query can be faster - since key can only be between 1 and 12 digits, try each possibility:
select 1 from slow_select where key in
(SUBSTR(&&value, 1, 1), SUBSTR(&&value, 1, 2), SUBSTR(&&value, 1, 3), SUBSTR(&&value, 1, 4), SUBSTR(&&value, 1, 5), SUBSTR(&&value, 1, 6), SUBSTR(&&value, 1, 7), SUBSTR(&&value, 1, 8), SUBSTR(&&value, 1, 9), SUBSTR(&&value, 1, 10), SUBSTR(&&value, 1, 11), SUBSTR(&&value, 1, 12) )
and other_columns = 'fds'

On 35,000 rows for which the original query takes 189 consistent gets, this takes 13 consistent gets
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=6)
1 0 INLIST ITERATOR
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SLOW_SELECT' (Cost=3 Card=1 Bytes=6)
3 2 INDEX (RANGE SCAN) OF 'SYS_C0067815' (UNIQUE) (Cost=2 Card=1)

It may be even faster with an index on key plus other_columns.

Thanks for your very helpful answer to my earlier question about DBMS_STATS.

Tom Kyte
February 09, 2003 - 9:46 pm UTC

Excellent idea -- bummer I did not think of it myself ;)


Very nice

Got embarassed twice.

Vladimir, February 10, 2003 - 1:49 pm UTC

Thank you, David and Tom.


A remark

Vladimir, February 10, 2003 - 1:50 pm UTC

Got embarassed and happy twice:-)

Data model

Vladimir, February 10, 2003 - 2:48 pm UTC

Now that I calmed down my next question would be
what changes can be made to improve data model?
Could you elaborate on this?
Thank you in advance.

Tom Kyte
February 11, 2003 - 7:46 am UTC

well, it seems we have to convert numbers to strings or strings to numbers - be nicer if we did not -- guess we could:

select 1 from slow_select where key in
( value, trunc(value/10), trunc(value/100), .... )

and just beware of false hits on ZERO values. There key_len is not relevant anymore....

look to remove any and all implicit or explicit conversions.

Data model.

Vladimir, February 11, 2003 - 10:35 am UTC

Thanks, Tom.
I already recreated table with appropriate datatypes.


Tom Kyte
February 11, 2003 - 4:52 pm UTC

Now that we have an appropriate way thanks to David -- seems dandy to me if performance is OK for you.

It is a sticky problem -- the question you are asking. You want every row that is "like" your input string -- in reverse

where INPUT_VARIABLE like db_column || '%';


the inlist, since it is short enough, looks like a valid approach.

Another Query

phil, February 16, 2004 - 6:39 am UTC

Hi Tom,
I am indeed been very cheeky by slipping my question in here, but I have an under performing (as in I would like to see better performance ) sql statement that when you have a moment may like to comment on - I am hoping indexes may be my saviour, the tkprof is below ...

the logicaltransaction table ( partitioned by value_date_time monthly) contains but 20000 rows the transactiontype contains but 10. The index logicaltransaction_ix3 is a local index on VALUE_DATE_TIME, ACCOUNT_FK, ISOCURRENCY_FK

do you have any pointers ?

SELECT txn.rowid,txn.pk,transaction_status_date_time,final,amount,statement_fk,
prev_transactionstatustype_fk,transactionstatustype_fk
from logicaltransaction txn, transactionstatustype
where value_date_time = :b7
and account_fk = :b6
and (abs(original_amount - :b5)<:b9
or abs(original_amount - :b5)<abs(original_amount*:b8/100))
and txn.transactionstatustype_fk = transactionstatustype.pk
and ( txn.servicing_institution_ref is null
or :b4 is null
or txn.servicing_institution_ref = :b4 )
and (:b3 = transaction_reference
or :b3 = related_ref
or :b3 = account_owner_ref
or :b2 = transaction_reference
or :b2 = related_ref
or :b2 = account_owner_ref
or :b1 = transaction_reference
or :b1 = related_ref
or :b1 = account_owner_ref
)
and is_unmatchable != 1
and rownum = 1
order by pk

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.20 0.20 0 658 0 0
Execute 4000 8.41 8.78 0 53 0 0
Fetch 4000 26.30 25.23 0 682281 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8001 34.91 34.22 0 682992 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 101 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 COUNT STOPKEY
0 NESTED LOOPS
0 PARTITION RANGE SINGLE PARTITION: KEY KEY
0 TABLE ACCESS BY LOCAL INDEX ROWID LOGICALTRANSACTION PARTITION: KEY KEY
6477839 INDEX RANGE SCAN LOGICALTRANSACTION_IX3 PARTITION: KEY KEY (object id 36100)
0 TABLE ACCESS BY INDEX ROWID TRANSACTIONSTATUSTYPE
0 INDEX UNIQUE SCAN TRANSACTIONSTATUSTYPE_PK (object id 36007)



Tom Kyte
February 16, 2004 - 7:43 am UTC

that query runs in 0.006575 cpu seconds.

how much "faster" do you envision?

problem is -- if you do anything over and over and over - it'll add up. If you want this to go "faster", bulk it up -- roll it into what query is causing it to be invoked over and over and over and over and over (that is -- look to your ALGORITHM here, get rid of the row by row processing you must be doing, think "sets")

thank you !!

phil, February 17, 2004 - 4:34 am UTC


Select too slow when using bind variables

Pichai Bala, June 15, 2004 - 3:21 pm UTC

Hi Tom,
Thanks for your wonderful service. One of my PL/SQL load scripts is taking too much time around 40 mins for a hourly load when I pass the values using parameters, but when I use literals it finishes in 2 or 3 minutes.
My requirement is to use bind variables as in future we can execute using dbms_jobs.

Using literals:-
====================
SELECT /*+ DRIVING_SITE(asset) index(asset,S_ASSET_F51) use_merge(asset,act_asset) use_merge(act,act_asset) */
ext.row_id accnt_id,
asset.created add_created,
asset.row_id ref_row_id,
ext.ou_type_cd customer_type,
prod.x_ext_prod_num add_equipment,
asset.serial_num esn,
asset.warranty_start_dt warranty_start_dt,
decode(act.todo_cd, 'System', 'Care', act.todo_cd) activation_channel,
act.created activation_created,
act.sra_type_cd add_type,
asset.x_min min,
asset.x_msid msid,
sysdate created,
sysdate modified
from siebel.s_asset@prsb_report asset,
(select /*+ index(evt,IX5_S_EVT_ACT_X) index(asset_in,S_ASSET_F51) */
evt.target_ou_id target_ou_id,
asset_in.row_id asset_row_id,
min(evt.created) act_created
from siebel.s_evt_act@prsb_report evt,
siebel.s_asset@prsb_report asset_in
where evt.todo_cd in ('Web','System','Web Care')
and evt.sra_type_cd in ('Activate Service','Port In Complete')
and evt.x_subclass = 'Success'
and evt.created >= trunc(to_date('07-JUN-2004 03:00:00', 'DD-MON-YYYY HH24:MI:SS'),'MI')
and evt.created < trunc(to_date('07-JUN-2004 03:59:00', 'DD-MON-YYYY HH24:MI:SS'), 'MI') + 1/1440
and asset_in.warranty_start_dt >= trunc(to_date('07-JUN-2004 03:00:00', 'DD-MON-YYYY HH24:MI:SS'))
and asset_in.warranty_start_dt < trunc(to_date('07-JUN-2004 03:59:00', 'DD-MON-YYYY HH24:MI:SS') +1)
and asset_in.warranty_start_dt >= to_date('23-JUN-2002','DD-MON-YYYY')
---------------------------------------------------------- created is used
and asset_in.created >= trunc(to_date('07-JUN-2004 03:00:00', 'DD-MON-YYYY HH24:MI:SS'),'MI')
and asset_in.created < trunc(to_date('07-JUN-2004 03:59:00' , 'DD-MON-YYYY HH24:MI:SS'), 'MI') + 1/1440
-------------------------------------------------------
and asset_in.type_cd ='MIN'
and asset_in.x_csa_num is not null
and asset_in.owner_accnt_id = evt.target_ou_id
and evt.created >= asset_in.created
group by evt.target_ou_id,asset_in.row_id
) act_asset,
(select /*+ index(evt_in,IX5_S_EVT_ACT_X) */
evt_in.target_ou_id,
evt_in.todo_cd,
evt_in.sra_type_cd,
evt_in.created
from siebel.s_evt_act@prsb_report evt_in
where evt_in.sra_type_cd in ('Activate Service','Port In Complete')
and evt_in.todo_cd in ('Web','System','Web Care')
and evt_in.x_subclass = 'Success'
--------------------------------------------------------------------
and evt_in.created >= trunc(to_date('07-JUN-2004 03:00:00', 'DD-MON-YYYY HH24:MI:SS'), 'MI')
and evt_in.created < trunc(to_date('07-JUN-2004 03:59:00' , 'DD-MON-YYYY HH24:MI:SS'),'MI') + 1/1440 + 2/24
--------------------------------------------------------------------
) act,
custom.esn@prbe_report esn,
custom.esn_sku_mcn_lkp@prbe_report sku,
siebel.s_prod_int@prsb_report prod,
siebel.s_org_ext@prsb_report ext
where asset.warranty_start_dt >= trunc(to_date('07-JUN-2004 03:00:00', 'DD-MON-YYYY HH24:MI:SS'))
and asset.warranty_start_dt < trunc(to_date('07-JUN-2004 03:59:00', 'DD-MON-YYYY HH24:MI:SS') +1)
and asset.warranty_start_dt >= to_date('23-JUN-2002','DD-MON-YYYY')
and asset.type_cd ='MIN'
and asset.x_csa_num is not null
and asset.owner_accnt_id = ext.row_id
and asset.serial_num = esn.esn
and esn.mcn = sku.mcn
and prod.x_ext_prod_num=sku.sku
and prod.prod_cd='Kit'
and asset.row_id = act_asset.asset_row_id(+)
and act.target_ou_id(+) = act_asset.target_ou_id
and act.created(+) = act_asset.act_created
and act.sra_type_cd is not null

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.30 0 0 0 0
Fetch 17 0.01 123.34 0 0 0 136
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 0.01 123.68 0 0 0 136

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 80 (REPORT) (recursive depth: 1)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE (REMOTE)
0 NESTED LOOPS
0 MERGE JOIN
0 SORT (JOIN)
0 HASH JOIN
0 TABLE ACCESS (BY INDEX ROWID) OF 'S_PROD_INT'
[PRSB.WORLD]
0 INDEX (RANGE SCAN) OF 'S_PROD_INT_M51' (NON-UNIQUE)
[PRSB.WORLD]
0 HASH JOIN
0 REMOTE [PRBE_REPORT.WORLD]
SELECT "MCN","SKU" FROM
"CUSTOM"."ESN_SKU_MCN_LKP"@PRBE_REPORT.WORLD "A3"
0 NESTED LOOPS
0 FILTER
0 MERGE JOIN (OUTER)
0 SORT (JOIN)
0 TABLE ACCESS (BY INDEX ROWID) OF 'S_ASSET'
[PRSB.WORLD]
0 INDEX (FULL SCAN) OF 'S_ASSET_F51'
(NON-UNIQUE) [PRSB.WORLD]
0 SORT (JOIN)
0 VIEW
0 SORT (GROUP BY)
0 NESTED LOOPS
0 TABLE ACCESS (BY INDEX ROWID) OF
'S_EVT_ACT' [PRSB.WORLD]
0 INDEX (RANGE SCAN) OF
'IX5_S_EVT_ACT_X' (NON-UNIQUE) [PRSB.WORLD]
0 TABLE ACCESS (BY INDEX ROWID) OF
'S_ASSET' [PRSB.WORLD]
0 INDEX (RANGE SCAN) OF 'S_ASSET_F51'
(NON-UNIQUE) [PRSB.WORLD]
0 REMOTE [PRBE_REPORT.WORLD]
SELECT "ESN","MCN" FROM "CUSTOM"."ESN"@PRBE_REPORT.WORLD
"A4" WHERE :1="ESN"
0 SORT (JOIN)
0 TABLE ACCESS (BY INDEX ROWID) OF 'S_EVT_ACT' [PRSB.WORLD]
0 INDEX (RANGE SCAN) OF 'IX5_S_EVT_ACT_X' (NON-UNIQUE)
[PRSB.WORLD]
0 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT' [PRSB.WORLD]
0 INDEX (UNIQUE SCAN) OF 'S_ORG_EXT_P1' (UNIQUE) [PRSB.WORLD]

Using bind variables:-
==========================
SELECT /*+ DRIVING_SITE(asset) index(asset,S_ASSET_F51) use_merge(asset,act_asset) use_merge(act,act_asset) */
ext.row_id accnt_id,
asset.created add_created,
asset.row_id ref_row_id,
ext.ou_type_cd customer_type,
prod.x_ext_prod_num add_equipment,
asset.serial_num esn,
asset.warranty_start_dt warranty_start_dt,
decode(act.todo_cd, 'System', 'Care', act.todo_cd) activation_channel,
act.created activation_created,
act.sra_type_cd add_type,
asset.x_min min,
asset.x_msid msid,
sysdate created,
sysdate modified
from siebel.s_asset@prsb_report asset,
(select /*+ index(evt,IX5_S_EVT_ACT_X) index(asset_in,S_ASSET_F51) */
evt.target_ou_id target_ou_id,
asset_in.row_id asset_row_id,
min(evt.created) act_created
from siebel.s_evt_act@prsb_report evt,
siebel.s_asset@prsb_report asset_in
where evt.todo_cd in ('Web','System','Web Care')
and evt.sra_type_cd in ('Activate Service','Port In Complete')
and evt.x_subclass = 'Success'
and evt.created >= trunc(:b2,'MI')
and evt.created < trunc(:b1, 'MI') + 1/1440
and asset_in.warranty_start_dt >= trunc(:b2)
and asset_in.warranty_start_dt < trunc(:b1 +1)
and asset_in.warranty_start_dt >= to_date('23-JUN-2002','DD-MON-YYYY')
---------------------------------------------------------- created is used
and asset_in.created >= trunc(:b2,'MI')
and asset_in.created < trunc(:b1, 'MI') + 1/1440
-------------------------------------------------------
and asset_in.type_cd ='MIN'
and asset_in.x_csa_num is not null
and asset_in.owner_accnt_id = evt.target_ou_id
and evt.created >= asset_in.created
group by evt.target_ou_id,asset_in.row_id
) act_asset,
(select /*+ index(evt_in,IX5_S_EVT_ACT_X) */
evt_in.target_ou_id,
evt_in.todo_cd,
evt_in.sra_type_cd,
evt_in.created
from siebel.s_evt_act@prsb_report evt_in
where evt_in.sra_type_cd in ('Activate Service','Port In Complete')
and evt_in.todo_cd in ('Web','System','Web Care')
and evt_in.x_subclass = 'Success'
--------------------------------------------------------------------
and evt_in.created >= trunc(:b2, 'MI')
and evt_in.created < trunc(:b1,'MI') + 1/1440 + 2/24
--------------------------------------------------------------------
) act,
custom.esn@prbe_report esn,
custom.esn_sku_mcn_lkp@prbe_report sku,
siebel.s_prod_int@prsb_report prod,
siebel.s_org_ext@prsb_report ext
where asset.warranty_start_dt >= trunc(:b2)
and asset.warranty_start_dt < trunc(:b1 +1)
and asset.warranty_start_dt >= to_date('23-JUN-2002','DD-MON-YYYY')
and asset.type_cd ='MIN'
and asset.x_csa_num is not null
and asset.owner_accnt_id = ext.row_id
and asset.serial_num = esn.esn
and esn.mcn = sku.mcn
and prod.x_ext_prod_num=sku.sku
and prod.prod_cd='Kit'
and asset.row_id = act_asset.asset_row_id(+)
and act.target_ou_id(+) = act_asset.target_ou_id
and act.created(+) = act_asset.act_created
and act.sra_type_cd is not null

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.04 0 0 0 0
Execute 1 1.63 103.91 0 0 0 0
Fetch 17 40.16 2329.50 7680 0 40 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 41.81 2433.46 7680 0 40 16

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 55 (BPICHAI) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
16 FILTER (cr=0 r=7680 w=7680 time=741509267 us)
16 HASH JOIN (cr=0 r=7680 w=7680 time=741509231 us)
16 HASH JOIN (cr=0 r=7680 w=7680 time=635326125 us)
16 NESTED LOOPS (cr=0 r=7680 w=7680 time=635313447 us)
16 NESTED LOOPS (cr=0 r=7680 w=7680 time=635292503 us)
16 FILTER (cr=0 r=7680 w=7680 time=635271668 us)
389354 MERGE JOIN OUTER (cr=0 r=7680 w=7680 time=635171879 us)
389354 SORT JOIN (cr=0 r=7680 w=7680 time=1398761973 us)
389354 MERGE JOIN CARTESIAN (cr=0 r=0 w=0 time=1383501850 us)
98 REMOTE (cr=0 r=0 w=0 time=829823921 us)
389354 BUFFER SORT (cr=0 r=0 w=0 time=547355426 us)
3973 REMOTE (cr=0 r=0 w=0 time=546795432 us)
1568 SORT JOIN (cr=0 r=0 w=0 time=520387290 us)
16 VIEW (cr=0 r=0 w=0 time=520261358 us)
16 SORT GROUP BY (cr=0 r=0 w=0 time=520261331 us)
16 FILTER (cr=0 r=0 w=0 time=520261042 us)
16 NESTED LOOPS (cr=0 r=0 w=0 time=520261025 us)
16 REMOTE (cr=0 r=0 w=0 time=520159623 us)
16 REMOTE (cr=0 r=0 w=0 time=22894 us)
16 REMOTE (cr=0 r=0 w=0 time=5430 us)
16 REMOTE (cr=0 r=0 w=0 time=5123 us)
47 REMOTE (cr=0 r=0 w=0 time=1764 us)
2786015 REMOTE (cr=0 r=0 w=0 time=103395259 us)

===========================================================
Any thoughts.

Thanks for your wonderful service.


Tom Kyte
June 16, 2004 - 11:09 am UTC

lose all of the hints except for driving site, try first_rows -- and what happens then.

A reader, July 15, 2004 - 11:29 am UTC

what a query

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.