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  |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Fri May 02 09:22:16 CDT 2025
|