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 <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
