Forum Stats

  • 3,782,592 Users
  • 2,254,669 Discussions
  • 7,880,131 Comments

Discussions

Suggest appropriate index

3062871
3062871 Member Posts: 1
edited Nov 1, 2015 12:39PM in General Database Discussions

Hi,

 

Can you please suggest an index on BEACON.CASES in the below query.  Full table scan happens on BEACON.CASES.   I tried to create index on CASESDEFID and it did not work.  Even tried CASEDEFID,CASEID and PXCREATEOPERATOR but still couldn’t avoid the full scan.  I did gather statistics after index creation.

  
SELECT c.caseid,
  
 c.pxcreateoperator,
  
 ma.functiontypeid,
  
 fn.FUNCTIONTYPE,
  
 ct.casetype,
  
 ct.casetypeid
  
 FROM BEACON.MASTER_FUNCTION ma,
  
 beacon.function_type fn,
  
 beacon.cases c,
  
 BEACON.CASE_TYPE CT
  
 WHERE MA.FUNCTIONTYPEID = FN.FUNCTIONTYPEID
  
 AND MA.CASETYPEID = CT.CASETYPEID
  
 AND c.casedefid = ma.casedefid(+);
  

 

Thanks,

Sudha.

Tagged:

Answers

  • Srini Chavali-Oracle
    Srini Chavali-Oracle Member Posts: 29,596 Blue Diamond
    edited Oct 30, 2015 8:08PM

    MOS Doc 67522.1 may help

    Without knowing various details (database version, init.ora parameters, number of rows in the databases involved, selectivity of the columns in the tables etc), suggesting an index is impossible

  • wbriceno
    wbriceno Member Posts: 64
    edited Oct 30, 2015 8:18PM

    Hi,

    I'm watching the SELECT you send. You are doing an outer-join on all tables.

    When yo do that the optimizer never use an index and never is going to use an index; is like doing a cartesian product.

    If you want the optimizer use an index, you have to include in WHERE clause one o more filters for your data.

    Where are you going to use this SELECT statement? It is part of a view?

    Regards.

  • Unknown
    edited Oct 30, 2015 10:46PM
    I'm watching the SELECT you send. You are doing an outer-join on all tables.
    When yo do that the optimizer never use an index and never is going to use an index; is like doing a cartesian product.
    

    Huh? A cartesian product would occur if the tables weren't joined at all or were joined on columns that don't even match each other.

    The joins OP is doing appear to be on matching columns.

  • wbriceno
    wbriceno Member Posts: 64
    edited Oct 30, 2015 10:51PM

    Hi,

    Look at the SELECT statement. He is using "(+)"... it's work like an outer join. But look, the outer is for all tables.

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    edited Oct 31, 2015 5:11PM

    > Full table scan happens on BEACON.CASES. 

    You're selecting all the rows from that table.  Why on earth do you want to use an index?

  • Unknown
    edited Oct 31, 2015 8:53PM
    Look at the SELECT statement. He is using "(+)"... it's work like an outer join. But look, the outer is for all tables.
    

    So? Maybe you are confusing an OUTER join with a CARTESIAN product.

    Yes - the 'outer is for all tables'. Which means that the TABLES ARE JOINED!

    A CARTESIAN product is when the tables are NOT joined. See the Oracle docs

    https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm

    Cartesian Products

    If two tables in a join query have no join condition, then Oracle Database returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product. If a query joins three or more tables and you do not specify a join condition for a specific pair, then the optimizer may choose a join order that avoids producing an intermediate Cartesian product.
    

    An OUTER join between a table with 10 rows and a table with 5 rows results in 10 rows.

    A CARTESIAN PRODUCT between those two tables results in 50 rows.

  • JohnWatson2
    JohnWatson2 Member Posts: 4,362 Silver Crown
    edited Nov 1, 2015 3:56AM

    If the join from C to MA is nested loop or hash, your CASES.CASEDEFID index won't be of any use. It can be used if the CBO chooses a merge join. For example,

    orclz>

    orclz> set autot trace exp

    orclz> select ename,dname from dept left outer join emp using (deptno);

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 2251696546

    ----------------------------------------------------------------------------------------

    | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time    |

    ----------------------------------------------------------------------------------------

    |  0 | SELECT STATEMENT            |        |    15 |  330 |    6  (17)| 00:00:01 |

    |  1 |  MERGE JOIN OUTER            |        |    15 |  330 |    6  (17)| 00:00:01 |

    |  2 |  TABLE ACCESS BY INDEX ROWID| DEPT    |    4 |    52 |    2  (0)| 00:00:01 |

    |  3 |    INDEX FULL SCAN          | PK_DEPT |    4 |      |    1  (0)| 00:00:01 |

    |*  4 |  SORT JOIN                  |        |    14 |  126 |    4  (25)| 00:00:01 |

    |  5 |    TABLE ACCESS FULL        | EMP    |    14 |  126 |    3  (0)| 00:00:01 |

    ----------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

      4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))

          filter("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))

    Note

    -----

      - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

    orclz>

  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge
    edited Nov 1, 2015 7:12AM

    since you have no selection criteria in your where clause a full table scan (FTS) and a hash join is the normal way to process this query. There is a common prejudice that full tables scans are evil. This is nonsense.

    Are you sure that it is really the FTS that is taking most of  the time? Did you check it?

    Since there is no good reason (selection criteria)  to use the index in an range scan an option you can hope for by creating an index is a fast full index scan . Therefore you should include CASEDEFID, PXCREATEOPERATOR , CASEID.

    Do not forget the compress the index. After all, the index contains the rowid which the table does not. Thus it might still be better to use the table if the table is smaller than the index.

  • John Spencer
    John Spencer Member Posts: 8,567 Bronze Crown
    edited Nov 1, 2015 12:39PM

    Are you sure that the query gives you the correct answer?  What it says is give me all of the rows from cases and outer join to master_function based on casedefid, if there is no matching row in master_function then make the columns from master_function null.  It then joins to function_type and case_type based on columns from master_function.  All of the rows from cases that did not mach in the initial join to master_function which have null values for  the master_function columns will get thrown away, because null will never match anything in function_type and case_type.

    Your query, as posted is equivalent to a regular join, so the outer join between cases and master_function is not required.  If you really do want all of the rows from cases whether or not there is a match in master_function, then I think you need something more like:

    select c.caseid, c.pxcreateoperator, maj.functiontypeid, maj.functiontype,
          maj.casetype, maj.casetypeid
    from beacon.cases c
      left join (select ma.functiontypeid, ma.casedefid, fn.functiontype,
                        ct.casetype, ct.casetypeid,
                  from beacon.master_function ma
                    join beacon.function_type fn
                        on ma.functiontypeid = fn.functiontypeid
                    join beacon.case_type ct
                        on ma.casetypeid = ct.casetypeid) maj
          on c.casedefid = maj.casedefid
    
    

    John

This discussion has been closed.