Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Irfan.

Asked: August 20, 2002 - 8:24 am UTC

Answered by: Tom Kyte - Last updated: September 10, 2020 - 6:51 am UTC

Category: SQL*Plus - Version: 9i

Viewed 1000+ times

You Asked

Hello Tom,

I have a live table (Investor) with customers records and another table (Invdatew) has the same records but have one extra column for date,. After each end of day I use dbms_job to insert the the exact data from live table to the other one plus sysdate. But it takes so long that it takes approximately three hours for inserting 36 rows (rows depends on new entries per day). Can you please help tune this query.

SQL> insert into invdatew
select inv, name, nama, type, clas, stat, act, rbrk, rest, reme,
adde, adda, cnum, dload, sysdate from investor
where inv not in
(select inv from invdatew);

36 rows created.


Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=170 Card=3672 Bytes=
168912)

1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'INVESTOR' (Cost=170 Card=3672 By
tes=168912)

3 1 TABLE ACCESS (FULL) OF 'INVDATEW' (Cost=196 Card=2614 By
tes=33982)


Statistics
----------------------------------------------------------
0 recursive calls
284958 db block gets
47270538 consistent gets
42091341 physical reads
4528 redo size
886 bytes sent via SQL*Net to client
808 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
36 rows processed

Thanks


and we said...

Well, not having the table defs makes it a bit of a guess but I'll guess that inv is defined as nullable which prevents us from doing some nice optimizations. Here is an example where I compare the results of three inserts and their performance. This should get you going. Suggest the hash anti-join method as being the probable "best". Depends on the size of the tables really. Also, I assume an index on invdatew for the NOT EXISTS query, if no such index, don't do that one!

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table invdatew;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table investor;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table invdatew
2 as
3 select 1 inv, a.*, sysdate dt from all_objects a where 1=0;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table investor
2 as
3 select 1 inv, a.* from all_objects a where 1=0;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index invdatew_idx on invdatew(inv);

Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec gen_data( 'INVDATEW', 5000);

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec gen_data( 'INVESTOR', 5000);

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table invdatew compute statistics for table for all indexes for all indexed columns;

Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table investor compute statistics for table for all indexes for all indexed columns;

Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sql_trace=true;

Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into invdatew
2 select a.*, sysdate from investor a
3 where inv not in (select inv from invdatew);

5000 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> rollback;

Rollback complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into invdatew
2 select a.*, sysdate from investor a
3 where inv is not null
4 and inv not in (select /*+ HASH_AJ */ inv from invdatew where inv is not null );

5000 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> rollback;

Rollback complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into invdatew
2 select a.*, sysdate from investor a
3 where not exists ( select *
4 from invdatew
5 where invdatew.inv = a.inv );

5000 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> rollback;

Rollback complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM>



Now, the three queries are *different* in that if INV is null in invdatew -- the first one returns a different answer (no rows) where as the second two might return some rows -- see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:442029737684 <code>
for the reasoning behind that...

So, I think the second two queries are OK for you (if you have null in inv in invdatew you would always get ZERO rows -- probably not what you wanted right?)

So, looking at the tkprof for these inserts we see hugely different performance characteristics:

insert into invdatew
select a.*, sysdate from investor a
where inv not in (select inv from invdatew)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 85.64 86.08 0 1974190 31754 5000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 85.66 86.10 0 1974190 31754 5000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 511

Rows Row Source Operation
------- ---------------------------------------------------
5001 FILTER
5001 TABLE ACCESS FULL INVESTOR
5000 TABLE ACCESS FULL INVDATEW


This query was processed like this:


for each row in investor
loop
FULL SCAN INVDATEW making sure that INV not in there
end loop

that is -- 5000 full scans of INVDATEW (5000 = number of rows in investor!)


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


insert into invdatew
select a.*, sysdate from investor a
where inv is not null
and inv not in (select /*+ HASH_AJ */ inv from invdatew where inv is not null )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.67 3.84 0 550 10631 5000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.68 3.85 0 550 10631 5000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 511

Rows Row Source Operation
------- ---------------------------------------------------
5001 HASH JOIN ANTI
5000 TABLE ACCESS FULL INVESTOR
5000 VIEW VW_NSO_1
5000 INDEX FAST FULL SCAN (object id 44573)

Wow -- that is different -- from over 85 cpu seconds down to .6, over 86 seconds runtime to under 4 seconds.

This was processed sort of like this:

for every row in outer join investor to invdatew
if invdatew.inv is null then insert this record

Much more efficient...

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

insert into invdatew
select a.*, sysdate from investor a
where not exists ( select *
from invdatew
where invdatew.inv = a.inv )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.72 4.32 0 10672 10623 5000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.72 4.32 0 10672 10623 5000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 511

Rows Row Source Operation
------- ---------------------------------------------------
5001 FILTER
5001 TABLE ACCESS FULL INVESTOR
5000 INDEX RANGE SCAN (object id 44573)

Now, not as good as the hash_aj but.... very close, very close. The logical IO's would make me avoid this approach however, rather do 550 LIO's then 10,672. This was processed like this:


for every record in investor
run a subquery that uses an index to find a row in invdatew
if not found
then insert
end if;
end loop



Hopefully, you can use that hash_aj and your query will run in minutes or less.



and you rated our response

  (33 ratings)

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

Reviews

Great !!!

August 21, 2002 - 7:47 am UTC

Reviewer: Irfan from Kuwait

Hello Tom,
Thanks very much for the solution. I worked fine, just took less than 40 seconds (compared to 3 hours, wow..). You are so humble and nice.


Tom Kyte

Followup  

August 21, 2002 - 8:23 am UTC

Dig it -- did it 270 times faster ;) sweet...

which one did you end up using?

As you suggested -- Second One

August 22, 2002 - 3:19 am UTC

Reviewer: Irfan from Kuwait

<quote>
Hopefully, you can use that hash_aj and your query will run in minutes or less.
</quote>

Hi Tom,
I used the second one and it just fires. Amazed... Never know that hash_aj is so powerful - and your ideas are so brilliant.

Thanks.


what is hash_aj

September 27, 2002 - 5:11 pm UTC

Reviewer: rajiv

tom,

brilliant

what is this hash_aj?how it worked? is it new feature in 9i?

thanks
rajiv

Tom Kyte

Followup  

September 27, 2002 - 6:33 pm UTC

It is a CBO thing, around for a while -- the example was in 817 here.

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/hints.htm#21060 <code>


Terrific!

March 28, 2003 - 9:39 am UTC

Reviewer: Urs from Germany

Tom,

the "where inv is not null" thing saved me more than 99% execution time. How can I rate 6 stars? (I am not worthy...)

However, shouldn't the CBO be clever enough to know that NOT NULL fields will always be not null?

Tom Kyte

Followup  

March 28, 2003 - 9:51 am UTC

inv was not "not null"

if the columns is NOT NULL in the data dictionary -- yes, it is "smart enough". If you do not tell it -- if the field permits NULL's then it is smart enough to know that and goes for the correct answer instead of a fast wrong one.

Excellent!!!

March 28, 2003 - 10:55 am UTC

Reviewer: Mirjana from Barcelona


Why not auto?

March 28, 2003 - 9:38 pm UTC

Reviewer: Christo Kutrovsky from Ottawa, ON Canada

Why did not the CBO choose to use HASH_AJ in this case?

For example, if we are to manually define the statistics for those tables, what should they be in order for the CBO to choose to use HASH_AJ without giving a hint to the query?



Tom Kyte

Followup  

March 29, 2003 - 9:28 am UTC

and can and will -- especially in 9i (incremental improvements).

also -- just discovered the test database I was using had always_anti_join = nested_loops ;(

turning that off -- we get:

insert into invdatew
select a.*, sysdate from investor a
where inv is not null
and inv not in (select inv from invdatew where inv is not null )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.02 0 0 0 0
Execute 2 0.72 4.86 350 556 11122 5000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.73 4.88 350 556 11122 5000

Misses in library cache during parse: 2
Optimizer goal: CHOOSE
Parsing user id: 687

Rows Row Source Operation
------- ---------------------------------------------------
1 HASH JOIN ANTI
0 TABLE ACCESS FULL INVESTOR
0 VIEW VW_NSO_1
0 INDEX FULL SCAN (object id 55526)


right all -- hash aj.

NOT NULL column on remote table

March 31, 2003 - 2:20 am UTC

Reviewer: Urs from Germany

Thanks for your reply, Tom.

In my case, I guess the CBO had no chance to know that the column is declared NOT NULL as it is selected from a remote database across a db link.

Is there a way to help the CBO in using the remote db's data dictionary?

Urs

Tom Kyte

Followup  

March 31, 2003 - 8:24 am UTC

distributed stuff changes all of the rules -- not every access plan is available.

ALWAYS_ANTI_JOIN

April 01, 2003 - 4:26 pm UTC

Reviewer: Kashif from Houston, TX

Hi Tom,

I know this parameter is obsolete in 9i, but I was wondering what the value NESTED_LOOPS means. Thanks.

Kashif

Tom Kyte

Followup  

April 01, 2003 - 7:36 pm UTC

means to use a nested loops anti join and is the reason my 8i example originally had a HASH_AJ hint in it.



Awesome!

April 02, 2003 - 7:03 am UTC

Reviewer: Max from Moscow, Russia

subj.

How about using Append Hint

August 11, 2003 - 11:40 pm UTC

Reviewer: A reader

Does it run more efficiently if we use the append hint along with the hash_AJ hint ?

Tom Kyte

Followup  

August 12, 2003 - 8:27 am UTC

maybe, maybe not.

it won't reuse any existing free space in the table if you do.

your dba must (has to, imperative) schedule a backup around your processing if you do this in nologging mode.

it won't have a tremendous affect if there are lots of indexes on the table (as they are always logged)

and given that the original question had:

...
But it takes so long that it takes approximately three
hours for inserting 36 rows
.....

for 36 rows -- append isn't going to save much of anything!

Superb Tom!

August 13, 2003 - 1:38 pm UTC

Reviewer: Denise from Virginia, USA

how do you assign always_anti_join=nested_loops in 8.1.7.

I had never known this existed until reading this thread.

We have queries that run excruciatingly slow and now I will
go back and take a look at them. Will print out your examples(I have a "script" book that I keep with all your examples from this website) and add to the others.

Tom Kyte

Followup  

August 13, 2003 - 2:15 pm UTC

ops$tkyte@ORA817DEV> show parameter always

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
always_anti_join                     string  NESTED_LOOPS
always_semi_join                     string  standard
ops$tkyte@ORA817DEV>

they are just init.ora parameters.... 

Any Difference?

August 13, 2003 - 9:10 pm UTC

Reviewer: Kamal Kishore from New Jersey, USA

Hi Tom,
Is there a difference between the two?

SQL> alter table emp add constraint ck_emp_deptno check(deptno is not null) ;

and

SQL> alter table emp modify (deptno not null) ;

The reason I ask is because if I use the first ALTER TABLE and then do the following query, I get this plan:

SQL> select * from emp where deptno not in (select /*+ HASH_AJ */ deptno from dept) ;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=40)
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 Bytes=40)
   3    1     INDEX (FULL SCAN) OF 'PK_DEPT' (UNIQUE) (Cost=1 Card=1 Bytes=2)



And, When I use the second ALTER TABLE command and then run the query again, I get this plan:

SQL> select * from emp where deptno not in (select /*+ HASH_AJ */ deptno from dept) ;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=42)
   1    0   HASH JOIN (ANTI) (Cost=5 Card=1 Bytes=42)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=560)
   3    1     INDEX (FULL SCAN) OF 'PK_DEPT' (UNIQUE) (Cost=1 Card=4 Bytes=8)


Are the two alter table statements behave differently?

Thanks,
 

Tom Kyte

Followup  

August 13, 2003 - 9:42 pm UTC

you just found the difference then.

the check constraint is just that -- a check constraint. it could be "check the sky is blue" for all we know. It is not a NOT NULL constraint.

in a not in, the subquery must be known to NOT return nulls for the anti-join

you could add the predicate "where column is not null" as well

I Agree.

August 13, 2003 - 10:24 pm UTC

Reviewer: Kamal Kishore from New Jersey, USA

Hi Tom,
One reason of my question was that at the client site they always insist that all integrity constraints (including NOT NULL) must be enforced using ALTER TABLE ADD CONSTRAINT and never use the ALTER TABLE MODIFY.

I was looking for a example where it would make a difference and found one. Thanks.

To sum it up, would you then say that NOT NULL check must always and always be enforced using ALTER TABLE MODIFY?
Thanks,


Tom Kyte

Followup  

August 14, 2003 - 7:49 am UTC

by definition a not null can only be added via modify -- not add constraint.  the wire diagrams in the sql reference tell us so.

http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_32a.htm#2104002

shows we can add an "out of line" constraint with ADD

http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/clauses3a.htm#1001938

shows us NOT NULL is an "in line" constraint only.


the syntax they are looking for for NOT NULL is:

ops$tkyte@ORA920LAP> alter table t modify ( x constraint x_not_null not null );

Table altered.




it is imperative they use NOT NULL and not a check constraint that checks if X is not null if they would like the optimizer to be able to fully do its job. 

??? could you please explain in detail ???

August 14, 2003 - 12:53 am UTC

Reviewer: A reader

I've to admit, I didn't get your answer above:

<quote>
you just found the difference then.

the check constraint is just that -- a check constraint. it could be "check the sky is blue" for all we know. It is not a NOT NULL constraint.

in a not in, the subquery must be known to NOT return nulls for the anti-join

you could add the predicate "where column is not null" as well
</quote>

could you please provide some further explanation?

Tom Kyte

Followup  

August 14, 2003 - 7:56 am UTC

umm, one of them tells the optimizer

a) nothing (that is the check constraint. to the optimizer that is just that -- some ARBITRARY check constraint)

the other tells the optimizer

b) the column does not allow null values, you never need to concern yourself that the column would be NULL -- it always has a value.




not able to tune the query

August 14, 2003 - 4:04 am UTC

Reviewer: mohsin from INDIA

Hi
Need help in tuning the data warehouse query
select
borrowername,
DESDEALER,
DESSOURCE,
dimrepmonth,
locdealer,
lan,
dimdisbmonth,
disbdate,
dimstartmonth,
DIMCURROSMONTHS,
dimcurrdpd,
subitem,
desdealer,
BRDITEM,
desitem,
a.dimitem,
TOTALNETDISBAMT,
TOTALGROSSDISBAMT,
TOTALEMIOUTSTANDING,
TOTALPRINOUTSTANDING,
TOTALINTOUTSTANDING,
TOTALOUTSTANDING,
descbucket
from mining.crash_monthlyfact a,
MINING.CRASH_DIMDEALER C,
mining.crash_dimitem d,
mining.crash_dimbucket e,
mining.crash_dimemi h,
mining.crash_dimsource f
where a.DIMitem =d.dimitem
AND A.DIMDEALER=C.DIMDEALER
and a.dimcurrbkt=e.dimbucket
and a.dimemi=h.dimemi
and a.dimsource=f.dimsource
and a.dimcurrbkt>=2
and c.locdealer='HYDERABAD'
AND a.DISBDATE >=to_date('01-JAN-02')

The execution Path of the Query is :::
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10150 Card=3896 Byte
s=907768)

1 0 HASH JOIN (Cost=10150 Card=3896 Bytes=907768)
2 1 HASH JOIN (Cost=10126 Card=3896 Bytes=720760)
3 2 TABLE ACCESS (FULL) OF 'CRASH_DIMSOURCE' (Cost=6 Card=
19880 Bytes=397600)

4 2 NESTED LOOPS (Cost=10115 Card=4081 Bytes=673365)
5 4 HASH JOIN (Cost=10115 Card=4081 Bytes=665203)
6 5 TABLE ACCESS (FULL) OF 'CRASH_DIMBUCKET' (Cost=1 C
ard=14 Bytes=126)

7 5 HASH JOIN (Cost=10113 Card=4081 Bytes=628474)
8 7 TABLE ACCESS (FULL) OF 'CRASH_DIMDEALER' (Cost=2
3 Card=248 Bytes=9672)

9 7 TABLE ACCESS (FULL) OF 'CRASH_MONTHLYFACT' (Cost
=10086 Card=344633 Bytes=39632795)

10 4 INDEX (RANGE SCAN) OF 'CRASHDIMEMI' (NON-UNIQUE)
11 1 TABLE ACCESS (FULL) OF 'CRASH_DIMITEM' (Cost=17 Card=213
65 Bytes=1025520)

The execution plan shows the FTS of CRASH_MONTHLYFACT.. Althougt the DISBDATE column of CRASH_MONTHLYFACT is indexed using a B tree
PLs help us ...


Tom Kyte

Followup  

August 14, 2003 - 8:02 am UTC

so,

a) how many rows does crash_monthly fact actually have?

b) are the statistics totally up to date? show me the output of

set autotrace traceonly explain
select * from crash_monthlyfact

c) how many rows in that table SATISFY the predicate on the date?

d) how many blocks in that table and what is the clustering factor of the index thereon?

e) you do know that indexes do not in any way shape or form imply "i will be fast", they as often imply "i'll be as slow as molasses in feburary in alaska"

TUNING OF SQL QUERY

August 20, 2003 - 8:33 am UTC

Reviewer: Mohsin from INDIA

a) how many rows does crash_monthly fact actually have?
Ans:2081311

b) are the statistics totally up to date? show me the output of

set autotrace traceonly explain
select * from crash_monthlyfact

Ans:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10086 Card=2081311 B
ytes=1371583949)

1 0 TABLE ACCESS (FULL) OF 'CRASH_MONTHLYFACT' (Cost=10086 Car
d=2081311 Bytes=1371583949)

c) how many rows in that table SATISFY the predicate on the date?
Ans: 739770
d) how many blocks in that table and what is the clustering factor of the index
thereon?
Ans:
BLOCKS
---------
112000

CLUSTERING_FACTOR
-----------------
918023
PLS GUIDE..

Tom Kyte

Followup  

August 21, 2003 - 7:56 am UTC

so, why do you believe that using this index would be useful?

everything I see indicates "it would not"


When the cluster factor is nearer the number of rows in the table and far from the number of blocks in the table -- that means the data in the table is not sorted by the index itself. Meaning as we index range scan thru that index, we'll be reading lots of blocks all over the place -- not just a few blocks.


facts:

o index and table are not sorted the same

o reading N rows via index will result in N different blocks being accessed in the table. (it is nice when reading N rows via an index results in 1 or 2 blocks being accessed -- your cluster factor however indicates N rows => N blocks)

o reading 112,000 table blocks using single block IO (and reading and re-reading many of those blocks 7,8,9,10 times bear in mind, as we skip around) is INFINITELY less effecient then full scanning.



Do this for us, HINT the query, get the index plan you think would be better and then post us the benchmark results between the two!

clarification on hash join and nulls

December 01, 2004 - 1:56 pm UTC

Reviewer: amit from New Haven, CT

From this it is clear that for using hash anti join the join column should be defined as not null, Would it be correct to say that hash joins can only be performed when the join key is defined as not null on both the data sets.



Tom Kyte

Followup  

December 01, 2004 - 3:12 pm UTC

no, not at all.


where x = y

can be hash joined, even if x and y allow nulls.

x = null is never true
x != null is never true

nor are they false

but that doesn't prevent them from being hash joined.

more clarification

December 01, 2004 - 4:31 pm UTC

Reviewer: amit poddar from New Haven, CT

two questions:

1. Will all the null values map to same hash bucket ?
I am asking this since if we have most of the columns as null then hash join would not be usefull since most of them would go to sam bucket.

2. If hash join can be done even with null join keys what prevents oracle from doing hash anti join when the join key can be null in the IN the case of (..) correlated subquery



Tom Kyte

Followup  

December 01, 2004 - 7:51 pm UTC

1) they don't have to hash anywhere --

where x = y

will NEVER be true (or false) if either X or Y is null!!! nulls are not relevant here at all, they do not come into play!

2) where x NOT IN ( select null from dual )

is not true
is not false
is always unknown.

it is the semantics of NULL's that prevent it. Nulls are never equal, nor not equal -- they are Unknown.

query tuning

October 22, 2005 - 9:17 am UTC

Reviewer: Mani from India


Please advise me to tune the below query

select count(*) cnt from hire_money_fnmvt a
where txn_cacn <> '1000'
and a.doc_type like 'CR%'
and a.doc_date >= get_finyr(sysdate)
and a.branch_code < 300
and exists (select 'x' from receipt_source_document b
where a.branch_code = b.branch_code
and a.doc_type = b.doc_type
and a.doc_no = b.doc_no
and src_doc_type <> 7)
and nvl(system_doc,'N') = 'N'
having count(*) >0

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 1 2076.35 2716.19 162242 3556958 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 2076.36 2716.20 162242 3556958 0 0

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

Rows Row Source Operation
------- ---------------------------------------------------
0 FILTER
1 SORT AGGREGATE
0 FILTER
1968 TABLE ACCESS BY INDEX ROWID HIRE_MONEY_FNMVT
5777997 INDEX RANGE SCAN HFNMVT_BRDNOTY (object id 190019)
0 TABLE ACCESS BY INDEX ROWID RECEIPT_SOURCE_DOCUMENT
1806 INDEX UNIQUE SCAN CU_RSD_BRCD_DCTY_DCNO_001 (object id 192276)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 FILTER
1 SORT (AGGREGATE)
0 FILTER
1968 TABLE ACCESS (BY INDEX ROWID) OF 'HIRE_MONEY_FNMVT'
5777997 INDEX (RANGE SCAN) OF 'HFNMVT_BRDNOTY' (NON-UNIQUE)
0 TABLE ACCESS (BY INDEX ROWID) OF
'RECEIPT_SOURCE_DOCUMENT'
1806 INDEX (UNIQUE SCAN) OF 'CU_RSD_BRCD_DCTY_DCNO_001'

Tom Kyte

Followup  

October 22, 2005 - 10:43 am UTC

rbo or cbo (with good stats)

SQL Query Tuning

October 24, 2005 - 3:20 am UTC

Reviewer: A reader

Oracle Version 9i (RBO)

Tom Kyte

Followup  

October 24, 2005 - 6:21 am UTC

share with us the output from an autotrace traceonly explain of the query.

SQL Query Tuning

October 24, 2005 - 7:28 am UTC

Reviewer: A reader

Trace output :

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER
2 1 SORT (AGGREGATE)
3 2 FILTER
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'HIRE_MONEY_FNMVT'
5 4 INDEX (RANGE SCAN) OF 'HFNMVT_BRDNOTY' (NON-UNIQUE
)

6 3 TABLE ACCESS (BY INDEX ROWID) OF 'RECEIPT_SOURCE_DOC
UMENT'

7 6 INDEX (UNIQUE SCAN) OF 'CU_RSD_BRCD_DCTY_DCNO_001'
(UNIQUE)



Tom Kyte

Followup  

October 24, 2005 - 11:35 am UTC

oh - i see, I read too fast, I was hoping to hear "cbo"

use CBO - rbo will be "index happy" and that'll kill you. the in/exists processing is best with CBO as well (it'll consider in versus exists type of processing)



SQL Query Tuning

October 27, 2005 - 2:32 am UTC

Reviewer: A reader

I still wasnt to user RBO

Tom Kyte

Followup  

October 27, 2005 - 6:47 am UTC

"sorry" jumps to mind then. The rbo is very limited and primitive.


select count(*) cnt from hire_money_fnmvt a
where txn_cacn <> '1000'
and a.doc_type like 'CR%'
and a.doc_date >= get_finyr(sysdate)
and a.branch_code < 300
and exists (select 'x' from receipt_source_document b
where a.branch_code = b.branch_code
and a.doc_type = b.doc_type
and a.doc_no = b.doc_no
and src_doc_type <> 7)
and nvl(system_doc,'N') = 'N'
having count(*) >0

could be written as
select count(*) cnt
from hire_money_fnmvt a,
(select distinct branch_code, doc_type, doc_no
from branch_code
where src_doc_type <> 7
and branch_code < 300
and doc_type like 'CR%') b
where a.txn_cacn <> '1000'
and a.doc_type like 'CR%'
and a.doc_date >= (select get_finyr(sysdate) from dual)
and a.branch_code < 300
and a.branch_code = b.branch_code
and a.doc_type = b.doc_type
and a.doc_no = b.doc_no
and nvl(a.system_doc,'N') = 'N'
having count(*) >0


Tuning status query... and question about pruning in views

November 08, 2005 - 11:45 am UTC

Reviewer: Vladimir Sadilovskiy from MA, US

Hello Tom,

We have queries that return set of natural values based on whether a particular id value is found in a set of corresponding tables. Original query looks like:

SELECT 0 FROM DUAL 
 WHERE EXISTS 
       (SELECT null
          FROM TABLE_1
         WHERE id IN (select id from LookupTable))
UNION 
SELECT 1 FROM DUAL 
 WHERE EXISTS 
       (SELECT null
          FROM TABLE_2
         WHERE id IN (select id from LookupTable))
...

If an individual id value is found in one of the tables TABLE_..., it will not be found in the rest. Do you think this logic could be optimized?

I've tried following form, but it seems that I'm missing something and optimizer doesn't push "flag" predicate into union-all partitioned view:

explain plan for
select  flag
  from (select 0 flag from dual
        union all
        select 1 flag from dual
        union all
        select 2 flag from dual
        union all
        select 3 flag from dual
        union all
        select 4 flag from dual
        union all
        select 5 flag from dual
        union all
        select 6 flag from dual
        union all
        select 7 flag from dual
       ) a
where exists
      (select null
         from 
              (
               select 0 flag, ID from TABLE_1
               union all
               select 1 flag, ID from TABLE_2
               union all
               select 2 flag, ID from TABLE_3
               union all
               select 3 flag, ID from TABLE_4
               union all
               select 4 flag, ID from TABLE_5
               union all
               select 5 flag, ID from TABLE_6
               union all
               select 6 flag, ID from TABLE_7
               union all
               select 7 flag, ID from TABLE_8
              ) b
        where a.flag = b.flag
          and b.ID in
              (SELECT id from lookuptable)
      );

------------------------------------------------------------------------------------------
| Id  | Operation               |  Name                     | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                           |    13 |    78 |   789  (34)|
|*  1 |  HASH JOIN SEMI         |                           |    13 |    78 |   789  (34)|
|   2 |   VIEW                  |                           |  1312 |  3936 |            |
|   3 |    UNION-ALL            |                           |       |       |            |
|   4 |     TABLE ACCESS FULL   | DUAL                      |   164 |       |     5  (20)|
|   5 |     TABLE ACCESS FULL   | DUAL                      |   164 |       |     5  (20)|
|   6 |     TABLE ACCESS FULL   | DUAL                      |   164 |       |     5  (20)|
|   7 |     TABLE ACCESS FULL   | DUAL                      |   164 |       |     5  (20)|
|   8 |     TABLE ACCESS FULL   | DUAL                      |   164 |       |     5  (20)|
|   9 |     TABLE ACCESS FULL   | DUAL                      |   164 |       |     5  (20)|
|  10 |     TABLE ACCESS FULL   | DUAL                      |   164 |       |     5  (20)|
|  11 |     TABLE ACCESS FULL   | DUAL                      |   164 |       |     5  (20)|
|  12 |   VIEW                  | VW_SQ_1                   |  2363K|  6923K|            |
|  13 |    NESTED LOOPS         |                           |  2363K|    49M|   501   (1)|
|  14 |     TABLE ACCESS FULL   | LOOKUPTABLE               |   164 |  2132 |     5  (20)|
|  15 |     VIEW                |                           | 14411 |   126K|            |
|  16 |      UNION-ALL PARTITION|                           |       |       |            |
|* 17 |       INDEX RANGE SCAN  | TABLE_1$ID_INDEX          |     1 |     6 |     3  (34)|
|* 18 |       INDEX RANGE SCAN  | TABLE_2$ID_INDEX          |     1 |    13 |            |
|* 19 |       INDEX RANGE SCAN  | TABLE_3$ID_INDEX          |     1 |    13 |            |
|* 20 |       INDEX RANGE SCAN  | TABLE_4$ID_INDEX          |     1 |    13 |            |
|* 21 |       INDEX RANGE SCAN  | TABLE_5$ID_INDEX          |     1 |    13 |            |
|* 22 |       INDEX RANGE SCAN  | TABLE_6$ID_INDEX          |     1 |    13 |            |
|* 23 |       INDEX RANGE SCAN  | TABLE_7$ID_INDEX          |     2 |    12 |     4  (25)|
|* 24 |       INDEX RANGE SCAN  | TABLE_8$ID_INDEX          |     4 |    24 |     4  (25)|
------------------------------------------------------------------------------------------

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

   1 - access("A"."FLAG"="VW_SQ_1"."FLAG")
  11 - access("TABLE_1"."ID"="LOOKUPTABLE"."ID")
  12 - access("TABLE_2"."ID"="LOOKUPTABLE"."ID")
  13 - access("TABLE_3"."ID"="LOOKUPTABLE"."ID")
  14 - access("TABLE_4"."ID"="LOOKUPTABLE"."ID")
  15 - access("TABLE_5"."ID"="LOOKUPTABLE"."ID")
  16 - access("TABLE_6"."ID"="LOOKUPTABLE"."ID")
  17 - access("TABLE_7"."ID"="LOOKUPTABLE"."ID")
  18 - access("TABLE_8"."ID"="LOOKUPTABLE"."ID")
SQL> 

Is there anything I can do to tell optimizer that if pair (0,id) is found in TABLE_1 don't look for this pair in the rest of the tables?

Thanks,

- Vladimir 

Tom Kyte

Followup  

November 08, 2005 - 10:24 pm UTC

use UNION ALL and

select *
from (current query with union all instead of union)
where rownum = 1;


how does that work - it should stop as soon as it generates a hit and the union all doesn't need to do the sort/distinct you are currently doing.

I'm confused... I need all possible flags.

November 09, 2005 - 1:18 am UTC

Reviewer: Vladimir Sadilovskiy from MA, US

Tom,

Perhaps, I was unclear. I needed all distinct flags from the query not just one. I was asking if it is possible to skip searching table with even numbers (table_1) if lookup table doesn't contain them as per the following test. Some kind of partition prunning only with separate tables.

Thank you for your thoughts.

Here is a test case.

set autotrace off

create table table_1 (id number);
create index table_1$index_id on table_1(id);
create table table_2 (id number);
create index table_2$index_id on table_2(id);

delete from table_1;
delete from table_2;

begin
    for i in 1..40000 loop
        insert into table_1 values (mod(i,100)+mod(i,2)); -- even
        insert into table_2 values (mod(i,100)+mod(i+1,2)); -- odd
    end loop;
end;
/
commit;

exec dbms_stats.gather_table_stats(null,'table_1',method_opt=>'for all columns size 254',cascade=> true);
exec dbms_stats.gather_table_stats(null,'table_1',method_opt=>'for all columns size 254',cascade=> true);

create table lookuptable (id number);

prompt case 1.1 (both tables involved)

delete from lookuptable;
insert into lookuptable values (1);
insert into lookuptable values (50);

exec dbms_stats.gather_table_stats(null,'lookuptable',method_opt=>'for all columns size 254',cascade=> true);

set autotrace on
select 1 from dual
where exists
      (select null from table_1
        where id in (select * from lookuptable))
union all
select 2 from dual
where exists
      (select null from table_2
        where id in (select * from lookuptable));

set autotrace off

prompt case 1.2 (both tables involved)

set autotrace on
select flag 
  from (select 1 flag from dual
        union all
        select 2 flag from dual) a
where exists
      (select null 
         from 
           (select 1 flag, id from table_1
            union all
            select 2 flag, id from table_2
           ) b
        where b.flag = a.flag
          and id in (select * from lookuptable));
set autotrace off

prompt case 2.1 (one table with odd values involved)

delete from lookuptable;
insert into lookuptable values (1);
insert into lookuptable values (51);

exec dbms_stats.gather_table_stats(null,'lookuptable',method_opt=>'for all columns size 254',cascade=> true);

set autotrace on

select 1 from dual
where exists
      (select null from table_1
        where id in (select * from lookuptable))
union all
select 2 from dual
where exists
      (select null from table_2
        where id in (select * from lookuptable));

set autotrace off

prompt case 2.2 (one table with odd values involved)

set autotrace on
select flag 
  from (select 1 flag from dual
        union all
        select 2 flag from dual) a
where exists
      (select null 
         from 
           (select 1 flag, id from table_1
            union all
            select 2 flag, id from table_2
           ) b
        where b.flag = a.flag
          and id in (select * from lookuptable));
set autotrace off

Here are the results:

SQL> prompt case 1.1 (both tables involved)

         1
----------
         1
         2


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=328)
   1    0   UNION-ALL
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'DUAL' (Cost=5 Card=164)
   4    2       NESTED LOOPS (Cost=7 Card=400 Bytes=2000)
   5    4         TABLE ACCESS (FULL) OF 'LOOKUPTABLE' (Cost=5 Card=2
          Bytes=6)

   6    4         INDEX (RANGE SCAN) OF 'TABLE_1$INDEX_ID' (NON-UNIQUE
          ) (Cost=2 Card=200 Bytes=400)

   7    1     FILTER
   8    7       TABLE ACCESS (FULL) OF 'DUAL' (Cost=5 Card=164)
   9    7       NESTED LOOPS (Cost=7 Card=8998 Bytes=143968)
  10    9         TABLE ACCESS (FULL) OF 'LOOKUPTABLE' (Cost=5 Card=2
          Bytes=6)

  11    9         INDEX (RANGE SCAN) OF 'TABLE_2$INDEX_ID' (NON-UNIQUE
          ) (Cost=2 Card=4499 Bytes=58487)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         20  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> SQL> SQL> SQL> case 1.2 (both tables involved)

      FLAG
----------
         2
         1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=152 Card=3 Bytes=18)
   1    0   HASH JOIN (SEMI) (Cost=152 Card=3 Bytes=18)
   2    1     VIEW (Cost=10 Card=328 Bytes=984)
   3    2       UNION-ALL
   4    3         TABLE ACCESS (FULL) OF 'DUAL' (Cost=5 Card=164)
   5    3         TABLE ACCESS (FULL) OF 'DUAL' (Cost=5 Card=164)
   6    1     VIEW OF 'VW_SQ_1' (Cost=138 Card=48998 Bytes=146994)
   7    6       NESTED LOOPS (Cost=138 Card=48998 Bytes=440982)
   8    7         TABLE ACCESS (FULL) OF 'LOOKUPTABLE' (Cost=5 Card=2
          Bytes=6)

   9    7         VIEW (Cost=67 Card=24499 Bytes=146994)
  10    9           UNION-ALL (PARTITION)
  11   10             INDEX (RANGE SCAN) OF 'TABLE_1$INDEX_ID' (NON-UN
          IQUE) (Cost=3 Card=784 Bytes=1568)

  12   10             INDEX (RANGE SCAN) OF 'TABLE_2$INDEX_ID' (NON-UN
          IQUE) (Cost=2 Card=90 Bytes=1170)





Statistics
----------------------------------------------------------
         10  recursive calls
          0  db block gets
         32  consistent gets
          0  physical reads
        128  redo size
        410  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> SQL> prompt case 2.1 (one table with odd values involved)

         1
----------
         2


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=328)
   1    0   UNION-ALL
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'DUAL' (Cost=5 Card=164)
   4    2       NESTED LOOPS (Cost=7 Card=1569 Bytes=7845)
   5    4         TABLE ACCESS (FULL) OF 'LOOKUPTABLE' (Cost=5 Card=2
          Bytes=6)

   6    4         INDEX (RANGE SCAN) OF 'TABLE_1$INDEX_ID' (NON-UNIQUE
          ) (Cost=2 Card=784 Bytes=1568)

   7    1     FILTER
   8    7       TABLE ACCESS (FULL) OF 'DUAL' (Cost=5 Card=164)
   9    7       NESTED LOOPS (Cost=7 Card=8998 Bytes=143968)
  10    9         TABLE ACCESS (FULL) OF 'LOOKUPTABLE' (Cost=5 Card=2
          Bytes=6)

  11    9         INDEX (RANGE SCAN) OF 'TABLE_2$INDEX_ID' (NON-UNIQUE
          ) (Cost=2 Card=4499 Bytes=58487)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SQL> SQL> SQL> case 2.2 (one table with odd values involved)
      FLAG
----------
         2


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=152 Card=3 Bytes=18)
   1    0   HASH JOIN (SEMI) (Cost=152 Card=3 Bytes=18)
   2    1     VIEW (Cost=10 Card=328 Bytes=984)
   3    2       UNION-ALL
   4    3         TABLE ACCESS (FULL) OF 'DUAL' (Cost=5 Card=164)
   5    3         TABLE ACCESS (FULL) OF 'DUAL' (Cost=5 Card=164)
   6    1     VIEW OF 'VW_SQ_1' (Cost=138 Card=48998 Bytes=146994)
   7    6       NESTED LOOPS (Cost=138 Card=48998 Bytes=440982)
   8    7         TABLE ACCESS (FULL) OF 'LOOKUPTABLE' (Cost=5 Card=2
          Bytes=6)

   9    7         VIEW (Cost=67 Card=24499 Bytes=146994)
  10    9           UNION-ALL (PARTITION)
  11   10             INDEX (RANGE SCAN) OF 'TABLE_1$INDEX_ID' (NON-UN
          IQUE) (Cost=3 Card=784 Bytes=1568)

  12   10             INDEX (RANGE SCAN) OF 'TABLE_2$INDEX_ID' (NON-UN
          IQUE) (Cost=2 Card=90 Bytes=1170)





Statistics
----------------------------------------------------------
         10  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
        375  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SQL> 
 

Tom Kyte

Followup  

November 09, 2005 - 9:30 am UTC

I'm confused - how about you don't post SQL and plans but rather just text describing the problem more clearly.

For example, in the first post you said:

...If an individual id value is found in one of the tables TABLE_..., it will not
be found in the rest. Do you think this logic could be optimized?....


that sounds like "search the tables and when you get a hit, stop"


statements like:

...if it is possible to skip searching table with even numbers
(table_1) ....

are confusing - since table_1 seems to be an odd number?

Clarification on the algorithm...

November 09, 2005 - 11:03 am UTC

Reviewer: Vladimir Sadilovskiy from MA, US

Tom,

Not a best thinking on my part, hence lots of confusion. Sorry for that. Please, drop my earlier explanation. I just mixed the desire to prune tables by a flag value when connecting to dual, not actually by an ID value.

So, fresh start. Here is the algorithm that I wanted to implement.

Input:
1. I have set of tables that contain different type of data:
strings,numbers,images,urls etc. But otherwise they have the same structure.

2. Each table is identified by a unique flag.
strings - 1, numbers - 2, urls - 3 ...

Goal:
The goal of the query is to retrieve set of flags that identify presence/absence of rows suttisfying cirtain condition in all these tables. The input condition is a set of ID values. This input array might get quite big. So, I wanted to combine it into a lookup table.

Algorithm:
start loop k in IDs
get ID(k);
start loop n in Tables (1)
if n has already been returned,
continue to next Table;
find one row in Table(n) that matches ID(k);
if found, return n;
end loop through Tables;
if all combination of Ns have been returned,
exit;
end loop through IDs;

algorithm ends here.

(1) - Ns are the flags assigned to each table.

Thanks again for looking into this.

- Vladimir

Tom Kyte

Followup  

November 11, 2005 - 10:05 am UTC

select 1
from t1
where id in ( set-of-ids )
and rownum = 1
UNION ALL
select 2
from t2
where id in ( set-of-ids )
and rownum = 1
UNION ALL
......


why does that not work? no procedural code, just sql.

Any other savings?

November 12, 2005 - 12:49 pm UTC

Reviewer: Vladimir Sadilovskiy from MA, US

Tom,

Yes it does work. I thought I could send less data to the server when binding lookup table (that's actually an ARRAY). Do you think I should use factored view. Or populate temp table?

Thanks,

- Vladimir


Tom Kyte

Followup  

November 12, 2005 - 1:04 pm UTC

with subquery would be my first choice. You'd bind it once.

global temporary table would be 2nd choice.

HASH_AJ in Oracle10g

November 25, 2005 - 11:23 am UTC

Reviewer: Alexander from Russia

Dear Tom,

Hint HASH_AJ was very useful in Oracle9i and we used it frequently enough. But Oracle 10g documentation contains information that the hint was deprecated.
Our small tests showed that it still works, but we would not like to use deprecated features.
Can you please advise any hint or tip which we can use in place of HASH_AJ.

Tom Kyte

Followup  

November 25, 2005 - 1:42 pm UTC

no hint at all!

Deprecated hash_aj hint

November 26, 2005 - 9:24 am UTC

Reviewer: Jonathan Lewis from UK

Failing the 'no hint needed' approach, you could try the 10g version of the leading() hint combined with a simple hash hint, viz:

/*+ leading(tabX tabY) use_hash(tabY) */

The leading hint can list all the tables in the query, so if you copy the table order that you get from the current hash_aj hint and simply ask for a hash then
a) Oracle ought to use the order and
b) will have to do an antijoin, which has been hinted to use a hash join.

I've not tried this - so I won't guarantee that Oracle won't find an alternative transformation that puts the hints out of context.

I suppose it's always possible you may have to put the tables the "wrong" way round with a swap_join_inputs hint to make it work.


Why is HASH_AJ hint not used?

September 08, 2006 - 4:39 am UTC

Reviewer: Rambabu from India

Hi,
Why is HASH_AJ not been used here?

select count(1)
from (select * from basket_detail
where the_date between '01-aug-2003' and sysdate - 500
) h
where h.hh_id not in
(select /*+ HASH_AJ */ hh_id
from basket_detail h1,
prod_item pi
where H.hh_id = H1.hh_id
and h1.the_date between '01-aug-2003' and sysdate - 500
AND pi.merchandise_type = 'TRU'
and pi.department_cd = 0
and pi.class_cd = 1
and pi.sub_class_cd = 95
and h1.hh_id <> -1
and h1.division_cd = 'TRU'
)
and division_cd = 'TRU'
and hh_id <> -1
group by h.hh_id




Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=706291 Card=1 Bytes=20)

1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 FILTER
4 3 PARTITION RANGE (ITERATOR)
5 4 TABLE ACCESS (FULL) OF 'BASKET_DETAIL' (
Cost=706234 Card=2390860 Bytes=47817200)

6 2 FILTER
7 6 MERGE JOIN* (CARTESIAN) (Cost=57 Card=4738 Bytes=118 :Q531577
450) 001

8 7 PARTITION RANGE* (ALL) :Q531577
001

9 8 TABLE ACCESS* (BY LOCAL INDEX ROWID) OF ':Q531577
BASKET_DETAIL' (Cost=50 Card=14 Bytes=168) 001

10 9 INDEX* (RANGE SCAN) OF 'FK2_HOU_MA_ID_HBD' (NO :Q531577
N-UNIQUE) (Cost=77 Card=21) 001

11 7 BUFFER* (SORT) (Cost=7 Card=327 Bytes=4251) :Q531577
001

12 11 INDEX* (RANGE SCAN) OF 'BT1_MT_DC_CC_SCC_PI' (NO :Q531577
N-UNIQUE) (Cost=1 Card=327 Bytes=4251) 000



7 PARALLEL_TO_SERIAL SELECT /*+ ORDERED NO_EXPAND USE_MERGE(A2) *
/ A1.C0,A1.C1,A1.C2,A2.C0,A2.C1,A2.C

8 PARALLEL_COMBINED_WITH_PARENT
9 PARALLEL_COMBINED_WITH_PARENT
10 PARALLEL_COMBINED_WITH_PARENT
11 PARALLEL_COMBINED_WITH_PARENT
12 PARALLEL_FROM_SERIAL






hash anti join seems to be unavailable with functions

November 21, 2007 - 9:12 am UTC

Reviewer: andy b from Nottingham, UK

Tom,

I have made good use of the information on your site regarding the hash anti join. However, in some cases I was unable to get the optimizer to use this plan. On investigation it appears to be down to whether the join is on a colunm or a pseudo-column that used a user-defined function, as in this example:


SQL> 
SQL> set echo on
SQL> set autotrace off
SQL> 
SQL> 
SQL> -- oracle 9r2
SQL> 
SQL> create table t1(t varchar2(20));

Table created.

SQL> insert into t1
  2  select substr(object_name,1,20) from all_objects;

26559 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> create table t2(t varchar2(20));

Table created.

SQL> insert into t2
  2  select substr(object_name,1,20) from all_objects;

26560 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> analyze table t2 compute statistics;

Table analyzed.

SQL> 
SQL> 
SQL> set autotrace on
SQL> 
SQL> 
SQL> 
SQL> select count(*)
  2  from t2
  3  where not exists
  4  (select null from t1
  5  where t2.t = t1.t);

  COUNT(*)                                                                      
----------                                                                      
         1                                                                      


Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=63 Card=1 Bytes=38)           
   1    0   SORT (AGGREGATE)                                                    
   2    1     HASH JOIN (ANTI) (Cost=63 Card=2 Bytes=76)                        
   3    2       TABLE ACCESS (FULL) OF 'T2' (Cost=11 Card=26560 Bytes=          
          504640)                                                               
                                                                                
   4    2       TABLE ACCESS (FULL) OF 'T1' (Cost=11 Card=26559 Bytes=          
          504621)                                                               
                                                                                




Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
        198  consistent gets                                                    
         35  physical reads                                                     
          0  redo size                                                          
        379  bytes sent via SQL*Net to client                                   
        499  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed                                                     

SQL> 
SQL> 
SQL> 
SQL> select count(*)
  2  from t2
  3  where not exists
  4  (select null from t1
  5  where length(t2.t) = length(t1.t));

  COUNT(*)                                                                      
----------                                                                      
         0                                                                      


Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=63 Card=1 Bytes=38)           
   1    0   SORT (AGGREGATE)                                                    
   2    1     HASH JOIN (ANTI) (Cost=63 Card=26294 Bytes=999172)                
   3    2       TABLE ACCESS (FULL) OF 'T2' (Cost=11 Card=26560 Bytes=          
          504640)                                                               
                                                                                
   4    2       TABLE ACCESS (FULL) OF 'T1' (Cost=11 Card=26559 Bytes=          
          504621)                                                               
                                                                                




Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
        198  consistent gets                                                    
        252  physical reads                                                     
          0  redo size                                                          
        378  bytes sent via SQL*Net to client                                   
        499  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed                                                     

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> create or replace function myfn
  2  (t1 in varchar2)
  3  return varchar2
  4  deterministic
  5  as
  6  t2 number;
  7  begin
  8  t2 := length(t1);
  9  return t2;
 10  end;
 11  /

Function created.

SQL> 
SQL> select count(*)
  2  from t2
  3  where length(t) < 20;

  COUNT(*)                                                                      
----------                                                                      
      5964                                                                      


Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=1 Bytes=19)           
   1    0   SORT (AGGREGATE)                                                    
   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=11 Card=1328 Bytes=252          
          32)                                                                   
                                                                                




Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
         99  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
        380  bytes sent via SQL*Net to client                                   
        499  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed                                                     

SQL> 
SQL> select count(*)
  2  from t2
  3  where myfn(t) < 20;

  COUNT(*)                                                                      
----------                                                                      
      5964                                                                      


Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=1 Bytes=19)           
   1    0   SORT (AGGREGATE)                                                    
   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=11 Card=1328 Bytes=252          
          32)                                                                   
                                                                                




Statistics
----------------------------------------------------------                      
         22  recursive calls                                                    
          0  db block gets                                                      
        110  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
        380  bytes sent via SQL*Net to client                                   
        499  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          5  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed                                                     

SQL> 
SQL> 
SQL> 
SQL> 
SQL> select count(*)
  2  from t2
  3  where not exists
  4  (select null from t1
  5  where length(t2.t) = myfn(t1.t));

  COUNT(*)                                                                      
----------                                                                      
         0                                                                      


Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=1 Bytes=19)           
   1    0   SORT (AGGREGATE)                                                    
   2    1     FILTER                                                            
   3    2       TABLE ACCESS (FULL) OF 'T2' (Cost=11 Card=1328 Bytes=2          
          5232)                                                                 
                                                                                
   4    2       TABLE ACCESS (FULL) OF 'T1' (Cost=11 Card=266 Bytes=50          
          54)                                                                   
                                                                                




Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
     135974  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
        378  bytes sent via SQL*Net to client                                   
        499  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed                                                     

SQL> 
SQL> 
SQL> 
SQL> 
SQL> create table t3
  2  as
  3  select myfn(t) as t
  4  from t1;

Table created.

SQL> 
SQL> analyze table t3 compute statistics;

Table analyzed.

SQL> 
SQL> 
SQL> 
SQL> select count(*)
  2  from t2
  3  where not exists
  4  (select null from t3
  5  where length(t2.t) = t3.t);

  COUNT(*)                                                                      
----------                                                                      
         0                                                                      


Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=47 Card=1 Bytes=21)           
   1    0   SORT (AGGREGATE)                                                    
   2    1     HASH JOIN (ANTI) (Cost=47 Card=25162 Bytes=528402)                
   3    2       TABLE ACCESS (FULL) OF 'T2' (Cost=11 Card=26560 Bytes=          
          504640)                                                               
                                                                                
   4    2       TABLE ACCESS (FULL) OF 'T3' (Cost=6 Card=26559 Bytes=5          
          3118)                                                                 
                                                                                




Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
        143  consistent gets                                                    
        210  physical reads                                                     
          0  redo size                                                          
        378  bytes sent via SQL*Net to client                                   
        499  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed                                                     

SQL> 
SQL> 
SQL> 
SQL> spool off




Is there a way of steering the optimizer towards the hash anti join plan?

SQL query tuning suggestions

March 15, 2008 - 9:38 am UTC

Reviewer: Megala from NJ

Tom:

Following sql query is taking several hours.
Any suggestions on rewriting the query to make it effecient.

Database version: 9.2.0.6

SQL> select BB.CTLACCT, BB.INV_NUM, BB.INV_DT, BB.IVC_LI_TX, BB.IVC_LI_GRS_AM, BB.IVC_LI_DCT_AM,
  2  BB.ADJUSTMENT_TAXES, BB.ADJUSTMENT_AMOUNT, AA.TOTAL_ADJUSTMENT_AMOUNT
  3  from
  4  (select
  5  A.INV_NUM,sum(A.ADJUSTMENT_AMOUNT) AS TOTAL_ADJUSTMENT_AMOUNT
  6  from ( SELECT BDB_BILCYC.INV_NUM INV_NUM,
  7  (Q_LDOL.IVC_LI_GRS_AM + Q_LDOL.IVC_LI_DCT_AM + Q_LDOL.IVC_LI_CNY_TAX_AM + Q_LDOL.IVC_LI_CTY_TAX_AM+Q_LDOL.IVC_LI_ST_TAX_AM) ADJUSTMENT_AMOUNT
  8  FROM BDB_BILCYC, Q_LSEQ, Q_LDOL
  9  where exists (select null from EJ_BILL_INV WHERE EJ_BILL_INV.CTLACCT=BDB_BILCYC.CTLACCT and EJ_BILL_INV.STARTDATE=BDB_BILCYC.CYCLE_START_DT )
 10  and BDB_BILCYC.INV_NUM= Q_LSEQ.IVC_NO
 11  and BDB_BILCYC.INV_NUM= Q_LDOL.IVC_NO
 12  and BDB_BILCYC.INV_TYP_CD='2' ) A
 13  group by A.INV_NUM) AA,
 14  (SELECT BDB_BILCYC.CTLACCT CTLACCT, BDB_BILCYC.INV_NUM INV_NUM, TO_CHAR(BDB_BILCYC.INV_DT,'YYYY-MM-DD HH24:MI:SS') INV_DT, Q_LSEQ.IVC_LI_TX IVC_LI_TX, Q_LDOL.IVC_LI_GRS_AM IVC_LI_GRS_AM, Q_LDOL.IVC_LI_DCT_AM, (Q_LDOL.IVC_LI_CNY_TAX_AM + Q_LDOL.IVC_LI_CTY_TAX_AM + Q_LDOL.IVC_LI_ST_TAX_AM) ADJUSTMENT_TAXES,
 15  (Q_LDOL.IVC_LI_GRS_AM + Q_LDOL.IVC_LI_DCT_AM + Q_LDOL.IVC_LI_CNY_TAX_AM + Q_LDOL.IVC_LI_CTY_TAX_AM+Q_LDOL.IVC_LI_ST_TAX_AM) ADJUSTMENT_AMOUNT
 16  FROM (select CTLACCT, CYCLE_START_DT, INV_TYP_CD, INV_NUM, INV_DT from BDB_BILCYC where INV_TYP_CD='2' )
 17  BDB_BILCYC, Q_LSEQ, Q_LDOL
 18  where exists (select null from EJ_BILL_INV WHERE EJ_BILL_INV.CTLACCT=BDB_BILCYC.CTLACCT and EJ_BILL_INV.STARTDATE=BDB_BILCYC.CYCLE_START_DT )
 19  and BDB_BILCYC.INV_NUM= Q_LSEQ.IVC_NO and BDB_BILCYC.INV_NUM= Q_LDOL.IVC_NO ) BB
 20  where AA.INV_NUM=BB.INV_NUM
 21  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4490 Card=1 Bytes=23
          0)

   1    0   SORT (GROUP BY) (Cost=4490 Card=1 Bytes=230)
   2    1     NESTED LOOPS (Cost=4482 Card=1 Bytes=230)
   3    2       NESTED LOOPS (Cost=4480 Card=1 Bytes=222)
   4    3         NESTED LOOPS (Cost=4477 Card=1 Bytes=146)
   5    4           HASH JOIN (SEMI) (Cost=4474 Card=1 Bytes=121)
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'BDB_BILCYC' (C
          ost=3 Card=1 Bytes=25)

   7    6               NESTED LOOPS (Cost=4440 Card=16 Bytes=1712)
   8    7                 NESTED LOOPS (Cost=4407 Card=11 Bytes=902)
   9    8                   HASH JOIN (SEMI) (Cost=4401 Card=2 Bytes=1
          00)

  10    9                     TABLE ACCESS (FULL) OF 'BDB_BILCYC' (Cos
          t=3262 Card=1776380 Bytes=63949680)

  11    9                     INDEX (FAST FULL SCAN) OF 'EJ_BILL_INV_E
          TL_IDX' (NON-UNIQUE) (Cost=33 Card=192002 Bytes=2688028)

  12    8                   TABLE ACCESS (BY INDEX ROWID) OF 'Q_LDOL'
          (Cost=3 Card=5 Bytes=160)

  13   12                     INDEX (RANGE SCAN) OF 'Q_LDOL_PK' (UNIQU
          E) (Cost=2 Card=5)

  14    7                 INDEX (RANGE SCAN) OF 'BDB_BILCYC_IDX5' (NON
          -UNIQUE) (Cost=2 Card=2)

  15    5             INDEX (FAST FULL SCAN) OF 'EJ_BILL_INV_ETL_IDX'
          (NON-UNIQUE) (Cost=33 Card=192002 Bytes=2688028)

  16    4           TABLE ACCESS (BY INDEX ROWID) OF 'Q_LDOL' (Cost=3
          Card=5 Bytes=125)

  17   16             INDEX (RANGE SCAN) OF 'Q_LDOL_PK' (UNIQUE) (Cost
          =2 Card=5)

  18    3         TABLE ACCESS (BY INDEX ROWID) OF 'Q_LSEQ' (Cost=3 Ca
          rd=7 Bytes=532)

  19   18           INDEX (RANGE SCAN) OF 'Q_LSEQ_PK' (UNIQUE) (Cost=2
           Card=7)

  20    2       INDEX (RANGE SCAN) OF 'Q_LSEQ_PK' (UNIQUE) (Cost=2 Car
          d=7 Bytes=56)



=====
  1* select table_name, num_rows from user_tables where table_name in ('BDB_BILCYC','Q_LSEQ','Q_LDOL','EJ_BILL_INV')
SQL> /

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
BDB_BILCYC                        3552760
EJ_BILL_INV                        192002
Q_LDOL                            1326401
Q_LSEQ                            1907054


Rows from BDB_BILCYC with INV_TYP_CD='2'  :
============================================
SQL> select count(*) from  BDB_BILCYC where INV_TYP_CD='2'  ;

  COUNT(*)
----------
     31108

SQL> @qry_ind
SQL> column column_name format a30
SQL> select table_name, index_name, column_name from user_ind_columns where table_name in
  2  ('BDB_BILCYC','Q_LSEQ','Q_LDOL','EJ_BILL_INV')
  3  order by table_name, index_name, column_position
  4  /

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
BDB_BILCYC                     BDB_BILCYC_ETL_IDX             CTLACCT
BDB_BILCYC                     BDB_BILCYC_ETL_IDX             CYCLE_START_DT
BDB_BILCYC                     BDB_BILCYC_ETL_IDX             INV_NUM
BDB_BILCYC                     BDB_BILCYC_IDX1                CYCLE_START_DT
BDB_BILCYC                     BDB_BILCYC_IDX2                CTLACCT
BDB_BILCYC                     BDB_BILCYC_IDX4                INV_TYP_CD
BDB_BILCYC                     BDB_BILCYC_IDX5                INV_NUM
EJ_BILL_INV                    EEJ_BILL_INV_IDX1              STARTDATE
EJ_BILL_INV                    EEJ_BILL_INV_IDX1              ENDDATE
EJ_BILL_INV                    EEJ_BILL_INV_IDX3              INV_NUM
EJ_BILL_INV                    EEJ_BILL_INV_IDX4              SITE_ID
EJ_BILL_INV                    EEJ_BILL_INV_IDX4              BEID
EJ_BILL_INV                    EJ_BILL_INV_ETL_IDX            CTLACCT
EJ_BILL_INV                    EJ_BILL_INV_ETL_IDX            STARTDATE
EJ_BILL_INV                    EJ_BILL_INV_FBIDX              INV_NUM
EJ_BILL_INV                    EJ_BILL_INV_FBIDX              SYS_NC00059$
EJ_BILL_INV                    EJ_BILL_INV_IDX5               CTLACCT
EJ_BILL_INV                    EJ_BILL_INV_IDX6               SYS_NC00058$
Q_LDOL                         Q_LDOL_PK                      IVC_NO
Q_LDOL                         Q_LDOL_PK                      IVC_LI_SEQ_NO
Q_LSEQ                         Q_LSEQ_PK                      IVC_NO
Q_LSEQ                         Q_LSEQ_PK                      IVC_LI_SEQ_NO

22 rows selected.



Tom Kyte

Followup  

March 15, 2008 - 9:58 am UTC

are the card= values in the plan close to reality. I'm guessing "no", else this would be going "pretty fast"

Query tuning

September 09, 2020 - 2:51 am UTC

Reviewer: Rich from USA

Can you please help with tuning this query.to make it more efficient.

It returns only few rows.

PRICING_DATE on EST.FFINCPRC is the partition key column. This is on Oracle 12.2 on Exadata.

Also noticed below-
1)
I am seeing the same query execution running slow with varying inputs eventhough stats are uptodate.

2) On 1st execution, Query always takes longer. Any way to get consistent response time even on 1st execution.


SQL> WITH LAST_TICKS_BY_DAY AS (
SELECT MAX(LAST_PRICE_TIME) AS LAST_PRICE_TIME, MAX(PRICING_DATE) AS PRICING_DATE
FROM TEST.FFINCPRC A
WHERE A.INSTRUMENT_ID = 8553958
AND A.PRICING_DATE BETWEEN TO_DATE('2020-08-24', 'YYYY-MM-DD') AND TO_DATE('2020-08-28', 'YYYY-MM-DD')
AND LAST_PRICE_TIME >= TO_TIMESTAMP('24-AUG-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF PM')
AND LAST_PRICE_TIME <= TO_TIMESTAMP('27-AUG-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF PM')
AND A.PRICE_TYPE_ID in(5 , 6 )
GROUP BY TRUNC(LAST_PRICE_TIME)
)
SELECT p.INSTRUMENT_ID, p.SOURCE, p.BID_PR 2 ICE, p.LAST_PRICE_TIME, p.ASK_PRICE,
p.MID_PRICE, p.BID_SPREAD, p.ASK_SPREAD, p.MID_SPREAD, p.BID_YIELD, p.ASK_YIELD, p.MID_YIELD,
p.ASK_SPREAD_CURVE, p.T_SPREAD, p.MID_T_SPREAD, p.ASK_T_SPREAD, p.TEST_CONDITION_CODE,
p.CURVE_YIELD, p.PRICE_ 3 TYPE, p.BID_SPREAD_CURVE, p.MID_SPREAD_CURVE, p.PRICE_TYPE_ID,
p.BID_DISCOUNT_RATE, p.ASK_DISCOUNT_RATE, p.EOD_LATE_BID_PRICE_DELTA, p.SPREAD,
a.PRICING_SPREAD_TYPE, a.BENCHMARK_NAME, a.TBA_BACK_BENCH, a.DIMINIMIS_ELIGIBLE_FLAG,
a.HALF_DEMIN 4 IMIS_ELIGIBLE_FLAG, a.BENCHMARK_TBA, a.PAYUP
FROM TEST.FFINCPRC p
LEFT JOIN TEST.FPRCASSUMP a
ON p.ASSUMP_ID = a.ASSUMP_ID
JOIN LAST_TICKS_BY_DAY B
ON P.LAST_PRICE_TIME = B.LAST_PRICE_TIME
WHERE p.INSTRUMENT_ID = 8553958
AND p.PR 5 ICING_DATE = B.PRICING_DATE
AND p.PRICE_TYPE_ID in(5, 6)
ORDER BY A.LAST_PRICE_TIME ASC 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
27 /



Elapsed: 00:00:00.24

Execution Plan
----------------------------------------------------------
Plan hash value: 86494900

--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 46 (5)| 00:00:01 | | |
| 1 | SORT ORDER BY | | 1 | 207 | 46 (5)| 00:00:01 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 207 | 45 (3)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 168 | 41 (3)| 00:00:01 | | |
| 4 | VIEW | | 1 | 22 | 36 (3)| 00:00:01 | | |
| 5 | HASH GROUP BY | | 1 | 29 | 36 (3)| 00:00:01 | | |
| 6 | PARTITION RANGE ITERATOR | | 1 | 29 | 35 (0)| 00:00:01 | 2428 | 2432 |
| 7 | INLIST ITERATOR | | | | | | | |
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FFINCPRC | 1 | 29 | 35 (0)| 00:00:01 | 2428 | 2432 |
| 9 | SORT CLUSTER BY ROWID | | 1 | | 17 (0)| 00:00:01 | | |
|* 10 | INDEX RANGE SCAN | FF_INC_PRICE_IDX1 | 1 | | 17 (0)| 00:00:01 | 2428 | 2432 |
| 11 | PARTITION RANGE ITERATOR | | 1 | 146 | 5 (0)| 00:00:01 | KEY | KEY |
| 12 | INLIST ITERATOR | | | | | | | |
|* 13 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | FFINCPRC | 1 | 146 | 5 (0)| 00:00:01 | KEY | KEY |
|* 14 | INDEX RANGE SCAN | FF_INC_PRICE_IDX1 | 1 | | 4 (0)| 00:00:01 | KEY | KEY |
| 15 | TABLE ACCESS BY GLOBAL INDEX ROWID | FPRCASSUMP | 1 | 39 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 16 | INDEX UNIQUE SCAN | FPRCASSUMP_PK | 1 | | 3 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------------

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

8 - filter("A"."PRICING_DATE"<=TO_DATE(' 2020-08-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
10 - access(("A"."PRICE_TYPE_ID"=5 OR "A"."PRICE_TYPE_ID"=6) AND "A"."INSTRUMENT_ID"=8553958 AND
"LAST_PRICE_TIME">=TO_TIMESTAMP('24-AUG-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF AM') AND
"LAST_PRICE_TIME"<=TO_TIMESTAMP('27-AUG-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF AM'))
13 - filter("P"."PRICING_DATE"="B"."PRICING_DATE")
14 - access(("P"."PRICE_TYPE_ID"=5 OR "P"."PRICE_TYPE_ID"=6) AND "P"."INSTRUMENT_ID"=8553958 AND
"P"."LAST_PRICE_TIME"="B"."LAST_PRICE_TIME")
16 - access("P"."ASSUMP_ID"="A"."ASSUMP_ID"(+))


Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
52412 consistent gets
0 physical reads
0 redo size
3308 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
35 sorts (memory)
0 sorts (disk)
3 rows processed

SQL>


2nd time execution with different inputs to the same query:
==========================================================


WITH LAST_TICKS_BY_DAY AS (
SELECT MAX(LAST_PRICE_TIME) AS LAST_PRICE_TIME, MAX(PRICING_DATE) AS PRICING_DATE
FROM TEST.FFINCPRC A
WHERE A.INSTRUMENT_ID = 36316463
AND A.PRICING_DATE BETWEEN TO_DATE('2020-08-24', 'YYYY-MM-DD') AND TO_DATE('2020-08-28', 'YYYY-MM-DD')
AND LAST_PRICE_TIME >= TO_TIMESTAMP('24-AUG-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF PM')
AND LAST_PRICE_TIME <= TO_TIMESTAMP('27-AUG-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF PM')
AND A.PRICE_TYPE_ID in(5 , 6 )
GROUP BY TRUNC(LAST_PRICE_TIME)
)
SELECT p.INSTRUMENT_ID, p.SOURCE, p.BID_PRICE, p.LAST_PRICE_TIME, p.ASK_PRICE,
p.MID_PRICE, p.BID_SPREAD, p.ASK_SPREAD, p.MID_SPREAD, p.BID_YIELD, p.ASK_YIELD, p.MID_YIELD,
p.ASK_SPREAD_CURVE, p.T_SPREAD, p.MID_T_SPREAD, p.ASK_T_SPREAD, p.TEST_CONDITION_CODE,
p.CURVE_YIELD, p.PRICE_TYPE, p.BID_SPREAD_CURVE, p.MID_SPREAD_CURVE, p.PRICE_TYPE_ID,
p.BID_DISCOUNT_RATE, p.ASK_DISCOUNT_RATE, p.EOD_LATE_BID_PRICE_DELTA, p.SPREAD,
a.PRICING_SPREAD_TYPE, a.BENCHMARK_NAME, a.TBA_BACK_BENCH, a.DIMINIMIS_ELIGIBLE_FLAG,
a.HALF_DEMINIMIS_ELIGIBLE_FLAG, a.BENCHMARK_TBA, a.PAYUP
FROM TEST.FFINCPRC p
LEFT JOIN TEST.FPRCASSUMP a
ON p.ASSUMP_ID = a.ASSUMP_ID
JOIN LAST_TICKS_BY_DAY B
ON P.LAST_PRICE_TIME = B.LAST_PRICE_TIME
WHERE p.INSTRUMENT_ID = 36316463
AND p.PRICING_DATE = B.PRICING_DATE
AND p.PRICE_TYPE_ID in(5, 6)
ORDER BY A.LAST_PRICE_TIME ASC
/


Elapsed: 00:00:13.17

Execution Plan
----------------------------------------------------------
Plan hash value: 86494900

--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 46 (5)| 00:00:01 | | |
| 1 | SORT ORDER BY | | 1 | 207 | 46 (5)| 00:00:01 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 207 | 45 (3)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 168 | 41 (3)| 00:00:01 | | |
| 4 | VIEW | | 1 | 22 | 36 (3)| 00:00:01 | | |
| 5 | HASH GROUP BY | | 1 | 29 | 36 (3)| 00:00:01 | | |
| 6 | PARTITION RANGE ITERATOR | | 1 | 29 | 35 (0)| 00:00:01 | 2428 | 2432 |
| 7 | INLIST ITERATOR | | | | | | | |
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FFINCPRC | 1 | 29 | 35 (0)| 00:00:01 | 2428 | 2432 |
| 9 | SORT CLUSTER BY ROWID | | 1 | | 17 (0)| 00:00:01 | | |
|* 10 | INDEX RANGE SCAN | FF_INC_PRICE_IDX1 | 1 | | 17 (0)| 00:00:01 | 2428 | 2432 |
| 11 | PARTITION RANGE ITERATOR | | 1 | 146 | 5 (0)| 00:00:01 | KEY | KEY |
| 12 | INLIST ITERATOR | | | | | | | |
|* 13 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | FFINCPRC | 1 | 146 | 5 (0)| 00:00:01 | KEY | KEY |
|* 14 | INDEX RANGE SCAN | FF_INC_PRICE_IDX1 | 1 | | 4 (0)| 00:00:01 | KEY | KEY |
| 15 | TABLE ACCESS BY GLOBAL INDEX ROWID | FPRCASSUMP | 1 | 39 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 16 | INDEX UNIQUE SCAN | FPRCASSUMP_PK | 1 | | 3 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------------

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

8 - filter("A"."PRICING_DATE"<=TO_DATE(' 2020-08-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
10 - access(("A"."PRICE_TYPE_ID"=5 OR "A"."PRICE_TYPE_ID"=6) AND "A"."INSTRUMENT_ID"=36316463 AND
"LAST_PRICE_TIME">=TO_TIMESTAMP('24-AUG-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF AM') AND
"LAST_PRICE_TIME"<=TO_TIMESTAMP('27-AUG-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF AM'))
13 - filter("P"."PRICING_DATE"="B"."PRICING_DATE")
14 - access(("P"."PRICE_TYPE_ID"=5 OR "P"."PRICE_TYPE_ID"=6) AND "P"."INSTRUMENT_ID"=36316463 AND
"P"."LAST_PRICE_TIME"="B"."LAST_PRICE_TIME")
16 - access("P"."ASSUMP_ID"="A"."ASSUMP_ID"(+))


Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
56697 consistent gets
229467 physical reads
0 redo size
3287 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10724 sorts (memory)
0 sorts (disk)
3 rows processed

Connor McDonald

Followup  

September 10, 2020 - 6:51 am UTC

please ask a fresh question, and include output from a gather_plan_statistics hint. (Examples of that on this site)

Continuation..

September 09, 2020 - 3:10 am UTC

Reviewer: Rich from USA

Sorry, forgot to mention that, the same query that ran fine in the session just before and when rerunning the query right after in the same session takes for ever and plan is also changing. There was no change in the data in the partition during the run. Plan is changing to HASH and PARTITION RANGE ALL.. Any idea what could be causing this ?