| Oracle join problem (full table scan) [message #65928] |
Tue, 01 February 2005 11:49  |
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 #65931 is a reply to message #65928] |
Tue, 01 February 2005 15:30  |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
