Đang chuẩn bị liên kết để tải về tài liệu:
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 pe.id_town_work wo.id 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 st.name from states st towns ho people pe whe r e ho.id state st. id and pe.id town home ho.id 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