Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Join cardinality and query tuning.

RE: Join cardinality and query tuning.

From: Charu Joshi <joshic_at_mahindrabt.com>
Date: Wed, 3 Nov 2004 11:18:05 +0530
Message-ID: <MHEAIPLKCACENJKNJIALCEMOCIAA.joshic@mahindrabt.com>

Hi Brad,

[BO]

given the cardinality of the larger index which has only 12 distint values and that there are a massive number of nulls it makes scense that there would be a fast full scan as the range scan would not be valid. I belive a range scan works best when you have a high level of cardinality.
[BO]

Ok, I think I have used wrong query to put forward my case. Let me tweak it a bit.

select

    T29238.*
from
W_LOV_D T230600,
W_ACTIVITY_F T29238
where
T29238.X_BT_OUTCOME_AREA_WID = T230600.ROW_WID and T230600.VAL in ('Save-No', 'Save-Yes')

The plan is:



| Id | Operation | Name | Rows | Cost |
|   0 | SELECT STATEMENT     |               |  3274K| 33098 |
|   1 |  HASH JOIN           |               |  3274K| 33098 |
|   2 |   TABLE ACCESS FULL  | W_LOV_D       |    17 |    13 |
|   3 |   TABLE ACCESS FULL  | W_ACTIVITY_F  |  3452K| 32981 |
--------------------------------------------------------------

Now would you agree that RANGE SCAN will be preferable instead of FTS on W_ACTIVITY_F? I have tried the query both ways and believe me RANGE SCAN is FAR better than FTS in this as well as earlier query. I am too lazy to extract and write the evidence here, but if you doubt I will send the query times, LIOs and other statistics.

[BO]

Now this table has over 1.3 million nulls in the indexed column and this makes this index have a very low selectability and thus a very innefficient index. The root of your problem is the data. It doesn't make much sence to select from a table using a column that has 90% plus nulls in that column....do any of the results of the query have a NULL.
[BO]

It's 13 million. Probably you are right Brad that the data is problematic, but it's not in my hands. I will ask this to the developers. My job is to tune the inefficient queries that the developers write.

[BO]

How many NULLS satisfy this where clause WHERE T29238.X_BT_OUTCOME_AREA_WID = T230600.ROW_WID
[BO]

NONE. NULLs NEVER satisfy equality condition.

[BO]

I would try...something like

select

    count(*)
from
W_LOV_D T230600,
W_ACTIVITY_F T29238
where
T29238.X_BT_OUTCOME_AREA_WID IS NOT NULL AND T29238.X_BT_OUTCOME_AREA_WID = T230600.ROW_WID and T230600.VAL in ('Save-No', 'Save-Yes')
[BO]

Good point; I had already tried that. The plan is same as above. If you check the estimated cardinality for W_ACTIVITY_F in the original explain plan, you can see it already eliminates NULLs while estimating number of records. Hence adding the IS NOT NULL condition won't (and didn't) make any difference.

Thanks & Regards,
Charu.

-----Original Message-----
From: Charu Joshi [mailto:joshic_at_mahindrabt.com] Sent: Tuesday, November 02, 2004 12:27 AM To: Odland, Brad
Subject: RE: Join cardinality and query tuning.

Hi Brad,

Sorry I didn't make it clear in my mail that there is an index on the VAL column of W_LOV_D. However, it does not matter whether it's an FTS or a RANGE SCAN on W_LOV_D table, since it's a fairly small table. What does matter is the Fast Full scan on W_ACTIVITY_F_N5 index. It should be RANGE SCAN for best results.

The best execution plan for this query is FTS/RANGE SCAN of W_LOV_D followed by *Nested Loops join* into the W_ACTIVITY_F table using RANGE SCAN on the W_ACTIVITY_F_N5 index. I have actually tested this premise by giving hints to the query:

select /*+ ORDERED

           USE_NL(T29238)
           INDEX(T29238 W_ACTIVITY_F_N5)
           INDEX(T230600 W_LOV_D_M3) */

    count(*)
from
W_LOV_D T230600,
W_ACTIVITY_F T29238
where
T29238.X_BT_OUTCOME_AREA_WID = T230600.ROW_WID and T230600.VAL in ('Save-No', 'Save-Yes')

This query regularly finishes in 2 seconds whereas the original query takes 9+ seconds. In this case 9+ seconds seems tolerable time, but the FFS on W_ACTIVITY_F converts to FTS in bigger queries and that really hurts the performance. Any ideas?

Thanks & regards,
Charu.

-----Original Message-----
From: Odland, Brad [mailto:Brad.Odland_at_quadtechworld.com] Sent: Monday, November 01, 2004 9:37 PM
To: joshic_at_mahindrabt.com; oracle-l_at_freelists.org Subject: RE: Join cardinality and query tuning.

It would help to actually have an index on W_LOV_D....and make sure that you reference all the keys in the index if there are more than one...you can't do a index range scan without a index... (:>P)

Nothing in your messege says that there is an index for the table W_LOV_D

The TABLE, W_ACTIVITIY_F has a INDEX named W_ACTIVITY_F_N5 according to your explain plan taht is being accessed. The TABLE is not being accessed. The Index is scanned for the rowid to fetch the data....

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]

Sent: Monday, November 01, 2004 9:22 AM
To: oracle-l_at_freelists.org
Subject: Join cardinality and query tuning.

Hi all,

Please see the plan below. The estimated cardinality of the join between the two tables is 3274K. This doesn't seem to tally with the figure I have derived from the statistics in the database. Where am I going wrong?



| Id | Operation | Name | Rows | Cost |
|   0 | SELECT STATEMENT       |                  |     1 |   543 |
|   1 |  SORT AGGREGATE        |                  |     1 |       |
|   2 |   HASH JOIN            |                  |  3274K|   543 |
|   3 |    TABLE ACCESS FULL   | W_LOV_D          |    17 |    13 |
| 4 | INDEX FAST FULL SCAN| W_ACTIVITY_F_N5 | 3452K| 525 |

Let me try to elaborate on the problem:

The SQL statement is:

select count(*)
from
W_ACTIVITY_F T29238,
W_LOV_D T230600
where
T29238.X_BT_OUTCOME_AREA_WID = T230600.ROW_WID and T230600.VAL in ('Save-No', 'Save-Yes')

I have used the following formula for calculating the join selectivity:

JS = (1/MAX(NDV(T1.c1), NDV(T2.c1)))
     * (Card(T1) - Num_Nulls(T1.c1)/Card(T1))
     * (Card(T2) - Num_Nulls(T2.c1)/Card(T2))

Join Cardinality = SelectedRows(T1)* SelectedRows(T2)* JS where SelectedRows(T) = Card(T) * FilterFactor

(From Mr. Breitling's famous paper on 10053 event).

The database statistics are:

siebel_at_VOLANL>SELECT table_name, num_rows FROM user_tables   2 where table_name IN ('W_ACTIVITY_F', 'W_LOV_D');

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
W_ACTIVITY_F                     16858500
W_LOV_D                             24000


siebel_at_VOLANL>SELECT table_name, column_name, num_distinct, num_nulls   2 FROM user_tab_col_statistics
  3 WHERE (table_name='W_ACTIVITY_F' AND column_name = 'X_BT_OUTCOME_AREA_WID')
  4 OR ( table_name='W_LOV_D' AND column_name = 'ROW_WID');

TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS

--------------- --------------------- ------------ ----------
W_ACTIVITY_F    X_BT_OUTCOME_AREA_WID           12   13406400
W_LOV_D         ROW_WID                      22720          0


So the Join Selectivity base on this information would be:

JS =(1/MAX(12,22720))*((16858500-13406400)/16858500)* ((24000 - 0)/24000)

And the Join Cardinality will be

JC = 9.0127E-06 * 17 * 3274K

where 17 = SelectedRows(W_LOV_D) - as per the explain plan.

    3274K = Card(W_ACTIVITY_F) -
Num_Nulls(W_ACTIVITY_F.X_BT_OUTCOME_AREA_WID)

So Oracle should have estimated it to be 529. But it has estimated it 3274K. Why?? What other factor is influencing the join cardinality estimate??

Now here's part 2:

I think the reason Oracle decides to do an FTS and Index FFS on W_LOV_D and W_ACTIVITY_F tables respectively, is because of the incorrect join cardinality estimate. The query actually returns only 67 rows!! The best execution plan for this query is obviously INDEXED RANGE SCAN of W_LOV_D followed by NESTED LOOPS join into W_ACTIVITY_F with INDEXED RANGE SCAN as the table access method. I cannot provide any hint as the front-end dynamically generates the queries. How do I manipulate the statistics so that Oracle will choose this execution plan?

Many thanks & regards,
Charu.



Disclaimer:

This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited.



Visit us at http://www.mahindrabt.com
--
http://www.freelists.org/webpage/oracle-l


*********************************************************
Disclaimer:

This message (including any attachments) contains
confidential information intended for a specific
individual and purpose, and is protected by law.
If you are not the intended recipient, you should
delete this message and are hereby notified that
any disclosure, copying, or distribution of this
message, or the taking of any action based on it,
is strictly prohibited.

*********************************************************
Visit us at http://www.mahindrabt.com



*********************************************************
Disclaimer:          

This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.

*********************************************************
Visit us at http://www.mahindrabt.com

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 02 2004 - 23:44:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US