tailieunhanh - Oracle SQL Internals Handbook phần 8

Tham khảo tài liệu 'oracle sql internals handbook phần 8', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | index on column id__town_work is sub-optimal and should be replaced by a bitmap join index that allows a query to jump straight into the people table bypassing the towns table completely. Figure 3 shows how I would define this index. create bitmap index pe_work_ldx on people wo. name f r om towns wo people pe where Figure 3 Creating a basic bitmap join index. Imagine that I have also noticed that queries about where people live are always based on the name of the state they live in and not on the name of the town they live in. So the bitmap index on column idtownhome is even less appropriate and could be replaced by a bitmap join index that allows a query to jump straight into the people table bypassing both the states the towns tables completely. Figure 4 gives the definition for this index create bitmap index pe_home_st_idx on people from states st towns ho people pe whe r e state st. id and town home Figure 4 Creating a more subtle bitmap join index. 124 Oracle SQL Internals Handbook You will probably find that the index pe_work_id is the same size as the index it has replaced but there is a chance that the PE_home_st_idx will be significantly smaller than the original PE_home_idx. However the amount of space saved is extremely dependent on the data distribution and the typical number of in this case towns per state. In a test case with 4 000 000 rows 500 towns and 17 states with maximum scattering of data the PE_home_st_idx index dropped from 12MB to 9MB so the saving was not tremendous. On the other hand when I rigged the distribution of data to emulate the scenario of loading data one state at a time the index sizes were 8MB and 700K respectively. These tests however revealed an important issue. Even in the more dramatic space-saving case the time to create the bitmap join index was much greater than the time taken to create the simple bitmap index. The index creation statements took 12 minutes 24 seconds in one