Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Oracle join problem (full table scan)

be_dbbe_db Member Posts: 1
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

Comments

  • infidelinfidel Member Posts: 2,900
    : 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;

    Not being a DBA, and assuming that group_id and metric_id are the unique primary keys of their respective tables, my best guess is to put a unique index on lu_metric_group (group_id, metric_id); put a foreign key constraint on lu_metric_group (metric_id) that references fact_captured_metric (metric_id); and a primary key constraint/index on fact_captured_metric (metric_id).


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

Sign In or Register to comment.