Oracle Scratchpad

January 13, 2009

FBI Stats

Filed under: CBO,Function based indexes,Indexing,Infrastructure,Oracle — Jonathan Lewis @ 7:29 pm GMT Jan 13,2009

It’s very easy to forget that 10g creates statistics on an index as it builds (or rebuilds) the index – and this can lead to some oddities when you add a function-based index (or, as I tend to name them, index on virtual columns) to a table.

This point (along with a couple of other observations) came up in a discussion on OTN a little while ago, with some useful responses from Richard Foote (the “index king”), Christian Antognini (author of  “Trouble-shooting Oracle Performance”) and a few others, including me.


  1. Hi Jonathan,

    Using trace 10053 on it, we could find that hidden column of FBI was having no statistics.

    Column (#3): SYS_NC00003$(NUMBER) NO STATISTICS (using defaults)
    AvgLen: 2.00 NDV: 31250 Nulls: 0 Density: 3.2000e-005
    Table: FBI_TEST Alias: FBI_TEST
    Card: Original: 1000010 Rounded: 10000 Computed: 10000.10 Non Adjusted: 10000.10
    Access Path: TableScan
    Cost: 559.91 Resp: 559.91 Degree: 0
    Cost_io: 477.00 Cost_cpu: 482509949
    Resp_io: 477.00 Resp_cpu: 482509949
    Access Path: index (AllEqGuess)
    Index: FBI_TEST_FBI
    resc_io: 1959.00 resc_cpu: 15431271
    ix_sel: 0.004 ix_sel_with_filters: 0.004
    Cost: 478.65 Resp: 478.65 Degree: 1
    Best:: AccessPath: IndexRange Index: FBI_TEST_FBI
    Cost: 478.65 Degree: 1 Resp: 478.65 Card: 10000.10 Bytes: 0

    Comment by Bunditj — January 14, 2009 @ 10:20 am GMT Jan 14,2009 | Reply

  2. Hi Jonathan,

    Thanks for sharing that useful discussion, I missed that one. There was one question that wasn’t asked in that OTN thread that immediately popped into my head: Why?

    What would be the purpose of creating a FBI to index the Y values, when they make up (1,000,000/1,000,010=)99.999% of the rows? I’ve only ever seen FBIs that partially index columns like that on the minority N values.

    The only thing I can think of is that the test case was reduced to the minimum to show the peculiarities, (which is a great practice,) and that it would be needed for a much more complicated query. In which case, there would most likely be much more selective columns. What use could this index give then? And why not a standard B*Tree index, to get the N values “for free” if needed?

    Sorry if any questions are simple, I still consider myself a “newbie” to Oracle. I appreciate any your thoughts you have on the above.


    Comment by Jon McCormack — January 14, 2009 @ 10:45 am GMT Jan 14,2009 | Reply

    • Jon,

      I think you’re right about this being an extreme test case to model an observed problem. The OP makes some sort of comment to that effect near the start of the post.

      I agree, it’s a very good practice – especially when working with optimizer problems: if something doesn’t seem to be behaving quite right it might be a little oddity in the statistics, or it might be something serious, so I often try to build two extreme cases, one which “can’t possibly” go wrong, and one which “can’t possibly” go right.

      I don’t know what Oracle does to generate statistics while creating the index – possibly it’s using the routines it used to use for “analyze index XXX compute statistics”, it certainly doesn’t seem to run the SQL that it uses in dbms_stats.get_index_stats(). On a couple of simple experiments, though, the results seem to come out the same as a call dbms_stats.gather_index_stats() with estimate_percent = 100.

      Comment by Jonathan Lewis — January 14, 2009 @ 9:38 pm GMT Jan 14,2009 | Reply

  3. What stats execution type (mode, estimation, etc…) oracle calculate al ‘create index’ time?

    Comment by Sandro — January 14, 2009 @ 4:22 pm GMT Jan 14,2009 | Reply

  4. […] stored in the index but not on the table itself – but it’s important that the optimizer has the statistics about the non-existent column […]

    Pingback by Auto Sample Size | Oracle Scratchpad — March 2, 2014 @ 6:39 pm GMT Mar 2,2014 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by