Pavb U r in my way.
I too tried to fine tune the subquery,but nothing helped me(i.e, not major change).So i posted here to get some guru's idea.
Printable View
Pavb U r in my way.
I too tried to fine tune the subquery,but nothing helped me(i.e, not major change).So i posted here to get some guru's idea.
How about doing what hrishy suggested for subquery #2?
Why don't you try to force the indexes or you can choose to accept the full scan...but can help it along with a PARALLEL HINT. In some cases it's quite good. Full table scans are not necessarily a bad thing. I have seen situations where indexes have performed worse.
Hrishy thanks a lot,Plan doesn't changes.but execution time remarkable.Another subquery has no change.Code:SELECT a.person_nr
FROM JOB_QUEUE b,
REGIMEN_PRES rp,
PERSON a
WHERE a.person_nr = b.person_nr
AND a.person_nr = rp.person_nr
AND b.job_queue_type_nr = 4
AND b.status_code = 'FNSH'
AND (a.status_code = 'ACTV' or
a.status_code = 'CHNG'
37115 rows processed
Elapsed: 00:00:34.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2435 Card=382725 Bytes=8802675)
1 0 HASH JOIN (Cost=2435 Card=382725 Bytes=8802675)
2 1 HASH JOIN (Cost=1981 Card=69850 Bytes=1257300)
3 2 TABLE ACCESS (FULL) OF 'PERSON' (Cost=673 Card=33831 Bytes=338310)
4 2 TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=1266 Card=158302 Bytes=1266416)
5 1 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRESC_QX28' (NON-UNIQUE) (Cost=293 Card=496478 Bytes=2482390)
Statistics
----------------------------------------------------------
35 recursive calls
0 db block gets
13929 consistent gets
0 physical reads
0 redo size
424699 bytes sent via SQL*Net to client
27709 bytes received via SQL*Net from client
2476 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
37115 rows processed
37115 rows selected.
----------------------------------------------------------------------------------------------------------------------------------------
After creating composite index
SELECT a.person_nr
FROM JOB_QUEUE b,
REGIMEN_PRES rp,
PERSON a
WHERE a.person_nr = b.person_nr
AND a.person_nr = rp.person_nr
AND b.job_queue_type_nr = 4
AND b.status_code = 'FNSH'
AND (a.status_code = 'ACTV' or
a.status_code = 'CHNG'
Elapsed: 00:00:02.68
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1479 Card=397716 Bytes=7556604)
1 0 HASH JOIN (Cost=1479 Card=397716 Bytes=7556604)
2 1 HASH JOIN (Cost=1028 Card=72586 Bytes=1016204)
3 2 TABLE ACCESS (FULL) OF 'PERSON' (Cost=337 Card=34834 Bytes=278672)
4 2 TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=654 Card=164400 Bytes=986400)
5 1 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRESC_QX28' (NON-UNIQUE) (Cost=293 Card=496478 Bytes=2482390)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13918 consistent gets
0 physical reads
0 redo size
528694 bytes sent via SQL*Net to client
27713 bytes received via SQL*Net from client
2476 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
37115 rows processed
please suggest to change NOT LIKE predicate.
As far as I can see you have test data in your production database... bad mojo but, not the first time I see something like this.Quote:
Originally Posted by ams-jamali
You can either delete those test rows or filtering them out; since I do not know your environment I would filter them out after the query completes meaning, run your query as if all your data is valid and then filter it.
You can accomplish this by enveloping your query into an outer filter query, something like:
Note: Code was not tested, this is just a hint.Code:select pern,
regs,
regt
from
(
SELECT rp.person_nr "pern",
rp.regimen_sequence "regs",
rp.regimen_type_code "regt",
p.email "mail"
FROM REGIMEN_PRES rp,
PERSON p
WHERE regimen_type_code = 'CD'
AND prescription_status_code= 'FNSH'
AND rp.person_nr = p.person_nr
-- and p.email not like '%test%'
...
...
...
)
where email not like '%test%'
ORDER BY pern;
Hi
Even though the plans are same the cardinalities are different.
So i am wondering now how do you collect stats .
Can you post exact statements which you use to collect stats
regards
Hrishy
I gave only timing and trace output of that statement.
before creating comp.index i took timing with trace for that query(i ran that query twice same time returns,since it may vary due to buffer cache),then after creating index i ran that same query.I saw a big difference in timing.
Hi
Can you run the query again and post the query and the plan again using code tags
regards
Hrishy
here is the report,
So what u r expecting..?Code:SELECT a.person_nr
FROM JOB_QUEUE b,
REGIMEN_PRES rp,
PERSON a
WHERE a.person_nr = b.person_nr
AND a.person_nr = rp.person_nr
AND b.job_queue_type_nr = 4
AND b.status_code = 'FNSH'
AND (a.status_code = 'ACTV' or
a.status_code = 'CHNG')
Elapsed: 00:00:02.47
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1448 Card=225193 Bytes=5179439)
1 0 HASH JOIN (Cost=1448 Card=225193 Bytes=5179439)
2 1 HASH JOIN (Cost=1007 Card=41099 Bytes=739782)
3 2 TABLE ACCESS (FULL) OF 'PERSON' (Cost=337 Card=34834 Bytes=278672)
4 2 TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=654 Card=41100 Bytes=411000)
5 1 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRES_QX28' (N
ON-UNIQUE) (Cost=293 Card=496478 Bytes=2482390)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13918 consistent gets
10271 physical reads
0 redo size
528694 bytes sent via SQL*Net to client
27713 bytes received via SQL*Net from client
2476 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
37115 rows processed
To..,PAVB
There is a slight change in timing.(nearly 1sec).,thanks for your suggestion
Hi
I want to see EXACTLY what statements do you use to gather your stats.
Your cardinalities still don't match to what you posted earlier by the way
regards
Hrishy