DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: explain plan help

  1. #1
    Join Date
    Sep 2001
    Posts
    120
    hi,
    I have a query :-
    select count(*) from portal.discussion_message, portal.Portal_Users where portal.discussion_message.status='ACTIVE' and portal.portal_users.user_Id = portal.discussion_message.User_Id and portal.discussion_message.reply is not null;

    there are no indexex build on it so the explain plan for this is :
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 SORT (AGGREGATE)
    2 1 MERGE JOIN
    3 2 SORT (JOIN)
    4 3 TABLE ACCESS (FULL) OF 'PORTAL_USERS'
    5 2 SORT (JOIN)
    6 5 TABLE ACCESS (FULL) OF 'DISCUSSION_MESSAGE'
    --
    now i build a no unique index on discussion table on user_id coloumn.
    then the exlpain plan is:
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 SORT (AGGREGATE)
    2 1 TABLE ACCESS (BY INDEX ROWID) OF 'DISCUSSION_MESSAGE'
    3 2 NESTED LOOPS
    4 3 TABLE ACCESS (FULL) OF 'PORTAL_USERS'
    5 3 INDEX (RANGE SCAN) OF 'IDX_DIS_USER_ID' (NON-UNIQUE)

    -----------
    now i analyze table discussion table: and after that the explain plan is:
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=30)
    1 0 SORT (AGGREGATE)
    2 1 HASH JOIN (Cost=5 Card=14520 Bytes=435600)
    3 2 TABLE ACCESS (FULL) OF 'DISCUSSION_MESSAGE' (Cost=2 Card=71 Bytes=568)
    4 2 TABLE ACCESS (FULL) OF 'PORTAL_USERS' (Cost=2 Card=409 Bytes=8998)
    -----

    i fail to understand why this has happened.

    please help
    saurabh
    Saurabh Garg
    OCP 9i

  2. #2
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    Hi,
    The ANALYZE command gathers statistics on a table and its indexes. Using the ANALYZE command forces the optimizer to work in CBO (Cost Base) mode.

    When analyzing a table, the statistics is saved in histograms. The histogram is used to get accurate estimates of the distribution of column data.
    If the data is not distributed evenly, Oracle can assume that a full table scan is needed.

    The histogram uses buckets to save the data in the column selected. Each bucket has an end-point value. If an end-point value appears in more than 1 bucket it is considered to be a popular value. Using the popular values, the optimizer can choose if to use the index or perform full table scan.

    This decision is based on the number of buckets (which eventually determines popular values) and the cardinality of the values selected.

    The default value for the number of buckets in the analyze table is 75. This value can be changed using the ANALYZE command.

    You can read more at:
    http://download-west.oracle.com/otnd...ance.htm#18928
    http://download-west.oracle.com/otnd...tats.htm#27980

    Cheers,
    R.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width