In the table 1, no any index: a full table scan, read all of the metadata in the table into the cache, and line by line filter can meet the requirements of Sex and Age of the Student, in line with the record Name value, in charge of the next line
2, the Sex as the index, the rest is not: first of all, according to the Sex index scans the RowID access to meet the conditions, then according to the RowID read all eligible Student metadata into the cache and screening by Age greater than 12 Student, in line with the record Name value, in charge of the next line
3, the Name, Sex, Age have indexes: the index of directly read, don't need to read the Student table metadata
Welcome to the great god give directions!!!!!!!!!!!!!! Above is self-understanding, there must be many mistake understanding, especially when reading metadata to the cache, to find a lot of stick and video, have found no question on the details while reading the cache, so only can write their own, still hope people, and if there is a similar post, please send a link, thanks a lot
CodePudding user response:
Metadata is metadata, data is the data, metadata is the data that you said?1, the general steps about, but not necessarily through memory, more can't one-time into memory;
2, if the index in the gender on this field, unless the where condition of other conditions, and multiple index through a variety of ways to use at the same time, the single scan index achieve the rowid return table access efficiency than table scan;
1, article 3 say directly read index, mean index covered the SQL? That also is a composite index, index of btree can't provide you with the function of the third, but use sex + there is some possibility of two indexes on the age, is a situation in which I said above, you can see in the execution plan BITMAP CONVERSION FROM ROWIDS such operation, can be understood as simply, oracle will btree index into the BITMAP indexes, through the operation of the two bitmaps (and) is your in chestnut, and then obtain the rowid FROM the combined BITMAP, further in to get the data FROM the table,