Skip to Main Content

Intelligent Advisor

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Inferring Entity Instance Values in Separate Locations (Rule challenge?)

Hello,
We have been faced with a bit of a challenge trying to author rules based upon specific requirements. Essentially,
Rules need to be isolated by owner (in this case, Team ABC and Team XYZ should only have access to their own rules). This is intended to be implemented via Inclusions
To simplify the usecase, the user will enter their age and the rules will infer possible Offices that they should visit. Each office is accompanied by a 'location' value.
Attempted Solution
Rule Structure:
image.pngThe above structure is required because we need to isolate the rule files that will eventually be in separate rulebases (and then included in a Parent rulebase) as these will be managed by different sets of administrators (e.g. Team ABC and Team XYZ).
Data Model:
image.pngimage.pngRelationships for the ABC offices and the XYZ offices have been created so that we can support the Rule File Structure above
Rules:
ABC_Office_Decision.xlsx
image.pngXYZ_Office_Decision.xlsx
image.pngThe Problem
OIA will not allow this implementation as the office's location is a multiply proven rule in two different files.
Alternative Solutions

  1. We are aware of having a separate 'Parent' rule file (named Office Locations.xlsx) with the following to populate the office's location for each of the offices:
    image.pngimage.pngHowever, this is not ideal as the objective is to allow ABC administrators and XYZ administrators manage their own rules, which does include their office locations.
  2. The other alternative solution that still allows Team ABC/XYZ to manage their own Location values involves adding several Global-level attributes:
    image.pngThese attributes are then assigned to the office's location in a separate 'Parent' rule file (named Office Locations.xlsx):
    image.pngThis meets all of our requirements, but will require one Global attribute per office which increases rule maintenance and volume (also given the fact that we have up to 90 different offices). This is also how we have authored the rules today.
    --
    We are trying to implement the above in the most streamlined manner and if anybody has some ideas (without RuleScripts!), please do share your thoughts :)
    I have attached a copy of the project for reference. This has been created with OPM 21D but there should be no issue upgrading/downgrading the project:
    InferEntityChallenge.zip (40.53 KB)

Comments

Antonio Navarro

Oracle update statistics during rebuild and create process. Except you have locked statistics in table with

dbms_stats.lock_table_stats

You can verified it with this query

SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TABLE_NAME' and owner = 'OWNER'

If it returned ALL it is locked.

HTH - Antonio NAVARRO

Zoltan Kecskemethy
Answer

Yes when automatic CBO stat collection job runs.

I usually issue a

ALTER INDEX <index_name> COMPUTE STATISTICS;

after index created.

Marked as Answer by 1723552 · Sep 27 2020
BrunoVroman

Hello Zlotan,

you reply "yes" AND you issue "alter index compute statistics"?  Maybe you mean that the statistics will be gathered "later"...

As all the info is available during the creation of the index, the statistics are gathered "for free" during the (re)creation (at the exception mentioned by Antonio, "expected and desired behavior"). You can easily check this by looking at the statistics after the rebuild.

So it is NOT necessary to gather them once again...

Best regards,

Bruno Vroman.

Zoltan Kecskemethy

Yes "later" when the default CBO stat gather job runs in the maintenance window if it is enabled.

I meant if I create a new index I usually issue compute stats just after. This is a best practice / habit for me.

Yes I see this could be an overhead in new versions ...

BPeaslandDBA

  1. ALTER INDEX <index_name> COMPUTE STATISTICS

Do not use this command.

For starters, when an index is created, Oracle will gather stats on the index at that time. There is no need to redo the work.

Lastly, use the DBMS_STATS.GATHER_INDEX_STATS command instead of the ALTER INDEX COMPUTE STATISTICS command.

Cheers,
Brian

Zoltan Kecskemethy

Thank you for fixing me.

I see. But compute statistics does not harm anything IMHO. (just extra effort)

I know the dbms_stats pkg and that runs from the auto collect job as well.

BPeaslandDBA

In some cases, its just extra effort. In other cases, it can be harmful. I have some production databases that don't like the extra work. The creation of an index is taxing on the system and the end users start to feel a performance downgrade until the index creation is completed. The only reason I do this during periods of high usage is to resolve a problem that cannot wait until a later time. In those cases, I wouldn't want to contribute more to their pain by calculating stats one more time than is necessary.

Cheers,
Brian

unknown-7404
Version : 11g

Will oracle update the statistics after creation or modification of index ?

Yes - since 10g see the SQL Language doc

ALTER INDEX

COMPUTE STATISTICS Clause 

This clause has been deprecated. Oracle Database now automatically collects statistics during index creation and rebuild. This clause is supported for backward compatibility and will not cause errors.

And if you have stats on that table you want to keep then you MUST lock them before any modification since there is no other way to prevent Oracle from recomputing them.

See the end of this AskTom thread:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5087906100346466422

Is anyway we can tell Oracle not to run Compute Statistics while creating the indexes?

. . .

no, there is not. it is a feature, we compute them on a rebuild/create.

Zoltan Kecskemethy

That's why I came here. Learn, learn and learn

I was looking for this in the reference and concepts book....

Thank you for the overwhelming info.

jgarry

Here's some examples of the difference: Analyze This – 2 | Oracle Scratchpad

jgarry

That asktom answer seems to beg a question.

Consider this thought experiment:

You have a master/detail couple of tables, each with a couple of indices.  There's enough data in the tables so no new statistics are collected for a long time, even though new data is being added to change data distribution.  Now, for whatever reason, one index is altered.  Might there be a problem coming with everything else having old statistics and one index having new?

Edit:  One more thing I wanted to point out in case anyone didn't know - the ability to revert to old statistics. Managing Optimizer Statistics

Martin Preiss

it seems the most important difference between dbms_stats and the ALTER INDEX ... COMPUTE STATISTICS call is that the latter does not create statistics at all in 11.2:

-- 11.2.0.1

-- drop test table

drop table t;

-- create test table

create table t

as

select rownum id

    , mod(rownum, 10) col1

  from dual

connect by level <= 100000;

-- delete a lot of rows

create index t_idx on t(id);

delete from t where col1 <= 5;

commit;

-- statistics after object creation and delete

select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';

INDEX_NAME                      NUM_ROWS LEAF_BLOCKS LAST_ANALYZED

------------------------------ ---------- ----------- -------------------

T_IDX                             100000         222 04.08.2014 19:59:22

-- table stats deleted

exec dbms_stats.delete_table_stats(user, 't')

select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';

INDEX_NAME                      NUM_ROWS LEAF_BLOCKS LAST_ANALYZED

------------------------------ ---------- ----------- -------------------

T_IDX

-- alter index compute statistics;

alter index t_idx compute statistics;

select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';

INDEX_NAME                      NUM_ROWS LEAF_BLOCKS LAST_ANALYZED

------------------------------ ---------- ----------- -------------------

T_IDX

-- dbms_stats

exec dbms_stats.gather_table_stats(user, 't', cascade=>true)

select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';

INDEX_NAME                      NUM_ROWS LEAF_BLOCKS LAST_ANALYZED

------------------------------ ---------- ----------- -------------------

T_IDX                              40000         222 04.08.2014 19:59:24

So it seems that alter index ... compute statistics; does not recreate the deleted statistics. And when I issue the command after an dbms_stats call then the last_analyzed value is not changed (and the index statistics are also the same). Already in 10.2 the documentation stated: "COMPUTE STATISTICS Clause: This clause has been deprecated. Oracle Database now automatically collects statistics during index creation and rebuild. This clause is supported for backward compatibility and will not cause errors. "ALTER INDEX. In the documentation for 11g the clause is not mentioned, does not result in an error - but seems to be quite useless.

Maybe it would be a good idea if someone checks this example to make sure that I does not talk utter nonsense...

Regards

Martin

Zoltan Kecskemethy

I have the very same results under 11g.

ME@MYDB> select banner from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

ME@MYDB> create table t as select rownum id , mod(rownum, 10) col1 from dual connect by level <= 100000;

Table created.

ME@MYDB> create index t_idx on t(id);

Index created.

ME@MYDB> delete from t where col1 <= 5;

60000 rows deleted.

ME@MYDB> commit;

Commit complete.

ME@MYDB> select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';

T_IDX

    100000          55 05-AUG-14

ME@MYDB> exec dbms_stats.delete_table_stats(user, 't');

PL/SQL procedure successfully completed.

ME@MYDB> select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';

T_IDX

ME@MYDB> alter index t_idx compute statistics;

Index altered.

ME@MYDB> select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';

T_IDX

ME@MYDB> exec dbms_stats.gather_table_stats(user, 't', cascade=>true);

PL/SQL procedure successfully completed.

ME@MYDB> select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';

T_IDX

    40000          55 05-AUG-14

and the same behavior under 10gR2 (10.2.0.5.0) I have no 12c handy.

1 - 13

Post Details

Added on Apr 28 2022
5 comments
214 views