Good day.
There is the following query:

select B.f_base_id from base B where B.f_groupbase_id = 123

Plan:

 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 81 | 648 | 2 (0)| 00:00:01 | |* 1 | VIEW | index$_join$_001 | 81 | 648 | 2 (0)| 00:00:01 | |* 2 | HASH JOIN | | | | | | |* 3 | INDEX RANGE SCAN | FK_R_GROUPBASE_BASE | 81 | 648 | 1 (0)| 00:00:01 | | 4 | INDEX FAST FULL SCAN| PK_BASE | 81 | 648 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("B"."F_GROUPBASE_ID"=123) 2 - access(ROWID=ROWID) 3 - access("B"."F_GROUPBASE_ID"=123)


Interested in the question of exactly how the selection of rows? Do I understand correctly that Oracle first in the FK_R_GROUPBASE_BASE index searches for ROWID records with a value of 123, after which the PK_BASE index is completely scanned (without reference to the table itself) and then through HASH JOIN the rows are joined, where the ROWID in the FK_R_GROUPBASE_BASE index equals the ROWID in PK_BASE? I can’t quite understand why an operation is needed.
1 - filter("B"."F_GROUPBASE_ID"=123) .

Also, I don’t understand why Orakl chose this particular plan, and not the scan (INDEX RANGE SCAN) of the FK_R_GROUPBASE_BASE index, and then TABLE ACCESS BY INDEX ROWID. How can a plan be better?

thank

Raised to the main page by a community spirit member ♦ 18 hours ago .

This question contains answers that can be both good and bad; the system offered them for verification.

  • What is base is a table or view. If view it is necessary to see the request which is inside this view. In addition, you need to know exactly how all the tables are created, is it particularly interesting with the non-IOT (index organized table) case? - Mike
  • Base is a regular table, not a view or an IOT. - Alexandra
  • What is the average record length in the table, how many records and how many extents and space does it take, what pctfree? Apparently orakl decided that he would quickly raise the entire index and find records by rowid in it by brute force to get the only selectable column that is present in this index than to crawl 81 times at the exact address in the data area. Apparently, he believes that he will need less I / O to read the index. - Mike
  • To answer the question "почему Оракл выбрал именно этот план" you need to know a lot about your data and indexes. For example: (answers to @Mike questions) + data skewness , index clustering factor , characteristics of indices for columns: f_base_id, f_groupbase_id and all indexes in which these columns participate - MaxU
  • Mike, MaxU, thanks, but I thought there was a more obvious answer. Now so far I am not ready to "dig" so deeply) - Alexandra

1 answer 1

Oracle will do the following (if I understand the execution plan correctly) in chronological order:

  • full scan of the index PK_BASE
  • range scan FK_R_GROUPBASE_BASE , and only those elements are read, cat. satisfy the condition ( "B"."F_GROUPBASE_ID"=123 ), i.e. superfluous / unnecessary blocks will not be read
  • hash join PK_BASE and read elements FK_R_GROUPBASE_BASE
  • Apply the filter "B"."F_GROUPBASE_ID"=123 to the data already read

Total all data is selected from two indexes, the table will not be read at all.

why orakl chose this plan

To answer this question you need to know all the data on the basis of a cat. CBO decides ...

  • those. when range scan, blocks are read that have 123 values, but there may be other entries, so you need an additional filter ("B". "F_GROUPBASE_ID" = 123) to cut them off? - Alexandra
  • @Alexandra, basically filter("B"."F_GROUPBASE_ID"=123) helps not to read those blocks in the cat at all. there are no values "B"."F_GROUPBASE_ID" = 123 - MaxU