explain plan / big table help plz...

Oracle 9.2, CBO=choose. Sorry if these are silly questions.

Ok we got a big table, gona have 100 million+ rows in it soon. We're trying various queries against this table to see what is most efficient.

Using explain plan, is the COST the main thing we should be looking at? Is it pretty much guaranteed that if two queries return the same results, but the COST is lower for one, then should we always go with the lower COST query?

Another wrinkle, regarding bind variables. The applications were written using Web Objects, which can turn bind variables off or on. Since the apps are not using cursors, its my contention that bind variables aren't doing us any good. But when we look at the explain plan results it sometimes shows lower COST values with binds.. same query plug in real values and COST goes higher. Any suggestions on if binds are doing us any good or not?

Finally, this table will have some range scans, which is probably going to be a performance problem. Would performance increase if we partition this table (using oracle's table/index partition stuff)? In other words is Oracle's CBO smart enuf to range scan only the partition with the appropriate data (assuming that is what the partition was on)?

thx... allen Received on Tue Sep 10 2002 - 19:57:51 CDT