Count(*) slower than *
Date: Mon, 15 Mar 2010 21:54:52 +0100
Message-ID: <4b9e9e9b$0$23454$426a34cc_at_news.free.fr>
Hi,
I have the following query (9.2.0.6)
with * => <1s
with count(*) => 7:52 min
do you know why ?
Thanks in advance
P98> SELECT count(*)
2 FROM siebel.s_party t1,
3 siebel.s_postn_con t2,
4 siebel.s_party t3,
5 siebel.s_evt_act t4,
6 siebel.s_contact t5
7 WHERE t3.row_id = t5.par_row_id
8 AND t5.pr_postn_id = t2.postn_id
9 AND t5.row_id = t2.con_id
10 AND t2.postn_id = t1.row_id
11 AND t4.target_per_id = t3.row_id
12 AND ( (t4.subtype_cd = 'Participant Relem')
13 AND ( t5.emp_flg = 'N'
14 AND (t5.status_cd != 'Invalide' OR t4.evt_stat_cd = 'A participé'
15 ) 16 ) 17 ) 18 AND (t4.par_evt_id = '1-5RVX5F')
19 /
Elapsed: 00:07:52.09
Execution Plan
0
SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5 Card=1 Bytes=127)
1 0
SORT (AGGREGATE)
2 1
NESTED LOOPS (Cost=5 Card=1 Bytes=127)
3 2
NESTED LOOPS (Cost=4 Card=13348 Bytes=1414888)
4 3
NESTED LOOPS (Cost=3 Card=1 Bytes=95)
5 4
NESTED LOOPS (Cost=2 Card=1 Bytes=52)
6 5
TABLE ACCESS (BY INDEX ROWID) OF 'S_EVT_ACT' (Cost=1 Card=1
Bytes=41)
7 6
INDEX (RANGE SCAN) OF 'S_EVT_ACT_F11' (NON-UNIQUE) (Cost=3
Card=2)
8 5
INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
9 4
TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT' (Cost=1 Card=1
Bytes=43)
10 9
INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2' (UNIQUE)
11 3
INDEX (FULL SCAN) OF 'S_PARTY_P1' (UNIQUE) (Cost=95 Card=263570
Bytes=2899270)
12 2
INDEX (RANGE SCAN) OF 'S_POSTN_CON_M3' (NON-UNIQUE)
Statistics
0 recursive calls
0 db block gets
163991324 consistent gets
452 physical reads
0 redo size
199 bytes sent via SQL*Net to client
278 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
P98> ed
Wrote file afiedt.buf
1 SELECT * 2 FROM siebel.s_party t1,
3 siebel.s_postn_con t2,
4 siebel.s_party t3,
5 siebel.s_evt_act t4,
6 siebel.s_contact t5
7 WHERE t3.row_id = t5.par_row_id
8 AND t5.pr_postn_id = t2.postn_id
9 AND t5.row_id = t2.con_id
10 AND t2.postn_id = t1.row_id
11 AND t4.target_per_id = t3.row_id
12 AND ( (t4.subtype_cd = 'Participant Relem')
13 AND ( t5.emp_flg = 'N'
14 AND (t5.status_cd != 'Invalide' OR t4.evt_stat_cd = 'A participé'
15 ) 16 ) 17 )
18* AND (t4.par_evt_id = '1-5RVX5F')
P98> /
310 rows selected.
Elapsed: 00:00:00.03
Execution Plan
0
SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5 Card=1 Bytes=1553)
1 0
NESTED LOOPS (Cost=5 Card=1 Bytes=1553)
2 1
NESTED LOOPS (Cost=4 Card=1 Bytes=1483)
3 2
NESTED LOOPS (Cost=3 Card=1 Bytes=1331)
4 3
NESTED LOOPS (Cost=2 Card=1 Bytes=754)
5 4
TABLE ACCESS (BY INDEX ROWID) OF 'S_EVT_ACT' (Cost=1 Card=1
Bytes=684)
6 5
INDEX (RANGE SCAN) OF 'S_EVT_ACT_F11' (NON-UNIQUE) (Cost=3
Card=2)
7 4
TABLE ACCESS (BY INDEX ROWID) OF 'S_PARTY' (Cost=1 Card=1
Bytes=70)
8 7
INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
9 3
TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT' (Cost=1 Card=1
Bytes=577)
10 9
INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2' (UNIQUE)
11 2
TABLE ACCESS (BY INDEX ROWID) OF 'S_POSTN_CON' (Cost=1 Card=5
Bytes=760)
12 11
INDEX (RANGE SCAN) OF 'S_POSTN_CON_M3' (NON-UNIQUE)
13 1
TABLE ACCESS (BY INDEX ROWID) OF 'S_PARTY' (Cost=1 Card=1 Bytes=70)
14 13
INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
Statistics
0 recursive calls
0 db block gets
4368 consistent gets
0 physical reads
0 redo size
188330 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
22 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
310 rows processed Received on Mon Mar 15 2010 - 15:54:52 CDT