Re: odd explain plan

The optimizer doesn't seem to have recognised the query as a possible target for STAR or STAR TRANSFORMATION. It is quite possible that the data sizes are so small that the option is not even considered.

One reason why Oracle might choose to do a cartesian join the first pair is if the optimizer has decided that it is a logical necessity that there will only be one row returned from the first table.
(It may even do it if it estimates that the first table
will return only one row).

There are various degrees of 'skill' built into the optimiser for Star and Star transformation - the requirements are highly version dependent, but:

Star:

    Needs a concatenated index on the fact     table that matches the sequence of primary keys     keys on the dimension tables.
    Ideally the fact table should be last in the list.     You can include the hint /*+ FACT(fact_table_name) */     to help Oracle spot what's going on/     You need at least two dimension tables

Star transformation

    Every dimension should have a PK.
    The corresponding FK's should have bitmap     indexes in the fact table
    The hint /*+ star_transformation */ helps.     The init.ora parameter star_transformation_enabled=true     may be required.

In either case, you usually find that the fact table has to be pretty big for a star(_transformation) to take place, as simply hash/merge joins on a set of small tables can be pretty cheap, especially if the sort_area_size or hash_area_size is anything other than tiny and the db_file_multiblock_read_count isn't kept very small.

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 27 March 2002 15:41

Lisa,

This is my explain plan:

1.19 SELECT STATEMENT SQL1 Cost = 19
  2.1 SORT GROUP BY
    3.1 HASH JOIN

      4.1 TABLE ACCESS FULL OPERS_CNTR_CDE
      4.2 HASH JOIN
        5.1 TABLE ACCESS FULL FEE_TYP
        5.2 HASH JOIN
          6.1 MERGE JOIN CARTESIAN
            7.1 TABLE ACCESS FULL RECM
            7.2 SORT JOIN
              8.1 TABLE ACCESS FULL MDB_DEPT
          6.2 TABLE ACCESS FULL CNCL_FEE

The two dim tables in the Cartesian join step are very small - 2 and 3 records. The fact table - CNCL_FEE is about 4000 rows.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).