Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    Unanswered: how to create table statistics without histograms

    Hi,

    We have a strange case at a customer site that his Oracle rewrite queries that hangs if we collect statistics on a table.
    In order to disable rewrite (query_rewrite_enabled already set to false) I have deleted the table histograms using the ANALYZE table DELETE STATISTICS and the re-write stopped.

    We can verify that histograms data is created using the following query:
    SELECT TABLE_NAME,COLUMN_NAME FROM DBA_HISTOGRAMS WHERE TABLE_NAME ='<table_name>';

    I tried using ANALYZE command or DBMS_STATS package (compute or estimate) in various options but both have created histograms data all the time.

    I have tried looking in Oracle docs but found not syntax to disable the histogram data collection.

    Is there a way to do it?
    Idea anyone?

    Thanks in advanced,

    Tal (otal@mercury.co.il).

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    Hi,

    for as far as I know, collecting statistics always result in at least 1 histogram per table / index. There's no way to avoid this.
    Seems like you have trouble using the Cost Based Optimizer.
    Did you try to avoid the CBO by changing to the rule based optimizer? Or is your db version too high (9i)?

    Other wise i think it's time to contact oracle about this.

    Edwin

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You could also try the norewrite hint which may disable rewrite if you have access to the sql.

    Alan

Posting Permissions

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