Given a schema with billions of rows in a star-join configuration, write a complex SQL query to calculate year-over-year growth for specific segments and explain how you would optimize the execution plan using indexing, partitioning, and materialized views.