In-depth understanding of Oracle table (2): What about driving tables

An in-depth understanding of Oracle table (2): An example of what drives a table is made by comparing two dictionaries: a dictionary with an index directory (dict a) and a dictionary with no (dict B) how do you compare the similarities and differences between all the words starting with? It is reasonable to find the page starting with a non-indexed dictionary dict B, and then find the corresponding entries in dict a for each word by using the index, will the efficiency be the same? The www.2cto.com driver table is generally used to retrieve from which table. a good driver table is half the result of successful optimization, for example: select * from a, B where. id = B. id and. name = 'magloan' and B. gender = 'female '; when tables a and B are of the same magnitude, obviously, it is better to use Table a as the driving table because the name can filter out more data than the gender, so we can try to make your execution plan scan table a first and then use nest loop and table B. the connection is ideal. Generally, when there are two tables, select a small table. When there are three or more tables, select a table with more association as the driving table. However, there is no absolute problem. Let's look at the case first: there are two tables T1 and T2. Assume that T1 stores the basic information such as name and ID. Each row is short, while T2 also contains the ID column and some descriptions and remarks class information, each President may have 1000 or 2000 bytes. For these two tables, nested loop connections are used. Table T1 occupies a small number of blocks, which is several times the number of blocks in Table T2 compared to table T1, is a big table. In this case, T1 should be used as the driver table, but in fact it is better to use the T2 big table as the driver table www.2cto.com. I do not know DBA or development, but I do not know this. In order to force the small table T1 as the driver table, the following statements are written: select/* + ordered use_nl (t1, t2) */from t2, t1 where t1.id = t2.id. Later, this statement causes some performance problems. By understanding how nested loops work, we can find that there are indeed large tables as driving tables. Here we illustrate a problem. We all say that nested loops require small tables as driving tables. If you don't know why, this can easily cause others to know their tables like this, but without knowing them, it is easy to affect the decision-driven table (driving table/outer table) that will be made on some issues in the future, also known as the outer table, the driver table is only used for nested_loop join and hash join. The driver table is used to drive the query in CBO. The optimizer automatically selects the driver table based on the cost, which is irrelevant to the table sequence, the driver table has a high selectivity (the ratio of the unique key of the column to the column is relatively high). tables with a large number of restrictions in where are suitable for driving tables with a small number of returned rows. In fact, a large table may also be used as a drive table. As long as there are many restrictions on a large table and a small number of rows are returned from the large table, a large table is also suitable for driving tables. All you need to remember is the returned result set (the data after a table is filtered, or the data generated by some table joins) small tables are suitable for driving tables. That is to say, which If the number of returned data items is small, the driver table should be created. That is, the connection operation should be performed from the few rows that are returned. When we look at the execution plan, we should leave the nested loops, the tables close to hash join are driving tables. That is to say, the tables on which the execution plan depends are driving tables. The following are some experiences in quickly determining the driving tables, which may be wrong: (1) the column of the clause used for join should be indexed, and the index should be used as much as possible in the Where clause, instead of avoiding the index (2) the join operation should be driven by a small number of rows returned (3) if the length of the connected tables A and B is much greater than that of Table B, we recommend that you drive from A large table A (4) if the Where clause contains selective conditions, where No = 20: Put the most selective part at the end of the expression (5). If only one table has an index and the other table has No index, the non-index table is usually used as the driving table. If the No column of Table A is indexed and the No column of Table B is not indexed, table B should be used as the driving table, table A, as the so-called driving table www.2cto.com under the drive table RULE, takes nested loop as an example. If neither of the two table connection fields has an index (generally sort merge/hash join ), the driver table selects the latter. If one of the two tables has an index and the other has no index, the driver table does not have an index. It is irrelevant to the order. If both tables have an index, the driver table is the following table. In fact, under RULE, the order problem must be considered only when both tables have indexed fields. That is, the small table is placed before the large table. (Of course, which one is better? This is actually consistent with the number of qualified records., data distribution, and other factors !, Therefore, the actual test should prevail.) in CBO, the key to understanding the execution plan is irrelevant to the order of www.2cto.com, instead of remembering the rules. For example, table join returns a record with two tables, one with 10 records and one with 10 million records. If both tables have joined field indexes, the cost is: 10 * (the cost of querying a record in a large table using an index): 10 million * (the cost of querying a record in a small table using an index) obtain a record through an index. For a 10 rows table, the cost is usually 3 blocks, two indexes are required, and one table is required. For a 10 million table, four indexes may be obtained and one table can be created, speed · all you know when you break your toes