Professional Web Applications Themes

Index Partition Rebuild is using Full Table Scan - Oracle Server

I am rebuilding multiple UNUSABLE index partitions on Oracle HP/UX ... Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production With the Partitioning option JServer Release 8.1.7.4.0 - 64bit Production The rebuild command ... alter index XIE12FCT_ITEM rebuild partition Y2002_M11 compute statistics pctfree 0 nologging parallel; .... which normally completes in less than ten seconds was observed to be taking very much longer. OEM showed a "Long Operation" for the session, and is showing scans of every partition of the table, despite showing the correct SQL. We have done this every month for over two years, and suddenly all of these ...

  1. #1

    Default Index Partition Rebuild is using Full Table Scan

    I am rebuilding multiple UNUSABLE index partitions on Oracle HP/UX ...

    Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
    With the Partitioning option
    JServer Release 8.1.7.4.0 - 64bit Production

    The rebuild command ...

    alter index XIE12FCT_ITEM rebuild partition Y2002_M11 compute
    statistics pctfree 0 nologging parallel;

    .... which normally completes in less than ten seconds was observed to
    be taking very much longer. OEM showed a "Long Operation" for the
    session, and is showing scans of every partition of the table, despite
    showing the correct SQL.

    We have done this every month for over two years, and suddenly all of
    these rebuilds are full-scanning the source tables. We are not aware
    of any config changes to the DB or server.

    Tables are all range partitioned by DATE with mostly bitmap indexes.

    Does any have any insights here?
    Dave Guest

  2. #2

    Default Re: Index Partition Rebuild is using Full Table Scan


    I think I noticed a bug on Metalink about
    this some time ago. But I can't guarantee
    that my memory is correct.

    --
    Regards

    Jonathan Lewis
    [url]http://www.jlcomp.demon.co.uk[/url]

    Next Seminar dates:
    (see [url]http://www.jlcomp.demon.co.uk/seminar.html[/url] )

    ____England______January 21/23


    The Co-operative Oracle Users' FAQ
    [url]http://www.jlcomp.demon.co.uk/faq/ind_faq.html[/url]





    Dave wrote in message ...
    >I am rebuilding multiple UNUSABLE index partitions on Oracle HP/UX
    ....
    >
    >Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
    >With the Partitioning option
    >JServer Release 8.1.7.4.0 - 64bit Production
    >
    >The rebuild command ...
    >
    >alter index XIE12FCT_ITEM rebuild partition Y2002_M11 compute
    >statistics pctfree 0 nologging parallel;
    >
    >... which normally completes in less than ten seconds was observed to
    >be taking very much longer. OEM showed a "Long Operation" for the
    >session, and is showing scans of every partition of the table,
    despite
    >showing the correct SQL.
    >
    >We have done this every month for over two years, and suddenly all of
    >these rebuilds are full-scanning the source tables. We are not aware
    >of any config changes to the DB or server.
    >
    >Tables are all range partitioned by DATE with mostly bitmap indexes.
    >
    >Does any have any insights here?

    Jonathan Lewis Guest

Similar Threads

  1. Index scan vs. Seq scan on timestamps
    By Michael Fuhr in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: December 12th, 03:08 AM
  2. How to insert data using index unusable/rebuild
    By aoshell2@yahoo.com in forum Oracle Server
    Replies: 0
    Last Post: July 10th, 08:06 PM
  3. Rebuild a table
    By Vishal Parkar in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 3rd, 05:33 PM
  4. Rebuild index in SQL server ??
    By SAlva in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: June 30th, 04:42 PM
  5. Replies: 0
    Last Post: December 15th, 01:15 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139