My problem is a little bit complicated, so please bear with me when I try to explain it ^_^.
I work on automatically generating several (a big number) DW snowFlake schemas, from a star schema input. In the other hand, i have a set of queries, that change according to the schema of course.
My purpose is to calculate the cost model of each query on each schema, knowing that I have only statistics about table sizes, row sizes, page sytem sizes, etc (all the parameters needed to calculate the cost model). if there would be data, I could use the "explan plan of the dbms" to generate the "best" plan of each query in order to calculate the cost model, and this will save me a lot of time :)
But unfortunately, I have no data, and I wonder if I could use the "explain plan", just by setting the parameters without data, in other words, defining size of tables without data. Is it possible on Oracle, or any other DBMS??
Thanks in advance.
P.S : i could just ask the question : "Can i set the size of tables in oracle (or any other DBMS) (without having data), but i prefered to explain the whole problem, hoping that i'll have alternative proposals.
You can set the statistics on tables even if they have no data - then the CBO will use your statistics when generating execution plans.
http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#i41857
Just be aware of dynamic sampling (i.e. probably need to turn it off).