Database Optimiser Cost Calculation : Suggestion for formula
Posted in 2000
My program allows user to formulate their query via drag and drop. Unfortunately, many of the tables are really big : hundreds of thousands and millions of rows. I need to access different databases (Oracle,Informix, SQL Server). I need to be able to build a cost estimate of the query and then prompt the user whether he wants the query run. Assuming I have 4 tables A,B,C,D I will store the table row count eg. Table A : 1,000 rows Table B: 100,000 rows Table C : 100 rows Table D : 1,000,000 rows So, if the query involves Table A and B (Select * from A,B where A.field1 = B.field1) The formula will calculate the cost based on row count. It gets more difficult as constrainst are allowed (Select * from A,B where A.field1 = B.field1, WHERE A.Field2 = 3). Any suggested formulas? rgds