Home » RDBMS Server » Performance Tuning » Oracle join problem (full table scan)
Oracle join problem (full table scan) [message #65928] Tue, 01 February 2005 11:49 Go to next message
John Smith
Messages: 25
Registered: February 2002
Junior Member
We are having problems with full table scans occuring on seemingly simple joins between tables. We have tried B-tree and Bitmap indexing strategies with little luck. Here is the background:

* Oracle 9i
* fact_captured_metric table with ~30 million rows
* lu_metric_group table with ~3000 rows
* metric_id approximately 400 distinct values
* group_id approximately 90 distinct values
* one metric_id can belong to multiple group_ids, and vice versa
* lu_metric_group has a primary key of group_id, metric_id
* have tried using B-tree and Bitmap indexes on all columns referenced in the SQL below, with no luck.

We often have only 3-5 metric in a single group, so the most logical course of action would be to determine which metric IDs are contained in lu_metric_group for a given group ID BEFORE scanning the fact_captured_metric table. Unfortunately, the entire fact table is scanned as step #1. Ideas?

SQL> set autotrace trace expl stat;
SQL> SELECT SUM(metric_value)
2 FROM fact_captured_metric a, lu_metric_group b
3 WHERE a.metric_id = b.metric_id
4 AND b.group_id = 5;

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

1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=10142 Card=771197 Bytes=8483167)
3 2 TABLE ACCESS (FULL) OF 'FACT_CAPTURED_METRIC' (Cost=10
142 Card=23603793 Bytes=118018965)

4 2 INDEX (UNIQUE SCAN) OF 'SYS_C003306' (UNIQUE)

Statistics
----------------------------------------------------------
126 recursive calls
0 db block gets
23670660 consistent gets
47214 physical reads
0 redo size
387 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
Re: Oracle join problem (full table scan) [message #65929 is a reply to message #65928] Tue, 01 February 2005 11:54 Go to previous messageGo to next message
oracle_haterz
Messages: 1
Registered: February 2005
Junior Member
30 million rows? What you tryin to do split the atom? Slow it down Speed Racer...
Re: Oracle join problem (full table scan) [message #65931 is a reply to message #65928] Tue, 01 February 2005 15:30 Go to previous messageGo to next message
Jonas Rosenthal
Messages: 4
Registered: January 2005
Junior Member
Hello,

are you sure the column metric_id of the table fact_captured_metric is a leading column of an index for that table? Your results seem to indicate there is no index for that column on that table.

The key is the selectivity of metric_id on fact_captured_metric. You don't indicate the amount of distinct rows here. Check your column statistics.

PS - if you have any doubts about the num_distinct and you want to do something different, try this query:
select
avg(count(*),
max(count(*),
min(count(*),
stddev(count(*)
from tablea
group
by column_a.

This is an old fashioned query which I modified that checks selectivity (number of rows returned per distinct value). Run it in parallel if you need. Avg is for average selectivity. Max gives you the worst case scenario. Min is the best case scenario and stddev is the skewing factor. The higher stddev is, the less reliable the performance of the query. That's why cost based is so good when you are able to use histograms. It accounts for selectivity variances to pick different paths.

Jonas Rosenthal
Re: Oracle join problem (full table scan) [message #65937 is a reply to message #65931] Wed, 02 February 2005 05:55 Go to previous message
John Smith
Messages: 25
Registered: February 2002
Junior Member
Following the suggestions in this article resolved the issue. Adjustments needed to be made to the way the CBO estimated execution times. Settings of 90 and 50 made a significant difference.

http://www.evdbt.com/SearchIntelligenceCBO.doc
Previous Topic: Detrimental effect of many extents
Next Topic: Joining tables - Compare multiple columns of a table with a Single column of oth
Goto Forum:
  


Current Time: Wed Jan 27 10:35:41 CST 2021