Are the default checks of sa_validate() and DBVALID identical?

[VolkerBarth]

Just a follow-up from this question on DBVALID:

If my understanding of John's clarifications in the cited question is correct, then DBVALID with no arguments does a VALIDATE DATABASE and a VALIDATE TABLE for each table/materialized view and a VALIDATE INDEX for each index.

Currently, the SA 12 docs state that ...

by default, dbvalid validates all the tables, materialized views, and indexes, in the database, and validates the database itself.

For sa_validate(), the SA 12 docs (as corrected in DCX) specify, that....

if neither owner nor tbl_name are specified, all tables and materialized views in the database are validated. Also, the database itself is validated, including checksum validation, and validation that the number of rows in the each table or materialized view matches the number of rows in each associated index.

So, is my conclusion correct that both DBVALID with no arguments and sa_validate() with no arguments perform the same set of checks?

---

If so, that might be another reason the clarify the docs:)

[VolkerBarth]

@Breck: Thanks for the correction (cf. http://sqla.stackexchange.com/revisions/1215/list) - oh well, I'd nearly written sa_failidate()...

[johnsmirnios · Accepted solution]

Yes, a default invocation of dbvalid appears to be equivalent to calling sa_validate() with no userid & no table name. For the record, I didn't say that dbvalid executes VALIDATE INDEX for each index. It executes VALIDATE TABLE for each table which also validates all indexes on the table.

The documentation also says that VALIDATE INDEX is a "complement" to the checking done by VALIDATE TABLE but it looks like that is not the case. VALIDATE TABLE actually does slightly more index checking than VALIDATE INDEX does.

[Breck_Carter · Accepted solution]

"Shall we break for lunch or open another can of worms?" - anonymous

[VolkerBarth · Accepted solution]

I feel it's primarily the fact that some checks seem to be done from different starting points with slightly different results (e.g. a table check checking the indizes, too, vs. an index check) that make all these validation issues that complicated. In the end, I just want to validate as much as possible - and do that with confidence:) - That being said, a BIG THANKS to you, John, for keeping on with exploring and explaining!

[johnsmirnios · Accepted solution]

I'm guessing here but I think some forms of VALIDATE were added so that a subset of the (very expensive) full validation could be performed. From where I sit, I would have expected "VALIDATE DATABASE" to validate everything it could. At one point, I'm pretty sure VALIDATE INDEX actually did do extra work just as I'm sure that there are historical reasons that made sense at the time for how things were implemented at that time. Largely, I think it is the documentation that has fallen behind.

[johnsmirnios · Accepted solution]

@Breck. Mmmmm. Worms! Open away 🙂