Query Rewriting using Multitier Materialized Views for Cyber Manufacturing Reporting
Abstract
Within cyber manufacturing context, Internet of Data (IoD) technology has enabled manufacturing sector to store and transfer mass data rapidly for processing. Data growth which is driven by advancement in the way data are produced and interconnected has caused volume of data a crucial issue to address. As such, in monitoring delicate wafer processing in semiconductor manufacturing, reporting delay problem caused by databases of high data volumes is intolerable. This is because, various reports (that require access to large databases) need to be frequently generated in the shortest time possible. Reporting delay is usually handled through SQL query rewriting. In this paper, the results of experimenting SQL query rewriting by utilizing multitier materialized views structure is presented. In particular, we define sub-materialized views (SMVs) concept, and implement it using real data sets from SilTerra (a semiconductor industry). The outcome of the experiment supports the hypothesis that SQL query rewriting using SMV outperforms the classic rewriting. The results reveal that the performance of SMV is far better (than without SMV) for complex queries against large data sets. The benefits of SMV are not limited to cyber manufacturing domain as the use of SMV can contribute other industries with similar problem.
Downloads
References
Economic Planning Unit. (2014). Complexity analysis study of Malaysia’s manufacturing industries [Online]. Available: http://www.epu.gov.my/en/content/final-report-research-complexity-analysis-study-malaysias-manufacturing-industries.
J. Lee, B. Bagheri, and C. Jin, “Introduction to cyber manufacturing,” Manufacturing Letters , vol. 8, pp. 11–15, 2016.
L. Wang and G. Wang, “Big Data in cyber-physical Systems, digital manufacturing and Industry 4.0,” International Journal of Engineering and Manufacturing (IJEM), vol. 6, no. 4, pp. 1–8, 2016.
T. Ponsignon and L. Monch, “Architecture for simulation-based performance assessment of planning approaches in semiconductor manufacturing,” in the Proceedings of Winter Simulation Conference (WSC), Baltimore, 2010, pp. 3341–3349.
S. Munirathinam and B. Ramadoss, “Big data predictive analtyics for proactive semiconductor equipment maintenance,” in IEEE International Conference on Big Data (Big Data), Washington, DC, 2014, pp. 893–902.
S. R. A. Rahim, I. Ahmad, and M. A. Chik, “Technique to improve visibility for cycle time improvement in semiconductor manufacturing,” in 10th IEEE International Conference on Semiconductor Electronics (ICSE), Kuala Lumpur, 2012, pp. 627–630.
P. Balakrishna, M. A. Chik, I. Ahmad, and B. Mohamad, “Throughput improvement in semiconductor fabrication for 0.13μm technology,” in IEEE Regional Symposium on Micro and Nano Electronics, Kota Kinabalu, 2011, pp. 228–231.
Mckinsey Global Institute. (2011). Big data: The next frontier for innovation, competition, and productivity [Online]. Available: https://www.mckinsey.com/business-functions/digital-mckinsey/our-insights/big-data-the-next-frontier-for-innovation.
D. Boyd and K. Crawford, “Critical questions for big data: Provocations for a cultural, technological, and scholarly phenomenon, “Information, Communication and Society, vol. 15, no. 5, pp. 662–679, 1986.
R. Dubey, A. Gunasekaran, and S. Childe, “The impact of big data on world-class sustainable manufacturing,” The International Journal of Advanced Manufacturing Technology, vol. 84, no. 1-4, pp. 631–645, 2016.
J. Lee, H. Kao, and S. Yang, “Service innovation and smart analytics for industry 4.0 and big data environment,” Procedia Cirp, vol. 16, pp. 3-8, 2014.
T. Wilschut, I. J. B. F. Adan, and J. Stokkermans, “Big data in daily manufacturing operations,” in the Proceedings of the Winter Simulation Conference, Savannah, GA, 2014, pp. 2364–2375.
J. Lee, E. Lapira, B. Bagheri, and H. Kao, “Recent advances and trends in predictive manufacturing systems in big data environment,” Manufacturing Letters, vol. 1, no. 1, pp. 38–41, 2013.
L. Hu, K. A. Ross, Y.-C. Chang, C. A. Lang, and D. Zhang, “QueryScope: visualizing queries for repeatable database tuning,” in the Proceedings of the VLDB Endowment, vol. 1, 2008, pp. 1488–1491.
D. DeHaan, D. Toman, M. P. Consens, and M. T. Ozsu, “A comprehensive XQuery to SQL translation using dynamic interval encoding,” in the Proceedings of the ACM SIGMOD international conference on Management of data, California, 2003, pp. 623–634.
L. Guodong, W. Shuai, L. Chang’an, and M. Quanzhong, “A modifying strategy of group query based on materialized view,” in 3rd International Conference on Advanced Computer Theory and Engineering (ICACTE), Chengdu, 2010, pp. V5-381-V5-384.
H. Herodotou and S. Babu, “Automated SQL tuning through trial and (sometimes) error,” in the Proceedings of the Second International Workshop on Testing Database Systems, Providence, RI, 2009, pp. 1–6.
D. Taniar, H. Y. Khaw, H. C. Tjioe, and E. Pardede, “The use of Hints in SQL-Nested query optimization,” Information sciences , vol. 177, no. 12, pp. 2493–2521, 2007.
S. Ji, W. Wang, C. Ye, J. Wei, and Z. Liu, “Constructing a data accessing layer for In-memory data grid,” in the Proceedings of the Fourth Asia-Pacific Symposium on Internetware, QingDao, 2012, pp. 1–7.
J. Cohen, B. Dolan, M. Dunlap, J. M. Hellerstein, and C. Welton, “MAD Skills: New analysis practices for big Data,” in the Proceedings of the VLDB Endowment, vol. 2, 2009, pp. 1481–1492.
L. Lim and B. Bhattacharjee, “Optimizing hierarchical access in OLAP environment,” in IEEE 24th International Conference on Data Engineering, Cancun, 2008, pp. 1531–1533.
R. Goswami, D. K. Bhattacharyya, and M. Dutta, “Materialized view selection using evolutionary algorithm for speeding up big data query processing,” Journal of Intelligent Information Systems. vol. 49, no. 3, pp. 1-27, 2017.
P. P. Karde and V. M. Thakare, “Selection of materialized view using query optimization in database management : An efficient methodology,” International Journal of Management Systems IJDMS, vol. 2, no. 4, pp. 116–130, 2010.
X. Li, X. Qian, J. Jiang, and Z. Wang, “Shuffled frog leaping algorithm for materialized views selection,” in 2nd International Workshop on Education Technology and Computer Science, New Jersey, 2010, pp. 7–10.
P. Bagale and S. R. Joshi, “Optimal materialized view management in distributed environment using random walk approach,” Journal of Advanced College of Engineering and Management , vol. 1, pp. 67–73, 2016.
R. Urbano. (2008). Materialized view concepts and architecture-Oracle Docs [Online]. Available: https://docs.oracle.com/cd/ B28359_01/ server.111/ b28326.pdf
D. Habich, W. Lehner, and M. Just, “Materialized views in the presence of reporting functions,” in 18th International Conference on Scientific and Statistical Database Management (SSDBM’06), Vienna, 2006, pp. 159–168.
Y. Xu and S. Hu, “QMapper: A tool for SQL optimization on hive using query rewriting,” in the Proceedings of the 22nd International Conference on World Wide Web, Rio de Janeiro, 2013, pp. 211–212.
R. Chirkova, C. Li, and J. Li, “Answering queries using materialized views with minimum size,” The VLDB Journal-The International Journal on Very Large Data Bases, vol. 15, no. 3, pp. 191–210, 2006.
L. M. Wein, “Scheduling semiconductor wafer fabrication,” IEEE Transactions Semiconductor Manufacturing, vol. 1, no. 3, pp. 115–130, 1988.
