I ask unanimous consent that the Chair be authorized to declare a recess at any time without objection so ordered and I ask unanimous consent that members may have five legislative days to revise and extend the remarks without objection so ordered. I want to welcome everyone to this hearing of the Military Personnel Subcommittee. Today we convene to address a matter of paramount importance how the Department of Defense monitoring of COVID-19 has impacted our military ranks and the implications of the COVID-19 vaccine on the health and well-being of our servicemen and women. Over the past four years the COVID-19 pandemic has presented unprecedented challenges to our nation and its armed forces. As the virus has become just another part of the yearly flu season we need to look with clear eyes and healthy skepticism at how the Department handled the pandemic, the effects of the virus and vaccines on our servicemembers' health, and if the Department's policies and practices actually mitigated any risk to servicemembers and their families. Many servicemembers and their families are concerned with the safety and value of the COVID-19 mRNA vaccine prompting questions about adverse reactions and unforeseen circumstances most concerningly related to heart conditions and hypertension in a young military population. And the data is worrying. In 2022 we saw heart-rated conditions like hypertension and cardiomyopathy among servicemembers increased by 47 percent and 94 percent respectively over DOD averages. In addressing this pandemic there is no doubt that the Department has made mistakes and that some decisions were made for political gain rather than based on science in fact. So today we seek clarity for the servicemembers who took the COVID-19 vaccine for their families and for everyone's future health and well-being. We seek to understand the extent to which the Department of Defense has monitored the impact of COVID-19 on our military personnel including any potential correlation between the virus itself and the development of medical conditions. Moreover, we aim to examine the data surrounding the administration of the COVID-19 vaccine within our ranks evaluating its safety profile and any observed trends in adverse reactions and health outcomes. As stewards of our nation's defense it is incumbent upon us to ensure the well-being of those who wear the uniform. We owe it to our servicemembers to provide them with the best possible care and support especially in times of crisis. By convening this hearing we demonstrate our commitment to transparency, accountability and above all the health and safety of our military community. I would like to welcome our witnesses Dr. Lester Martinez Lopez the Assistant Secretary of Defense for Health Affairs at the Department of Defense and Dr. Shauna Stallman, Senior Epidemiologist of the Armed Forces Health Surveillance Division at the Defense Health Agency Public Health. Thank you for being here today. I hope this hearing provides us an opportunity for our members to have a productive exchange. Before hearing from our witnesses let me offer ranking member Takuta an opportunity to make any opening remarks. Thank you Mr. Chair. Thank you to our witnesses for being here today and providing testimony regarding the Department of Defense's Health Surveillance efforts which includes monitoring health threats and emerging infections, biosurveillance and epidemiological analysis to include the impacts of infections and vaccines. As a member of the House Select Subcommittee on the Coronavirus Pandemic I am not unfamiliar with whether it's to politicize science behind vaccines to the detriment of public health and national security. I cannot emphasize enough the importance of using a fact-driven science-based approach to this conversation today. Let's focus on the facts. Safe and effective COVID-19 vaccine options have been readily available since 2021. According to the CDC in the first 10 months that COVID-19 vaccines were available they saved over 200,000 lives and prevented over 1.5 million hospitalizations in the United States. This is the purpose of these vaccines to save lives and prevent severe illness. While the military COVID-19 vaccine requirement was rescinded in January 2023 96% of the active and reserve force over 1.9 million people safely received one or more doses of a COVID-19 vaccine. Vaccine requirements have longstanding precedent in our armed forces. Since the founding of the U.S. military vaccine requirements have been necessary to preserve military readiness and personnel safety from General George Washington's smallpox vaccination of the Continental Army in 1777 to the flu vaccine requirement in the mid-20th century. Today the Department administers as many as 17 different vaccinations and while it was in effect the COVID-19 vaccination requirement helped ensure that our armed forces remained healthy and medically ready. Service members that have received COVID-19 vaccines have done so under the most intense safety monitoring program in United States history. The CDC, the FDA and other federal partners use multiple passive and active surveillance systems and data sources to conduct comprehensive safety monitoring of COVID-19 vaccines and the Department of Defense conducts near real-time monitoring and research on the impacts of COVID-19 vaccinations and infections through the military health system. Studies continue to show that the benefits of COVID-19 vaccines outweigh the risk yet concern and apprehension regarding the safety of COVID-19 vaccinations do still exist. This may be in due in large part to a fundamental misunderstanding of the Department's COVID-19 vaccine surveillance data which has unfortunately been the subject of misleading news stories over the past year. The Department of Defense's monitoring efforts of COVID-19 have reported a small number of increases in adverse health effects following the COVID-19 vaccine requirement but correlation does not imply causation. The legitimate questions remain as to the root cause of these identified adverse health effects. The overarching question for today's panel is one of paramount importance. Are there long-term effects from COVID-19 on our service members? And if so how do we discern whether any increase in reported adverse health effects are attributable to the virus itself or to the vaccine? To address this question comprehensively we must approach today's discussion with scientific rigor ensuring that we prioritize the health and safety of our all-volunteer force as a whole above all else. As we navigate the complexities of this issue we must acknowledge the profound impact that the COVID-19 pandemic has had on the operational readiness of our armed forces. First and foremost the pandemic resulted in thousands of hospitalizations across the department and the tragic loss of hundreds of lives. It also had far-reaching second and third order effects on our military including disruptions in training exercises and deployments, the mobilization of military medical personnel to support civilian pandemic response efforts and negative impacts to military family quality of life issues like delays and move, child care and health care access. At the heart of today's discussion regarding the department's monitoring of COVID-19 lies the fundamental commitment to the health and well-being of our service members that must ultimately include a shared dedication to transparency and facts grounded in scientific evidence. Mr. Chairman I'd like to request that the Department of Defense's report on cardiac and kidney issues and service members prior to and following the COVID vaccine requirement be included in the record for today's hearing. Thank you Mr. Chair. Congress required this report in fiscal year 23 NDA and it serves as an example of the careful and thoughtful monitoring the department is doing. Thank you again Mr. Chair for this hearing and I look forward to our witness testimony and the responses to questions that will be posed today. I yield the balance of my time. Thank you. I understand that you have one consolidated opening statement. We respectfully request that you summarize your testimony in five minutes or less. Your written comments and statements will be made part of the hearing record. Following opening statements each member will have an opportunity to question the witnesses for a very liberal five minutes. With that Dr. Martinez Lopez you may make your opening statement. Chairman Banks, ranking member Takuta, distinguished members of the subcommittee. We are pleased to represent the Office of the Secretary of Defense to discuss the department's ongoing health surveillance of the force related to COVID-19 in the aftermath of the global pandemic. This testimony provides the committee with information on some of the key data used to track the health of service members and provides updates on some past and future studies related to the impact of COVID-19 on the health of the service members. Service members like all members of our nation experience the effects of the global COVID-19 pandemic. However, unlike the civilian population when service members particularly those deployed or on operational units became sick with COVID-19 it impacts national security. This is an unacceptable risk for the military and our nation. As part of force health protection the Department of Defense took actions to blunt the impact of the pandemic on the force and to maintain operational readiness. This was achieved primarily through force health protection measures like vaccinations, testing, masking, symptom monitoring and remote work. These actions saved lives and resulted in less severe disease and fewer hospitalization among those service members that were infected. Nevertheless, the impact of COVID-19 lingers with some service members and veterans like just like many other Americans are experiencing the long-term effect of COVID-19 infections including long COVID and heart-related conditions. As we seek to keep the total force healthy and on mission the department monitors for infectious diseases and a range of other health threats. We do this through the dedicated staff with public health commands collocated with military units around the world. In addition, we have a team of analysts evaluating the data for trends and investigating any signal that are identified. One of the primary tools this health threats analyst used to answer complex epidemiological questions is a relational database called the Defense Medical Surveillance System or DMSS. As the central repository of medical surveillance data for the U.S. Armed Forces, DMSS contain up-to-date and historical data on diseases and medical events including inpatient and ambulatory medical encounters, immunizations, prescriptions, laboratory data and deployment health assessment and casualty data. To enhance our ability to identify signals in the noise of infectious disease data, we have related capability to a DMSS called the Defense Medical Epidemiology Database or DMSS. DMSS using its proper context is a useful tool for DOD medical and public health professionals to monitor health trends among their local populations and identify potential issues that require further inquiry or research. The DOD's data is compelling. In looking at the impact of vaccine, the department's data show that unvaccinated individuals with a reported COVID-19 infection were at significantly higher risk of developing three cardiac conditions, myocarditis, pericarditis and acute myocardial infarction, compared to individuals who receive a COVID vaccine. Further, the DOD data show that among the 31 active duty service members who died from COVID-19, none of them were fully vaccinated. Now, today, four years after the emergence of SARS-CoV-2 virus, it continues to circulate in our military communities and evolve into new variants, presenting an ongoing health threat capable of harming service members and affecting operations. The department remains committed to protecting the health of the force and to better understand these impacts as we prepare for future health threats. Our ongoing studies will support the development of therapeutics and medical countermeasures. We will also continue to evaluate the relationship between COVID-19 infection or COVID-19 vaccinations and cardiac conditions through surveillance and research. Our ongoing data surveillance will help inform future DOD policy on force health protection, improve readiness, and help prepare for and mitigate against future health threats. Thank you for inviting us here today to speak with you about the department's health data, which enables our ongoing surveillance of the impact of the COVID-19 and the force and the health of the force. We look forward to answering your questions. Thank you for your opening statement. I'll begin with questions and yield myself five minutes. Dr. Martinez Lopez, I find it convenient that in the report to Congress you cited in your testimony and the same report that the minority just entered into the record, that the researchers chose to use 45 days as the at-risk period following a COVID-19 infection, but only 21 days for the at-risk period following the COVID-19 vaccination, especially when the administration and the Biden administration, the CDC, told everyone that you weren't considered immune immediately after the shot. Seems to me like you were skewing the data to make it fit what you wanted the conclusion to be by doing that. And to justify your use of the vaccine, you also admitted that the sample sizes are inaccurate due to underreporting. So how are we to trust the department and the Biden administration that you all are being honest when it reaches a conclusion that all of these medical problems were due to the infection and not the vaccine? Mr. Chairman, as a retired soldier and now giving the opportunity to serve the safety, the health, and the readiness of the force and the service members is the most important to me. The data is very clear, you know, that you have higher risk of developing these conditions if you just got the disease without the vaccine. The vaccine doesn't exempt you from getting some of these complications, but it really does decrease the risk to the service members. I would defer to Dr. Stallman on the 45 versus the timeline differential. Sure, thank you. As an epidemiologist with the DHA, I am concerned as well with the health and wellness of our service members and we take reports of any increase in medical conditions that are potentially due to the vaccine or to the virus seriously. In that report, we worked with cardiologists, specialists within DHA to determine the best risk window to use when looking at an adverse event in relation to the vaccine or to the virus. If you're looking at an event due to a vaccine, say five years later, it becomes less likely that that event is actually due to the vaccine because you've accumulated much more exposures over time. So in talking with cardiologists and SMEs and in the work that the immunizations healthcare division has done in clinically following the myocarditis and pericarditis cases within DOD, we knew that most myocarditis and pericarditis cases when they occur due to result of vaccine will occur within 21 days after the vaccine. We also know in working with cardiologists, experts within DOD that if you're going to have myocarditis or pericarditis event following COVID-19 infection, it's most likely to show up within that 45-day period. So we chose that period because we're using administrative data. We were not able to go in to confirm that the event was clinically ruled out due to some other conditions. So using administrative data, you have to use a risk window period so that it's likely you're looking at an event that's due to your exposure. Okay, so on that point, either one of you, can you tell me how many new cases of myocarditis there were among active duty service members in 2020? Thank you. There are around 100 to 200 cases of new myocarditis among active component service members each year. What about 2020? Obviously you tracked this. We do, but I do not have that exact number in front of me. Okay, so according to DOD data obtained by Senator Ron Johnson, there were 275 new myocarditis cases among active service members in 2021, which is a 151% more than average over the five years prior. And the reason I bring that up is because I asked your office before this hearing to give me that specific number, and you gave me the 20... Instead of giving me the 2020 numbers, you gave me the 2021 numbers. So it's very suspicious why you wouldn't have that data available when you had an exact answer for me for 2021. Thank you. We do have the number. I do not have it in front of me. It takes our analysts time to write programming code to pull the data. It then has to be reviewed by an epidemiologist to ensure that the code is accurate, that the output is accurate, and we will get you those numbers. Can you at least remember if there were fewer cases of myocarditis in 2020 than what there were in 2021? I mean... I believe they were higher in 2021 than in 2022. As the report, the DOD report on cardiac and kidney issues shows, there was more than a 10 times increase rate in myocarditis among active component service members who had a recent COVID-19 infection compared to a 2.6 increase rate among active component service members who had recently received the COVID-19 vaccine. I'm going to yield five minutes to Ms. Takuta. Thank you, Mr. Chair. Just some basic questions, perhaps, so that we get a better understanding of the research and the data that you folks have been doing. What does DOD currently use the DMSS, the Defense Medical Surveillance System in D-Med, Defense Medical Epidemiological Database, data form? I'm just trying to get an understanding of the regular practical uses of the data beyond research. Congresswoman, we take very seriously... I mean, data to formulate policy is critical to us, especially when it comes to clinical policy. So I'm not the expert, I would defer, but I'll open up saying we have two systems. We have multiple systems. The two key systems is the DMSS, the Defense Medical Surveillance System. That's a relational database that encompasses pretty much all the many of the healthcare points of every service member since 1990. And then we have another system, it's called D-Med, the Defense Medical Epidemiological Database. That's not a database, that's a web-based tool that actually can perform queries into the DMSS. But it's really for the field that that information is not identifiable for a particular patient. So it gives the people in the field an idea that something may be happening and that's what we want. But then if you have a question about something happening, then we have to do further studies using the other system, the DMSS. But I'll defer to Dr. Almans if she wants to expand. Yes, thank you. D-Med is used more as hypothesis generating. It allows users to do certain limited canned queries of the data. The default output, if you do a query on D-Med looking at a certain ICD diagnostic code from a drop-down list that you can choose, the default output that it will give you include numbers of outpatient encounters with diagnoses made in the first diagnostic position. So it's a useful tool to get a quick idea of how commonly we're seeing encounters for certain conditions. It can also do very basic population level queries. It does not contain any information about a vaccine. The Defense Medical Surveillance System is used by health analysts at the Armed Forces Health Surveillance Division to do comprehensive health surveillance for service members. It's the data source that feeds the D-Med. So D-Med is refreshed on an approximately monthly basis with data from the DMSS, but just a limited amount of those data. Thank you. That differentiation is very helpful. I think part of it is while D-Med seems to be more of that open source that you have it is also very limited. And if people do not understand that in fact it is an aggregated data set, it's not disaggregated. Obviously because you have privacy issues, although you could potentially de-identify some of that, but because it is not disaggregated out, you really can't differentiate between new encounters, follow-up encounters. I believe that's something that you've referenced in your testimony that this D-Med is very much limited, potentially open to misinterpretation of results for those that are using it in the field to try to figure out if something is happening. So my question would be given that it's subject to misinterpretation and it's very limited in its scope because it is aggregated, has there been conversations about perhaps making D-Med more of a disaggregated type of system so that you can get truer results if you are actually using it? I mean, if not, it's always going to be subject to potential misinterpretation by the users or limited by user understanding of the data that's within it. Congresswoman Vanityen, the intent of the D-Med is to have it available across the force as a first trigger. In other words, you have a question, you have a query. But I guess my concern is you have it as a first trigger, but if the user is unsophisticated to understand that it is limited, what you're going to have out there is misinformation and false assumptions. So I do feel that we have to make sure when we do have these data sets that it gives the most accurate information possible and is this user-friendly as possible. I think right now the way D-Med is, you know, it is great that it's there, but I think it is going to be subject to more misinformation and false assumptions being made if users are unaware of its limitations and misinterpreting the data that they're getting from it. I know, Chair, I'm almost out of my time, so I'll just yield back to you. Thank you. Yield five minutes to Mr. Gates. Dr. Martinez Lopez, is the Department of Defense covering up vaccine injuries? Congressman, no. So who is Lieutenant Ted Macy? Congressman, I don't know the Lieutenant. Well, it's sort of the reason we're here. On November 27, 2023, Navy Medical Corps Officer Lieutenant Ted Macy shared a video on X where he expressed grave concern for his patients suffering after receiving the COVID-19 vaccine. And according to Lieutenant Macy, he tried reporting the DoD data from the D-Med system to his superiors, and he was subsequently silenced and punished. He lost access to the D-Med system. He's been removed from seeing his patients and has been relegated to some broom closet somewhere to continue his service. It seems to me that Lieutenant Macy has suffered more than the people who screwed up the D-Med system. So why is this person being punished for trying to showcase data that was alarming? Congressman, I'm not prepared to talk about specifics on the Lieutenant because I really don't know. But I'll be glad to answer any questions regarding the system of the vaccines and our findings. Well, right. But part of the system and the vaccines and how we conduct oversight is that if there are whistleblowers who say that you're not doing your job right, if there are whistleblowers concerned about a cover-up, there's a process by which that has to get to the Inspector General and be reviewed. And in the case of Lieutenant Macy's concerns, those languished for like more than five months. Do you have any reason why a request made through the chain of command to view this data that could illuminate concerns over vaccine injuries was smothered? Again, Congressman, I'm not prepared to talk details. Okay, let's maybe let's get to what you're prepared to talk about. Let's get to the actual data that's so concerning since the people who raise concerns about the data, they get punished and we don't seem to remember them. The hyper-tensive diseases up 23 percent when you compare the 2016 to 2020 averages to cases in 2021. Does that sound right? That sounds right. Okay, so hyper-tensive diseases up 23 percent. Then ovarian dysfunction up 35 percent. Does that sound right? I'm not specific. Can we... Does that sound right, Dr. Stallman? I think you're referencing something from an older document, but it could be. I'm referencing data from the Defense Medical Surveillance System. Is that a system that you're both familiar with? Yes. Okay, so that system says that hyper-tensive diseases up 23 percent. Ovarian dysfunction up 35 percent.  Myocarditis as Chairman Banks was describing, up 151 percent. Is it really your testimony that these massive spikes in these serious ailments are a consequence of contracting COVID? Is that your best medical opinion? Congressman, not all, but I mean many of them obviously ovarian dysfunction. There are other reasons, emboli, there are other reasons, but yes, there's an influence, there's a correlation not only from our data for the data of CDC that yes, correlate COVID with having higher likelihood of having some of these... Yeah, just pardon me for not treating the CDC assessment. Not the ovarian one, but the other... The vaccine or the virus? Both. The virus, and you know like the cardiomyopathy is a little bit higher. The risk is much higher if you just get the disease, but you have an enhanced risk, not as big as when you get the infection, but you do get some risk from getting the vaccine. It's minimal, but yes. So there is vaccine risk associated with hypertensive diseases, right? Hypertension, help me out. Not that I'm aware of, but... Okay, well how about ovarian dysfunction? Not that I'm aware of. And how about pulmonary embolisms? Yes. Okay, so you're here giving us testimony that the vaccine increases someone's risk of pulmonary... No, pulmonary the COVID virus doesn't... I'm asking about the vaccine. No, the vaccine. Not that I know of. No, and myocarditis, you think there is a risk? Yes, a slightly higher risk, but it's much higher that when you get the virus itself, when you get infected. And to tease out those data distinctions, wouldn't it be responsible to assess these conditions in people who got the disease and were unvaccinated versus the people who got the disease and were vaccinated? Has that type of an analysis been done? We did look at this in the DOD report on cardiac and kidney conditions. The information stratified by all the different ways vaccinated not vaccinated, those are not all included in the report. I do have the data on that. When we reported the 10 times increase rate due to recent infection that is adjusting for vaccination status, it's also adjusting for demographic risk factors, including age, sex, and BMI. Right, so did that analyze ovarian dysfunction? It did not. Did it analyze pulmonary embolisms? It did not. And did it analyze hypertensive diseases? It did not. Well, I mean, we got thousands more people than the average in 2021 getting hypertensive diseases, thousands more people getting ovarian dysfunction, thousands more people, or I'm sorry, hundreds more people getting these pulmonary embolisms. What's the case against analyzing those conditions that have seen these increases in the vaccinated versus the unvaccinated? We are continuing to do surveillance on these conditions, and we are open to doing additional work on this. With chronic conditions, it is tricky to look at that in relation to a vaccine. Is a pulmonary embolism a chronic condition, or is it an acute condition? We can look at acute conditions. Yeah. Your medical knowledge goes far beyond mine, but I would consider a pulmonary embolism acute, not chronic. With hypertension, it could be difficult to get causal evidence to link that to the vaccine, but yes, we can look at acute outcomes. Right, but see, that's how you get the causal evidence.  And in the one area you've looked, myocarditis, you're here giving testimony that that actually causes this increased risk factor. And so, Mr. Chairman, I hope we continue to follow up on this because my deep concern is that there is a cover up here and that they're playing games with the data so that we can't actually assess whether it's the vaccine or the ailment that is causing these acute conditions. And wouldn't it be a tragic thing to have to discover that we hurt people with the vaccine more so than the virus did with the ailment, particularly in a condition where now the CDC, whose opinion, I guess, we treat like the gospel, is saying that you just should quarantine for 24 hours after you're done with your fever. So they have evolving sensibilities on this. And the only way we get to the bottom of it is that data comparison. I thank the Chair's indulgence and I yield back. Thank you. I agreed. It's why we asked for the 2020 figures. And I didn't ask you on the record before, but will you please, will you submit the 2020 figures to the committee? Can we take that for the record? Yes, sir. Okay. Mr. Moylan. Thank you, Mr. Chairman. Dr. Martinez-Lopez. And by the way, thank you for your service and military. I appreciate that. And our guard unit back in Guam, Air and Army, were also very responsive to the COVID-19 situation. They played a big role in supporting our island. And our adjutant general, he has a lot of medical background too. He's a surgeon as well. He's really concerned now we need to be ready for the next public health emergency on Guam. After all, we're Indo-Pacon region. We're the most western territory. We need to protect our community and our troops. So what I need to know is your interest in the Indo-Pacon area specifically in Guam to support our National Guard and Air Force out there because they need to be properly staffed. So I need to know your interest in that and making sure their training is up to date and equipped as well so we can have, we'll be ready for the next pandemic health emergency. Congressman, we are actually, I'm intimately involved with the issues of Guam. I'm very concerned about that. My concern is that we have the systems, not only for reserve and guard for the many active duties that we have in Guam and family members. We are concerned about biosurveillance, making sure it's not just about COVID. Not only about the things we know, but the things that we may not know coming about. And we want to make sure that, A, we detect them early. And number two, we have a response mechanism to ameliorate whatever threat comes in Guam way or any other way. I appreciate your concern and your continuous interest in the Indo-Pacon specifically, thank you for that. Another question, doctor, what do you and Admiral Valdez need to safeguard the Defense Health Agency's ability to support the military readiness if we were enter a conflict in Indo-Pacific? While ensuring patients do not experience a lap and care, what steps are you taking with stakeholders or doctors, hospitals in on Guam to prepare for future conflicts? We're way out there. We have no support from the mainland. Time is of the essence, please. Congressman Lutheran General Crossland just came from the theater, went to visit Guam and visited with many of the civilian and military leadership on the island to address the medical. She's the director of the Defense Health Agency. And she came back with a report, you know, trying to understand, she understood what the issues are. Now we're working through how we're going to counter whatever gaps she found on her trip. This has to be a two-way conversation not only the military leadership, it has to be with the civilian leadership of the island, medical in the medical aspects. So we make sure that at least that we do our best to be in a good position to respond to any needs that in particular our service members and family members need. Very good. And final question, doctor. Currently the U.S. Army Reserve on Guam carries innovative readiness training mission in one of the villages, Jigo, to provide medical care to my community. Efforts like this are important for building good will between the people of Guam and the military, especially as the department plans to station increasing numbers of personnel on island. What can be done to expand efforts like this? This is very good for our community as well. Congressman, is in our interest to aid to, you know, have our troops ready and prepared to do the care they're going to be asked to do in combat. The way we achieve that is by seeing patients and taking care of patients. If there's an opportunity, you know, a mutual opportunity by providing care to the local communities, we also enhance our skill sets as clinicians. That's a win-win for the department and our neighbors. So we are pursuing this, not only in Guam, we're pursuing this across the country. In those places where we can have a mutually agreeable and acceptable benefit, then we're going to exactly go in that direction. And I hope that there will be many opportunities in Guam just to do that. I appreciate that. And I look forward to working with you closely on how we can assist as well. So thank you for your efforts. Thank you, Mr. Chairman. Thank you, Mr. Chairman. I appreciate you both being here. Although I must say there's a growing trend within the DOD that my colleagues recognize as well where people come here unprepared to be able to have the substantiated data that we require and that we've requested to make sure that we're able to get the answers and follow up. This is not the first time, so I hope that in future hearings you'll actually make sure that we have the subsequent data that we're trying to ask for and all the algorithms and all the other data planning has actually gone forth. I want to start out with the fact that kind of following along one of my colleagues, Mr. Gates' testimony, where he talks about how many people have been impacted negatively, whether it be by myocarditis, whether it be by ovarian issues, whatever the case may be, in addition to those who are unconstitutionally purged out of our military for religious and medical freedoms that they should have been afforded. So I just want to say for the record, do either one of you have an opinion, an objective opinion, on whether or not you feel that medical and religious freedoms should be a key element for all members of our armed forces? Congressman, DOD is committed to protect religious liberties. As you know, there's a process to request. Actually, I do know that process by the way, and I got to say if it was actually to be true would be impressive because on average they were able to adjudicate through six individual layers per the Under Secretary of Readiness who is here in less than five minutes. Imagine the ability to reach out to a minister, to a priest, to other religious figures who they actually are trying to get this counsel from or looking at their individual medical background from historical medical data from their families and being able to determine that in five minutes. I can tell you, as a person who now works for the federal government, we are not that efficient. If anything, it would take us about five weeks to be able to do so. But they were adjudicating these in less than five minutes. Do you think that they could adequately adjudicate a medical or religious exemption within five minutes or less? Congressman, I'll have to defer to the services that executed that for us. You know, there has been an admission to the significant errors in the defense medical epidemiology database that disordered the true numbers of medical encounters faced by service members. How can you be certain this issue has been satisfactorily rectified as to not continue to mislead the American public? Thank you. I could take that. We do take data accuracy seriously. We know that data goes into making decisions about healthcare that's provided to service members. When we became aware of the programming error that was done in DMED, this was in January 2022, the error, by the way, was an analyst had used a count function instead of a sum function, which led to the data that existed between 2016 and 2020 to be corrupted. That error was immediately corrected. Since then, we've implemented both additional technical and functional controls. So on the technical side, they're doing additional QC steps. We've also implemented a functional team that's doing additional quality assurance checks on a periodic basis. So this is for both of you, and I'd really like to hear your thoughts on this. Uniform service members were expelled from the military and punished for standing up for their personal rights. How do we ensure that they are properly compensated for rightfully expressing these rights? How do we address the discrimination and mental drain that these individuals have faced and continue to face by things such as giving them a general discharge as opposed to honorable? Also the DOD forcing individuals to pay back their bonuses where they did not separate from the military at their free will. They were forced out of the military. What would be your recommendations and how we would adequately compensate these individuals unconstitutionally purged? By the way, almost 9,000 who was unconstitutionally purged, in addition to the 41,000 recruitment deficits, pretty significant for the largest volunteer force in the world. Congressman, as you probably know, all those service members had the right to appeal the discharge to the services. Dr. Martinez Lopez, we have seen where many of them had tried to appeal this and in many cases wasn't actually given any answer whatsoever. Again, we can adjudicate things in five minutes whenever we're denying people their medical and freedom religious rights, but we can't actually adjudicate something quickly where it should be a simple thing that if you did not exit the service for something which was disciplinary and reasoning, not medical and religious freedom, but disciplinary and U.C.M.J. Article 15 or above, court marshaling, then I don't understand how we can at least acknowledge the fact that this is unconstitutionally purged and at least given the opportunity on an honorable discharge as opposed to a general where in many cases this plagues them and follows on in their careers and in future jobs. But that still doesn't answer the bottom question, which is that these individuals, and my personal opinion, I know there's others on this committee that feel the same way, should be compensated. They should have their benefits restored. They should have their original rank reinstated for those who actually still want to serve our country, not a political agenda that is placed before us. And they should be given the rights that they were actually denied. Would you not at least admit to the fact that these people who are trying to serve as you have served and as I have served should be denied these rights or be given these rights? Congressman, we have processes and there are laws and processes in the system. I hope that the services who, you know, I'm confident the services are doing their best to exercise those procedures to look at the Arish case in particular. I'll have to defer to the services. I appreciate that you have the confidence. I wish that I had that and shared that same confidence levels. But under the direction of someone like Secretary Lloyd Austin, I have very little when you talk about the dereliction of duty that has been placed forth and the prioritization of things that are not to the military armed forces as benefits. With that, I yield back. Thank you. I want to thank Mr. Gates who just left the room for requesting this hearing. I think it's a really important conversation, the type of oversight that this committee should be doing more of. It's important that we work together to differentiate between the rise of medical conditions due to COVID-19, the infection, or the COVID-19 vaccination. This effort is vital for guiding public health responses, informing treatment and management strategies, monitoring vaccine safety, and maintaining the public trust and immunization programs. By systematically investigating and addressing these concerns, policymakers and healthcare professionals can effectively safeguard public health and the health of our men and women in uniform who put their lives on the line for this great country. I want to thank both of our witnesses again and thank you for providing your testimony and answering your questions this afternoon. I want to thank the members who participated. There being no further business, the subcommittee stands adjourned.


Let us do a very brief refresher on what is indexing, first of all what is a sparse index shown by this example, I have data which is stored sorted on the ID here, so now this is an index which helps me quickly find an ID that I want to find. The index does not have an entry per ID otherwise it will be very big, it has one entry per some number of IDs here. So first I will search, if I want to look up ID 45565 I will go here, is it greater than 101, well this index starts with the lowest value but I could even not store this value and it will be fine. Is it greater than 32343, if not look at the previous pointer otherwise check if it is greater than 76766, the value I am looking for is 45565, so it is greater than this but less than this, therefore I will follow this pointer and then search sequentially here which I will reach it pretty fast, this is the basic idea of an index, this is a single level index, of course database data is very big, single level index is not sufficient, so what is actually used is a multi level index. So the way it is organized is data is stored in blocks here, sorted on whatever key the index is built on and what is stored here I will come to in a little bit, it could be the actual record, it could be a pointer to the record. But regardless of what is stored here, if I build an index on a particular attribute or set of attributes, you sort the data on that key and put it in blocks and then the pointer here you know I will start with the outer index, find the appropriate pointer, go in there, within that I apply the same algorithm to find the data block and then in the data block I will find the key that I want, this is conceptually what tree indices do in general. There are many, many types of tree indices but all of them use basically the same concept. So each level of the tree index is a sparse index on the lower level, so this level is a sparse index on this, this level is a sparse index on this, why sparse? If you see for maybe 100, 200 entries here I have just one pointer here, now each of these blocks itself will have several hundred entries, but there is just one pointer here for several hundred entries, so it becomes smaller, each level becomes like a hundred times smaller than the previous level. So with a few levels I can quickly find what I want, now each level here is potentially a new disk seek because each of these may be at some different location on disk, each is a block, may be somewhere on disk, so if I want to fetch a block I have to do a disk seek. On hard disk how long will this take, well if it is already in the, in memory buffer it may be fast but if it is not it might take something like 5 to 10 milliseconds. So if I have a disk which takes say 5 milliseconds for a request, how many requests can it satisfy in one second, 200 requests. Now if I am building a website with, so first of all 200 is the limit for one disk. Now for each level in this index I may have to do a disk seek. So if I have an index with 10 levels I will have to do 50 milliseconds and then instead of 200 lookups I can only do 10 look, sorry 20 lookups. So this tree has to be short, so an index like this is always short. The second thing is if you keep inserting deleting data how do you maintain this index, what if too many records are deleted from this block it has very few records what do you do, you need to maintain this index and the key idea of B trees which is proposed in 1972 long time back now, 40 plus years now is that they adapt bit by bit each time you do an insertion or deletion either no change happens or a small change happens but you never have to go and reorganize the whole index at once, that is the key idea. How many of you do not know about B trees, all of you have few, so since there are very few I am not going to actually cover it here, I am not going to cover basics of B plus trees but I will cover some more advanced topics, so all of you know this is a B plus tree. So there are some examples in the book of B plus trees, we are going to skip all that and I just want to say a little bit here actually this slide is outdated, I am going to replace it for the main workshop with newer slides. So the key point I want to make here is if you have a unique key that is there is only one record with a particular key, not more than one record with a particular key it is very easy to understand how to use a B plus tree. So all the standard B plus tree algorithms make this assumption to keep life simple, if you see any textbook description of B plus tree typically it will make this assumption that the key is unique, in fact keys need not be unique, why? If it is an index on a primary key yes it is unique, no problem but many times I want to index on something which is not a primary key, if I want, if I am a bank or a university and I hear that so and so student has done something I want to find out who is this student I do not know their roll number, I want to index on name, so I can look up the student by name, now there will be many students with one name, let us say I do it by first name or last name separately, there are many people with the same first name, many with the same last name, so now the key is no longer unique, there are many records with the same key and I need to store pointers to all of them, what to do about this, this is an issue which any real system has to address and by and large textbooks show this, we also kind of did this we in the sixth edition we decided to address this issue and say how do you handle duplicates and in the hurry to put it out we goofed up and our algorithm for handling duplicates actually has bugs, we have corrected it in the Eritre, we realized it soon after it was published, so if you read the sixth edition and read the part on B plus trees, please read it in two ways, one is assume there are no duplicates there is no problem, everything works, we do talk about duplicates but there is a bug in the way we have described it, so for the case with duplicates please look up the Herata, now it is possible that you have a more recent printing where the Herata was fixed, we did give it to the publishers but it is all even more likely you have a copy I do not know the current one printing whether it has been fixed but in all the older printings this bug is there, so please look up the Herata page, okay so that of course leaves the question and so how do you deal with duplicates, so there are several options, so oh okay this slide actually does have the Herata node, I thought this is a older slide, this does have it, so I do not have time to get into this now but I will just mention that you should go read this up to see how to deal with duplicates if you want, but in fact there is another solution which is also very widely used in the industry which is get rid of duplicates, how do you get rid of duplicates, I am going to tell you that my B plus tree implementation will not handle any key with duplicates, at the same time you want to be able to do lookups based on names which might have duplicates, can we do something about this, so the basic idea is B plus trees luckily support range queries, so if I what I will do is in the B plus tree I will create a key containing the name followed by the roll number, okay name has duplicates, roll number does not, name followed by roll number does not have duplicates, now my B plus tree code can handle this because it is unique, I do not have to deal with duplicates in my B plus tree code, but now what about my original problem, I wanted to find students with a given name, how do I do it, I can do a prefix query or I can do a equivalently a range query, I can say give me all names the key is name plus roll number, so name followed by the minimum roll number, let us say 0 0 0, up to name followed by the maximum roll number, that is the range I am looking for, so find me all things in this range and that will give me all the things, so when I insert there are no duplicates, when I do a single lookup based on name plus roll number there are no duplicates, when I get a range query that is not a problem, the standard B plus tree code handles range queries and gives me back a set of names, is this clear, I do not want duplicates, so I get rid of duplicates by adding something unique roll number or it can be a record ID or anything else and then I do a range lookup, so this is a standard trick which many databases use, why do they do it, are they too lazy to go implement non-unique search keys on B plus trees, turns out there are other good reasons for doing this and the book talks about some of these reasons, in particular efficiency of deletes is better if you make it unique and the second thing is concurrency control techniques are a little easier to deal with and even the code for handling B plus trees splits and so on is easier if you make the unique assumption, but we have discussed this in the new edition of the book, in the old editions it was not there. Now there are several implementations like I said, one is to keep actually duplicate keys, keep a list of duplicate, sorry, keep the key ones but with that key there are many records, you can keep a set of record pointers or make it unique, so that is where we left off. Then the next topic is what should the index contain, should it contain a pointer to a record which is stored somewhere in the database or should it contain a record itself, the contents of the record. If you ask this question about say 15 years back or 20 years back, pretty much all databases would store the record in some place when the record is created in the system it goes to some page and it lives there for the rest of its life, unless for some reason the record increases in size, there is no space in the page, it has to be moved. Moving of records was a rare occurrence, they land up at one place and live the rest of the life there, this is like being an a government job, get a government job, be there tell you retire, but today's world is a little bit different, people keep moving across jobs and it is no longer viewed as important that a record sits in one place for its whole life. So why does this matter? So if you go to an old database and say that I want the data sorted in some order, I want to fetch all records in a certain key range, I can build a secondary index on some keys, but the records will be scattered all over, if I want all the records in a range they will be scattered all over, now it is very inefficient to jump around on disk and fetch data. So there are a lot of examples where you can see a huge performance difference because of this, so we had index which would index a record on keywords and the records were not, the index records themselves were not organized properly on keywords, I won't get into details, but we had a table which had a keyword to record mapping and that table was indexed on keyword, but the table was not sorted on keyword initially. So now to fetch all the record IDs which are scattered all over would take a very long time. So then we told the system in this case PostgreSQL, sort the file, sort the relation and once it did that suddenly if you wanted to find all the records with the keyword they were all together, what took 20 seconds would now finish in half a second, it is that kind of huge difference. So you do want in many cases data to be sorted so that you can fetch many records which are co-located. So old database systems would allow you to take a relation and say sort this relation now and build an index after sorting it, but you could only sort a relation in one way, if I want two different sortings tough luck. So in PostgreSQL as of at least 9.1 they may change it in future, as of 9.1 this still had, I could say sort the relation, cluster the relation and I sort it on something if I build an index on that attribute on which it is sorted is very efficient to find all records with certain range of key values. But supposing I want the same relation I want to index it in this way on two different attributes, what to do. So the solution to this there are actually two possible solutions, one solution was to say look I will take the record sorry the index, the index is on let us say word, but I will create an index not just on word, but on word comma the extra data which I want. So in this case I wanted record ID which is one of the fields of the relation. So I build an index on word comma record ID. Now when I go through this index contents what happens? I will find all the word record ID pairs in the index and I may escape not even going to the actual relation that will also have a pointer to the relation, but I need not go there because I already have the data in the index. So this is called index only access, index only access is very useful because it can get records which are clustered even though the relation may not be sorted on that attribute. So what about PostgreSQL does it support this kind of thing? It turns out PostgreSQL has a multi version concurrency control mechanism which interacts a little badly with this. So there may be two versions of the record. Both those versions are there in the index. One of the versions is old, one is new. Looking at the index I cannot tell which is the version that I am looking for. I have to go to the record. So there is a slight catch. So PostgreSQL did not have this earlier. I think in more recent versions people have been working on some very clever hacks to avoid the lookup for most records, to pay the extra lookup only for some records not for all. So they are working on that and there are some nice ideas there. But other database system we did not have multiple versions. It was a lot easier for them and so they could provide index indices where you can add extra attributes. They are called covering indices then index only access. So many databases supported and PostgreSQL has started supporting parts of it now. That is one answer. The other answer is what about storing the records themselves in the B plus tree? Why store a pointer to a record? Why not store the whole record in the B plus tree? So this is called a B plus tree file organization. So the records do not live outside of the B plus tree. They live only in the B plus tree. This is why they live. In the earlier systems the record would be put in some page when it is inserted and it lives there. Now it lives in the B plus tree. Now all of you who are familiar with B plus tree know that there is a difference. B plus trees are dynamic. Pages get split, records move. So earlier there was a notion of a record ID and a typical record ID was like a page number. Like in the file which page in the file and inside that page which record and the record never moves. So the record ID never changes for a record. Now when pages split in a B plus tree the record moves. So what is the record ID? Now why do I want a record ID? If I have another index on that same relation it needs to store some kind of a pointer to the record. And that pointer used to be the record ID, the physical record ID, the location in some sense of that record. But now you cannot do that because every time a page splits half the records in that page move and if I have to go and update all the other indices that point to this record it becomes very, very expensive. One page split can turn into 50 different page accesses or another indices each of which has to be updated. So it becomes very slow. So this is one reason people initially did not implement B plus tree file organization. But eventually there is a simple solution to this problem which eventually many people implemented. And the idea is in the secondary indices, so there is one index which is the primary index. The primary index does not have to be on the primary key, it can be on any key, it is clustered on that and the records are stored in a B plus tree file organization, the record is stored right in the leaves of the B plus tree. And now there are other indices on other attributes. Those indices no longer store a record ID, instead they store the primary index search key. And that has to be made unique of course. So some form of unique ID for the record has to be primary key, also has to be part of that. So if the relation is, let us say sorted on some key C, you will add the primary key of that relation or some other unique key, append it to C. So it is sorted on C, but you can still have a unique ID for that record. And so when a split happens here, nothing happens to the secondary indices. Why? Because they stored the key value. When a split happens, the key does not change. And normally you will use something which does not change typically, that is what you would like to use. Because if that is updated then you have to change it in all the indices, but that is part of life. If you update a record, you have to go change all indices where the record occurs, so that is okay. But when you split a leaf, you do not want to go and update all other indices. So now you suddenly touch hundreds of records, you do not want to do hundreds of updates. So this takes care of that. Now what is the cost of this? This means when you use the secondary index, you traverse down the index, you do not find a pointer to the record. You find a key. You have to take this key, come back to the primary index and then search down again. So instead of one B plus three traversal, you have turned it into two B plus three traversal. So that is the price you pay for it. But it has been very useful for many applications. So one after another, all the commercial databases supported this about 15, 20 years back, they all supported it. I mean the idea is very old, but they had not implemented it for many years. Any questions? This is regarding, in 1995 itself we started using one tool which is called as fragmentation, defragmentation tools. Is there any relationship in between these two ideas? In some sense, yes. So disk be fragmented. What do they do? So if you have a file which is fragmented, that means pieces of the file reside here on disk pieces, the next piece there, the next piece somewhere else. And when you try to read that file, it takes a long time. And this particularly starts happening if your disk is full. Any new files you create in a disk which is get over say 90% full, tend to be fragmented. So the way to improve performance is to re-sort all the files on disk, reorganize such that all the blocks of a file are sequential on disk. So if you read them, you do not have to move the disk come around. That is very expensive. So disk defragmentation essentially sorts the blocks of the file and in the order, sorts meaning the physical layout is in the order that they appear in the file. So clustering a relation, like I told you, is kind of like this, but at a logical level. At the physical level, how the system manages it is up to the system. But what happens is systems are usually good at this. So if you sort a file, database systems will make efforts to keep the blocks consecutive. So there are some nice tricks which they use. They cannot be guaranteed to be consecutive on disk, but it will do a best effort so that you minimize the amount of disk movement to scan a particular file. One more question. This is related to that composite key. So we are using composite key in the relational database management system. It means that we are going to treat more than one field values as a primary domain values. Composite key is one which has multiple fields put together. And you use the normal sorting order, first sort on the first key, then on the second and then on the third. So they are very useful for many things. So it is common to build indices on a composite key. And then you can actually do an index lookup on a prefix of the key, if you wish. So that is what we did actually. When the thing I told you, first you have some key, second which may have duplicates, and then the primary key, that is a composite key, because it had two attributes. And you can do a lookup on a prefix which will return possibly many records. So in the B plus T file organization, the complete record would be copied in the index file or some partial record, typically the columns which are frequently accessed. So if you store only some columns, then you have to store the entire record somewhere else. So that is a regular index with extra attributes and that is called a covering index. So when you store the record itself somewhere else. But when you store the record itself in the leaf of the B plus T, then it is a B plus T file organization. Sir, DBMS have three level architecture. And at the physical level, it is defined that we have the frame memory model and unifying model. How they are related with this indexing part to the system at physical level.  Those are all very high level concepts which don't have a direct bearing on this. So that is something which we have already dealt with. All of this is a physical level. Relations are the logical level. We are working at this point. We are moved from relational database design to storage and indexing and so on which are all purely at the physical level. So the other levels are irrelevant to us right now. Now when you're doing database design, you need to worry about the other levels, but not so much now. Although in some cases, depending on how things are accessed at higher levels, you may choose appropriate physical structures below. What do I mean by this? Supposing you have a lot of queries which retrieve a record based on some key, then you should build an index on that key. So what you do here depends on what queries are being executed on the system. The queries don't know about the physical layer. They are working at the logical level. But your physical design is generally optimized for the workload which you find on the system. So that choice of what indices to create, and I mentioned materialized views earlier, what materialized views to create and so on is part of the physical design of the database. And that's an important part of tuning a system. We don't have time to get into it. Sir, from which interface we can put this type of indexing, B3, or we want to change the structure of the organization of the files in the DBMS? Yeah. So most databases will provide SQL extensions. So the SQL standard try to stay out of this. But most people, most databases provide a fairly standard syntax for create index. Now the create index command can take a number of extra arguments which are database specific. So there you can say you want a B plus 3 index, you want a file organization, you want it this way, you want it that way. So there are many options which you can specify. So PostgreSQL has many such options. Oracle has its own set of options. So that is database specific. But you can do all of this from the SQL interface. In a B plus 3, B plus 3 file organization, you said that entire records will be stored. What if records are of variable length? How node size will be defined initially? That's a good question. What if record is variable length? How is it stored? So as long as the fields of the record are relatively small, and the whole record is smaller than a page, you can store records with a variable length. If you want to know how the records are physically stored, chapter 10 has a description. Let me just briefly show that. So this is an example of a variable length record. So what it does is, there are four fields here. The first one is the ID, the name, the department, and the salary. Now if you see here, the first three are variable length fields. Name, sorry, ID can be variable length, let's say. If it's fixed length, it can be stored here. But variable length, it goes there. So what it's storing is, where is it starting in this array, and how long is it? So the first field is name. So it says 21.5. And there is a data dictionary which says that this is a variable length field. So I'll interpret these first few bytes as an offset plus length. So now I know it's starting at 21, and it's length 5. And I read those 5 bytes to get the ID. The next field is name. So I say 26.10. So I go to 26 and read 10 characters, get Srinivasan. The next one is 36.10. So I go to position 36.com side. The next one is 65,000. It's fixed length. So it is in line here. And then there's one extra thing which is null bitmap. So if any of these fields is null, you can actually store zero offset, zero length. But if this is null, you would store a bit in the bitmap here. So that's how variable length records are represented. So when you fetch this record, you have to interpret this structure and then extract the fields that you want. And in turn, this is one single record. Now how do you store these records in a page? You have variable length records in a page. If it's all fixed length, you divide the page into equal size pieces, it's very easy. If it is variable size, typically what is done is this is called a slotted page architecture. So you store all the records at the, let's say one end of the page. And then at the other end of the page, you have an array of entries which point to the records. In between is the free space, unused. So what does this header say? It says there are so many records in this page currently. And some of these may be empty actually. If the record is deleted, you may have a hole here. The entry will not point to anything. But if it does exist, it has a pointer to the place where the record starts. And then there's a length also which can be stored here. The size of the record is stored here. And the location is stored here, logically. I mean, physically in the page. The diagram is logical. Physically, it's all linear in the page. So that's how you store variable length records in a page. And if a record changes in size, you move records around in the page to make space. So for B plus 3, we say that we have to define nodes of size n. If it is n is 3, then it will have three pointers. And here, we are talking about p. I'm not understanding those two terminal, relation between those two terminologies. So again, I didn't talk about variable length keys. It's again there in the book and in the slides. For today, I skipped it. But since you asked that question, the initial coverage of B plus 3 assumes fixed n that you will have at least n by 2 entries for some number n, which is perfectly fine for fixed length records, fixed length keys, the B plus 3 stores keys. And if you are talking of a file organization for fixed length records. But if the key itself is variable length, this can happen. Name is a keys variable length. Or for a file length, a file organization, the record is there and the record can be variable length, as we just saw. So now, how many things you can fit in a page depends on how big the key or the record is. We can't guarantee this. But what we can guarantee then is not the number, but the occupancy of the page. So we can say that the B plus 3 page will be half full with data, at least. Maybe more than half full, but it will be at least half full of data. Now, how many entries are there? That depends on how long the keys are. We can't say that upfront. But if we say that the average key is so long, say average name length is 20 bytes, we can say the average entry is let's say 32, 35 bytes. Now we can calculate the average number of pointers out of the B plus 3. But we don't actually have a minimum number of pointers. But we can say that space will be half occupied, that we can guarantee. And now given a particular level of the B plus 3 and the average size of the keys, you can say that on average, each node will have so many children at a minimum. Some may have less if they have longer keys. Sir, excuse me. Sir, here. Yeah. Sir, you have mentioned that each record, the starting address and the length will be there. So for example, if the department, if it changed, that is if initially it's a computer science, if it changed, then the consecutive value address would be changed. Absolutely. So you will actually change the size of the record. So what happens then is you have to move some records here to make space for it. But you will keep it compacted, meaning you won't have holes in this area. And the important thing is you can move records in a page because externally nobody will store a direct pointer to the record. They will store an offset into this header array. So all you do when you move this issue, update the pointer in the header. So an external, let's say an index which is storing a pointer to record. The record is moving in the page, but the index does not have to be updated because it is storing an offset in the header array, not the actual data pointer. Even though that's a, it is in the continuous location, it will affect all the data. So yeah, it will affect everything in this page. So if supposing you change the size of this record, now everything here may have to be moved. But only in that one page. You don't have to touch other pages. By order, do you mean the physical order or the number of children? Sometimes the word is used for number of... The physical order. So if you create a B plus tree on a particular key, the thing is sorted on that key. Now how is the sort order defined? That is dependent on the implementation and on the type. Integer sorting is standard. What about sorting of strings? That may be language specific. You know, sorting in Hindi is different from sorting in English. So whatever it is, it's sorted in that order. Sir, it will be the different number of nodes for the searching in each label. If you have variable length keys, the number of children may vary depending, if you have variable length. Yes, there is a different number of nodes, sorry, different number of keys in a particular node. Yeah, it can vary. Even in a normal B plus tree with fixed length keys, depending on what order insertion deletion happened, a node can have anywhere from N by 2 to N, for some N, that many children. It can vary. So that will be, that is true for fixed length records. It is also true for variable length records. Only thing is with variable length records, you can't guarantee a minimum of N by 2. It depends on the record length or the key length. Excuse me sir. Yeah. The indexing will it work? I am seeing the option index or by default it will do the indexing for all the relations. So, okay, that's a good question. So what indices I interpret it as? If you create a relation, will it be indexed automatically? Is that what you mean? Yes. Yeah. Okay, that's a good question. So on pretty much all databases will automatically create an index on the primary key. If you define a primary key, they will create an index. Why? Because if we insert a record, they have to check that there is no duplicate. Without an index, it will be very expensive. So they will create an index. Most databases will also create an index on a foreign key. Why? Because if you delete the referenced record, then the referencing record is affected. Unless you have an index, it's very expensive to find out if there is a referencing record. So if you declare a foreign key, usually an index will be created on the foreign key also. These are the minimum set which most databases will automatically create. Now beyond this, what indices do you need? It depends on the queries which are asked on the system. So many databases have a tool called index tuning wizard or database tuning assistant or there are many different names for it. So what that tool can be used for is you know what kind of queries run on that database. You can actually run a test system and run the kind of queries which you expect to run. It can record those things. And then the system will see you're asking a lot of queries which are doing a lookup on some field which is not the primary key field. I will add an index for that. So that kind of distance, these tuning assistants, wizards, call it what you want. But there are tools. Post-critical, I don't know if it has one at this point. But the commercial databases all have such tools. So if you delete all the records for a particular key, then that key record will also be deleted from the index or it would still maintain it. So at the leaf level, that key will vanish. At internal nodes, the key may still be present. So we have an example in the book which shows this because the internal level, the keys are simply used to guide the search. It can remove it but it doesn't make any difference because the keys of the internal nodes are simply guiding. There's no implication that they actually are present in a record. So if a B plus three node becomes underfull, you are going to take action. You might merge it. Actually, many implementations don't do merging of underfull nodes. Only if the node becomes empty, do they do something. But that's an implementation choice. The B plus three algorithms allow you to do merging if they become underfull. And at that point, whenever any such operation happens, some keys may get removed. I want to add something. Sir, ask the questions about the logical level and physical level where the indexing will fit. As I think it will come in logical level. So the actual index construction is at a physical layer. But the decision to build an index, so the interface says build an index on this attribute of this relation. But the index itself is very much part of the physical design. It's not part of the logical design. That's how the layers are defined. The logical design only sees a relation. It is not worried about performance. Indexing is a performance issue which is at the physical layer. Just it is easy to access or fast. Nothing more than that. Exactly. That's why it is a physical feature, not a logical feature. It could be a change when you change the function or the fetching. You can add and drop indices. Applications will still run. They may run very slowly. But they will, it won't affect the correctness. So it's purely physical. Anyway, let's move on.  I'll stop questions on indices here. Just want to mention a few couple of words, buzzwords. So one of them is called bulk loading of indices. And the idea is supposing you take a relation and fill it with a lot of records, one at a time. And you have defined some indices on that relation. Initially, everything will run fast when the relation is small. But if the relation becomes very big, you will find that it takes a lot of time to add all the records to that relation. So any book on tuning will say first drop the index on the relation, then add the record, drop all indices on the relation, including the primary key, drop everything. Then add the records, then create the indices back again. Now, why would they recommend that? And the answer is that if you insert records one at a time into the index, it's a lot of IO which happens. On the other hand, there is a technique called bottom up building bulk loading of B plus 3 index, which is much more efficient. It basically sorts the records and then creates a tree with far fewer IO operations than if you inserted one record at a time. But it's much, much faster. I mean, it's like something which takes hours can run in minutes. That's the kind of difference which people actually see. This is not just hypothetical. It's real. So what has happened is the underlying B plus 3 implementation when there's already a relation, you say create index on it. It doesn't do the insertion as we saw. Instead, it does sorting and builds it. How does it do it? Again, the details are there in the book. This is the bottom up build. And so that is why the tuning books will always tell you drop indices, insert records, then rebuild indices. Now, the question arises, what if you can't do this? If you are, I mentioned big table, Google's big table as a key value store.  Now, there, if you say drop the index and rebuild it on one billion keys, this is not a joke. It will take a huge amount of time to build the index. What happens to applications which run during that time? So there's a bunch of research which happened on this, including some which we did in IIT Bombay back in 1997 on how to have efficient insertion into B plus trees. And one such technique is used in big table. I won't get into the details. To make inserts into the big table B plus 3 index much more efficient than the normal B plus 3 insertion. If you're interested in it, there's a paper on big table which describes all these details. You can go read it. The big table is essentially a enormous B plus 3 index spanning many, many machines. So it's pretty cool. If you're interested, do read that paper. That's the very last slide on indices. I have focused on B plus 3 indices. It's not the only kind of index though. There are several other kinds of indices. There is something called a bitmap index which is primarily used for attributes with very few distinct values. And its goal is a little different. Usually, its goal is not to find a few records. Its goal is usually to do some other operations such as what the bitmap index does is it creates a bitmap which says that a bitmap for a particular value, let's say I want department is com side. Let's say the institute has only 10 departments. A bitmap index will create a bitmap. One bitmap, one array for computer science. For every instructor who is in computer science, the bit will be one for others, it will be zero. Now what do I mean by that? The instructors also have to be in some order, one, two, three, four. Somehow that order is fixed. It should not change. And then you can create a bit which is one if the instructor has computer science, zero otherwise. This is the computer science bitmap. There's another bitmap for electrical, another for civil and so forth. Now what is the use of these bitmaps? I can do various intersection and union operations very, very efficiently with it. I won't get into the details. So, it's used for certain OLAP kind of operations, data analysis operations. It's not very useful for finding a record with a particular roll number. That's not what a bitmap is for. It's used for other operations. Then there are hash indices, which are used a lot in memory during query processing. They were used at one point for disk indices, but people have given up on it. They don't give any great benefits over B plus trees, but they have a big drawback of not allowing range queries. So, they have been more or less abandoned. And then there are R trees, which are used to index geographical data, multi-dimensional data. So, B plus tree assumes a sort order. But how do you sort? Let's say locations in a map. You can't sort on X or Y. Which one do you choose? Latitude or longitude. So, it's not clear. So, for such two-dimensional data, R trees and there are many other indices, but R trees are widely used in databases. So, PostgreSQL supports R trees for example. Most databases today support R trees for such spatial data. So, that's it for indices. Any last questions on indices before we move on? Oh, I should also mention one other point here, which is when we do query plans, you will, on PostgreSQL, you will see something called a bitmap index scan. I think bitmap, I forget the exact name that is there. Now, I just told you about bitmap indices. Now, PostgreSQL does not actually have bitmap indices, but it has something else which is not really an index. It's a kind of data structure computed on the fly, which it calls bitmap index. I'll come to that later when we work on query plans in PostgreSQL. But I just want to mention that now because PostgreSQL, which we will look at in the lab, uses this term bitmap index scan for a different thing. It's not the regular bitmap index. It's related, but it's a different beast. So, don't get confused when you see it. In the book, there is a section on bitmap indices that is completely different from the PostgreSQL's use of bitmap index scan. It's a different term.


Greetings learning tons welcome to the Q&A. I will get straight into it. We have some nice questions here Not as many as last time because I am the more questions the less Time I can spend on each question. So this will be a bit shorter, but hopefully a Great video nonetheless So we begin Hello Marcus. Will you write another politically oriented book in the future? I've purchased both runs of Dauntless and find your writing style pleasant to read So if you're planning on releasing another book in the future, it would be a great addition to the world's literature The same goes for Julia's cookbook I'm having ordered it for my wife and want to support physical aspects of the culture as much as possible However, even if the book is not politically nature. I'm sure it will be highly glorious So, yes, I actually thought about making an announcement video, but I can announce it now and I can announce it as we Progress further along the way. I am currently writing a second book which will be More about the mind so Dauntless is very well first and foremost I forgot to thank you very much for the the kind words and your support Truly appreciated and my wife appreciates it very much as well. So thank you to everyone who's ordered Dauntless and Old World Feast now back to the Book announcement, which isn't real an announcement. I'm just saying what I'm doing now Dauntless is a bit more the practical aspects you can you can do in your life The coming book will be a bit more about the mind how you can think about yourself how others try to influence you how you're influenced by culture how the great enemy tries to subvert your mind via different Magic spells magic propaganda mental techniques whatever we shall call it I like to use the term magic because it encompasses quite a lot of things, but if you don't want to use the term magic you can say Mental warfare or propaganda or whatever it might be. So basically my next book will be more on The mental aspect how you can think about yourself to achieve certain results how you can view culture to see How they are trying to influence you what sort of culture you can expose yourself to in order to gain certain results. So yeah, I will I will elaborate more on it later on But that's the short answer more books are coming I am Books maxing like crazy now, and I am I'm author maxing as well. Good times. Good times second question What are your favorite video games of all time and why I will make a separate video on this? But I would say the greatest game of all time By far actually, I don't really see any competitor to it is the Witcher 3 Again, I only make a separate video where I talk about the books and the game because I've read all of the books The game just great in every single way. I can't recommend it highly enough even if you aren't a Gamer even if you aren't familiar with the computer games or video games. I would still recommend it just because of the The stories, you know, you have side quests in the game that You know can can hit your heart even because they're quite Yeah, quite deep actually there is one mission concerning a Baron and his wife, which Yeah, it hit me at least I will talk more about it at later time Then of course you have the aesthetics. You have the music you yeah, it feels like you're going into a mythological Europe of the late Middle Ages Absolutely great. I again, I can't recommend it enough then of course age vampires, too I think most of us many of us we've grown up with it in my last video I mentioned it as well since I had a vision from there from those campaigns So it's perhaps not the greatest game of all time, but it's the game that has Had the biggest impact I would say on a personal note age of mythology also had a great impact on me and Same thing there is the feel of it really really great stuff Third question thoughts on everything going on in Canada right now. I Haven't really delved all the deep into it But of course I am on the side of the of the people there and this is what happens if our regime is kicking on a people for long enough and you know subjecting them to various humiliating measures Yeah, people will rise up against it just as the truckers have done and now and I completely understand it I completely understand that people are getting Angry when they are being treated in this way. So I know the regime They are always talking about democracy here and democracy there, but then they see democracy in action and say That it's something else But in my view, this is the people making a stand against the regime and of course, I'm no fan of Justin Trudeau I think he is a bit of a vicious man Can't say too much, but yeah, I'm I'm no fan of him in the list so he could Resign and I would be happier for it But again, he would probably just be replaced by another puppet So I'm not sure how much difference it would make but yeah among the side of the the people of Canada against the regime Of course now we have some spicier questions that I will respond to in the podcast. So So yeah, I will respond to some questions there But we have a few more questions that I will get into Greetings Marcus. I have enjoyed your content very much and of course your glorious garments of legio gloria In fact, I've gotten several younger ladding tones I am the same age as you a fellow Leo good stuff good stuff to get into your content That is highly appreciated. Thank you very much for that and continuing I have a few questions. What are your latest projects that you are working on and what can we expect next? Yes, as I began I am currently writing a new book. Perhaps it will even be out later this year if I Managed to work hard enough But yeah, I will keep you all updated on it then in terms of clothing It's full steam ahead a massive linen release coming out in the spring I have fallen in love with linen such a great material I've also fallen in love with wool some more wool garments are coming out in the autumn and then some other garments as well loose fitting underwear in organic cotton So I wanted it for myself and I thought all of my ladding tones need that as well So that is incoming the second thing have a few more supplements coming in for the American Jew to name page so collagen and sink and also coffee for good old America I am currently in the process of testing some coffee from a roastry in In the US something is gonna see if it's up to par so my valiant Ladding tones in the US are well supplied with high quality coffee So yeah good times many things in coming and I have some more cool artwork that I want to share the final question we have Is the following? Have you read the Oreo Linda manuscript? If so, what do you think of it? I have not read it myself however when there are Things that are up for discussion or interpretation. I always simply ask I Send my man Tom Rausel survived a jive. I send him a message. I ask what do you think of this? And then he either says this is not Reasonable at all or he says yeah, there is some truth to it in this particular case. He says it's not Legit and I trust him. I do know that another content creator, which I very much appreciate Asha logos shout out. He makes great videos. He has made videos on it Which I think yeah, they're very compelling. He makes compelling arguments for them But I will go with Survival jives take on it in this particular instance But I do recommend you to check out Asha logos all of his videos. They are really well made and he doesn't say For certain that anything is in a certain way, but he presents certain things that you can Contemplate yourself. So yeah, I would recommend his his videos now to all of my supporters Thank you very much and thank you to everyone for watching the video xx so boom