This content has been marked as final. Show 7 replies
What are the query plans?
Why does the query return 0 rows? It's possible that Oracle is doing the sort that you request when you add a DISTINCT before applying whatever predicate eliminates all the rows. If you post the query plans, that should be clear. It seems odd, though, that you would have a predicate that eliminates all the rows from the result set...
Here is the plan. VW1 is the view and created on 5 tables, TAB1, TAB2, TAB3, TAB4 and TAB5. VW1 has been joined with TAB01 table.
SELECT STATEMENT CHOOSECost: 589,872 Bytes: 639,331 Cardinality: 77
22 HASH UNIQUE Cost: 589,872 Bytes: 639,331 Cardinality: 77
21 MERGE JOIN Cost: 589,871 Bytes: 639,331 Cardinality: 77
17 VIEW VIEW VW1 Cost: 589,868 Bytes: 3,026,654,130 Cardinality: 365,097
16 SORT ORDER BY Cost: 589,868 Bytes: 123,037,689 Cardinality: 365,097
15 NESTED LOOPS OUTER Cost: 573,427 Bytes: 123,037,689 Cardinality: 365,097
12 HASH JOIN RIGHT OUTER Cost: 556,567 Bytes: 112,084,779 Cardinality: 365,097
2 TABLE ACCESS BY INDEX ROWID TABLE TAB1 Cost: 27 Bytes: 123,522 Cardinality: 2,422
1 INDEX FULL SCAN INDEX (UNIQUE) TAB1_PK Cost: 4 Cardinality: 2,422
11 NESTED LOOPS
9 NESTED LOOPS Cost: 556,538 Bytes: 93,464,832 Cardinality: 365,097
7 HASH JOIN Cost: 191,191 Bytes: 16,064,268 Cardinality: 365,097
4 TABLE ACCESS BY INDEX ROWID TABLE TAB2 Cost: 5,375 Bytes: 6,571,746 Cardinality: 365,097
3 INDEX FULL SCAN INDEX (UNIQUE) TAB2_PK Cost: 472 Cardinality: 365,097
6 TABLE ACCESS BY INDEX ROWID TABLE TAB3 Cost: 168,252 Bytes: 285,585,482 Cardinality: 10,984,057
5 INDEX FULL SCAN INDEX TAB3_IDX Cost: 10,618 Cardinality: 10,984,057
8 INDEX UNIQUE SCAN INDEX (UNIQUE) TAB4_PK Cost: 0 Cardinality: 1
10 TABLE ACCESS BY INDEX ROWID TABLE TAB4 Cost: 1 Bytes: 212 Cardinality: 1
14 TABLE ACCESS BY INDEX ROWID TABLE TAB5 Cost: 1 Bytes: 30 Cardinality: 1
13 INDEX UNIQUE SCAN INDEX (UNIQUE) TAB5_PK Cost: 0 Cardinality: 1
20 SORT JOIN Cost: 3 Bytes: 1,001 Cardinality: 77
19 TABLE ACCESS BY INDEX ROWID TABLE TAB01 Cost: 2 Bytes: 1,001 Cardinality: 77
18 INDEX RANGE SCAN INDEX TAB01_IDX Cost: 1 Cardinality: 77
It's a little difficult to follow that plan. Can you take a look at the entry from the forum FAQ on When your query takes too long ...? That walks through how to generate a more readable query plan. You'll also need to use the \
tag before and after the plan to preserve white space and make the plan easier for us to read. Is this the plan for the fast query? Or for the slow query? Why does the query return 0 rows? As I said previously when I asked this question, that seems pretty unusual. Justin
Thanks a lot Justin and I am very sorry for not providing information that you have asked.
1. The plan I have provided is for long running query (as I said, result comes in fraction of a second without DISTINCT),
2. Query is not returning rows for given filter criteria (i.e., a.col1 = 1).
I am unable to keep white space in the plan. In the "Preview" tab, white space is getting removed even though I kept white space/tabs.
I am not getting how to keep tags for the white space from the link that you have provided. I don't have access to the server for trace/tkprof.
Now I got it Justin..Thanks. Can you please look into it and give suggestions.
SELECT STATEMENT [CHOOSE] Cost = 589872 HASH UNIQUE MERGE JOIN VIEW VW1 SORT ORDER BY NESTED LOOPS OUTER HASH JOIN RIGHT OUTER TABLE ACCESS BY INDEX ROWID TAB1 [ANALYZED] INDEX FULL SCAN TAB1_PK [ANALYZED] NESTED LOOPS NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID TAB2 [ANALYZED] INDEX FULL SCAN TAB2_PK [ANALYZED] TABLE ACCESS BY INDEX ROWID TAB3 [ANALYZED] INDEX FULL SCAN TAB3_IDX [ANALYZED] INDEX UNIQUE SCAN TAB4_PK [ANALYZED] TABLE ACCESS BY INDEX ROWID TAB4 [ANALYZED] TABLE ACCESS BY INDEX ROWID TAB5 [ANALYZED] INDEX UNIQUE SCAN TAB5_PK [ANALYZED] SORT JOIN TABLE ACCESS BY INDEX ROWID TAB01 [ANALYZED] INDEX RANGE SCAN TAB01_IDX [ANALYZED]