The DB you are using is from Oracle 9i to 21c.

/* New Table1 */
CREATE TABLE TABLE1 (
    COL VARCHAR(25) NOT NULL, 
    COL2 VARCHAR(25) NOT NULL, 
    COL3 VARCHAR(25) NOT NULL,
    COL4 VARCHAR(25) NOT NULL, 
    COL5 VARCHAR(25) NOT NULL,
    COL6 VARCHAR(25) NOT NULL 
);

CREATE UNIQUE INDEX PK_TABLE1
    ON TABLE1 (
        COL ASC,
        COL2 ASC,
        COL3 ASC,
        COL4 ASC,
        COL5 ASC,
        COL6 ASC
    )
    STORAGE (
        BUFFER_POOL DEFAULT
    )
    NOLOGGING
    ONLINE
    COMPUTE STATISTICS
    COMPRESS 3
    SORT
    NOPARALLEL;

ALTER TABLE TABLE1
    ADD
        CONSTRAINT PK_TABLE1
        PRIMARY KEY (
            COL,
            COL2,
            COL3,
            COL4,
            COL5,
            COL6
        );

I want to inquire Compute Statistics, Sorted, and Online values here. Which table should I check?

When checking only in 9i and 21c, it was not possible to search in the ALL_INDEXES table.

CodePudding user response：

What problem are you trying to solve here?

The three attributes you mentioned aren't attributes of the index. They're instructions on how to build the index. I'm hard pressed to think of a reason that it would be useful to know whether statistics were gathered on the index when it was created. Or whether the table was sorted before the index was created. Or whether DML on the table was allowed during the index build.

You can check to see whether statistics exist for the index. Immediately after the index was created, you could look at the last_analyzed column in all_indexes to see whether statistics were gathered and to infer what the value of that parameter was (though since the compute statistics option is depricated and ignored in more recent versions where Oracle always gathers statistics, this is less useful). But if you come back after the system has been running for a year, statistics will have likely been gathered many times (barring corner cases like an ancient 9i database that's still running the rule-based optimizer).